Help

Linked records conundrum: substitute for vlookup?

Topic Labels: Base design
1984 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Justin_Eppley
6 - Interface Innovator
6 - Interface Innovator

I have a base where I have a table that is a list of product names. I have another table in the base which is a synced source of data from emails, where I have the same product names and a few other fields coming in. Basically, the goal of the exercise is to somehow indicate to the person creating a new record that the name they just created already exists in the synced table. Ideally, this would be a signal in another field on the table, such as a flag (already exists). In which direction do I need to do a linked record field? I can’t use the product name as the linked record because they may actually be creating a brand new one that doesn’t exist in the synced table, and that’s fine.

How do I pull in other fields from another synced table if the record name matches the other table? I can’t figure out how to arrange my fields.

Target table:
Product Name

Synced Table (the one I want to pull stuff from):
Product Name
Product Id

This feels exceptionally simple and I’m way overcomplicating something but I’ve been going in circles for too many hours now. :slightly_smiling_face:

Any thoughts?

1 Reply 1

Hi @Justin_Eppley ,

If I understand the problem correctly, you could do it in 2 ways - via an automation or via script.

1) Automation

When a record is updated - Lookup the values in the SyncTable and update record in Target table.
image

  • not using here record created - because human created record will be created as empty first

  • limit the trigger to react only to update of the Product Name field

  • use Find records to look up the name in the other table
    image

  • if there are records found, updates the fields you want with the “List of” values you want from Product table:
    image

You might encounter 2 issues with the approach above:

a) The Update record can also run while you are updating the Name field… if you take long time to type the name , the field might get updated half way and trigger the lookup
b) If the result of Find records returns multiple results, you will unfortunately get list of resulting values.

2) Script

You could

  • use automation above and instead of “Find records” - trigger a script - that would solve problem b) (by hardcoding some solution, e.g. select newest result)
  • use script as standalone extensions and trigger it by hand - that could solve issue a) and b), but needs to be manually triggered.

You could adjust code here to have it update the fields instead of linking:

I hope that helps!