7 Replies Latest reply on Nov 25, 2011 12:12 PM by NaturSalus

    Help with Script to Add Records Through a Join Table

    NaturSalus

      Title

      Help with Script to Add Records Through a Join Table

      Post

      Hello,

      I am writing a script that from an open record in Table A will create a new record in Table C, and after doing that will return me to the original record in Table A.  

       

      Table A and Table C are related through a join Table B

       

      Table A --< Table B >-- Table C

       

      Usually when one moves from an open record from Table A to another Table C, one gets listed all the records of Table C.  But this is not what I want the user to see. I want the user to get the "feeling" that there are no records in Table C when directed form an open record in Table A.

      Let's assume that Table A has 250 records and that Table C has 500 records. If we  were in the process of creating Table A record 251 and once created we wanedt to create 5 related Table C records. The moment we move, through a Button attached to the Add Records Script, from Table A record 251 to Table C layout, the user has in sight the 500 records of Table C. THIS IS NOT what I want the Add Records Script to do.Once the Add Records script is triggered the user should be presented with a new record created by the Script in Table C layout. So, all the records previously created in Table C won't be on sight for the user.

       

      Tables:

      • Deviation

      • DeviationEvidence

      • Evidence

      Table Occurrences:

      • DEVIATION
      • deviation_DEVIATIONEVIDENCE
      • deviation_deviationEvidence_EVIDENCE

      Relationships

      DEVIATION::__kp_Deviation = deviation_DEVIATIONEVIDENCE::_kf_Deviation

      deviation_DEVIATIONEVIDENCE::_kf_Evidence = deviation_deviationEvidence_EVIDENCE::__kp_Evidence


      On a layout based on the DEVIATION TO there is a Portal to the deviation_DEVIATIONEVIDENCE TO of the DeviationEvidence table with two fields:

      • deviation_deviationEvidence_EVIDENCE::NameDocumentEvidence

      • deviation_deviationEvidence_EVIDENCE::DocumentEvidence

      And there is the + Button that triggers the Add Records Script that will create a new record in the Evidence Table from the Portal to the Evidence Table in a layout based on the Deviation Table.

       

      I have come up with the following Add Records Script that I don't know if it is right.

      As mentioned above the script is triggered from a layout based on the Deviation_Detail TO of the DEVIATION Table.

       

       

      Add Records Script


      # Error Handling
      Allow User Abort [ Off ]
      Set Error Capture [ On ]
      # Get DEVIATION::__kp_Deviation
      Set Variable [ $DeviationID; Value:Get(RecordID) ]
      # Create EVIDENCE::__kp_Evidence
      Go to Layout [ “Evidence” (Evidence) ]
      Show All Records
      Show Omitted Only
      New Record/Request
      Set Variable [ $EvidenceID; Value:Get(RecordID) ]
      Pause/Resume Script [ Indefinitely ]
      Commit Records/Requests
      # Assign DEVIATION::__kp_Deviation = deviation_DEVIATIONEVIDENCE::_kf_Deviation
      Go to Layout [ “dev_deviation_DEVIATIONEVIDENCE” (deviation_DEVIATIONEVIDENCE) ]
      New Record/Request
      Set Field [ deviation_DEVIATIONEVIDENCE::_kf_Deviation; $DeviationID ]
      # Assign EVIDENCE::__kp_Evidence = deviation_DEVIATIONEVIDENCE::_kf_Evidence
      Set Field [ deviation_DEVIATIONEVIDENCE::_kf_Evidence; $EvidenceID ]
      # Go back to the original layout
      Go to Layout [ “Deviation_Detail” (DEVIATION) ]

       

      The Add Records script seems to be wrong because doesn't assign correctly the __kp_Evidence = _kf_Evidence

      For example, before starting I make sure that tehre are no records in any table and that the EVIDENCE::__kp_Evidence as well as the DEVIATION::__kp_Deviation start from 1 in increaments of 1. After creating the first record for the Deviation Table and after triggering the Add Records Script I get the following values:

      Deviation::__kp_Deviaation =1

      deviation_DEVIATIONEVIDENCE::__kp_DeviationEvidence = 1

      deviation_DEVIATIONEVIDENCE::_kf_Deviation = 161 instead of 1 (I guess this is what it is supposed to be)

      deviation_DEVIATIONEVIDENCE::_kf_Evidence = 9 instead of 1 (I guess this is what it is supposed to be)

      Evidence::__kp_Evidence = 1

      Obviously the Add Records Script is wrongly establishing the relationships. So, what is wrong with the script?

       

      Once the Add Records script has been triggered and has finished, the new record created in the Deviation Table is not shown in the  Portal to the deviation_DEVIATIONEVIDENCE TO of the DeviationEvidence table. So, I must have set up the Portal erroneously. Where is my mistake?

      After the Add Records script has been triggered the user ends up in the Evidence table where a new record has been created and the user has to fill in the data for the two fields on the layout based on the Evidence table. Once the user has finished entering the values in the fields, he/she  sees FM11 message "Script paused". So he/she has to select the Continue button. What should be the setting like so that the user does not see the message "Script paused" and the script continues after he selects for example a Continue button?

       

       

      Thanks

       

      NaturSalus

       

       

        • 1. Re: Help with Script to Add Records Through a Join Table
          philmodjunk

          Here's your first problem:

          # Get DEVIATION::__kp_Deviation
          Set Variable [ $DeviationID; Value:Get(RecordID) ]

          RecordID's are not a good choice for primary keys.

          See here for why: Why Record ID's should not be used as Primary Keys in Filemaker Relationships.

          It's much better to add auto-entered serial number fields to both Deviation and Evidence fields to serve as your primary keys for these tables.

          That doesn't, however, explain this issue:

          Once the Add Records script has been triggered and has finished, the new record created in the Deviation Table is not shown in the  Portal to the deviation_DEVIATIONEVIDENCE TO of the DeviationEvidence table. So, I must have set up the Portal erroneously. Where is my mistake?

          What you show, while it has inherent risks better avoided, should work. Make sure that your kp and kf fields are all the same data type and make sure that the kf fields do not auto-enter any data. Examine the data in these fields to see if they contain the correct values.

          You may want, as a test, enable "allow creation of records via this relationship" for the deviation to join table relationship, then create a record directly in the join table. You can then examine it's values to compare to one created by your script to see if you see any differences. If, on the other hand, a record created in the portal then disappears on you it indicates an issue with either the portal's relationship or that a portal filter is filtering out the record.

          • 2. Re: Help with Script to Add Records Through a Join Table
            NaturSalus

            Hello Phil,

             

            Thanks for looking into my question.

            I wrongly assumed that RecordID = Record Primary key.

            What would be the right expression then, instead of Get(RecordID) would be Get ( )

            I will check what you have suggested and will carry out the test.

             

            Thanks for your help

             

            natursalus

            • 3. Re: Help with Script to Add Records Through a Join Table
              NaturSalus

               

              Make sure that your kp and kf fields are all the same data type and make sure that the kf fields do not auto-enter any data

              Both the kp and kf fields are numbers. Only, the kp fields are auto-entered serial numbers generated On creation

               

              Examine the data in these fields to see if they contain the correct values.

              The only strange values are the ones created by my Add Records script and already mentioned aboved:

              deviation_DEVIATIONEVIDENCE::_kf_Deviation = 161 instead of 1 (I guess this is what it is supposed to be)

              deviation_DEVIATIONEVIDENCE::_kf_Evidence = 9 instead of 1 (I guess this is what it is supposed to be)

               

              Thans

              natursalus

               

              • 4. Re: Help with Script to Add Records Through a Join Table
                philmodjunk

                Don't use Get() at all.

                Define the __pk_ fields as fields of type number. Use field options to specify that they auto-enter a serial number.

                Define the _fk_ fields as fields fo type number, do not specify any auto-enter options for these--they'll get values copied from the __pk_ fields via your scripts.

                Note that if you add a Serial number field to an existing table, any records already in that table will show this new field as empty. You'd need to update them with the serial numbers option in a Replace Field Content operation to give such pre-existing records a serial number.

                • 5. Re: Help with Script to Add Records Through a Join Table
                  NaturSalus

                  Phil,

                  Define the __pk_ fields as fields of type number. Use field options to specify that they auto-enter a serial number.

                  Define the _fk_ fields as fields fo type number, do not specify any auto-enter options for these--they'll get values copied from the __pk_ fields via your scripts.

                  As I already said that is the way they are set up.

                   

                  Don't use Get() at all.

                  Sorry, I am double checking that I understand what you are saying. If I got you right, does it mean that the right Add Records script should be the following?

                   

                  Add Records Script


                  # Error Handling
                  Allow User Abort [ Off ]
                  Set Error Capture [ On ]
                  # Get DEVIATION::__kp_Deviation
                  Set Variable [ $DeviationID; Value:Get(RecordID) ]
                  # Create EVIDENCE::__kp_Evidence
                  Go to Layout [ “Evidence” (Evidence) ]
                  Show All Records
                  Show Omitted Only
                  New Record/Request
                  Set Variable [ $EvidenceID; Value:Get(RecordID) ]
                  Pause/Resume Script [ Indefinitely ]
                  Commit Records/Requests
                  # Assign DEVIATION::__kp_Deviation = deviation_DEVIATIONEVIDENCE::_kf_Deviation
                  Go to Layout [ “dev_deviation_DEVIATIONEVIDENCE” (deviation_DEVIATIONEVIDENCE) ]
                  New Record/Request
                  Set Field [ deviation_DEVIATIONEVIDENCE::_kf_Deviation; $DeviationID ]
                  # Assign EVIDENCE::__kp_Evidence = deviation_DEVIATIONEVIDENCE::_kf_Evidence
                  Set Field [ deviation_DEVIATIONEVIDENCE::_kf_Evidence; $EvidenceID ]
                  # Go back to the original layout
                  Go to Layout [ “Deviation_Detail” (DEVIATION) ]

                   

                  I appreciate your patience.

                   

                  natursalus

                  • 6. Re: Help with Script to Add Records Through a Join Table
                    philmodjunk

                    Use:

                    # Error Handling
                    Allow User Abort [ Off ]
                    Set Error Capture [ On ]
                    # Get DEVIATION::__kp_Deviation
                    Set Variable [ $DeviationID; Value:Deviation::__kp_Deviation ]
                    # Create EVIDENCE::__kp_Evidence
                    Go to Layout [ “Evidence” (Evidence) ]
                    Show All Records
                    Show Omitted Only
                    New Record/Request
                    Set Variable [ $EvidenceID; Value:EVIDENCE::__kp_Evidence ]
                    Pause/Resume Script [ Indefinitely ]
                    Commit Records/Requests
                    # Assign DEVIATION::__kp_Deviation = deviation_DEVIATIONEVIDENCE::_kf_Deviation
                    Go to Layout [ “dev_deviation_DEVIATIONEVIDENCE” (deviation_DEVIATIONEVIDENCE) ]
                    New Record/Request
                    Set Field [ deviation_DEVIATIONEVIDENCE::_kf_Deviation; $DeviationID ]
                    # Assign EVIDENCE::__kp_Evidence = deviation_DEVIATIONEVIDENCE::_kf_Evidence
                    Set Field [ deviation_DEVIATIONEVIDENCE::_kf_Evidence; $EvidenceID ]
                    # Go back to the original layout
                    Go to Layout [ “Deviation_Detail” (DEVIATION) ]

                    • 7. Re: Help with Script to Add Records Through a Join Table
                      NaturSalus

                      Phil,

                      Perfect, sorted out!

                      Many thanks

                       

                      natursalus