Need help w/referncing a Hierarchy

[m3gan0]

My problem is this: I want to be able to search for a record and have the other related fields auto fill in based upon the record searched for.

I have a database of taxonomic names that goes in this order (commas represent new fields):

[color:blue]ORDER, SUBORDER, INFRAORDER, SUPERFAMILY, FAMILY, SUBFAMILY, TRIBE, SUBTRIBE

A typical entry looks like this:

[color:blue]Coleoptera, Polyphaga, Cucujiformia, Chrysomeloidea, Cerambycidae, Cerambycinae, Trachyderini,Trachyderina

I want to be able to enter "Cerambycidae" for FAMILY and have the other fields that come before it fill in (order, suborder, infraorder, and superfamily).

There are thousands of entries, each with 1 to a maximum of 8 filled fields. I originally generated the data in excel, which is why the data is laid out the way it is. Is there an easy way to do this or does each category need to refernce each other?

Thank you,

M.

PS. I'm pretty new to FileMaker in case you couldn't tell.

Edited January 5, 2009 by Guest

[comment]

Is this a rigid hierarchy - i.e. do ALL records follow the same cascading parent-child pattern?

[Ocean West]

Here are a few techniques:

http://www.filemakermagazine.com/videos/infinite-assignments-never-ending-value-lists.html

http://www.filemakermagazine.com/videos/infinite-hierarchies-creating-a-folder-tree.html

[m3gan0]

Is this a rigid hierarchy - i.e. do ALL records follow the same cascading parent-child pattern?

Yes and no. Sometimes the heiarcy skips steps. Things like "superfamily" and "subfamily" for example are optional and depend on the group.

The system I listed above is listed in order of rank - everything is with in an [color:blue]Order but not everything is within a [color:blue]Superfamily, sometimes it skips strait to [color:blue]Family.

Here are a few techniques:

http://www.filemakermagazine.com/videos/infinite-assignments-never-ending-value-lists.html

http://www.filemakermagazine.com/videos/infinite-hierarchies-creating-a-folder-tree.html

Thanks for the links - it looks like they would be wonderful guides, too bad I don't have a subscription and the demo video cuts out right as it starts getting interesting!

I did find a similar article in Filemaker Advisor (which my dept. does subscribe to) Here.

Hopefully it will be of the same quality as the ones you suggested.

Edited January 6, 2009 by Guest

[comment]

It's a tough call. Basically, to do this correctly, you have two options: either create a table for each level and link them according to their rank order, with additional relationships for exceptions, or put all the classifications in a single table with a parent/child self-join (recursive hierarchy).

Another possibility is to forgo data normalization, and just import your data as is. Then define a relationship matching Family to Family and do a lookup. If the lookup fails, have a "backup" lookup into a higher level.

Not to discourage you, but none of these is exactly simple.

[m3gan0]

Not to discourage you, but none of these is exactly simple.

Sadly I have come to the same conclusion. I may have to leave this part of the database unfinished for now and go back later.

It seems like this is a fairly common question/problem, I'm surprised that a plug-in or other, more streamlined solution hasn't been implemented into the program.

[comment]

It's not a limitation of the program. It's just that databases are built of tables with columns and rows, and as such aren't easily adaptable to more "fluid" situations. Still, a recursive hierarchy is certainly possible, and could probably suit your needs - it's just not that easy to implement.

[m3gan0]

True, but it seems that building a self-referencing database is more complicated than it should be (keep in mind that i'm a database outsider for the most part). I guess I just wish there was a button that says "link" A to B or something similar.

Anyhow, thanks to everyone's help I have a rough draft of this working, I'll post a sample when it's a bit further along.

[comment]

Life in general is more complicated than it should be...

Seriously, Filemaker (like any other relational database) is primarily geared towards rigid data models, e.g. Clients -< Invoices -< LineItems >- Products. You cannot well except flexibility in a tool that's designed to handle millions of records.

Linking A to B *is* quite simple. It's linking A to B or - if there's no B - to C, that's problematic.