I’m new to coda and want to aggregate and summarize data which I then send in JSON format to create a weekly summary report. The problem I’m running into is that I’m unable to summarize multiple line items into a unique list with a count of each.
I found a help page on the community examples that looks promising, but I can’t access the formulas which renders the community example pretty useless to me and I’ve asked multiple times to access the example but nothing happens. So please help!
I’ve outlined exactly my setup and how I currently do the calculations etc. in this shared doc:
Please help! I’m convinced that it must be the forEach() formula, but I simply can’t get it right as a beginner and the available tutorials assume that you already know a lot of things which makes it hard to use.
Thanks Troy. This is super (and I’ll have to spend a bit of time trying to make sense of it). My payload adds other data as well. So let me see if I can make it work on the actual page.
Thank you!
PS. Would also be curious to see what prompt you would use to generate the AI.
Why did you group the data? The grouping is what I first tried, but that doesn’t allow me to work with the data so I find that wasn’t very useful for me as I can’t use the data, just look at it inside Coda. It also looks as if the formula doesn’t reference this view at all? So it’s a bit confusing to understand what you were doing and why (and the formula you used is way above a beginner’s abilities to understand).
Another stupid question, but I can’t get the formula working on the actual data doing it myself (and my goal is really to be able to understand what I’m doing so that I can do it myself) as my payload also includes other things which I’m doing in a slightly different way (like the week number and the total for the week).
Where do you get the “StockItem” and other ‘pink’ formula items in your formula?
Maybe a better question would be where can I find the resources to learn how to do this myself?
This is what I want to do (option 1), but I’m getting no response (for more than a week already) on requesting editing access to actually see how it is done. It’s super frustrating as I’ve been struggling for a really long time. It feels as if Coda is designed to keep people out rather than build a community.
Sure! I did the grouping strictly as a point of reference to compare the data in the formula. It doesn’t change the actual data or how you can reference it. Grouping is purely “cosmetic.”
However, the view FILTERING is used in the formula. So if you referenced the original table in your formula, you would have to also filter the data by “this week.”
I added comments to the formula:
Concatenate(
"[", // Starting the JSON array string with an opening bracket.
[View of stock received (weekly)].Item // Accessing the 'Item' column from the view 'View of stock received (weekly)'.
.Unique() // Getting unique values from the 'Item' column.
.FormulaMap( // Mapping each unique item to a new structure.
CurrentValue.Item // For each unique item, access the 'Item' value.
.WithName( // Assigning a name to the current value for easier reference.
StockItem, // Naming the current item as 'StockItem'.
Format( // Formatting the current item into a JSON object string.
"{ \"Item\": \"{1}\", \"Total\": {2}, \"Quantity\": {3} }", // Template for the JSON object.
StockItem, // Replacing {1} with the 'StockItem' value.
[View of stock received (weekly)]
.Filter( // Filtering the view to get rows where 'StockItem' matches the current item.
StockItem = CurrentValue.Item.Item
)
.Total
.Sum(), // Summing up the 'Total' column for the filtered rows and replacing {2}.
[View of stock received (weekly)]
.Filter( // Filtering the view again to get rows where 'StockItem' matches the current item.
StockItem = CurrentValue.Item.Item
)
.Quantity
.Sum() // Summing up the 'Quantity' column for the filtered rows and replacing {3}.
)
)
),
"]" // Ending the JSON array string with a closing bracket.
)
StockItem is simply a temporary “variable” representing the current item, making it easier to read with the WithName function.
Ironically, @Christiaan_Huizer has an amazing article that really helped me understand how this works.
