Set Foriegn Key value according to chosen value from a drop down list
Hello all, thank you in advance for any help provided.
I am creating a database for an Art Gallery.
I have the following tables:
Works, Owners, Invoices In
The problem here is with the "Invoices In" layout, in which I have a container for an invoice we have received from another gallery, with details of that invoice (amount, date, gallery name etc) next to it.
A user needs to be able to search for and select which work on our database corresponds to the invoice on that page.
Each work has a key, _WorkIDpk (primary key)
Each invoice has a _WorkIDfk (foreign key)
I have had some success so far, but am falling at the last hurdle.
Please see the attached screenshot.
I have created a drop down box beside "Related Work:" (called, surprise surprise, "WorkRelated") which reveals a searchable ValueList of the 'Title' Field of the 'Works' table. So if you click in this field and start typing 'Mona Lisa', you can find the 'Mona Lisa' work from the 'title' fields of all the Work records. This is a good start.
What needs to happen is that when a user selects 'Mona Lisa' from that list, the _WorkIDfk for this invoice would change to the corresponding _WorkIDpk for Mona Lisa. That would mean that this invoice was linked to the work it concerns, because its foreign key matches the primary key of the work.
At the moment, I have tried these two script trigger for the drop down box:
Set Field [InvoicesIN::_WorkIDfk; InvoicesIN::WorkRelated]
Set Field [InvoicesIN::_WorkIDfk; Get(ActiveFieldContents)]
Of course this does not produce the required result. What happens with this script is that the _WorkIDfk is changed to the full 'Title' of the work, rather than just the _WorkIDpk.
I cannot for the life of me (despite about 2 hours of searching) work out how to write a script where 'OnObjectSave' on a selected work would find that work's corresponding WorkID and set the current invoice's WorkIDfk to that value.
I have a feeling it will require a rather complicated 'Get' script, but I cannot do it on my own.
I will be very very grateful for any help.