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": 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

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. :arrow_forward: 0: Object

  3. :arrow_forward: linkedRecordIds: Array(1)

  4. :arrow_forward: valuesByLinkedRecordId: Object

  5. :arrow_forward: (1) [Object]

  6. :arrow_forward: 0: Object

  7. :arrow_forward: linkedRecordIds: Array(1)

  8. :arrow_forward: valuesByLinkedRecordId: Object

  9. :arrow_forward: (1) [Object]

  10. :arrow_forward: 0: Object

  11. :arrow_forward: linkedRecordIds: Array(1)

  12. :arrow_forward: valuesByLinkedRecordId: Object

  13. :arrow_forward: (1) [Object]

  14. :arrow_forward: 0: Object

  15. :arrow_forward: linkedRecordIds: Array(1)

  16. :arrow_forward: valuesByLinkedRecordId: Object

  17. :arrow_forward: (1) [Object]

  18. :arrow_forward: 0: Object

  19. :arrow_forward: linkedRecordIds: Array(1)

  20. :arrow_forward: valuesByLinkedRecordId: Object

  21. :arrow_forward: (1) [Object]

  22. :arrow_forward: (1) [Object]

  23. :arrow_forward: (1) [Object]

  24. :arrow_forward:

here the output

  1. (1) [Object]

  2. :arrow_forward: 0: Object

  3. :arrow_forward: linkedRecordIds: Array(1)

  4. :arrow_forward: valuesByLinkedRecordId: Object

  5. :arrow_forward: (1) [Object]

  6. :arrow_forward: 0: Object

  7. :arrow_forward: linkedRecordIds: Array(1)

  8. :arrow_forward: valuesByLinkedRecordId: Object

  9. :arrow_forward: (1) [Object]

  10. :arrow_forward: 0: Object

  11. :arrow_forward: linkedRecordIds: Array(1)

  12. :arrow_forward: valuesByLinkedRecordId: Object

  13. :arrow_forward: (1) [Object]

  14. :arrow_forward: 0: Object

  15. :arrow_forward: linkedRecordIds: Array(1)

  16. :arrow_forward: valuesByLinkedRecordId: Object

  17. :arrow_forward: (1) [Object]

  18. :arrow_forward: 0: Object

  19. :arrow_forward: linkedRecordIds: Array(1)

  20. :arrow_forward: valuesByLinkedRecordId: Object

  21. :arrow_forward: (1) [Object]

  22. :arrow_forward: (1) [Object]

  23. :arrow_forward: (1) [Object]

  24. :arrow_forward:


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

Terms & ConditionsAccessibility statement