AnsweredAssumed Answered

Is this calculation achieveable?

Question asked by BKamp on Jan 30, 2017
Latest reply on Feb 2, 2017 by BKamp

Hi guys!


I have the following tables:

  • Properties
  • Monthly Financials
  • Property Taxes


Properties has the ID field '__pkPropertyID' that is used by the other two tables to relate them to the Properties table.


Monthly Financials is used to track monthly rent incomes each month, it has a field 'period' where you select a date. the field 'period_year' extracts the year from the 'period' field.


Property Taxes is used to track tax expenses on the properties. each record has a field 'Year' that has a dropdown in which you can pick the year this expense was paid.


now here's what I need:

I need to automatically equally spread all the taxes of year X over all the Monthly Financials entries of year X


Monthly Financials has three records:

  1. january 2017
  2. februari 2017
  3. march 2017


Property Taxes has two records:

  1. year: 2017 ; tax amount: $500
  2. year: 2017 ; tax amount: $1000


now the to be made 'tax_per_month' field in Monthly Financials should have calculated values of $500 each ($1500/3).

If I add the month April to Monthly Financials, it should have the value $375.

If I add the month April 2016 to Monthly Financials, it should remain on the value $375 (2016 is not 2017

If I add to the Property Taxes 2017 $1000, the 'tax_per_month' field should be recalculated again.


Is this achievable? I guess I'll need SQL calculations right?


Thanks guys!