get # of unique values within automations

[Melagence]

Hi,

I'm struggling a bit with a slack automation. the idea: we have a list where all new customer appointments go in. Every day at a certain time the automation pushes the total number of appointments into a slack message. Now we have customers that make more than one appointment and we would like to also understand the number of customers. It would need something like: length of records unique but I cannot find anything like this.

thanks a lot for your support!

[Rupert_Hoffsch1]

Hi @Melagence, getting the total number of appointments in the customer appointments table will require a script (only pro / enterprise) currently (or all records would need to be linked to a single record in a different table to use a roll up). The script is very easy though and could work like this: 

const table = base.getTable("your table")
const records = await table.selectRecordsAsync().then(result => result.records)
const howManyRecords = records.length;

output.set("recordsTotal", howManyRecords)

This would output the total number of records in that table. For the unique number of customers, you could grab records based on a view that you created in the Airtable frontend. In the script above, instead of using await table.select... you would first grab the view and then select records based on that view.

Hope that helps 🙂

Best
Rupert

[Melagence]

thanks a lot! that's clearly the right direction. unfortunately I have no clue of scripts. I would need to get the number from the view SS24 in the table appointments. could you help me getting the view into the script?

thanks again for your help!

[Melagence · Accepted solution]

ok. I asked chatGPT to alter the script and it works perfectly now!

const table = base.getTable("Table Name");
const view = table.getView("View Name");
const records = await view.selectRecordsAsync({ fields: ['Title'] }).then(result => result.records);

// Extract unique values from the "Title" field
const uniqueValues = [...new Set(records.map(record => record.getCellValueAsString('Title')))];
const numberOfUniqueValues = uniqueValues.length;

output.set("numberOfUniqueValues", numberOfUniqueValues);

[Rupert_Hoffsch1]

Glad you got it working!