7 Replies Latest reply on May 6, 2014 3:30 PM by pnomads

    Set Field script step problems

    pnomads

      Title

      Set Field script step problems

      Post

           I’ll start by saying that I have this script working with all of the functionality that I want by using the “Copy and “Paste” script steps, but I’ve read that this is a cardinal sin in filemaker. So I’m trying to accomplish the same thing by using the Set Field script step, but I’m having trouble getting it to work.

            

           There are 3 tables involved in this script: Donations, Correspondence, and a join table called PartnerCorrespondence. In plain english, in the Donations table we have a list of all donations from last month and we want to create one “Thank You Note” for this group in the Correspondence table, then take the Unique ID for that “Thank You Note” and apply it to every donor from the last month. Specifically, the script is supposed to:

            

           1) Create a new record of Correspondence with a unique ID (Correspondence::__pkCorrespondenceID) with “Thank You Note” as the type in the Correspondence table

            

           2) Then looks at the found set in the Donations table and creates a new record for each donor with its unique ID (Donations::_fkPartnerID) along with the Unique Correspondence ID in the PartnerCorrespondence table 

            

           With the Set Field rather than Copy and Paste, what it actually does is:

            

           1) Creates a new record of Correspondence with a unique ID of a “Thank You Note” in the Correspondence table

            

           2) Creates a new record for each donation in the found set (donations from last month) with all fields blank with the exception of the “PartnerCorrespondence::Notes” field which has the string “For Donation ID “ (it is missing the related data “Donations::DonationID” as well)

            

           Attached is a screenshot of the script. I’m probably missing something simple, but I just can’t figure this out…thanks for any help!

            

      Set_Field_Script.png

        • 1. Re: Set Field script step problems
          philmodjunk

               You have a "can't get there from here" situation. Set field can't enter data from Donations until _fkPartnerID gets the correct value to link the new record to the correct record in Donations. And it can't enter data from Correspondence until _fkCorrespondenceID gets the correct value to link this same new record to the correct record in Correspondence.

               So it can't directly enter data from either related table until another method is used to enter the needed ID values.

               Assuming that this script starts on the Donations layout, you need to add this as the very first script step:

               Set Variable [$DonationID ; value: Donations::_fkPartnerID ]

               Then add this step immediately after the new record step that creates a new record in the Correspondence table:

               Set Variable [$CorrespondenceID ; value: Correspondence::__pkCorrespondenceID ]

               These are the steps that take the place of "copy" in your original copy and paste script.

               then your set field steps become:

               Set Field [PartnerCorrespondence::_fkPartnerID ; $PartnerID ]
               Set Field [PartnerCorrespondence::_fkCorrespondenceID ; $CorrespondenceID ]

               and you will not need the commit records steps after each set field step.

               PS, I'd also replace the steps in your script that start with the word "insert" with set field steps as well. Like copy and paste, insert steps will not work unless the specified field is present on the current layout and enabled for Browse mode access.

               Set Field [ Correspondence::Date ; Get ( CurrentDate ) ]
               Set Field [Correspondence::Type ; "Thank You Note"]
               Set Field [PartnerCorrespondence::Notes; "For Donation ID " & Donations::DonationID ]

          • 2. Re: Set Field script step problems
            pnomads

                 Thanks, works brilliantly! Just so I understand correctly for future reference: Set Field cannot display related records by itself, only records from the current table, correct?

            • 3. Re: Set Field script step problems
              philmodjunk

                   That is not correct. Note that one of the set field steps that I recommended you use refers to a field from a related table.

                   Take a look at your PartnerCorrespondence layout immediately after using New Records from the records menu to create a new record.

                   Note that all your fields except any with auto-enter field options are empty. There's no data in either _fkPartnerID nor _fkCorrespondenceID. WIthout a value in _fkPartnerID, the record is not linked to ANY record in donations. Without that link any calculation in any script step that refers to field in Donations will evaluate as though the field from donations is empty. The same is true for accessing data in the correspondence table from this record.

                   Once you have the links established by using set field to enter values into these _fk fields, then your calculations and this layout can access data from related records in those two tables.

              • 4. Re: Set Field script step problems
                pnomads

                     Ah ok, that makes sense. Thanks so much for the help and explanation!

                • 5. Re: Set Field script step problems
                  pnomads

                       Actually, on further review, the script has a little problem. It creates the exact number of PartnerCorrespondence records as the found set in donations, however, they all have the same value for the _fkPartnerID field (of the record that is selected when the script is run). So say I have 10 different donors for last month, I'm getting 10 PartnerCorrespondence records but all with the same _fkPartnerID. And also the calculated result of "Donations::DonationID" in the "PartnerCorrespondence::Notes" field is the same in all as well. What did I do wrong?

                  • 6. Re: Set Field script step problems
                    philmodjunk

                         Well I'd say it was what did I do wrong as I should have spotted that issue.

                         Move the first set variable step down until it is the first step after Loop. That way we "copy" a new ID into the variable each time the loop moves to a new record in the donations table.

                    • 7. Re: Set Field script step problems
                      pnomads

                           I was just about to try that before you responded. That solved it!