Completely stuck on filtering through and array

[Kim_Trager1]

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';
}); 
*/

[kuovonne]

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.

[Kim_Trager1]

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 []

[Stephen_Suen]

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

[Kim_Trager1]

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

[kuovonne]

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.

[kuovonne]

Kim_Trager1:

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

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

[kuovonne]

Kim_Trager1:

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

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.

[Kim_Trager1]

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]

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.