"cascading" conditional value lists

[jbenner]

I'm getting a little confused trying to make value lists based on calculations from a prior choice.

Here's the deal: I'm trying to create a paleontological database. Organisms can be arranged within hierarchies (the Linnean system): Kingdom > Phylum > Class > Order > Family > Genus > Species.

Here's what I want to do: I want to create conditional value lists based on these hierarchies. For example, when I choose Kingdom value "Animalia", I want all of the Phyla within that kingdom to be available as a choice in the Phylum field. When I coose the appropriate Phylum, I want all the Classes within that Phylum available as values in the Class field, and so on down the line.

I have no problem hard-coding the value lists, but I can't tell if Filemaker has the ability to show a specific value list based on a script or calculation. For example: If Kingdom="A"; Go to field Phylum; use value list "A_phyla".

Are there any precedents for this operation that I could take a look at for assistance? Any ideas? Thanks in advance for help.

[Ender]

Welcome jbenner,

Sounds like a cool project. There are ways to do conditional value lists in FMP, but you need a relationship set up. Then in the value list definition, choose Use values from a field...Use related values.

One way to do this for your system is to set up a self-join relationship with each hierarchy field. When "Animalia" is chosen for Kingdom, a relationship is created between this record and all other "Anamalia" records in the database, then the value list for Phylum (showing related Kingdom records), will let you choose from the Phylums that exist for that Kingdom. If the value doesn't exist yet, you will need some way to add a new one.

[Ender]

Oh, I made an assumption about the Linnean system that I'm not sure about--I 'm assuming the names of the Phylums are unique, the names of Classes are unique, etc. If there are duplicates then there's another step to make this work.

[jbenner]

This kinda works so far, Ender! Here's what I did: made a separate file called "taxabase" that includes some sample data fields: Phylum and Kingdom. I made three sample phyla for each of two kingdoms. I made a self-join relationship Kingdom:Kingdom in that file. In my main database, I made a relationship between the field tax_Kingdom to the Kingdom field in "taxabase" (don't know if this step was necessary ?) and made it use a value list using all values from the field Kingdom in taxabase. Then I set the tax_Phylum field in my main database to use a value list using related values only from the Phylum field of "taxabase".

Next step is to see how it handles additions to the related file--I'll have to set the value list to accept "other entries"...?

Oh, and you were pretty much correct in assuming uniqueness of names...at least for our purposes.

Thanks,

Jake

[Ender]

Using related files would work, but I'm thinking the data entry needed to keep those files up to date would not be worth the trouble (if all you need it for is the value lists). With self-joins (one file) when there are new hierarchy parts entered, the value lists are updated automatically.

On the other hand, if you can make use of a Kingdom file relating to a Phylum file relating to a Class file, etc, then separate files may be the way to go, and the value lists can come from those. The value lists should be faster in this scheme because there are fewer records in each file than there are in the entire species file that you were entering into.

[jbenner]

You're right--the upkeep on a separate db would be a little too much. The single file system works and it's all we need.

Thanks for your help on this.

[sloiselle]

Hi,

I also need a subclass field related to a main class field (the values of the subclass depending on the value selected in the main class field)

Your example was perfect for my needs so I created a classification file and a main file just the way jbenner did. But there must be something I did wrong : in my main data base i get ALL the values in the subclass field (not only the one from the related main class).

I don't see where I can set to use a value list using related values ONLY from the SubClass field of the classification. Is it available in FM v4?

Thank you...