Relationship Limitation Problem

[FUBAR]

I have a database that contains the fields 'Application' , 'Classification' and 'Equipment'.

Each Application contains many different Classifications, and each Classification contains many different Equipment.

Using self-relationships I could create a value list that when a certain Application was chosen, the list of Classifications inside the specified Application were displayed. I also created a value list that would display a list of available Equipment for a specified Classification the same way.

I use these lists in a "Detailed Search" where the user chooses the Application they want from a value list based on the field 'Application'. After the Application is chosen, the value list of available Classifications is shown and after the Classification is chosen, the value list of available Equipment is shown.

But here's the problem, I now have run into a case where the same Classification name is in more than one Application. Therefore when the Application is chosen, and the Classification (one that is contained in more than one Application) is chosen, there are too many Equipment choices being displayed.

To try to solve the problem, I created a Calculation field (Application/Classification) and then created a self-relationship with this field and I tried to set the value list of available Equipment to this relationship.

However, now no equipment shows up on the value list. I've read somewhere that FM only looks at about the first 20-30 (can't remember the exact number) of characters when establishing its relationships. I'm pretty sure that the reason why the value list is not working is because I'm over that character limit in my 'Application/Classification' field.

Am I correct on why my value list isnt working? And if I am, can anyone give me a suggestion on another way to create an Equipment list based on the selected Application/Classification?

Thanks in advance

[LiveOak]

Don't try and use a self-relationship. Create another file with three fields: Application, Classification, Equipment. Build value lists based upon relationships using this file. If you need a classification in more than one application, create more than one record:

App Class Equip

Dentistry Drilling Dental Drill

Woodworking Drilling Drill Press

-bd

[FUBAR]

I originally chose to use self-relationships because all of the records are being imported from existing excel spreadsheets. And to be honest, if I did split the database like that I have no idea how to run the searches and summaries that I am trying to do without using Look-Up fields which will create a lot of unnecessary file space because the database will be quite large (+100 000 records.)

The two records that you gave as examples are how I have the records in the database.

Also, even if I split the database into two seperate ones, I don't see that solving my original problem since I would still need an Application/Classification calculation field and then the relationship still wouldn't work if I am correct about the max number of characters in dealing with relationship fields.

Thanks for the effort and feel free to yell at me if I'm being stupid about the "staying with one database" issue.

[FUBAR]

Actually using your two example records maybe I can explain myself a bit more clearly...

App Class Equip

Dentistry Drilling Dental Drill

Woodworking Drilling Drill Press

Now I have 3 other global fields that I use in the self relationships, 'Wanted Application' , 'Wanted Classification' and 'Wanted Equipment'.

I turn Wanted Application into a list of all the Applications. When an Application is chosen (say Dentistry) I want a list of all the available Classifications to appear in Wanted Classification (so in this case only Drilling would show up.) However, without the Application/Classification field, when Drilling was selected from the Wanted Classification field, both 'Dental Drill' and 'Drill Press' will show up in the Wanted Equipment field whereas I only want 'Dental Drill' to appear.

Does this help clarify at all?

[LiveOak]

I don't see the problem. FM will index up to 20 characters per word and up to 60 total characters in a line of text with imbeded spaces. You can also reverse lookup numbers based upon App and Class and combine these to create a key into the file I proposed. The length allowed for an index shouldn't be a problem.

-bd

[FUBAR]

Woohoo!

Thanks for the indexing limitation tidbit, that solved my problem. See I knew there was a limitation on the number of characters in the field (which you mentioned was 60) but I didn't know that there was a limitation on the number of characters in a single word!

To try to avoid the character limitation in the field, my calculation field looked something like this...

Application & Classification

Which meant that I had 2 words looking like 1 word and that "1" word was over 20 characters. Put a space in between the words and everything works!

Many thanks LiveOak