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 = A];

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 = R];

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 = o]



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?

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 = e]



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)



}

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



let mainTableRecords = await mainTable.selectRecordsAsync();



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



let talentRecords = await lookupTable.selectRecordsAsync();



let recordUpdates = e]



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.