Strings from a long text field to unique primary keys

[Tom_R]

Hi all,

I have a form which asks the user to input student numbers from an excel/google sheet into a long text field. This will happen weekly.

How might I take each of these student numbers (separated by a space) and put them onto a separate record (where student number is the primary key) in a separate table?

Happy for other suggestions.

[Kamille_Parks · Accepted solution]

Create a formula field which separates your student numbers by commas: SUBSTITUTE({Whatever field your student numbers are in},' ',',')

Then make a Link to Another Record field linking to the separate table. Copy and paste the Formula Field entries into the Link Field. (you can copy the whole column at once, a select few records, or one at a time)

Your result will look something like this:

[Tom_R]

Thanks @Kamille_Parks, I didn’t even think/know about the substitute formula. It didn’t work initially, however I realised by student numbers were separated by a line break not a blank space. As found on this form I used the following: SUBSTITUTE({Whatever field your student numbers are in},’\n’,’,’) and it worked perfectly.

Going to implement a zap to copy paste from the formula field to the linked students table.

Thanks again!

[Tom_R]

Next problem :-/,

I’m trying to perform a similar process, this time taking a single line text field, using CONCATENATE() in a formula field to insert a comma at the end of the string, then copying from the formula field to a linked field (very similar to the above).

However - the copy paste from the formula field into the linked subject field doesn’t seem to work. The result is blank - this didn’t occur earlier with the student numbers.

I believe this is because the linked table has a formula as a primary key (Concats one field with another).

Is there are workaround here?

If not, then I will have to find a workaround in the linked table and remove the formula aspect there.

Regards,
Tom.

[Kamille_Parks]

The pasted cell will return blank if the record in the linked table does not yet exist, because the primary key is a formula (you had it right).

I can’t think of an immediate work around for this as you would need to fill in, presumably, multiple fields in the linked record to get the primary key to be “filled in”.

Your description implies the formula in question can/should result in the same computed phrase with data available in both tables. The closest idea I have is to make whatever fields you’re concatenating to get the primary key in Table B Lookup fields pointing to Table A’s data. That way as soon as you add a new record to Table B via the Link field, the primary key should fill in for you. This might not be possible depending on your data structure.