Vlookup for Talent Matching in Base with two tables
0
1640
8
Hi everyone,
Goal: I want to match Companies to matching Talents based on different criteria (multiple).
What I did so far
-
Set up a base with two tables.
-
Table 1: Companies, containing Fields “Name”; “NecEducation” (necessary level of education); “Rel.postal.codes” (all postal codes within a 40 mile radius as proximity of talents to company is key in my example)
-
Table 2: Talents, containing Fields “Name”; “Education” (same choices as in field “NecEducation”) and “PostalCode” (just one value, referring to the postal code the talent lives in).
Now I would like for there to be a field next to each company, displaying links to each Talent that matches the necessary education and who´s postal code is within the “Rel.postal.codes” of the company.
Using @Greg_F ´s method here Compare two Tables (VLOOKUP on Excel) - I was already able to map single postal codes to single postal codes (not the range though!!)
I´m facing the following issues:
- How do I compute results based on multiple matching criterias (in my case “NecEducation” = “Education” and “PostalCode” contained within “Rel.postal.codes”)
- How do I get the code to not just compare the postal code of the talent to the postal code of the company but to look if the postal code of the talent is contained within the relevant postal codes of the company? (I tried @Justin_Barrett ´s solution of using the “index0f” function but I´m getting an error that index0f is not a function).
let mainTable = base.getTable("Companies");
let mainTableRecords = await mainTable.selectRecordsAsync();
let lookupTable = base.getTable("Talents");
let lookupRangeRecords = await lookupTable.selectRecordsAsync();
for (let record of mainTableRecords.records) {
let lookupValue = record.getCellValue("rel.postal.code.com");
for (let rangeRecord of lookupRangeRecords.records) {
if (rangeRecord.getCellValue("Postal.Code.Tal").indexOf(lookupValue) != -1) {
let linkID = rangeRecord.id;
mainTable.updateRecordAsync(record, {
"Talents": [{id: linkID}]
});
}
}
}
