zooguy1492 wrote:I am sorry I've hit a nerve...
I know in the past, you attempted to explain to me the difference between some of these fields. To be honest, I'm still not clear. It would probably make sense if I was the programmer that implemented it or was familar with the history of the system but from the end user's point of view, I'm not understanding. I am EXTREMELY familar with SQL and relational databases. I've also been a software programmer for 30 years. In general, all I really want to do is relate two database and I'm being given too many choices. As you see by my other posts, I picked a "relationship-unique" fields, which I'm now being told was incorrect. The Windows Desktop Version 4 manual (dated 12/5/2007) doesn't shed much light on the intriquicies of these fields.
First off, my apologies if it sounds like I was upset or frustrated in my last post. Was not intentional! I would agree that the concepts are confusing at first, but really most users seem to master them once they understand. There are several ways to relate data on HanDBase. Let's go over them here:
DB Popup: the simplest to understand, the field type is like a text field but it's popup list can come from another table. Many would argue this is not a truly relational field as the data is copied from the other database into this text field, but nonetheless, it still lets you relate data in one table to another. I assume this field type is not an issue and won't mention it any further!
Link and Linked: If you are familiar with other databases and SQL this field type may seem quite foreign to you. Here's the concept- the idea was to relate a parent table (containing a link) to a child table (containing the linked field) without forcing the end-user to create a unique value in either table. The link and linked field when set up properly take care of all of this for you, and because the relationship type is one to many, much of the program knows what to do with it. In addition, since HanDBase is a mobile application, there is a good chance that there are more than one user or device creating records, and the link and linked fields can handle this automatically as well.
I would hope that the tutorials for link and linked can explain how to set this field up properly, but the basic idea is that the parent needs a link field that points to the linked field in the child table. The child table needs it's linked field to point to the parent table.
There is only one downside to the link/linked field- interfacing with other database programs. While an export to another program like Access, SQL Server, etc is not a problem- the field types can be set up using the relationship designers of those programs, creating new records in a program that doesn't know how to create these values is a major issue. This is because in order to be unique among many devices and users the uniquely identified value must contain several facets to guarantee it's uniqueness. A simple number alone won't do- it would have to be many, many digits long to be represented this way!
So that said, if you are working with an outside program (ie, something that is not HanDBase) or importing existing data that already has some type of relationship defined, the Link and Linked field types are not the ones for you!
Relationship field type: This was designed in HanDBase 3.0 because of the above limitation and the increasing use by customers of 3rd party database programs like Microsoft Access where they had related tables already configured.
The concept here is that the database field type itself does not store any data, it's actually a connection whereby you specify two related fields. These related fields are a field in each database table that has a value matching the other, so that the program knows that the two records are related when their respective fields have a match. In this case, defining the values is not automatic- it's up to the end user to come up with a unique key value for the records and it would be most common to use something like a customer ID, patient ID, or something that you know is unique for each entity.
That should offer the most basic of explanations between the field types. My understanding of what you have been wanting to do was use the relationship field type because you had to export/import from some other database application. If you didn't, the link/linked field is definitely the best choice! If you do, then most typically you'd select a text field to store your key value in each database, and relate the two via the relationship field.
It seemed that you wanted to point the relationship field to a unique value so that the end-user would not have to come up with a value that is unique on their own. I and others have explained that the unique value is not globally unique- it's really more of a counter field and since there are potentially multiple copies of this (eg, your desktop, your handheld, and perhaps other desktops and handhelds, the chance of this field being and staying unique for each record is actually very small). If you need to do this, we suggested one of two options:
Using the 3rd party plugin called UserID from UnpluggedIdeas- this plugin lets you have a text field filled with a globally unique value and solves the problem of auto generating something unique.
An alternate workaround was to use the link fields generator to somehow set a value in the field that can be used as a globally unique identifier. This is I believe where you have been struggling for a few weeks now.
I'd like to be able to help you further with this, but really think that this is more of a personal technical support issue than it is a public forum topic at this point. In other words, if I could see your databases, get an idea of exactly what you're hoping to do, I can probably point you in the right direction with specific instructions to you!
Simple question.. What do you mean by "outside database managers"? I just want to sync one handheld with one desktop...
Hopefully the above clarifies this for you. If it's as you describe here, the link/linked field type are the perfect solution here.
This link (
http://www.ddhsoftware.com/knowledgebase.html?read=195&UID=) describes supposedly describes the relationship field vs link/linked fields but most of it simply describes how to setup the database rather than the specific advantage and disadvantage of each (in addition to it being dated 2002 and describing HanDBase 3.0):
The information may be dated from 2002, but it's still accurate for the current HanDBase program as these field types have not changed since HanDBase 3.0! You mentioned it does not list the advantages and disadvantages, but there is a chart doing just that a few paragraphs down!
I'm seeing this as well and thanks for letting me know. I hope we'll have this link fixed tomorrow- sorry about that!
Also, I think what you've told me in the past is that I shouldn't use a "relationship-unique" relationship when you could modify the database on the handheld and desktop (see another thread about unique fields). Instead you say I should use "relationship-(link/conditional) trick to make sure I get a true unique field.
That was if you wanted to also sync to something like Access, SQL Server, etc. If not, then the link and linked fields do this for you!
dhaupert wrote:Regarding the unique field, this too is still useful- for example, I use it often to resolve ties in sorting, as you've likely seen. When you are for example sorting by a value or two where there is a chance for ties, and to keep the sort order stable in this instance, you can add the unique field value as a tie-breaker. It's also quite useful in cases where you are entering data on one device only!
By "one device" you mean only one desktop OR one handheld and not both. Correct?
Correct!
Please let me know if this answers or doesn't answer any further questions you may have. And feel free to contact me directly at my email address listed below!