Skip to main content

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! 

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.


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.


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'.


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:

Basic automation structure:

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!!


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'.


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


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:

Basic automation structure:

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!!


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

I'm new with scripts!


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 *** )


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 *** )


Worked like a charm! 
Thanks @Arthur_Tutt !