I have a table of costs with the date the cost was received.
I’d like to create some more tabs where users can see the costs grouped by the date received.
I’m aware of the Group by options but this doesn’t achieve what I’m looking for.
Is it possible to have a separate table with a row for each unique date in the costs table? I can then create a relation and will have much more flexibility.
If there’s a column type which can achieve this besides using a looping workflow to create rows that would be good.
Before starting, I would advise checking the actual date values to see if they also include different times. If they do, then I would first use a math column to convert them to integers using the following:
Year(Date)*10^4
+Month(Date)*10^2
+Day(Date)
In your helper table, add a RowID column and add enough empty rows to cover the maximum number of unique dates.
Number your rows beginning at zero using the Lookup->Find Element Index method.
Add a Lookup column that targets all of the Dates. This will give an array containing all dates.
Add a unique elements column that targets the array of dates
Add a Single Value column that targets the unique array, taking N from start where N is the row index. This should give you a list of unique dates, one per row.
You can then create a relation or query back to your data table, and add whatever rollups you need to aggregate the data by date.