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:
- gets concatenated with other fields to create a unique composite that is copied into a junction table to record that particular snapshot,
- 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
- 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.
