How to output a list of cell values with Scripting automation

[Webs]

Hello - I have another frustrating issue with regard to using the output.set function.

I am able to access a list of records from a query using  output.set("Record List", query.recordIds), but as expected it only outputs a  list the record ID strings.

What I want the output.set list to output is a list of values from the query result.  Put differently, I am trying to build a list of records, and have the output  be a list of the cell values.

Once I have that list, I plan to pass it to a repeating group automation step.

I am not understanding what I need to do to prep output.set to output a list of cell values.

// query for every record in "User Accounts"
let table = base.getTable("User Accounts");
let view = table.getView("Unlinked Member Script");
let query = await view.selectRecordsAsync({fields:["User ID","User Login"]
});

//this outputs the list as record ID string;
output.set('Member List',query.recordIds);

//I want the output to be a list of User Login strings

[TheTimeSavingCo · Accepted solution]

You'll need to loop through your record IDs, use `.getCellValue` on each of them, and push that into an array for use in your output.set

[Webs]

Thanks for the reply. I had a suspicion that was going to be the case. With regard to using air table scripts, what type of loop makes the most sense. ForEach, while, do, etc. I’m not a programmer by profession and it’s a challenge to wade through. All the JavaScript options and then layer on the quirkiness of air table scripting. 

[Sho]

Hi @Webs,

For example, like this.

const memberList= query.records.map(record => ({
    "RecordID": record.id,
    "UserID": record.getCellValueAsString("User ID"),
    "UserLogin": record.getCellValueAsString("User Login")
}));
output.set("Member List",memberList);

[kuovonne]

There are many ways to loop through records, but no one best way for everyone. The best method for you is one that you understand and can maintain.

If you are interested in learning scripting, I suggest trying writing it several different ways, then see which method you like best. 

If you are not interested in learning scripting and just want a working automation, consider outputting only the list of record IDs. Then use a “Find Records” action inside the loop based on the record ID. You may need to create a new formula field that displays the record ID. You can then grab the cell values from the “find records” action. Sometimes I prefer this method even though I am comfortable writing code because with this method, changes to field names will not break the automation, and the fields will show up in the native dependency checker. 

[Webs]

Thank you for providing an example.  

[Webs]

This is helpful - especially the alternative solution you mentioned that is not sensitive to future changes to field names.  I hadn't thought about that.

[Webs]

For reference to anyone who stumbles upon this post, this is the script I ended up using.  So far it seems to be working well.  I used the for loop because it seemed to be the most straight forward way to do it.

// query for records in "User Accounts"
let table = base.getTable("User Accounts");
let view = table.getView("Unlinked Member Script");
let query = await view.selectRecordsAsync({fields:["User ID","User Login","Account Number"]
});

//create an array
let accountList = [];

//get a cell value and push to the array
for(let i = 0; i<query.records.length; i++){
accountList.push(query.records[i].getCellValue("Account Number"));
};

//set output to the new array
output.set("Member List", accountList);
console.log(accountList.length);

[kuovonne]

If you are writing this script as an exercise in learning scripting, or if you have additional complex logic that is not shown in your script, feel free to ignore this message.

It seems that your new script is getting the records in the view, and then outputting an array of account numbers. As long as you have less than a thousand records in the view, you can get this information without scripting. You can use a "Find Records" action that will find all the records in the view. You can then get a list of values for a single field from the "Find Records" action. It used to be that "Find Records" action was limited to only 100 records, but that was recently increased to 1000 records.

[JeffJohnVol]

Thanks for sharing your code.  That is always appreciated, as I did stumble on this while searching for this answer.

So to ask the braintrust, when we do this below, we still have to do the getcellvalue thing?  I thought it might build an array in the input.config() method?  Or is the getcellvalue pulling from the input.config?  I thought the getcellvalue might be pulling from the airtable grid??