Vlookup for Talent Matching in Base with two tables

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

image

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}]
        });
   }
}
}

Regarding the indexOf() error, what type of field is “Postal.Code.Tal”? That will tell us why that error is being thrown.

Thanks you @Justin_Barrett - I belive you´re onto something for this part.

The field “postal.code.tal” is a number field containing a postal code (f.ex “10119”)

The field “rel.postal.code.com” is a long text field containing a range of postal codes separated by spaces. example dataset below. I´m using german postal codes and using this website: http://www.serp-perception.com/umkreissuche.php to search for all postal codes of a given company postal code in a 50 km radius. The idea is, that the “postal.code.tal” has to be contained within “rel.postal.code.com” for the talent to be relevant to the company.

10115
10117
10119
10178
10179
10243
10245
10247
10249
10315
10317
10318
10319
10365
10367
10369
10405
10407
10409
10435
10437
10439
10551
10553
10555
10557
10559
10585
10587
10589
10623
10625
10627
10629
10707
10709
10711
10713
10715
10717
10719
10777
10779
10781
10783
10785
10787
10789
10823
10825
10827
10829
10961
10963
10965
10967
10969
10997
10999
12043
12045
12047
12049
12051
12053
12055
12057
12059
12099
12101
12103
12105
12107
12109
12157
12159
12161
12163
12165
12167
12169
12203
12205
12207
12209
12247
12249
12277
12279
12305
12307
12309
12347
12349
12351
12353
12355
12357
12359
12435
12437
12439
12459
12487
12489
12524
12526
12527
12529
12555
12557
12559
12587
12589
12619
12621
12623
12627
12629
12679
12681
12683
12685
12687
12689
13051
13053
13055
13057
13059
13086
13088
13089
13125
13127
13129
13156
13158
13159
13187
13189
13347
13349
13351
13353
13355
13357
13359
13403
13405
13407
13409
13435
13437
13439
13442
13465
13467
13469
13503
13505
13507
13509
13581
13583
13585
13587
13589
13591
13593
13595
13597
13599
13627
13629
14050
14052
14053
14055
14057
14059
14089
14109
14129
14163
14165
14167
14169
14193
14195
14197
14199
14089,1358
10243,1024
10115,1011
13403,1340
10119,1024
12043,1204
10315,1031
10777,1077
10585,1058
12555,1261
16356
16548
12157,1216
16341
16567
16761
14513
15366
16562
14532
16540
14979
16547
15827
15831
15566
15370
16321
15345
16556
14612
15732
14624
16727
14974
15738
15569
16348
15537
15834
15562
16767
15745
16565
14461
14467
14469
14471
14473
14478
14480
14482
16515
16766
14552
14656
15711

Thanks for the detailed update. It looks like there are several issues at play here.

This explains the error message, and also points out an error in your logic. Here’s a generic example of how indexOf() operates:

a.indexOf(b)

