Skip to main content

Hello All,

I've been attempting to create a more efficient way for my organization to sort through volunteers and create a matching system to the elderly. So far, I've created two main tables: Volunteers and Old Friends that are populated by a use of forms. There are certain columns that are referenced in the forms controlled by other tables i.e. hobbies. Using Scripting in an attempt to create a system that can sort through the Volunteers table and Old Friends table to match based on the hobbies (to start, I plan on using other variables once I get this part working). Any help would be greatly appreciated!

Here are the links to the two tables:

Volunteers - https://airtable.com/app63KIAsdReM2VbZ/shrsqIBAQVIhCcfEA

Old Friends - https://airtable.com/app63KIAsdReM2VbZ/shrLDOcutrx5CNfjR

This is what I have from Scratch but I'm unsure where I am going wrong:

 

// Fetch the tables
let volunteersTable = base.getTable('Volunteers');
let oldFriendsTable = base.getTable('Old Friends');

// Fetch records from both tables
let volunteersQuery = await volunteersTable.selectRecordsAsync({
    fields: ["ID", "Hobbies"]});
let oldFriendsQuery = await oldFriendsTable.selectRecordsAsync({
    fields: ["Elder_ID", "Hobbies"]});

// Create arrays to store records from each table
let volunteersRecords = volunteersQuery.records;
let oldFriendsRecords = oldFriendsQuery.records;
 
// Array to store common hobbies matches
let commonHobbies = [];

// Compare records based on hobbies
for (let volunteer of volunteersRecords) {
    let volunteerHobbies = volunteer.getCellValue('Hobbies') || [];
    
    for (let oldFriend of oldFriendsRecords) {
        let oldFriendHobbies = oldFriend.getCellValue('Hobbies') || [];
        
        // Find common hobbies by converting hobbies to lowercase for case-insensitive comparison
        let common = volunteerHobbies.map(hobby => hobby)
                                    .filter(hobby => oldFriendHobbies.map(hobby => hobby).includes(hobby));
        
        if (common.length > 0) {
            commonHobbies.push({
                VolunteerID: volunteer.id,
                OldFriendID: oldFriend.id,
                CommonHobbies: common
            });
        }
    }
}

// Output the results
if (commonHobbies.length > 0) {
    output.table(commonHobbies);
} else {
    output.text('No matching records found based on hobbies.');
}

 

 

Hi,
You are trying to script action, that in fact belongs to the core Airtable functionality.
Imagine you have only these 2 tables, and no links, just text data.
First thing you can do -  create link field to a new table, that will be a table of hobbies

then copy-paste whole hobbies field into link field

Hobbies table will be auto-populated

Now you can do the same with 2nd table (link to the same Hobbies table).
The you will see matches and using lookups and rollups, get other info you need.

I would recommend to not use number ID as primary field. 
Primary field must be descriptive. You can use formula instead like 
TRIM(Name & " " & LastName)

trim is important otherwise you can get single space on a place looking like empty value, and then make mistake with filter 'when {field} is empty'
If you have several persons with the same Name and Last Name, add something else to make it unique. With unique primary field it's very easy to link tables.

Terms & ConditionsAccessibility statement