I need some guidance on scripting to update some fields in another table, providing one specified field in one table matches a specified field in the other table.
The tables are as follows:
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 to a maximum of 4 records that are to be 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 there is a match in records from a portal row in Layout 1 with any of the records in table 3, I want to set some of the fields in table 3 to match those in table 2.
I'm guessing that:
First I have to see if any records match a specific field in the portal with a specific field in table 3.
If there is no exact match, I want to create a new record in table 3 and set various fields in table 3 to match the fields in table 2 (based on the portal row I'm sitting on). I can already do this.
If there is a match (there would only be a maximum of 1 match), I need to set various fields in table 3 (on the record that matches) to be the same as the various fields in table 2 (in case the values have changed from the time the record was originally created).
I do not have a clue as to what script functions and steps I need to take to make this happen (newby).
Any direction to accomplish this would be greatly appreciated.