I need to create an automation that works between three tables.
Whenever a record from table B is linked to a record in table A, I need to create a record in table C that references those two records. If I link multiple records in at the same time, I need to create a new record for each that are linked.
So basically, I need to create a record in table C that is [Table B Value]-[Table A Value.]
My problem right now is that if I use the "when record updated" condition, it just won't work.
If I link multiple records at once I can't pull out the Table B values individually. It ends up just creating one record of all the values.
And if I there are multiple values in the table, it pulls them all.
If I link[Table B Value]-[Table A Value 1] and [Table A Value 2] simultaneously,
I need the automation to create simultaneously in Table C:
[Table B Value]-[Table A Value 1]
[Table B Value]-[Table A Value 2]
Thanks for reading and any help would be appreciated.
An automation with a "Run a script" action would be simplest if you know how to do that / know someone who could help you with that
And if you're open to third party options, Zapier's got a line item action that may help with this as well I think
If not, an alternative is to force the creation of new records by pasting unique comma separated values into a linked field, and you can find an example of that here
The example works by: 1. Having an automation that finds the relevant template records and pastes them into a specific text field 2. Having a formula field that will format it into a unique comma separated list of values 3. Having another automation that will then paste the values from the previous point into a linked field, forcing multiple record creation
In your case, it would trigger when a Table A record is linked to a Table B record, and it would find all the Table A records that are linked to that one Table B record, and it would paste that list into the text field as mentioned in Step 1 above, and follow through with it.
So, if you link[Table B Value]-[Table A Value 1] and [Table A Value 2] simultaneously, you'd end up with two new records in Table C like you said:
- [Table B Value]-[Table A Value 1] - [Table B Value]-[Table A Value 2]
At this point, both records are linked to the correct Table B record, but are not linked to the Table A records. To deal with this, you'll need to create a formula field in Table C to extract the name of the relevant Table A record (e.g. [Table A Value 1]), and then use an automation to paste that value into a linked field to Table A