Help

Re: Completely stuck on filtering through and array

3863 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Im trying to expand the report example, to make my own custom report.
However I’m completely stuck on filtering an array of records that contains a specific name.

I’ve tried a few different things (commented out below), but I don’t seem to get it to work.

I have a table called ‘report’ in that base there is a field called ‘names’ and I want to filter all away all the records that does not contain the value ‘Dan’ in my names field.

I hope someone has time to help an aspiring JS developer.

let tableReport = base.getTable('report')
let filter = 'Dan'

//loadReportRecords
let reportRecords = await tableReport.selectRecordsAsync();

//let filteredRecords = reportRecords.getRecord(reportRecords.recordIds).getCellValue("names")


//let filteredRecords = reportRecords.records.filter(record => record.getCellValue("names")===filter);

/*
let filteredRecords = reportRecords.records.filter(function(reportRecord){
    return reportRecord.getCellValue('names') === 'Dan';
}); 
*/
15 Replies 15

It looks like you have tried several things with mixed success. Keep trying.

In Scripting block, the easiest debugging tool is to put an output.inspect() after every let statement and before every return statement. That way you can compare the variable’s value with what you expect and narrow down where the problem is.

This isn’t the pinpointing of the error that you want, but hopefully it is still helpful.

Thank you @kuovonne for the output.inspect(). What is the difference from this to a console.log?

I my problem is it they all return empty arrays, despite there is definitely a few values in names column that contain ‘Dan’.

let tableReport = base.getTable('report')

let filter = 'Dan'

//loadReportRecords

let reportRecords = await tableReport.selectRecordsAsync();

output.inspect(reportRecords)

let filteredRecords2 = reportRecords.records.filter(record => record.getCellValue("names")==='Dan');

output.inspect(filteredRecords2) //Output []

let filteredRecords3 = reportRecords.records.filter(function(reportRecord){

return (reportRecord.getCellValue('names') === 'Dan');

});

output.inspect(filteredRecords3) //Output []

@Kim_Trager1 What is the field type of the names field?

It’s a linked field - should that make a difference?
I have the feeling you’ll say ‘Yes’…

I also tried to filter based on a lookup of the name field and, still giving me an empty array

If you are searching for a name in a set of linked records, you cannot do a search for a specific name. The cell value is either a null (no linked records) or an array (that is possibly empty). Your filter needs to check if the cell value is null, and if it isn’t null to check the name value of each each element in the array.

Since it is a linked record, this will always return false, because the value of the cell is either a null or an array, never a string.

reportRecords.records.filter(record => record.getCellValue("names")==='Dan')

I recommend that you have a multi-line anonymous function and output.inspect the return value of the getCellValue function and play around with those results.

You’re really close.

The read value of a lookup is unstable. You can’t count on it being anything in particular.

output.inspect() is the same as console.log() The only difference is that output.inspect() is explicitly mentioned in the documentation, and console.log() is not.

Thank you @kuovonne

I’m trying my best, I’ve tried a few things again - I see what you’re saying with using id’s

It seems to me I need to load the id of the linked record. which I do in personID.
Then I need to access the id’s of the linked records in the names field and match and see where they === personID.

I think where I’m stranded is I can’t figure out how to get get id’s of the linked records in the name field.
I tried various things without luck and at the moment just trying to string things together without understanding what I’m doing, as everything I thought could work returns errors.

let tableReport = base.getTable('report')

let reportField = tableReport.getField('names') // need for getting ID of linked record

let tableContactView = 'Staff'

let tableContact = base.getTable('Contacts')

//Choose person to run report

let viewStaff = tableContact.getView(tableContactView);

let choosePerson = await input.recordAsync('Pick a person', viewStaff);

let personID = choosePerson.id; //have to use Id's when using linked records

output.inspect(personID)

//loadReportRecords

let reportRecords = await tableReport.selectRecordsAsync();

output.inspect(reportRecords)

let filteredRecords2 = reportRecords.records.filter(record => record.getField('names').id===personID);

output.inspect(reportRecords.reportField.id) //Output []

Error:
TypeError: record.getField is not a function
at reportRecords.records.filter.record on line 18
at main on line 18

Stephen_Suen
Community Manager
Community Manager

Hey @Kim_Trager1:

The return type of a linked record field is an array of linked record objects, which look like {id: string, name: string}.

So in order to compare a linked record against your filter, you need to index into the array. You could try something along the following lines:

let reportTable = base.getTable('report');
let namesField = tableReport.getField('names');
let contactsTable = base.getTable('Contacts');
let staffView = tableContact.getView('Staff');

let personRecord = await input.recordAsync('Pick a person', staffView);
let personId = personRecord.id;

let reportQuery = await reportTable.selectRecordsAsync();
let reportRecordsForPerson = reportQuery.records.filter(record => {
    // Arrays in JavaScript have a method called some, which returns
    // true if at least one element in the array matches a condition
    return record.getCellValue(namesField).some(nameRecord =>
        nameRecord.id === personId
    );
});

output.inspect(reportRecordsForPerson);

I’m making the assumption that names is a linked record field that links to the Contacts table, let me know if this is not the case.

Hi @Stephen_Suen I hope one day I’ll get good at this.

It sounds to me your assumtions are right. However I still get the error saying:
TypeError: reportQuery.filter is not a function

@Kim_Trager1

reportQuery in your case is an Object - the Object has an array inside of it called records. That’s what you are wanting to filter. To access the records you queried, you need to pull them out of the query Object with reportQuery.records.

So you want:

let reportRecordsForPerson = reportQuery.records.filter(record => {
    ...
});

Ah yes, that was my mistake — I’ve updated the snippet @Kim_Trager1

Thank you @Jeremy_Oglesby for pointing out the obvious and make me understand…

I should have tried something like that considering my reportQuery output a bunch of objects.

Thank you @Stephen_Suen and thank you for introducing me to the some() method…

Kathy_Benson
6 - Interface Innovator
6 - Interface Innovator

I had a similar problem and I changed from getCellValue to getCellValueAsString which helped me. I’m not sure I fully understand your use case, but I wanted to offer this potential suggestion,.