Matching on multiple values in two linked tables, and returning value from linked table

Hmm, assuming you’re trying to link the records together if their Partner, Territory & Business Line values are the same you could try using an automation for that, and I’ve set it up here for you to check out.  Not sure I understand what you’re trying to do though, so lmk if I got it wrong and I’ll see how I can help!

Thanks - Will look into this deeper today and see if it will do the trick. To expand a little on what I’m looking to do, I have two tables: Table 1 = Operations, Table 2 = Account Management Table

Each table contains these 4 fields (and many others): 1) Partner, 2) Business Line, 3) Territory, 4) Account Manager.
 

In the Account Management Table, all 4 fields are updated. 

In the Operations Table, we receive ~500 new operations each week and only the 1) Partner, 2) Business Line, 3) Territory are filled out. Account Manager is blank.
 

Current process is to manually look up Account Manager in the ‘Account Management’ Table and manually fill in the name in Operations Table.

I’m looking to automate this, by saying if these three fields match in both tables (Partner, Business Line, Territory) - then take the Account Manager from Account Management Table and auto-populate it into the Operations Table in the Account Manager field.

Thanks!

Oh the automation should work just fine for that I reckon

Hi, Todd

In general, you have two options.

First way is automation, and if you don’t want to make this copy-paste each time new data added, it is better choise. ​@TheTimeSavingCo instructions are good and clear (But be careful with “contains” and things like “inactive” contains “active”. sometimes it’s better to set “is”), the only thing you might want to change is automation trigger.
The checkbox way exists because if you program automation to launch when all three column are not empty (“When record matches condition”) and you fill them manually, it starts right after you enter first char in third column (and obviously it works wrong). In your case, if you bulk import these values, there is nothing to care about. Except that your automations run monthly limit is bigger that amount of records added monthly.
You can start from it, and later, if needed, try to change to something done in bulk by “Repeating group”. 
Btw, if you decide to stay with checkbox trigger, you can switch in on/off by bulk-pasting 1 or 0.


Second way - for manual linking, create formula field combining all three columns in something like
“Partner - Business Line / Territory”, similar in both tables. In Account mgmt table, make it the primary field. Now, when you copy-paste several records or the whole column to the linked field, it will link to respective records. Preferably, column must contain unique values (in Account mgmt), otherwise if there are duplicates it will always link to the first record from set, ignoring second.
Then you can add Lookup field in first table. Lookup is computed field, it based on the links you were set in Linked field. 
 

Thank you both for looking at this!!

I’m trying the automation path, and for some reason I just can’t get it - not sure what is happening. Here’s a walk through - I created two test tables in our Dev environment; 1) Test Grid View 2 & 2) Test POM Contact.

Each table contains the following fields; 1) Partner, 2) Territory, 3) Offer Type & 4) POM Contact.
The POM Contact contains a fifth field 5) Lookup POM Contact, which is my ‘trigger’ field.
 

I run my ‘trigger’, and it pulls the three records with the Lookup POM Contact checked - all good so far.

Now, I’m trying to set my ‘find records’ to only select records where the following fields match between the two tables: “Partner”, “Territory” and “Offer Type”. This is part 1 of where I’m getting stuck. I feel like I have this set properly, but it’s not returning results I’m looking for.
Setup: (Why is it showing ‘HBO’ below Partner in the screenshot below, will it only look for matches on HBO? If so, how do I get it to look for matches on all Partner values.

To make it simple, I kept the request to only match on “Partner” between the two tables. If “Partner” is “Partner” I’m thinking it should pull  pull the records. I’m getting zero results, and I think I should get either 2  records returned (POM Contact box checked, AND Partner matches between the two tables OR 3 records (checkbox doesn’t matter, and three Partners match between the two tables. 
No results with that run - maybe because ‘HBO’ doesn’t match between the two tables. But it should be matching on AMC & Apple TV+
 

Once I get this piece figured out, I can move on to match on “Offer Type” and “Territory” in addition to “Partner”.

After I match on all 3 fields, I want to automatically pull the value of “POM Contact” from the the POM Contact Table over to the “POM Contact” field in the “Grid View” table. 

In the tables above, you see that the first 3 rows (Todd Studio, Apple TV+ and AMC) match on partner name, offer type and territory in both tables. So, at the end of this automation (if successful) the POM Contact in the Grid View table should automatically update to:
Todd Studio - POM Contact = Zahra
Apple TV+ - POM Contact = Kevin
AMC - POM Contact = Sean

If you can see what I’m doing wrong with ‘find records’ it would be super helpful :). I was also having trouble with ‘Update’ Record step, but that may have been a result of not getting this step set properly.

No results with that run - maybe because ‘HBO’ doesn’t match between the two tables. But it should be matching on AMC & Apple TV+

Yeap, that's right.  Try it again with a record where there’s a corresponding ‘Partner’ value in the other table

---

(Why is it showing ‘HBO’ below Partner in the screenshot below, will it only look for matches on HBO? If so, how do I get it to look for matches on all Partner values.

That’s the value of the ‘Partner’ field in the triggering record and it’s displayed to you as an example / guide sort of thing, and so in this test it’s going to look for ‘HBO’ on all Partner values in the table you’re looking through

Automation Test is important, because you are starting from trigger and data you get used as Example to setup further steps, and if you changed anything significant in the middle, it is better to click Test again, starting from trigger.
But in your case, it’s simple - on trigger, you can select the record on your choice, or you pick random by clicking “Use suggested”. So, to look on the script behavior with “Apple TV+”, choose it when testing trigger and go further.

another important fact: after you test and enable it, automation works for a single record and launched when the record match condition. Records already checked, are not affected. Until you uncheck them and then check again.

Also, it seems like you need a better understanding how Links/Lookup works - read docs
 (example)
 

​​​​