There are better ways of doing this:
1) Just forget about the supplier table, and make a status field in the product sourcing table. Then you can run a layout script trigger to omit any "non-suppliers" when you go to a supplier layout.
2) Set up a script that exports the product sourcing record, goes to suppliers, and imports the record you just exported as a new record.
3) Set up a script that sets a list of variables of all the values you need to carry from product sourcing, goes to suppliers, creates a new record, and uses set field to set the values that you need in the new supplier record.
carrying values through a relationship is clunky and context driven. Unless you have a need for a duplicate record in two tables, I'd suggest method 1 for simplicity sake.
Thank you Mike.
I think I'll give option 1 a go.
I did as you said in option 1 and it is working great, thank you!
One issue I am having though is when I leave the Supplier layout the records are omitted for all other layouts. I added a script for 'OnLayoutExit' to Show all Records which works in most layouts but in others I get an error message saying "No records match this find criteria", presumably because I still have the found set.
How do I clear this found set so that the user can browse other layouts viewing all records?
Try switching to onLayoutEnter script steps to show all records or to find for your non-suppliers. If you're going to have something setup like this (using a single table as two separate tables), you always want to execute actions in the context of the layout you are on/loading/viewing/going to.
You can use global variables to track the origin and destination layouts ( get(layoutname) should work if you name everything appropriately), thereby allowing you to leave a found set intact (IE if you are going from a supplier layout to another supplier layout), or to re-find if you're going to a non-supplier layout.