Convert a synced multiple select (text) field back into a multiple select field in the second base

I have a base where we log samples into and out of our laboratory. Samples in and out are in separate tables. Samples out has a link to the corresponding sample in.

In the samples out table I have a link field (multiple selection) to a separate table that lists the tests that are done on the samples that go out. We started with a simple multi select but changed to the current system so that we could associate more information with the list of tests (methods to be used, approved laboratories, levels of detection etc.).

We have set up a second base where we record the various test results for each sample. We could do this in the original base but for various complicated reasons we need a separate base for this. Each time a new sample out record is added to the first base, the second base is updated with a new record, ready to record results against.

I have put a lookup field into my second base to reproduce the information from the linked to tests field in the first table. So we can see what tests have been requested each time a sample goes out. The links of course are converted to text with multiple links separated by a “,” (you have got to fix this Airtable - it is a MAJOR problem!). This field is important for data analysis so I would like to convert the information here - automatically - into a multiple select field. Presumably using a formula ---- but how? All ideas gratefully accepted.

The last paragraph describes a link field from a source table being converted to a text field in the destination table, but you want the text field converted to a multiple select field? Is that right? Linked Record Fields and Multiple Select Fields are very different things.

If you want to use a Multiple Select field then create a new field of that type, and use an Automation triggered when “Text field does not equal the Multiple Select Field” and use and Update Action step to copy the Text field into the Multiple Select.

If you want to use a Liked Record Field then you need to sync the table that is being linked to into Base 2 as well, then follow the same advice above with the Automation.