Relationships via ID# rather than by name

[iNik]

In my SQL (mis)adventures, I've been very careful to only query/relate tables by integer primary/foreign key relationships.

In Filemaker, I find that I'm forced, as often as not, to use relationships based on often lengthy text fields (like category names and so forth) in order for value lists to function properly.

Is Filemaker somehow creating PK/FK relationships in the background to speed up these operations, or am I heading for a world of hurt when my solutions grow beyond their current (small) size? If the latter, how can I avoid this kind of problem and still benefit from value lists and other niceties which make FMP such a wonderful rapid development environment?

[Ender]

Hi Nik,

I do my best to use the unique identifiers for the primary relationships in my solutions. This is a good practice because names change all the time, and it's a whole lot easier to change the name in one place, than to have to change it all over the place.

For secondary relationships (like those used to make the interface work,) it is often necessary to use relationships based on other things. But the data is not dependant on these.

[iNik]

Is a Lookup auto-enter a reliable way to set foreign keys on related tables? I tend to mistrust auto-enter due to some bugs waaaaaay back when I started with FMP (or alternately, due to mis-coding on my part, I really can't say which) and rely on calculation fields instead (slow, unstored, ugh!).

Based on some basic testing (see attached -- I also used the GUID/UUID system by Cap'n Kurt on this one, just to try it out), it appears as though a lookup auto-enter should do quite nicely to set up the necessary PK<-->FK relationships. But is this robust enough to survive file importing/synchronization and other troublesome tasks?

PK_Test.fp7.zip

[Brian C]

I find that in the long run, having things driven by ID number instead of text descriptions is a better choice. Have the value list be based on the ID for the primary field, and the description for the 2nd field.

A user would see:

1 Home

2 Work

Selecting "Home" would input a "1" into the field.

You could then use a 2nd field on the layout to display the related text description of "Home"

In the end this helps to save on disk space and if the description changes, nothing breaks.

The only thing I will caution you on in regards to using the "Lookups" feature is to be very careful of using the "Relookup Field Contents" command in the Records menu. Also a similar feature is available to you when importing records as well which can possibly be even more potentially devastating since it will be applied to everything in the table that relies on using lookups...

Example:

Selecting an ID for a particular record causes and autoenter to occur on all of the necessary fields so that fields are populated with keys and tag data as well as default information. This is great until the user changes anything from it's default "lookup" value to something else - at which point it needs to stay that way. If at this time you forget that a particularly useful field is connected in this manner and issue a relookup, presto, all the changes that a user has made is now replaced with the default result again...

Fields that are this important, if you want to be very cautious should not have thier content populated by a lookup, OR you just need to be careful to avoid the relookup unless you are absolutely sure it will not break anything.

[PeterHW]

When I was playing with Servoy a while back, one of the nice things was that you were able to display a total of 3 fields in a value list, e.g.

{ID} {FIRSTNAME} {LASTNAME}

but that you were able to specify which of the values should be actually returned to the field. IIRC you could even return a value not even displayed in the value list. I would love it if Filemaker would make this possible.

Regards,

Peter

[iNik]

The only thing I will caution you on in regards to using the "Lookups" feature is to be very careful of using the "Relookup Field Contents" command in the Records menu. Also a similar feature is available to you when importing records as well which can possibly be even more potentially devastating since it will be applied to everything in the table that relies on using lookups...

Does this only apply to auto-enter looked up data, or do the same problems occur with a lookup calculation?