Portal Field Control - ID to Text

[Jasmine]

Hello all,

I'm new to FMP, but not to db programming, and am having a hard time doing something that's pretty easy in other programs. I've read the "User's Guide", but that's decidedly elementary; I've searched, but I guess I need to get the hang of the jargon.

It's an artwork database, in which depicted characters depicted could conceivably show up in hundreds of artworks. Because of this, I put those names in one table, and created another table for the artwork/character relationships. The db schematic for the relevant tables & fields:

Artwork

- ID (auto-serial)

Global Characters

- ID (auto-serial)

- Name (text)

Record Characters

- ID (number, to be filled with Artwork ID)

- Character ID (number, to be filled with Global Character ID)

I need a portal on the Artwork layout that will enable the association one or more characters to the artwork. The portal should have a dropdown menu so the user can select the name of the character (from the Global Characters table) while saving the ID of the character along with the Artwork ID in the Record Characters table.

When browsing the Artwork records, it should retrieve the characters from the Record Character table based on the Artwork ID and show the name of the character -- not the ID.

Am I approaching this all wrong from the FMP standpoint? Any help or suggestions you might be able to offer will be greatly appreciated.

Thank you,

Jasmine

[Ocean West]

Welcome Jasmine...

Thank you for your detailed and thought out description. :

This is a classic many-to-many join relationship with a "join" table.

Here is a sample attached.

artwork_join.fp7.zip

[Jasmine]

It's a perfect, elegant solution. Thank you!

PS: That was FAST! Grazie!

[Fenton]

That is pretty much exactly how you'd do it. As far as the "choose the name, but enter the ID, then only show the name", that can be done using a Value List. For the 1st field, specify the ID field of the Characters table. For the 2nd field, specify the Name field of that table. Down at the lower left is an option to "Show only values from the 2nd field."

On the layout format the field to use this as the value list. There are 2 choices of format that would work. The pop-up menu choice would show only the name when it popped up, then show only the name after you chose it. The drop-down list would also show only the name when it dropped down, but would show the ID after you chose it. This can be fairly easily "covered" by an opaque field of the (then) related name from the Character table. So either way works. The drop-down has a weakness that they could type something else in the field. Though that also could be prevented (after the fact) by a Validation of the field, to "Only allow values from a list."

So a pop-up is a little less trouble, but some prefer drop-downs, especially on Macs, where pop-up menus, for some OS reason (intrinsic or constrained) will not drop down like other fields in the Tab Order.

[Jasmine]

Thank you! I've tried the Values list, and it showed the text in the dropdown, but it always saved it as text (not the id) in the Record Characters table, though I had column 1 set with the ID and column 2 with the text. I must have missed a step somewhere.

I'm done for the night, and will get back to business in the am.

Thanks again!

Jasmine