I have the following tables:
- 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:
- january 2017
- februari 2017
- march 2017
Property Taxes has two records:
- year: 2017 ; tax amount: $500
- 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?