Get a value from another table based on conditions.

[ArthurLapeyrère]

Hi there, 

I'm trying to get value on my main table from another table based on conditions I've defined in the main table and that will meet the parametres from a second table.

I've worked with thw ways in order to tackle that issue. First is to write a formula able to do that. 

IF(AND(Table1.UOs = Sync.UOs,Table1.SP=Sync.SP),Sync.Name)

The other way investigated is to go through script like the one below. Still working on it to include to loops for the two conditions to meet.

let mainTable = base.getTable("Table1");

let mainTableRecords = await mainTable.selectRecordsAsync({fields:["UOs","SP","Test Script 2"]});

let lookupTable = base.getTable("Sync");

let lookupRangeRecords = await lookupTable.selectRecordsAsync({fields:["Nom","UOs","SP"]});

for (let record of mainTableRecords.records) {  

     let lookupValue = record.getCellValue("UOs");

    for (let rangeRecord of lookupRangeRecords.records) {

        if (rangeRecord.getCellValue("UOs") === lookupValue) {

            let returnValue = rangeRecord.getCellValue("Nom");

            await mainTable.updateRecordAsync(record, {

                "Test Script 2": returnValue

            });

       }

    }

}

Not sure if the first apporach can work (that will be my favorite).
Otherwise, the script option could definiletly work but i'm facing issue with "multiple select" and the two conditions to include.

If someone can help, that will be awesome !

Best regards,
Arthur

[TheTimeSavingCo]

Hm, may I know what issues you faced when attempting to use automations to link the records together and then using a lookup to display the field you want?

For example, you could create a linked field between the two tables and an automation that:
- Triggers when a new record is created in the table "Sync", 
- First action: "Find Records" where the "Sync" record's "UOs" value matches the "UOs" value in "Table 1", f
- Second action: "Update Record" action to link all of the found records from "Table 1" to the triggering record in "Sync"

And once you had that in place you could create a lookup field in "Table 1" for the field "Nom", which should do what you're looking for?

[ArthurLapeyrère]

Thanks Adam for your help.

I'm trying to use your proposal.

In the "Update record" section and specifically in the Record ID option, i'm not sure what to use.

Best 

[TheTimeSavingCo]

In your "Update Record" action, you'll need to change the table to "Sync".  The option you've selected for the record ID is correct

If you private message me an invite to your base I can set this up for you real quick