Help

Exporting formatted list if all records not linked

Topic Labels: Scripting extentions
823 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Lea_Castonguay
4 - Data Explorer
4 - Data Explorer

Continuing the discussion from Exporting a formatted list of linked records:

Hello everyone !

I’m trying to find a solution to my problem but I think it’s beyond my capabilities. I’m learning JavaScript, so I’m able to take a script that’s already written, and modify it minimally, but in this particular case, I can’t figure out how to make it work.

I integrated this script into an equipment rental database, and managed to get it to work but only under one particular condition.

In my assets (“Types”) table, some assets are linked to a rental (“Locations” table) but some are not. The script only works if all the assets are linked to a rental. It breaks if the first asset (position [0] of the array) of the list does not have a linked record.

I figured it was not working because the field was not populated, so I added a new rental to all the other assets to see if it worked and it did.

The problem is that I have a list of over 700 assets, and they aren’t always all rented at the same time. I would like to find a way for the script to skip over assets that are not linked, and display only items that are linked.

// table set up
let linkedTable = base.getTable('Types');
let rootTable = base.getTable('*Locations*');

// pick the Reservation you want
let locationChoisie = await input.recordAsync('Choose a record', rootTable);

let cellValue = locationChoisie.getCellValue('Types');

// get *all* records from the linked table
let linkedQuery = await linkedTable.selectRecordsAsync();
// console.log(linkedQuery)

// filter these records to get those that match on group
// Here the script doesn't get past the [0], if it doesn't have a linked rental:
let filtered = linkedQuery.records.filter(
  (record) => record.getCellValue('Locations')[0]['id'] == locationChoisie.id
);

// console.log(filtered);

// use map to format these records into an array of objects
// for output.table
let mapped = filtered.map((filteredR) => {
  return {
    asset: filteredR.getCellValue('Type'),
    category: filteredR.getCellValue('Catégorie du type'),
  };
});

// console.log(mapped)

//output the array of objects
output.table(mapped);

Could someone help me with this problem ?

Sorry for my english, french is my first language.

Thanks a lot!

Lea

1 Reply 1

If there is no linked record, the cell value is null, not an array, which is why trying to get the first element of the array throws an error.

You need to test if the cell value is null before trying to access the array.

There are many ways to do this. Here is one to show you what I mean.

let filtered = linkedQuery.records.filter(
  (record) => {
  // store the cell value in a variable
  const cellValue = record.getCellValue('Locations')
  if (cellValue) { 
    // cell was not empty
    return cellValue[0]['id'] == locationChoisie.id
  } else {
    // cell was empty
    return false 
  }
});