Popup Menus & Relationships using ID's

[Quagmire]

Hello all,

This is my first post here, so I apologize if this has been covered before. Also, pardon my terminology, I haven't done active FMP development for a few years.

Being that I like to keep data links nice and arbitrary, I'm in the habit of using integer ID's (auto-generated serial numbers in FM) as primary and foreign keys. This is generally good DB design practice as far as I know, and I'm trying to do this in FM. However, this makes choosing a value from a related table (for example, Category) in a popup menu difficult. I want to insert the ID in the many table, not the name, but I want the name to be what the user sees and selects.

What I'm do now is have the value list be two values ("and also use value from...") where the ID is first and name is second, so they can at least see the name. For example, a menu might look like:

3 - Cats

2 - Dogs

4 - Hamsters

1 - Rabbits

Then when the user selects a value, it puts the number in the menu as the selected choice, with no name. So I make up for this by putting <<Category::Name>> in the layout to the right of the menu.

There's got to be a more elegant way to do this without reverting to using the name as a key. All I want is to have one thing be the visible list, and something else (which can easily be matched) be the actual value. This seems like something so common I wonder why it's not an included feature. This is a no-brainer for PHP/MySQL. Or maybe it is included in FM and I'm blissfully ignorant?

Thanks!

[Peter1]

If you want your field to display the CategoryID and the CategoryName, you could do it as follows:

In your Category table create a calculation: CategoryID & " - " & CategoryName

You already have CategoryID in the table where you want to input the related category. I would create 2 fields for this: CategoryID Display and CategoryID. CategoryID is a calculation: LeftWords ( CategoryID Display, 1 ). You can only do this if your CategoryID will always be a specific number of words.

See attached file. I have also include a 2nd option, where the user never chooses (or even sees) the ID. The only downside of this is if you change category name at a later date. The correct relationship is not corrupted, but the "hidden" selection stays as the old category name. As I said this does not effect the relationship.

A 3rd option, the best in my opinion, and widely used is to overlay the CategoryName in front of the CatgoryID being selected. The user cannot enter the CategoryName field.

Peter

Tester1.zip

[Quagmire]

Thanks Peter. I'll play around with it tonight.

Option 2 intrigues me, and to fix it I can probably script the category edit screen to update the "hidden" selection (not entirely sure how this works, I don't have FM7 here at work).

But 3 might be simpler and get the job done with no scripting.

Thanks for the help. I just wish this was a built-in feature to encourage the use of arbitrary keys.