Need Help with Dynamic Rollups for Sales by Country Without Predefined Countries

[nelsonestrada]

Hi Airtable friends,

I'm new to Airtable and really loving it. But currently stuck on something that seems simple. I'm looking for a way to automate my sales summary by country without having to predefine the countries in my Airtable base.

I have a sales order table with detailed information, including the quantity of products sold and the country they were shipped to. I need to create a separate table that dynamically summarizes the total sales by country.

Challenge:

• The "Total Sales by Country" table should update automatically as new sales data comes in.
• I cannot predetermine the countries since new orders may come from previously unlisted countries.

Current Setup:

• My orders table includes fields for order number, customer name, quantity purchased, shipping city, shipping country, and product.
• I have created an example of what the summary table should look like with hardcoded data, but I need this to be generated automatically.

Request:

• How can I set up a rollup or a summary table that automatically pulls in new countries as they appear in the sales data?
• What formulas or automations can I use to ensure that the "Total Sales by Country" table will reflect real-time data and include all countries from the sales order table?

I have attached screenshots of my current tables for reference.

Example product sales table:

Expected resulting table:

Btw, the grouping function that Airtable has is great, but doesn't give me the flexibility i want from the resulting totals. I'd like to sort the total sales to see which countries are selling the most/least.

Any advice or guidance on how to set this up would be hugely appreciated!

Thank you for your help!

[Valentino_Escal]

Hi @nelsonestrada 
There is so many avenues you can take to achieve what you want. I suggest recommendations based on some of your points. My main suggestion would be to create linked fields and automate when it's possible.

Question The "Total Sales by Country" table should update automatically as new sales data comes in.

Answer: You should create a linked field with Total Sales by Country instead of using a single line text. This can be automated so every time a new entry is made, it links it. Or you can do this manually.

Question: I cannot predetermine the countries since new orders may come from previously unlisted countries.

Answer: If you create a linked field, this can be done manually when new orders come in. I would create a list of all the main countries you deal with in the "Total Sales by Country" table.

Question: What formulas or automations can I use to ensure that the "Total Sales by Country" table will reflect real-time data and include all countries from the sales order table?

Answer: If you create a linked field, this will solve the issue.

[nelsonestrada · Accepted solution]

Hey @Valentino_Escal!

Thanks for taking the time to respond. I previously attempted the linked record approach, but I couldn't find a way to dynamically create entries for new countries without pre-determining them. Ideally, the solution should work for any new set of countries and be adaptable to other groupings where I don't have a preset list.

Eventually, I crafted a script that triggers whenever a new record is created. This script updates the "Total Sales by Country" table by either updating existing country records or creating new ones when they don't exist:

Here is the complete script:

// Define tables and queries
let salesTable = base.getTable("Product Sales");
let summaryTable = base.getTable("Total Sales by Country");
let salesQuery = await salesTable.selectRecordsAsync();
let summaryQuery = await summaryTable.selectRecordsAsync();

// Object to track total quantity per country
let countryTotals = {};

// Sum quantities from the Product Sales table
for (let record of salesQuery.records) {
    let country = record.getCellValue("Shipping - Country");
    let quantity = record.getCellValue("Quantity");

    // Skip records without a specified country
    if (!country) continue;

    // Convert quantity to a number and add to country total
    countryTotals[country] = (countryTotals[country] || 0) + Number(quantity);
}

// Update the Total Sales by Country table
for (let country in countryTotals) {
    let existingRecord = summaryQuery.records.find(
        record => record.getCellValueAsString("Country") === country
    );

    // Update or create the country record with the new total
    if (existingRecord) {
        await summaryTable.updateRecordAsync(existingRecord.id, {
            "Total Quantity": countryTotals[country]
        });
    } else {
        await summaryTable.createRecordAsync({
            "Country": country,
            "Total Quantity": countryTotals[country]
        });
    }
}

// Log output to console (optional)
console.log("Total Sales by Country updated successfully.");

It may not be the most refined approach, and I'm curious about its performance at scale, but it's operational for now!

I hope this can be of assistance to others too!

Best, Nels

[Valentino_Escal]

@nelsonestrada 

Happy you found a solution that works 👌