1 Reply Latest reply on Nov 12, 2013 6:07 AM by philmodjunk

    Script for populating fks.

    JohnDee

      Title

      Script for populating fks.

      Post

           Can I just run this by you guys to tell me what you think. I need a few more experienced eyes to catch any mistakes here, since this will be the backbone for a lot of operations in my db.

            

           Set Variable [ $Var; Value: Get ( ActiveFieldContents ) ]

           Go to Layout [ “‘Clients” (Clients) ]

           Perform Find [ Specified Find Requests: Find Records; Criteria: Clients::NameSurname “=$Var” ] [ Restore ]

           Set Variable [ $Var2; Value:Clkients::_pk_Client_List_Serial_Number ]

           Go to Layout [ original layout ]

           Set Field [ Projects::__fkClientID; Projects::__fkClientID=$Var2 ]

           Commit Records/Requests

           Exit Script [ ]

            

           So basically I am in the Projects layout, grabbing a Client name inputted via predefined value list based on the available clients in the table, finding it in the clients table, then going back and pasting this id in the Projects layout. 

            

           This is for a simple case not the case of a joint table, which maybe I ‘ll post later. 

            

           Is this good practice? I want to start, so to speak, on the right foot here in terms of good practice. 

        • 1. Re: Script for populating fks.
          philmodjunk

               Hope you use a script trigger on your value list formatted field to perform the script as it assumes that the focus is in a specific field at the time that the script is performed.

               This step is incorrect:

               Set Field [ Projects::__fkClientID; Projects::__fkClientID=$Var2 ]

               Change it to:

               Set Field [ Projects::__fkClientID; $Var2 ]

               A value list of client ID's and client names could enter this data directly into Projects::__fkClientID without use of a script. But I also realize that for large lists of names, the lack of auto-complete for such a value list makes it less than ideal to use. But your script also makes no provision for the fact that you might get two clients with the same name.

               You might find the scripts in this demo file to be of interest. It uses a relationship to find the ID of the selected name and includes code that detects and correctly handles situations where you get two individuals with the same name:

               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7