Add fields for repeat that matches a certain year in a CDM

Modeling
1

Hi all,
I’m sure I’m just missing the correct language construct or the right syntax but I just can’t figure out how to write this computation:
I have two entities: accounts and tasks.
Accounts represent “money pots” where I need to track via budget how much we plan to spend and how much has already been spent. For one account, I can define multiple budgets, one for each year, so I can plan and track money for different years (this year, we plan to spend this much, next year only this much).
Tasks are things that get done that cost money. Every task needs to be assigned to one account; this relationship defines from which “money pot” the task takes its money. Accounts can have multiple tasks assigned to them. Tasks are only defined for a specific year, so all money that gets spend is spent from the budget from this year. Therefore, I can only define one budget for a task.
In my “Account” data model, I want to add all the spent budgets from all the tasks assigned to this account together, so I can see how much budget has been spent across all the tasks in this account. Here, I need to make sure that I only add the spent budgets one year together and put that sum in the “budget spent” field for the specific year.
For example, in “Account A”, I have two tasks “Task 1” and “Task 2”. In Task 1 2000€ have been spent so far for 2025, for Task 2 1500€ have been spent for 2025. So I want to write “3500 €” into the “Budget spent” field for 2025 in Account A.

For my computation I want to put

Sum(/AccountTask_RM*/Task/Budget/BudgetSpent Having [/AccountTask_RM/Task/Year] == "2025")

and for my precondition I’m not sure. I want to put something like: For the repetition of [/Account/Budget*/] where /Account/Budget/Year]== “2025” calculate [/Account/Budget/BudgetSpent]. I put this, but it’s not a complete condition:

FirstFilledValue(/Account/Budget*/BudgetSpent Having [/Account/Budget/Year]== "2025")

Here are my example models:
Account_DM.json (3.9 KB)
Task_DM.json (5.7 KB)
Account_CDM.json (3.0 KB)
AccountTask_RM.json (1.6 KB)
graph.diagram (1.3 KB)

Best,
Lea

side note: for some reason I can’t specify a specific year for a the “Year” field, which is a date fragment, so I use “Today” in my models.

2

Hey @lea-clear-clover,

“FirstFilledValue” returns the value of the first Field Instance it finds (that matches your Having statement) → you can then check this value against something to have a proper condition:

FirstFilledValue(/Account/Budget*/BudgetSpent Having [/Account/Budget/Year]== "2025")   > 10

But you might want to only check, if there is at least one value that matches your Having statement. In this case, you could use:

AtLeastOneFieldFilled(/Account/Budget*/BudgetSpent Having [/Account/Budget/Year]== "2025")

Beside that your approach looks fine to me.
But be aware, that the Computation is only done when you open the CDM. There is no automatic logic in standard A12 to compute this if you eg change the value of a BudgetSpent on a task or link a new Task to an Account outside this specific CDM.

Sidenote: you could use YearFromDate(/Account/Budget/Year) == 2025

3

Hi Felix,
thanks for the pointers. Unfortunately, the SME is not happy with this as a precondition:
image
image

Looking at it again now, I’m not sure if this is actually the correct precondition… :see_no_evil_monkey:

4

But the error is of a different origin :wink:
The problem now is, that the Rule is not in the same parent group as the Repeatable Field you want to compute.
There is ticket A12-15148 to lift this restriction, but for now you can also replace the include by an import of the original Group.

5

That works!
If the computation is within the same repeatable group as BudgetSpent, it can be:

Sum(/AccountTask_RM*/Task/Budget/BudgetSpent Having [/AccountTask_RM/Task/Year] == [$Year])

with the Year being matched up dynamically and the precondition can be the same thing just using AtLeastOneFieldFilled.
(First time successfully using the $ symbol in a computation, so I feel like I leveled up as a modeler :grin:)
Best,
Lea