5 Replies Latest reply on Jul 23, 2017 10:51 AM by cmj

    Set Field / Replace Field in other table

    cmj

      I'm using FM 12 Advanced.

       

      I have a script that writes a new record into table 2 when I leave a portal row based on table 1.

      That works fine.

      However, after the record is initially made in table 2, I don't wish to keep adding more records into table 2 every time I leave the portal row.

      Instead, I only want to update the values in table 2 (i.e.  just write over existing values) when I leave the portal row after the initial record exists.

      I'm guessing that I need to see if a specific record exists and then determine if I create a new record or append the existing.

      If (field 1 ="", Perform Script - add record)

      Else

      Perform Script - to append existing values.

      End If

       

      Is this the correct approach? And what is the script command to append a field's value?

       

      Thank You

        • 1. Re: Set Field / Replace Field in other table
          philmodjunk

          That works, but you might not need to. If "Allow Creation" is enabled for the relationship, You can use set field to set data to any field in that portal row's record. If no record exists, this action creates the record. If a record does exist, it updates that existing record.

           

          Note that set field can modify a field without it being visible in the portal row. The key detail is to keep the focus in the portal row while the set field steps do their thing.

          • 2. Re: Set Field / Replace Field in other table
            cmj

            I should clarify before moving forward:

            Connection Table 1 to Table 2 is one to many. Allow creation of records in Table 2 via the relationship.

            Connection Table 1 to Table 3 is a one to many. Allow creation of records in Table 3 via the relationship.

             

            Layout 1 is based on Table 1 and contains a portal based on Table 2.

            Table 3 values reside in layout 3 (in layout view). There can be many records in layout 3 because it's in this layout that all records are created - except for the 1 or 2 records that are populated from the portal in layout 1.

             

            The goal is to have table 2 populate table 3 (via trigger script on a portal in layout 1). If the specific record already exists in table 3, don't add it again - only update it. I'm presently creating a new record with a script trigger On Exit of the portal:

             

            Regarding Set Field function - are you saying that it will create a new record if one does not exist and it will just append a record that does exist? If that's true,  I could drop the "New Record/Request" from my script that populates layout 3. But keep in mind that there is no direct relationship (& ability to create new records) between table 2 and table 3. Currently the "New Record/Request" is a problem because it keeps creating new records, even when a record already exists.

             

            However, I would need to know if the record already exists in table 3 and if it does, how do I point to the correct record in table 3 to append, given that there is no direct relationship (pk / fk) between table 2 and table 3?

             

            Thanks

            • 3. Re: Set Field / Replace Field in other table
              philmodjunk

              "are you saying that it will create a new record if one does not exist and it will just append a record that does exist?"

               

              Yes If:

               

              1) relationship is correct to do this

              2) the focus is still on the correct portal row.

              3) you are modifying data in the portal's table

               

              the need to modify data in a third table makes this more problematic.

              • 4. Re: Set Field / Replace Field in other table
                cmj

                I just tested it without the "New Record/Request" and it creates a new record in Table 3 if one does not exist and it appends the record in Table 3 if it does exist. That's great!

                 

                Step 2: I want it to create a second record, providing the second record differs from the existing first record in Table 3.

                Maybe use Set Error Capture in another script first? If Error Capture is false, create a new record and set fields....

                 

                Otherwise

                 

                Step 3: If the 1st record in the portal exists, I want to append the same record created in Table 3 or if the 2nd record in the portal changes, I want to append the second record in Table 3.

                 

                If Error Capture is true, append the existing record?

                 

                I'm not sure how to define the existing record. Maybe this is done by default given the portal row that I'm sitting on?

                 

                 

                 

                 

                • 5. Re: Set Field / Replace Field in other table
                  philmodjunk

                  However, there is a method known as "MagicKey" that might be adapted to do this same trick.