A quick file to explain. On the Contacts page, just enter a committe for the person to join.
For simplicity, I just used the committee name as a key.
For simplicity I used plain lookups in the Membership table.
On the portal to the join table from the perspective of the Committee...
...place the fkContactID with a valuelist of the Contact ID is assigned to the field as a popup menu. This valuelist would be where the first value is the pkContactID and the second value is "Full Name" made by a calculation of First & " " Last.. choosing to only show the second value.
On top of that field place the Contact Full name field from the related contacts table. In the Behaviour section of the Inspector... make the field not enterable in Browse mode.
The effect of this will be that when you click on the top field... the popup from the bottom one displays allowing you to choose by name but have the actual id entered. When you commit that record... what displays is the full name.
The problem with using the committee name as a Key is that the name of that committee might change...
1 of 1 people found this helpful
"The problem with using the committee name as a Key is that the name of that committee might change...
If you re-read my post, I qualified (or at least tried to) that this was a simple example.
Sometimes folks ask a question because they are momentarily "stumped" on a concept.
They are asking for a nudge and eliminating all but the basics of the question can be more helpful than a disertation.
But you may be right and I should include a list of disclaimers regarding design theory / best practices... Thanks.
1 of 1 people found this helpful
The trick to picking the name to populate the fKey ID is to format the selection field as a popup-MENU using a value list which has been set to use 2 fields, the name and the ID, with only the name showing.
Picking the name IN THE FKEY FIELD (not the name field) will populate the ID/fKey but will display the name.
Not particularly intuitive, and you MUST use the popup-MENU option to enable thsi functionality, which is often less than optimal for viewing the list.
A work around many of us have used is to use to relationships and have the ID/fKey as a lookup when the name is selected from any other kind of list. Unfortunately, that necesitates entering redundant data to trigger the lookup.
- Stephen Huston
Many thanks for the suggestions. I've tested a version of Lyndsay's solution, which appears to work; and indeed I wouldn't have originally thought about stacking fields (a technique that I dimly remember from Visual FoxPro when doing reports... yikes!)
Anyway, the stacked fields appear to work, and I was able to use a dropdown list.
I'm wondering why you both mention the constraint of requiring a pop-up menu?
The dropdown list seems to work better; definitely from the user's perspective in that they are able to type in a letter or two and rapidly search the list (which in my case is extensive).
I'm using FMP Advanced V.11 for Windows if that makes any difference between the pop-up menus and the drop down list.
The whole business seems a bit of a kludge... especially thinking that it takes just a few clicks to have the same effect in Access.
Again, many thanks to all. I'll go with this for the moment and see how it works out with th users.
LOL... this has just been topical for me as I just fixed up a system someone else built where all the keys were based on data that could change. I did take on board your "For simplicity" intro... but wasn't sure if ikeyes had..
Yes... pop-up vs drop-down....
My preference is drop-down too... but with a pop-up they can't accidentally enter something else...
Just wanted to say that I wrote up this discussion with screen shots... located in a .PDF on this page.
I'd appreciate any feedback, on the writeup. --- L