Exporting a formatted list of linked records

[KyleS]

Hi All,

Looking for some guidance as I’m well outside my basic scripting depth.

Use case: I have a table of applicants each linked to a group. The groups are listed out in another table and then all the group’s applicants linked in the “group members” field. Each applicant has a field that I would like to compile into a list and email or display with markdown formatting. The goal is that I have a button on the Groups table that allows a user to “generate list” which outputs or emails them the formatted list of the 5-6 group members that can then be copied into an email.

Where I’m stuck is I am able to get as far as building an array of the ids for the linked applicants, but don’t know how to then use that array to retrieve the individual field values for each applicant from the applicants table.

Script so far is below. Also thanks to those whose scripts I already borrowed/mutilated to get this far.

This script is born because the field I’m trying to pull out is a custom hyperlink and while stored in a long text field with RTF, it’s not copy and paste-able into an email client. We also can’t create templates for the emails, otherwise I would have it compiled into an email and sent.

Thank you in advance for the help!

-Kyle

    //Script exports formatted listing of group participants to use in follow-up email

    let linkedTable = base.getTable('Peer Group Applications');

    let rootTable = base.getTable('Groups');

    //script is initiated from the group on the "groups" table

    let group = await input.recordAsync('Choose a record', rootTable);

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

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

    //Generate array of linked applicants for the selected group

    let linkedQuery = await linkedTable.selectRecordsAsync();

    let linkedRecordIds = (group.getCellValue('Group Members')).map(linkedRecord => linkedRecord.id);

    //Use each id and return the "Linked Role and Company" field from the "applicants" table

    //Output of group ids for testing

    output.table(

    linkedRecordIds

    );

[JonathanBowen · Accepted solution]

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:

Airscript

Home

An introduction to scripting on Airtable bases for new coders

[KyleS]

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!

[KyleS]

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:

[KyleS]

One further update. I removed the automation that was creating the “linked role” field and the script now does that as well to save on the automation runs for the base.