Am I asking too much of AT?: Some strings copied into linked records field not linking to their counterparts

In my [Workers] table, I just copied 6,000 cells from formula field {All employers for automation} to a link records field {All employers new} which is linked to the table [Employers]. All of the text strings in {All employers for automation} were taken directly from a linked records field {Temp employer} which is also linked to [Employers].

5,700 of them went through with no problem and linked back with the original record in [Employers] no problem. About 300 of them, though, failed to link and created a new record with the same name in [Employers].

Why would that be? Am I just asking too much of AT? (to be precise, i did this for three columns at the same time, totaling 18,000 records copied and linked. the other items were serial numbers and matched perfectly with their counterpart).

The applicable structure of the table is

{Temp employer} linked to [Employers]
{Temp empID} lookup of {Temp employer} “orgID”
{All employers for automation} returns a unique array of {Temp employer} and {All employers new}
{All employers new} linked to [Employers]
{All empIDs} lookup of {All employers new} “orgID”

The formula for {All employers for automation} is:

IF({All employers new},IF(FIND({Temp empID},{all empIDs})=1,{All employers new},{All employers new}&’,’&{Temp employer}),{Temp employer})

You’ll notice that the empID in {Temp empID} doesn’t match to {all empIDs} for Sierra Telephone, whereas it does for L Leon. When I copied from {All employer for automation} to {All employer}, most text strings acted like L. Leon and found their pair in [Employers]. A small percentage didn’t. When I copy directly from {Temp employer} to {All employers}, as I did in the first occurrence of Sierra Telephone, it finds the match. But when I copy from {All employers for automation}, it doesn’t.

Furthermore, when I apply the dedupe app in [Employers], the dedupe app does not recognize the duplication, but if I copy and paste “Sierra Telephone” into the filter in [Employers], both records show up. I’m confused.

To get a little deeper, the reason I am doing this is that we have reps filling out forms in the field that connect workers (many) to employers (many). My way out of this many to many relationship is to have the agents fill out a placeholder field called {Temp employer}, which:

  1. gets concatenated with other fields to create a unique composite that is copied into a junction table to record that particular snapshot,
  2. then gets copied into {All employers new} along with all of the other employers this worker has worked for via the formula field {All employers for automation}, and
  3. gets deleted… to be born anew

I can’t see anything that differentiates the employers that got matched up from the ones that didn’t. My workaround is to make the PK in [Employers] a concatenation of the employer name and the orgID. that should resolve it, but would love to understand why this is happening.

Indeed, and my only comment (in advance of some linking experts here in the community) is to say that this is a really good narrative. Thank you for being thorough and detailed - this is the poster-child for how you ask for help resolving complex issues!

2 Likes

Thanks for the input, @Bill.French. I’m blushing :blush:

The good news is my workaround seems to be working fine. Would still love to get to the bottom of this mystery.

When copying text into a linked record field, if the text has a comma in it, Airtable thinks that you want to create multiple linked records.

This support article has more info on how Airtable deals with commas when pasting into linked record fields, including how to deal with primary field values that contain commas.

1 Like

that’s not it. none of the strings pasted to the linked field had commas at the time of pasting.

those commas in the formula field are because AT is reading “Sierra Telephone” and “Sierra Telephone” as two different entities, even though they consist of the exact same series of characters

to clarify my original post, when I copied from {Temp employer} to {All employers} in the first record, it returned the Sierra Telephone with “org1368”. But when I copied from {All employers for automation} to (All employers} in that same record (same text and no commas), it returned the Sierra Telephone with “org2323”. And if I were to delete the Sierra Telephone org2323 record in [Employers] so that there is only one Sierra Telephone (org1368), when I copied from {All employers for automation} it would create a new Sierra Telephone rather than link to the old one.

And the strange thing is that when I copied that same string of text using the “is” condition to filter records in [Employers], it would show both occurrences, whereas dedupe wasn’t matching them at all (not even fuzzy or similar).

Thanks for reporting back.

Another possibility is that there is a hidden leading or trailing space in one of the names.

i tested that in several ways, and found no leading or training spaces.

plus, i’m copying from a formula field, and that formula was directly taking the text from the original linked record, so there should be no place for it to acquire extra characters.

This is indeed a puzzling case. I don’t have any other ideas without a closer look at the exact data. Perhaps someone else will chime in.

It looks like you have already looked at the top culprits for difficulties when copying/pasting into linked record fields. I’m summarizing them here just for future reference for anyone else reading this thread.

  • commas (in either pasted value or the record in the linked table)
  • hidden leading/trailing spaces (in either pasted value or the record in the linked table)
  • the pasted value is not unique in the linked table
  • the primary field in the linked table is a computed field (only a problem when the pasted value does not yet exist in the linked table)
1 Like

ok. thanks for playing :laughing:

everything else in AT i’ve been able to figure out. this one :man_shrugging:

OMG. Total beginner error. :man_facepalming:

I’m sorry @kuovonne, you were totally right. There was a trailing space in the original record in the ones that were duplicated. I kind of panicked and drowned :man_facepalming:again.

I guess the formula trimmed the original record automatically. I didn’t imagine it would do that.

OK. I’ll go back into my corner now :grin:

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.