Setup your relationship between the two tables to "allow creation of records in this table via this relationship".
In your script use the set field script step to set all the fields you want.
For example The script might look like this:
Set Field [CustName field in 2nd table; CustName from 1st table]
Set Field [OtherCustInfo field in 2nd table;OtheCustInfo from 1st table]
Set Field [OtherCustInfo2 field in 2nd table;OtheCustInfo2 from 1st table]
Set Field [OtherCustInfo3 field in 2nd table;OtheCustInfo3 from 1st table]
This will create a new record in the second table if non matches. And if there is already a match then it will update the info.
An alternative would be to just set the keyfield and then over in the second table have the fields be formatted to auto enter the data you want.
Another alternative would be to load all the data you want up into variables and then change layouts and then create a new record and then set the appropriate fields from the data in your variables.
Be careful with your relationship keys here. If you are using customer name, you will likely encounter two issues to resolve: 1) people and companies change their names 2) names are not unique (you might have two or more people named "John Smith" for example.
Restructuring your relationships to base them on an auto-entered serial number will avoid these issues.
Thanks for both of your responses. They were very helpful.
I've redone the fields so that there is a Unique Id (auto-generated serial number) for each company name, and that is what I've used as the match field between the tables.
I now have two working scripts:
- after I have created a new record I have a script that creates a new record in the other table and generates a serial number (which is the same because both tables will have the same number of records), and then the name field of the company from the first table gets put into the name field of the company in the second table. That looks like:
Set Field [Table2::Company name; Table 1;: Company name]
- the other script is for if I go back and edit the name of a company in the first table, then it will go the other table and update the new name. That looks like: Set Field [Table2::Company name; Table 1;: Company name]. That's working.
My problem now, is how to make those scripts happen at the appropriate times.
I can't seem to find a script trigger for only when a new record is created.
I have an "OnObjectSave" trigger for when a record gets updated, and that works with the second script, but if I have that with the first script, it creates a whole new record in the other table every time I just want to update the name.
Is it possible to combine those two scripts into one (but it would have to recognise or not whether a new record needs to be created in the other table, or whether it simply needs to be updated), and have the script trigger set for anytime the company name field gets saved?
Delete the company name field completely from table2 you don't need it nor do you need a script to update it in any way.
Any time you need the company name to appear on a layout based on table 2, simply add the company name field from table 1. The company name from the matching related record will then appear on your layout and no updates will be necessary. This works for any company specific data that you store in table 1.