The simple solution--not necessarily the best, is to set up a "use values from field" value list that lists both the ID and the title as field 1 and field 2 from the Works table. This produces a value list that shows both the works title but enters the ID into the fk field in invoices when a value is selected from this list.
This works well for short lists of values. Selecting from longer lists of values can be cumbersome. For that reason, I set up a demo file of enhanced value selection techniques--one of which is an auto-complete enabled drop down list of names where selecting a name looks up the ID and enters it into the correct field.
Thank you for your response and for sending to your example files. They are really interesting.
I have found the solution you suggested above works to some extent, but has a problem. Once I have the value list with both the ID and the work name set up, I tick the box 'Show only the second value'. Then, on the layout, once a work is selected, the value appearing in the drop down box turns to the WorkID, not the work's name. So, for example, if I open the drop down box and select 'Mona Lisa', the box then displays the id '1', not the name of the work. This in theory works, since with the script: OnObjectSave: Set Field [InvoicesIN::_WorkIDfk; InvoicesIN::WorkRelated]. The value from the box (now an id) will be copied over as the Invoice WorkFK. But it is no use for users who are not familiar with the WorkIDs.
Furthermore, this solution is not desirable as it disallows the auto-complete option.
I was really interested by your "Auto Complete Value Lists II" example in the file you provided. It is an excellent solution and exactly the kind of thing I need to implement here.
I cannot get this to work, though. I have set up the relationships so that they mirror those in your example (see attached image), with a normal ID link between InvoiceIN "_WorkIDfk" and Works "_WorkIDpk", and a further link between InvoiceIN "WorkRelated" and Works "Title".
The part I do not understand from your script is that the script triggered from OnSaveObject does not actually perform the action of changing the fk ID of the 'MainAutoII' layout. You write in the notes above the script:
#If there is only one matching record with the specified name, this script does nothing. An auto-enter calculation copies over the ID number.
How does this auto-enter calculation function? It seems that this is the crux of the matter - making the work selected from this box change the foreign key, and yet I cannot find from your example exactly how this is achieved.
I am very grateful for your help so far, alone as I am in my office being driven mad by filemaker.
That's why you have two relationships. One matches by name and the other by ID. The auto-enter field option (either looked up value or calculations options can do this) copies over the ID of the first related record using the relationship that matches by name. If there is only one such matching record, there is nothing that the script needs to do. If there is no matching record for the name entered, the script asks the user if they want to add the name. If there are multiple records, the script pops up a list of the multiple matches with added info about each in the list so that the user can click to select the correct value from the list.
BTW, there are other, simpler ways to keep the ID from showing--such as using a pop up menu or hiding it behind a name field from the related table, but since this still disables auto-complete, I prefer using versions of the above method unless the list of values will be pretty short.
Oh my goodness I finally understand. I did not see the auto-enter calculation in the Contact fk field in your example (image attached) but this makes it all makes sense.
This is an ingenious way to get around this problem, a problem I am sure occurs in many solutions. It will be useful across my solution.
You have been invaluable and I thank you for the work you put into these examples.