Hi Beth,

for a suggestion which makes sense it would be helpful if you could share screenshots of both apps to see their structure (the screenshots should show the fields needed for the calculation).
Some questions:
1. I assume the month budget fields are money or number fields - correct?
2. Are the apps where the monthly budgets are in and the Expense Reports app connected via a relationship field?
3. Do you use Globiflow (it's included in the Podio Premium Licence)?

Rainer

Hi Rainer,

I think I've narrowed it down to just 2 apps: Expense Reports and Expense Budgets.

I have linked them with a relationship field in Expense Reports.

I also added 2 category fields in Expense Report - "Year" and "Month" for the sole purpose of being able to do a conditional calculation in Expense Budgets.

I'm thinking, for example, if I can make the "March Actual" calculation in Expense Budgets say "if Year = 2019 and Month = March, then calculate the sum, otherwise don't calculate a number."

That way, I'm hoping it'll only total the sum of Expense Report items that reference that Account Name if the Year = 2019 and Month = March.

Would that work?

Here's a screenshot of both apps. Thank you!

I think a formula like this in your Accounts app calculation field [March Actual] should give the figure you want.

`var amounts = @All of Amount with nullsvar years  = @All of Year with nullsvar months  = @All of Month with nullsvar total = 0;for(var i = 0; i < amounts.length ; i++) {if (years[i][0]==@Year && months[i][0]=="March") {total +=amounts[i][0]||0;}} total;`

If it works I must credit Rainer here who taught me to do these sorts of calculations!

Hi Andrew,

It alllllmost worked. I had to sub out "@year" for "2019" because it wasn't pulling up a variable and I need the calculation to be for the year. (was that wrong?)

Now the equation is populating a 0 - which is closer than I've gotten! 😄

I tested it on one account that should be totaling \$294 based on an expense that was submitted with that account code but it's still saying \$0.

Any insight?

If you remove

`if (years[i][0]==@Year && months[i][0]=="March") `

do you get a number? If so then there's a problem with one of the two conditions.

I assume the Marketing Account field in the lower app (Expenses?) is a direct reference to the Subscriptions Account shown above?

Hi Beth,
can you please share a screenshot of your calculation field and also a screenshot of the item where you tried it plus a screenshot of one of the related items (where we can see the fields amount, year, month).

Rainer

Hi Rainer and Andrew,

Attached are the screenshots requested:

The calculation

The app the calculation is in

The app the calculation is trying to total amounts from

Beth

var years = @all of years with nulls

not

var years = @all of amount with nulls

Does that fix it?

Also, you can automate the production of the Notes entries using a calc field to create an html table

See Rainer's previous post for details.