Context:
I have created a custom coda integration that pulls data from our custom built project management platform.
This coda pack pulls in projects and their respective spend/budgets, epics, spend by epic, and a laundry list of other things.
The primary goal with creating these formulas is to track & visualize budget spend overtime for each project in Coda.
These are the two most important columns. As you can see in the budget data column, there are multiple ‘objects’. Each object, is a total spend per each month of the year.
This is what each budget data object contains:
I’ve split the requirements out to try and address each of them on their own before creating the final formula.
Goal #1:
In a new table, create a row for every object in the budget data column for every Project. Meaning, for the images above, there should be 7 rows for this project: Project name | Spend | Date.
I have complete this with the following formula on a button:
RunActions(
// Add new rows
[Projects budget table].FormulaMap(
[Budget data].FormulaMap(
AddRow(
[Target Table],
[Target Table].[Project Name], CurrentValue.ProjectName,
[Target Table].[Budget Data], CurrentValue.BudgetConsumed,
[Target Table].Months, CurrentValue.Month
)
)
)
)
Goal #2:
Modify rows. We need to be able to Compare the original budget data table, and the new target table mentioned in goal #1, to identify rows that have an updated budget spend (this will only happen for the current month, and maybe the previous month as a possible edge case), and then update the spend on the target table. Currently goal#1 formula will just add more and more rows(as expected) but this is not the desired end goal.
A restriction. I am not able to just add a delete all rows to the formula from Goal #1, and re-add everything with updated values due to future plans with rowids.
On this step, I moved into using two test tables instead of the actual tables with real data, I just didn’t want to deal with the amount of data on the original.
I have a formula that modifies a single row as desired, but as soon as I begin trying to loop through both tables to update the rows in the target table, I begin running into issues. In this example, table 1 is the original table, and table 2 is the target table.
My Formula:
RunActions(
// Iterate through each row in Test table 1
[Test table 1].FormulaMap(
// Find matching rows in Test table 2
WithName(
First(
Filter(
[Test table 2],
[Test table 2].[Name 2] = [Test table 1].Name
)
),
matchingRow,
// Check if the spend values are different and update if necessary
If(
Not(IsBlank(matchingRow)) AND matchingRow.[Spend 2] != CurrentValue.Spend,
ModifyRows(
matchingRow,
[Test table 2].[Spend 2], CurrentValue.Spend
), ModifyRows(
[Test table 2],[Test table 2].[Spend 2],"Rows Match Test")
)
)
),
// Iterate through each row in Test table 2
[Test table 2].FormulaMap(
// Find matching rows in Test table 1
WithName(
First(
Filter(
[Test table 1],
[Test table 1].Name = [Test table 2].[Name 2]
)
),
matchingRow,
// Check if the spend values are different and update if necessary
If(
Not(IsBlank(matchingRow)) AND matchingRow.Spend != CurrentValue.[Spend 2],
ModifyRows(
matchingRow,
[Test table 1].Spend, CurrentValue.[Spend 2]
), ModifyRows(
[Test table 2],[Test table 2].[Spend 2],"Rows Match Test 2")
)
)
)
)
Goal #3:
Merge - Add & Modify rows formulas.
A formula that will modify rows as explained in goal#2, and then add new rows when there is a row from test table 1 that is not currently on test table 2. Because each new month will bring a new Budget Data object.
I assume that these formulas will encounter capacity issues. We have 181 primary rows, that each contain budget data columns with up to 12 budget data objects. So A batching process will also most likely need to take place. But I will get to this when we get there.