Running Total

[ARiios]

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! 

[Ella]

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]

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]

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

[Ella]

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

[ARiios]

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

I'm new with scripts!

[Arthur_Tutt · Accepted solution]

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

[ARiios]

Worked like a charm! 
Thanks @Arthur_Tutt !