This means that I want to look in a and get the index (position) of b. In your code, you’re looking at the postal code (a number) and trying to find the index of your full range of codes. The reason for the error is that numbers have no .indexOf() method (strings and arrays do, for example), but the bigger issue is that this is the inverse of what you actually want to do, which is look in the full list and find that single value. You also need to grab the postal code as a string because .indexOf() requires a string argument. With those changes, that line should be written like this:

    if (lookupValue.indexOf(rangeRecord.getCellValueAsString("Postal.Code.Tal")) != -1) {

That will perform the search correctly, but there are still some unrelated issues with the way your script is designed:

  • Updating a record in the main table with a found talent will currently overwrite any existing links to other talents because the update replaces field contents. It doesn’t keep what’s there and add to it.
  • Each update will occur one at a time, which could take a while. A more efficient setup would be to store the changes in an array, then use updateRecordsAsync to batch-update everything. This can only be done in groups of no more than 50, but the code to do that is pretty easy.
  • You’re missing the await keyword in front of the update line, which means that some updates might not get processed.

The search process can also be optimized by using the .filter() method on the array of records in lookupRangeRecords.

Here’s the full script updated with all of the above changes:

let mainTable = base.getTable("Companies");
let mainTableRecords = await mainTable.selectRecordsAsync();

let lookupTable = base.getTable("Talents");
let lookupRangeRecords = await lookupTable.selectRecordsAsync();

let recordUpdates = [];
for (let record of mainTableRecords.records) {  
    let lookupValue = record.getCellValue("rel.postal.code.com");
    let matches = lookupRangeRecords.records.filter(rangeRecord => lookupValue.indexOf(rangeRecord.getCellValueAsString("Postal.Code.Tal")) != -1);
    if (matches) {
        recordUpdates.push({
            id: record.id,
            fields: {
                "Talents": matches.map(matchRecord => {return {id: matchRecord.id}})
            }
        });
    }
}

// Update all records
while (recordUpdates.length > 0) {
    await mainTable.updateRecordsAsync(recordUpdates.slice(0, 50));
    recordUpdates = recordUpdates.slice(50)
}

Thank you very much @Justin_Barrett - this helps a lot.

I´m currently cleaning the data and implementing - I´ll update once I see if / how it works.

Any insights from your side on the second part of my question?

I need to not just map “rel.postal.code.com” to the “postal.code.tal” but also need to match talents by education as a second variable. E.g “nec.education” on the “company” table and “education” on the “talent table”

In the end, I only want the output of matching talents to be talents that are within the “rel.postal.code.com” and that have an equal to ANY value for “nec.education” in their “education” field. “education” is a single select field (a talent can only have one education) and “nec.education” is a multiple select field (hence, if a talent matches any of the nec.education field entries it would be matched)

So - Talent A that has Education E and has postal code 2 WOULD be mapped to Company B that requires nec.education E OR F and has postal codes 2,3,4,5,6,7 and 8.

Thank you again so much - you´ve been a lifesaver!

That would just be another level of filtering. The current filter on the talent records looks for a postal code match. Take those results and run a filter that looks for an education match. Here’s the script with those changes applied (including renaming some variables to be more clear about their contents):

let mainTable = base.getTable("Companies");
let mainTableRecords = await mainTable.selectRecordsAsync();

let talentTable = base.getTable("Talents");
let talentRecords = await talentTable.selectRecordsAsync();

let recordUpdates = [];
for (let record of mainTableRecords.records) {  
    let postalCodes = record.getCellValue("rel.postal.code.com");
    let eduOptions = record.getCellValueAsString("nec.education");
    let postalMatches = talentRecords.records.filter(talentRecord => postalCodes.indexOf(talentRecord.getCellValueAsString("Postal.Code.Tal")) != -1);
    let eduMatches = postalMatches.filter(talentRecord => eduOptions.indexOf(talentRecord.getCellValueAsString("education")) != -1);
    if (eduMatches) {
        recordUpdates.push({
            id: record.id,
            fields: {
                "Talents": eduMatches.map(matchRecord => {return {id: matchRecord.id}})
            }
        });
    }
}

// Update all records
while (recordUpdates.length > 0) {
    await mainTable.updateRecordsAsync(recordUpdates.slice(0, 50));
    recordUpdates = recordUpdates.slice(50)
}

Thanks again @Justin_Barrett - the amount of support you´re giving is incredible.

I have implemented your changes to the letter and the script is running but returning any talent matches.

Here´s my setup

Table 1 (“company”

  • Field 1 (single line text): “Name” = Name of company
  • Field 2 (multiple select): “nec.education” = different types of education that might be necessary for that company.
  • FIeld 3 (Number): “postal.code.com” = Postal code of the company
  • Field 4 (Long text) “rel.postal.code.com” = all relevant postal codes within a xyz km radius
  • Field 5 (linked field) “talents” = this is where the matches should be displayed (can be several)

Table 2 (“talents”)

  • Field 1 (single line text): “Name” = First and last name of talent
  • Field 2 (single select): “education” = education of the talent
  • Field 3 (number): “postal.code.tal” = talents postal code
  • Field 4 (linked field): “company”

This is my code:

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

let mainTableRecords = await mainTable.selectRecordsAsync();

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

let talentRecords = await lookupTable.selectRecordsAsync();

let recordUpdates = []

for (let record of mainTableRecords.records) {  

     let postalCodes = record.getCellValue("rel.postal.code.com");

     let eduOptions = record.getCellValue("nec.education");

     let postalMatches = talentRecords.records.filter(talentRecord => postalCodes.indexOf(talentRecord.getCellValueAsString("postal.code.tal")) != -1);

     let eduMatches = postalMatches.filter(talentRecord => eduOptions.indexOf(talentRecord.getCellValueAsString("education")) != -1);

     if (eduMatches) {

         recordUpdates.push({

             id: record.id,

             fields: {

                 "talents": eduMatches.map(matchRecord => {return {id: matchRecord.id}})

             }

         });

     }

}

while (recordUpdates.length > 0) {

    await mainTable.updateRecordsAsync(recordUpdates.slice(0, 50));

    recordUpdates = recordUpdates.slice(50)

}

Thanks again Justin - I truly appreciate your support. Somehow my answer was flagged as Spam so I´m trying again.

I implemented the code to the letter and it´s running wihtout errors - but no matches are returned.

This is my current setup:

Table 1 “company”

  • Field 1 (single line text) “Name” = Company Name
  • FIeld 2 (multiple select field) “nec.education” = all possible forms of education that could be necessary to have for a position at that company. A single education match of the sum of all possible educations is enough
  • Field 3 (number field) “postal.code.com” = the postal code of the company
  • Field 4 (long text field) “rel.postal.code.com” = all postal codes wihtin a xyz km radius of the company
  • Field 5 (linked field) “talents” = this should display all matching talents

Table 2 “talents”

  • Field 1 (single line text) “Name” = Talent first- and last name
  • Field 2 (single select) “education” = the talents education which should match to one of the nec.educations of the company
  • Field 3 (number field) “postal.code.tal” = the talents postal code which should match on of the values within “rel.postal.code.com
  • Field 4 (linked fiel) “company”

Again - I´m running the script and nothing happens at all - any ideas on what´s happening? are the values not being written? Also - on a sidenote - how do you best post your scripts here so the formating is right? :laughing:

This is my script:

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

let mainTableRecords = await mainTable.selectRecordsAsync();

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

let talentRecords = await lookupTable.selectRecordsAsync();

let recordUpdates = []

for (let record of mainTableRecords.records) {  

     let postalCodes = record.getCellValue("rel.postal.code.com");

     let eduOptions = record.getCellValue("nec.education");

     let postalMatches = talentRecords.records.filter(talentRecord => postalCodes.indexOf(talentRecord.getCellValueAsString("postal.code.tal")) != -1);

     let eduMatches = postalMatches.filter(talentRecord => eduOptions.indexOf(talentRecord.getCellValueAsString("education")) != -1);

     if (eduMatches) {

         recordUpdates.push({

             id: record.id,

             fields: {

                 "talents": eduMatches.map(matchRecord => {return {id: matchRecord.id}})

             }

         });

     }

}

while (recordUpdates.length > 0) {

    await mainTable.updateRecordsAsync(recordUpdates.slice(0, 50));

    recordUpdates = recordUpdates.slice(50)

}

Strange…I saw it just fine. Not sure why it would have flagged.

Anyway, I’ll definitely circle back to this, but I’m backlogged on a lot of other work right now. It may be a day or two before I can revisit this. If anyone else can jump in, feel free.