AnsweredAssumed Answered

Script to calculate totals based on criteria and store in separate table

Question asked by BKamp on Apr 17, 2017
Latest reply on Apr 18, 2017 by BKamp

Hi guys!

 

I have the following tables:

Properties (main table)

Monthly Financials (to store monthly rent income data, linked to properties table)

Results (new, this is the target table form the script to store data, standalone table)

 

Each Properties record is assigned a fund (properties::fund)

 

My idea is to create a script to calculate and store quarterly or yearly results of a selected fund in the Results table.

 

So in my results table and layout I have the following editable fields: (A)

Fund (to select the fund to generate for)

Type (yearly or quarterly result)

Year (the year to generate for)

Quarter (only if Type is set to quarterly this is displayed, select the quarter here)

 

the other fields are: (B)

gross income

management

insurance

tax

water

repairs

vacancy

leasing fee

result

 

those are not editable and are to be populated by the script.

 

I've added a button to the results layout I want to assign a script to. This script need to calculate the totals for the fields (B) for the selected fund and the selected timespan (year, yearly or quarter)

 

What I've thought so far:

  1. check if the A fields are populated, if not echo error
  2. create variables for the B fields with SQL queries
  3. Set the B fields with the variables

 

I'm struggling with #2, I'm not really familiar with SQL queries, especially with the fund requirement which comes from the properties table.

basically I ned something like this for every B field:

ExecuteSQL(

"

SELECT SUM (\"rent_income"\) FROM \"Monthly Financials"\ WHERE \"Properties::fund"\ = ? AND WHERE \"Monthly Financials::date"\ = ?

"

; "" ; "" ; "Fund A, 01/01/2017...31/03/2017"

)

 

Can anybody help me with this complicated query, how would I do this?

 

I think this query is applicable to all the B fields.

 

Thanks a lot!

Outcomes