Setting Relationships, lookups etc.

[Charlie Butterworth]

I have used FileMaker for years now and have developed a complex relational database for storing data on my plant DNA collection which I use for research purposes.

So far, things have worked, but I want to clean-up the database and make it more streamlined. This has caused me to re-address a couple of relationship issues!

For example, I have a small table containing the plant name, each plant name has a unique numeric identifier - PlantID (automatically generated by Filemaker). This identifier is used to link to my DNA table, so that I can see layouts that include the plant name. The plant name is not stored in the DNA table only the PlantID is stored.

OK, so when I add a new DNA sample, I can use a drop-down list to show the full plant name, along with the PlantID. When I select the required plant name, the PlantID is stored.

However, I want to be able to use a drop-dwon list that does not show the PlantID, but that will store it in the DNA table, thereby preserving the relationship.

Any ideas?

Charlie

[comment]

This is one of the features in the new version 8.

You can implement a workaround - if you're 100% sure that plant names are unique. If so, you can select the plant name from a value list of names, and have the ID field lookup the ID from a relationship based on plant name alone.

[Charlie Butterworth]

Thank-you,

However, a relationships based on PlantName may not work, because plant names, although unique can change. What I would like is to be able to modify the plant name in the Plant Name table without changing the PlantID.

I remember working in Access that a value list could be set for a particular field / relationship, but display values from another field without showing the actual linking field.

[comment]

You can still change the plant name, because all your basic relationships, based on IDs, remain unchanged. The relationship based on name is used only for selection, and the correct ID is immediately looked up.

Another option is to use a script that opens a new window with a list view of plants and pauses. You click on the plant you want, which resumes the script. The script then takes the ID and stores it in the originating record.

As I said, the Access-like feature has been implemented in Filemaker 8 - not before.

[Charlie Butterworth]

Great.....but how do i do this?

Unfortunately, I do not have FM8. Don't tell me that I need to upgrade already?

[comment]

You only need to upgrade if you want to have this feature built-in in Filemaker.

For a DIY workaround that works in v.7 see the discussion and demo files in this thread.