Skip to main content

Automated linking of comma-separated strings

  • January 7, 2024
  • 2 replies
  • 76 views
RIWFP_Admin
Forum|alt.badge.img+5

Hello! I'm trying to use automations to link a comma-separated string of names to a separate table.

I have to pass the name from Table A, to a column titled "Parse Match" (because names in A do not match record titles in B) in Table B, then link them back in Table A.

This was easy to do via automations for the "Lead Sponsor" column, because there's only one name to link.

But I'm stuck figuring out how to link a string of comma-separated last names in table A, column "Co-Sponsors" to the "Parse Match" column in Table B, then back to Table A.

Any suggestions? If this requires a script to separate the comma-separated values per record, that's beyond my abilities right now… 😛

Table A

Table B

2 replies

Alexey_Gusev
Forum|alt.badge.img+25

Hi,
Do you need to connect these tables as single action, or you want to dynamically maintain these links for new records as well? To make things easier in future, you might need to change one of these tables, choosing another primary field, so it can be used to link. 
But you can continue with the current configuration. To link records at this moment, you can duplicate second table, remove sync source from duplicate (table will turn to usual unsynced). Now create new formula field containing {Parse Match} as formula, and switch it to be primary field, right-clicking on Name field. Then switch to Table A, it receive additional link field from duplicated table. Even 2 in you case, for each of your sponsor fields. Use Lead sponsor & Co-sponsors values to link (Parse Match should be unique, without comma and ampersand in values. If a few records not match this rule, you can link them manually). Ensure the links are ok, complete linking manually, if needed. 
Now add 2 lookups of Name, each for it's own linked field, that you just filled. First will be a column of single values 'Rep. XXX', second - multiple comma-separated 'Rep. ' values. Use them to link where red and green arrows on your picture. When links are OK, delete duplicated table and links/lookups connected to it.

RIWFP_Admin
Forum|alt.badge.img+5
  • RIWFP_Admin
  • Author
  • New Participant
  • January 12, 2024

Hi,
Do you need to connect these tables as single action, or you want to dynamically maintain these links for new records as well? To make things easier in future, you might need to change one of these tables, choosing another primary field, so it can be used to link. 
But you can continue with the current configuration. To link records at this moment, you can duplicate second table, remove sync source from duplicate (table will turn to usual unsynced). Now create new formula field containing {Parse Match} as formula, and switch it to be primary field, right-clicking on Name field. Then switch to Table A, it receive additional link field from duplicated table. Even 2 in you case, for each of your sponsor fields. Use Lead sponsor & Co-sponsors values to link (Parse Match should be unique, without comma and ampersand in values. If a few records not match this rule, you can link them manually). Ensure the links are ok, complete linking manually, if needed. 
Now add 2 lookups of Name, each for it's own linked field, that you just filled. First will be a column of single values 'Rep. XXX', second - multiple comma-separated 'Rep. ' values. Use them to link where red and green arrows on your picture. When links are OK, delete duplicated table and links/lookups connected to it.


Thanks for this response. I wanted to hold off until I had a solution. Basically, I did the following:

  1. A new table with two columns for matching only.
  2. Auto-break my comma-separated strings into 9 columns (there are a max of 9 listed bill cosponsors)
  3. Re-format each of those columns into a new column simply to trim the whitespace that shows up on many of them.
  4. Make a column to count the number of cosponsors per bill. Sometimes 1, sometimes 5, sometimes 8…
  5. Apparently, a step that returns empty in an automation will prevent following steps from happening. So I had to make 9 separate automations: matching and linking columns 1-9 if 9 cosponsors; matching and linking columns 1-8 if 8 cosponsors…

Honestly this was an absolutely terrible process, I hated it. And if the script I used for breaking the comma-separated strings into the columns stops working for some reason, then idk what I'm going to do. Step 3 above—making 8-9 additional columns just to trim the 1 [space] of whitespace that follows a comma—particularly annoyed me. I'm very open to any suggestions on how to drop this should-be simple task into something much shorter than a manually-run script, a new table, 19 new columns, and 9 automations totaling ~63 steps…

Terms & ConditionsAccessibility statement