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.
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