but when selecting from the dropdown one foreign id always shows at the top.
Can you describe that in more detail? I don't see why that would be the case.
Another option that can sometimes be used is to invert the whole setup and use the name field to look up the ID. This enables you to use auto-complete on the name and can gracefully handle situations where you have more than one record with exactly the same name--something that will result in an omitted value in your list with the standard ID in field 1, name in field 2 value list.
FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7
Attached is an image to show what I mean. This is the People table which is a child of the Company table, related by primary and foreign key (company::id -< people::id_company). The dropdown/popup value list uses values from "first field" (id) but is set to only show values from "second field" (company name). The dropdown menu DOES show only the company name, except in the field itself, which shows the id.
In the meantime, I'll take a look at the demos you linked to.
OK, hadn't noticed that little wrinkle before that a previously entered ID shows in the field.
I suppose you can use OnObjectEnter to perform a script that clears the field when you enter it, but then you have an issue when the user clicks in the field and then decides not to change the current value... That could also be handled with scripting, but I'm not sure that it's worth the effort.
Yeah, I might just live with it. But setting a variable, clearing the field, and then resetting the field if empty seems like a viable solution if it remains too bothersome to see the id, especially becuase I'm now using UUIDs which are uglier than the 2-digit number in my example. I also downloaded your demo file, and will be looking at that soon.
Let's say your field is called Invoices::ContactID. Set up an OnObjectEnter script trigger and enter Invoices::ContactID into the optional script parameter box. Then your script has these steps:
Set Variable [$$PrevValue ; value: Get ( ScriptParameter ) ]
Set Field [Invoices::ContactID ; "" ]
That saves the original value in a global field before clearing the field.
Then use this script with the OnObjectSave trigger:
If [ IsEmpty ( Invoices::ContactID ) ]
Set Field [ Invoices::ContactID ; $$PrevValue ]
I was playing with this very idea yesterday. The only problem is you are unable to CLEAR the company name field. I know could create a button whose sole purpose was to clear the field, but not sure if the clutter is worth it. Still thinking…
Perhaps it would be better not to use Get (UUID). The only reason to use it instead of an auto-entered serial number is when you are creating records in distributed copies of your database (such as several ipads/iphones) that are then merged back into a central file with the primary key values still unique.
Okay, this is very close to being elegant…
For state "In Focus", make:
font size = 1
tyle = condensed and subscript
alignment = bottom
text color = 76% gray
This only affects the field font itself (my UUID); the font for the dropdown is not affected. Only weirdness is just the bottom strip of the field is highlighted, and there's a small black dot in the lower left of the field. I'll probably keep experimenting.
We seem to have posted almost at the same time :) I decided to use UUID because I don't know if we will be using the database more remotely in the future; figured I'd rather start now than find I have to redo all the ID's in the future.
Thanks as usual for all your help in this forum!
Hmmm, maybe you can add an extra field in here that's empty instead of the ID match field and format it with the value list. Use the OnObjectSave trigger to set the Match field to the value entered into this field, then clear that extra field...
That's a good idea. I'll try it.