Exporting a formatted list of linked records

Hi @KyleS - you’re on the right lines and there’s going to be a few different ways to achieve what you’re after, but here’s one solution. If my table is like this:

my script is:

// table set up
let linkedTable = base.getTable('Peer Group Applications');
let rootTable = base.getTable('Groups');

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

let cellValue = group.getCellValue('Name');

output.text(`You have selected: ${group.name}`);

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

// filter these records to get those that match on group
let filtered = linkedQuery.records.filter(record => record.getCellValue('Group')[0]['id'] == group.id )

console.log(filtered);

// use map to format these records into an array of objects
// for output.table
let mapped = filtered.map(filteredR => {
    return {
        name: filteredR.getCellValue('Name'), 
        company: filteredR.getCellValue('Company'),
        role: filteredR.getCellValue('Role')
    }
})

console.log(mapped)

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

A few comments:

• you’ve picked the group you want to output, so what we’re trying to do is filter the list of applicants by this group attribute, i.e. include those that have it and exclude those that don’t. We use the filter method to do this:

record.getCellValue('Group')[0]['id'] == group.id

The filter test here is where the group id from the applicant record matches the group id from the group you picked initially. As Group on the applicants table is an array, we want to pick the first element on the array, using the [0] index, then get the id for this element. There’s an assumption here that the group linked field only has one link so we can use [0] without worrying about other links in this field. If you have multiple links (so a person could belong to more than one group), then we’d need to iterate over the group field too

• Once we’ve filtered to the set of records we want, we need to be format the result for the output table. We use map here to form an array of objects (one object for each applicant record)

• You can combine filter and map into a single block, so something like:

let result = linkedQuery.records.filter(record => {
    return record.getCellValue('Group')[0]['id'] == group.id
}).map(filteredR => {
    return {
        name: filteredR.getCellValue('Name'), 
        company: filteredR.getCellValue('Company'),
        role: filteredR.getCellValue('Role')
    }
})

but I find it makes it hard to figure out what is going on when you come back to a script after some time, so splitting the operations is sometimes a good thing.

• I always use console.log generously when developing scripts so that I can see the state of the data at different points. These can be removed later when you’ve got it working as you need.

The output from the above is:

If you’re interested in learning more scripting for use in Airtable, have a look at my site Airscript here:

Thanks so much @JonathanBowen!

That’s incredibly helpful and your approach simplifies it even further.

I think the filtering solution actually works better and theoretically negates the need for the “groups” table all together since you could run the script from an individual applicant record to pull all the other group members that are in the same group, simplifying things a little bit and better fitting how the table is actually used.

And thanks for pointing me to Airscript. I actually used that resource to build some other scripts, so thank you for that.

My last step is to output a list of the applicants’ “Linked role and company” field, which is stored in a long text field as RTF with markdown to generate a url created at record creation via automation. I could also re-assemble that same field in the script since I have each piece of data as well.

I’m able to get all the data into the array and output the table, but now I’m stuck on how I would create a markdown list of the links. This shows my limited knowledge of working with arrays so appreciate the help.

So lets say we have three fields (role, company, and url).

What I want to end up with is a list of values for each record in the array, ideally not within a table to ease copy and paste.

role1 @ company1
role2 @ company2
role3 @ company3

Right now I’m outputting the field in the table just so I can see the script is pulling the right records.

I’m using the filtering and mapping method you provided to produce the list of records.

Thanks!

I figured it out!

Had to do a crash course in arrays and loops, but this seems to work. Certainly probably not the cleanest, so feedback much appreciated. I am very obviously not an engineer.

//Script exports formatted listing of group participants to use in follow-up email
//script is from a record on the applicants table
let applicants = base.getTable('Peer Group Applications');
let startingRecord = await input.recordAsync('Choose a record', applicants);

//choose which grouping field to use for the query: all fields
//let groupSelect = await input.fieldAsync(
//'Which field would you like to use?', applicants);

//choose which grouping field to use: buttons
let groupNum = applicants.getField('Group #');
let newGroup = applicants.getField('New Group#');

let groupSelect = await input.buttonsAsync(
'Which group field would you like to use?',
[
    {label: 'Group #', value: groupNum},
    {label: 'New Group', value: newGroup},
]);

//set the selected group to use for the filtering and subsequent actions
let selectedGroup = startingRecord.getCellValueAsString(groupSelect);

//Generate array of linked applicants for the selected group
let queryResult = await applicants.selectRecordsAsync();

//Filter records based on group id
let filtered = queryResult.records.filter(record => record.getCellValueAsString(groupSelect) == startingRecord.getCellValueAsString(groupSelect));

//log to confirm correct records filtered
//console.log(filtered);

//use map to format the records into an array for output table
let mapped = filtered.map(filteredR => {
    return{
        'Name': filteredR.getCellValue('Full Name'),
        'Title': filteredR.getCellValue('Title'),
        'Company': filteredR.getCellValue('Company'),
    }
    });

//output table of filtered records in group
output.markdown(`Members of **${selectedGroup}**:`);
output.table(mapped);

output.markdown('**Formatted for Email:**');

//loop through each record and return the linked role and company field
for (let filteredR of filtered) {
let linkedRole = filteredR.getCellValue('Linked Role and Company');

output.markdown(`- ${linkedRole}`);
};

Output: