Skip to main content

Hello the community,

I would like to add a return value condition in the vlookup script depending on another column containing a specific text.


your support is really appreciate.

Thank you


Here my current script that I would like to improve:


let mainTable = base.getTable(“Inscriptions”);

let mainTableRecords = await mainTable.selectRecordsAsync();


let lookupTable = base.getTable(“Ateliers”);

let lookupRangeRecords = await lookupTable.selectRecordsAsync();


for (let record of mainTableRecords.records) {

let lookupValue = record.getCellValue(“Promo”);


for (let rangeRecord of lookupRangeRecords.records) {

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

if (“new column”) is containing this specific text "A1"

let returnValue = rangeRecord.getCellValue(“Date”);


        await mainTable.updateRecordAsync(record, {

            "Atelier 3": returnValue

        });

   }

}

}

HI.

your condition will be like

if (someRecord.getcellValue(‘new_column’).includes(‘sometext’))


if you new column type is not a regilar string (e.g. it’s a link, lookup, single/multi select), use getcellValueAsString

HI.

your condition will be like

if (someRecord.getcellValue(‘new_column’).includes(‘sometext’))


if you new column type is not a regilar string (e.g. it’s a link, lookup, single/multi select), use getcellValueAsString


Hello Alexy,


thanks for your feedback. it works but issue I have now is concerning the return value.

By this code:


let mainTable = base.getTable(“Questionnaire”);

let mainTableRecords = await mainTable.selectRecordsAsync();


let lookupTable = base.getTable(“Participants”);

let lookupRangeRecords = await lookupTable.selectRecordsAsync();


for (let record of mainTableRecords.records) {

let lookupValue = record.getCellValue(“Adresse e-mail”);


for (let rangeRecord of lookupRangeRecords.records) {

    if (rangeRecord.getCellValue("Adresse e-mail") === lookupValue) {

        let returnValue = rangeRecord.getCellValueAsString("Nom de la promotion");

 

        await mainTable.updateRecordAsync(record, {

            "nomdelapromo": returnValuee0]

        });

   }

}

}

my return value is only the number “7” instead to “7-201020-PM”. Only first letter is considered.


is there any reason of ?


Best regard

Hello Alexy,


thanks for your feedback. it works but issue I have now is concerning the return value.

By this code:


let mainTable = base.getTable(“Questionnaire”);

let mainTableRecords = await mainTable.selectRecordsAsync();


let lookupTable = base.getTable(“Participants”);

let lookupRangeRecords = await lookupTable.selectRecordsAsync();


for (let record of mainTableRecords.records) {

let lookupValue = record.getCellValue(“Adresse e-mail”);


for (let rangeRecord of lookupRangeRecords.records) {

    if (rangeRecord.getCellValue("Adresse e-mail") === lookupValue) {

        let returnValue = rangeRecord.getCellValueAsString("Nom de la promotion");

 

        await mainTable.updateRecordAsync(record, {

            "nomdelapromo": returnValue 0]

        });

   }

}

}

my return value is only the number “7” instead to “7-201020-PM”. Only first letter is considered.


is there any reason of ?


Best regard


add line to debug, and show output


if (rangeRecord.getCellValue(“Adresse e-mail”) === lookupValue) {

let returnValue = rangeRecord.getCellValueAsString(“Nom de la promotion”);

output.inspect(rangeRecord.getCellValue(“Nom de la promotion”))

here the output




  1. (1) [Object]




  2. ▶ 0: Object




  3. ▶ linkedRecordIds: Array(1)




  4. ▶ valuesByLinkedRecordId: Object




  5. ▶ (1) [Object]




  6. ▶ 0: Object




  7. ▶ linkedRecordIds: Array(1)




  8. ▶ valuesByLinkedRecordId: Object




  9. ▶ (1) [Object]




  10. ▶ 0: Object




  11. ▶ linkedRecordIds: Array(1)




  12. ▶ valuesByLinkedRecordId: Object




  13. ▶ (1) [Object]




  14. ▶ 0: Object




  15. ▶ linkedRecordIds: Array(1)




  16. ▶ valuesByLinkedRecordId: Object




  17. ▶ (1) [Object]




  18. ▶ 0: Object




  19. ▶ linkedRecordIds: Array(1)




  20. ▶ valuesByLinkedRecordId: Object




  21. ▶ (1) [Object]




  22. ▶ (1) [Object]




  23. ▶ (1) [Object]




  24. ▶



here the output




  1. (1) [Object]




  2. ▶ 0: Object




  3. ▶ linkedRecordIds: Array(1)




  4. ▶ valuesByLinkedRecordId: Object




  5. ▶ (1) [Object]




  6. ▶ 0: Object




  7. ▶ linkedRecordIds: Array(1)




  8. ▶ valuesByLinkedRecordId: Object




  9. ▶ (1) [Object]




  10. ▶ 0: Object




  11. ▶ linkedRecordIds: Array(1)




  12. ▶ valuesByLinkedRecordId: Object




  13. ▶ (1) [Object]




  14. ▶ 0: Object




  15. ▶ linkedRecordIds: Array(1)




  16. ▶ valuesByLinkedRecordId: Object




  17. ▶ (1) [Object]




  18. ▶ 0: Object




  19. ▶ linkedRecordIds: Array(1)




  20. ▶ valuesByLinkedRecordId: Object




  21. ▶ (1) [Object]




  22. ▶ (1) [Object]




  23. ▶ (1) [Object]




  24. ▶




Hi,

you should expand it, find which value is different from expected and analyse - why.

Reply


Terms & Conditions