Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Sales Total

Solved
Jump to Solution
2118 0
cancel
Showing results for 
Search instead for 
Did you mean: 
ARiios
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi! 

I think this is simple, but can't figure it out...

I have a base where I have a “Sales“ tab, here I have the date, client, and price sold. This tab has all the sales for the entire month. I can make a new “Daily view“ and filter by date, so now I can see only the current date. I can see totals here...

I need to have an automation send me the total sales for the DAY. I can get(find records) the “Daily View“ from the sales tab, but I can only see a list/grid with numbers for each sale... i want to see a TOTAL DAILY SALES.

Help! 

1 Solution

Accepted Solutions
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Hey @ARiios It's your lucky day - as I was reviewing my script I realized it was a little clunky, and found a way to truly automate so you receive a daily total without having to tweak anything each time. I recorded a quick Loom video to take you through step by step how it works. 

Updated Script (delete the previous one):

// Get Table
var salesTable = base.getTable("Daily Sales")
var salesDailyQuery = await salesTable.selectRecordsAsync({fields: ["Date", "Sale"]});
var salesDailyRecords = salesDailyQuery.records;
var salesTotal = 0;

// Get Today's Date
var today = new Date();
var todayDate = today.toISOString().split('T')[0]


// Loop through all records and add sales data IF matches today's date
for (var i = 0; i< salesDailyRecords.length; i++) {
     if (salesDailyRecords[i].getCellValue("Date") == todayDate) {
          salesTotal += salesDailyRecords[i].getCellValue("Sale")
     }
}

// Output daily sales total
console.log(salesTotal)
output.set("Daily Sales", salesTotal)

 

Automation Structure And Auto-Email:

(***Remember to update email and all variable names to match your own *** )

Screenshot 2023-10-31 115816.png

See Solution in Thread

7 Replies 7
Ella
7 - App Architect
7 - App Architect

How about a second table with a linked record to sales and a rollup field with the total daily sales. You can then email this field value where ever you wish.

ARiios
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi! 
Yes, I had something like this working now. It's a little manual since I have to add each sale per day. 
The Rollup option shows me all sales made that day, so I have to add each one 'by hand'.

Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Hey @ARiios got a quick automation you can run. Essentially it just goes through and sums the sales totals from your Daily View. Remember you will need to update with your Table names / views / fields / variable names to make it work for you. 

Basic base structure:

Screenshot 2023-10-30 200419.png

Basic automation structure:

Screenshot 2023-10-30 200438.png

Script:

var salesTable = base.getTable("Daily Sales")
var salesDailyView = salesTable.getView("Grid view  - Oct 29 2023 Sales")
var salesTotal = 0;

var salesDailyQuery = await salesDailyView.selectRecordsAsync({fields: ["Sale"]});

var salesDailyRecords = salesDailyQuery.records;

for (var i = 0; i< salesDailyRecords.length; i++) {
     salesTotal += salesDailyRecords[i].getCellValue("Sale")
}

console.log(salesTotal)

 

I ran it for myself in my example and salesTotal returned 80, as expected. Let me know if it works for you!!

Could you build an automation to do the linking for you? And then a second automation to send you the sum?

Thanks! 
Is there a way of sending the result via email in the same automation? 

I'm new with scripts!

Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Hey @ARiios It's your lucky day - as I was reviewing my script I realized it was a little clunky, and found a way to truly automate so you receive a daily total without having to tweak anything each time. I recorded a quick Loom video to take you through step by step how it works. 

Updated Script (delete the previous one):

// Get Table
var salesTable = base.getTable("Daily Sales")
var salesDailyQuery = await salesTable.selectRecordsAsync({fields: ["Date", "Sale"]});
var salesDailyRecords = salesDailyQuery.records;
var salesTotal = 0;

// Get Today's Date
var today = new Date();
var todayDate = today.toISOString().split('T')[0]


// Loop through all records and add sales data IF matches today's date
for (var i = 0; i< salesDailyRecords.length; i++) {
     if (salesDailyRecords[i].getCellValue("Date") == todayDate) {
          salesTotal += salesDailyRecords[i].getCellValue("Sale")
     }
}

// Output daily sales total
console.log(salesTotal)
output.set("Daily Sales", salesTotal)

 

Automation Structure And Auto-Email:

(***Remember to update email and all variable names to match your own *** )

Screenshot 2023-10-31 115816.png

Worked like a charm! 
Thanks @Arthur_Tutt !