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

example:

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!

Outcomes