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!
Best answer by Arthur_Tutt
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 *** )
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?
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 *** )
