I am able to script for changes to the data types I need (Date, numbers, single select, short text etc) but this last one has thrown me.
In my test table (ConorTest) there are 3 fields
Status - Single select
Employee Currently Issued the Equipment
Employee Previously Issued the Equipment
With fields 2 and 3, the value in these fields is linked to a different employee table
Field 2 - with “Allow linking to multiple records” NOT selected
Field 3 - with “Allow linking to multiple records” selected
When the status field is changed, I want to populate the second field with the value for the first (without overwriting what is there)
Create an Automation
Trigger: When record matches condition - Status is ‘whatever’ (When this field is changed to this option the automation will kick off)
Action: Update record, record ID from Step 1, field - Employee Previously Issued the Equipment: press the blue + and add both Employee currently issued the equipment and Employee previously issued the equipment with a comma between them.
Add one more action: update record: recordID from step 1, field - Employee Currently Issued the Equipment, leave blank
Now when your status changes to the option you picked, the automation will copy the text from 2 and 3 and place them both in 3, then clear the option in 2.
Add current and set the drop down to trigger the automation
Thanks for the reply vivid-squid, and for your very detailed response.
I had tried this before and it always removed the value from field 3 after field 2 was deleted but it is working now. Ah well, I learnt a lot playing about with it all yesterday trying to get it working so it wasn’t time wasted.