Skip to main content

Quickly Linking Records From Another Table

  • March 18, 2020
  • 5 replies
  • 35 views
R

I’m routinely linking a record from another table to match text in another field. Is there a way to do this fast/automatically?

For example, we have an Invoices table, with data that we import from our invoicing software, Harvest. From the imported data, we have a text field with client names. We then link the client names in a linked record field to client records, which live in another table. One of us has to do this manually, each month. Is there a way to use a formula or some other function to return a record from another table if it matches the text in another field or return something like '[NO MATCH]" if there’s no matching record in the other table.

5 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • March 19, 2020

Do the client names match exactly? If so, you can copy and paste from a text field into a linked record field, and the link is created automatically.

You could also look into the Batch Update block or the scripting block.

R
Justin_Barrett
R
  • Ryan_Juliano
  • Author
  • New Participant
  • March 19, 2020

Do the client names match exactly? If so, you can copy and paste from a text field into a linked record field, and the link is created automatically.

You could also look into the Batch Update block or the scripting block.


Great idea! The client names will mostly match exactly, but–if they don’t–I’d like to avoid automatically creating a new record in the other table and somehow call out “No Match” or something like that. Any suggestions?

Justin_Barrett
Forum|alt.badge.img+21

Great idea! The client names will mostly match exactly, but–if they don’t–I’d like to avoid automatically creating a new record in the other table and somehow call out “No Match” or something like that. Any suggestions?


That’s where a script in the Scripting block would come in really handy. It could check for exact matches and make links where they’re found, and provide some alternate notice if not.

R
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • March 19, 2020

Great idea! The client names will mostly match exactly, but–if they don’t–I’d like to avoid automatically creating a new record in the other table and somehow call out “No Match” or something like that. Any suggestions?


Option 1 (no script): Let the new unmatched records be created

  • Let new records be created for client names that don’t match.
  • Create a new formula field that will flag a client name as new based on a lack of info in some other field
  • Create a view that shows only the new client names, and deal with them manually

Option 2: Adapt an existing script

Adapt one of the existing matching scripts recently written for this contest, which has built-in logic for matching close-but-not-exact matches. One is by @JonathanBowen and the other is by @Jeremy_Oglesby.

These scripts are entries in a contest that is currently being judged, and I believe that community usefulness is part of the judging, so if you find them helpful, please indicate so on the contest page. (I’m hurting my own changes at winning the contest by promoting other entries, but I believe that great work deserves to be recognized.)

Option 3: Check for new client names before doing the linking

  • Write a script that will do the checking (possibly adapting one in option 2)
  • Make changes to the records (edit client name or create new client records) before doing the linking
R
JonathanBowen
Justin_Barrett
R
  • Ryan_Juliano
  • Author
  • New Participant
  • March 19, 2020

Option 1 (no script): Let the new unmatched records be created

  • Let new records be created for client names that don’t match.
  • Create a new formula field that will flag a client name as new based on a lack of info in some other field
  • Create a view that shows only the new client names, and deal with them manually

Option 2: Adapt an existing script

Adapt one of the existing matching scripts recently written for this contest, which has built-in logic for matching close-but-not-exact matches. One is by @JonathanBowen and the other is by @Jeremy_Oglesby.

These scripts are entries in a contest that is currently being judged, and I believe that community usefulness is part of the judging, so if you find them helpful, please indicate so on the contest page. (I’m hurting my own changes at winning the contest by promoting other entries, but I believe that great work deserves to be recognized.)

Option 3: Check for new client names before doing the linking

  • Write a script that will do the checking (possibly adapting one in option 2)
  • Make changes to the records (edit client name or create new client records) before doing the linking

:point_up_2: Brilliant! This will probably do it for us. (Also, sounds like it would be in my best interest to get Pro plan and some scripting chops.

J
Terms & ConditionsAccessibility statement