4 Replies Latest reply on Jul 27, 2015 4:49 AM by BenHough

    Set Foriegn Key value according to chosen value from a drop down list



      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.






        • 1. Re: Set Foriegn Key value according to chosen value from a drop down list

          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.


          "Adventures in FileMaking #2 - enhanced value selection"

          • 2. Re: Set Foriegn Key value according to chosen value from a drop down list

            Hi Phil,

            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.




            • 3. Re: Set Foriegn Key value according to chosen value from a drop down list

              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.

              • 4. Re: Set Foriegn Key value according to chosen value from a drop down list

                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.