Daily Recalculation of Accumulated Interest field

Daily Recalculation of Accumulated Interest field

Dear community,

I am new to Zoho Creator and currently running a trial version in an attempt to create an internal management system for my property development company.

One part of the application contains financial information related to different development projects, including daily loan interest calculation which is based on the purchased price of land as well as incurred development costs. Development costs are added into a subform on the main "Update Financials" form.

Currently, I have several workflows with the following deluge script to update the Total Cost and Total interest fields on form load, successful submission, and field changes, which works nice and neat:

  1. for each  row in Development_Costs
  2. {
  3. if(row.Date_Paid != null && row.Cost_ex_VAT != null)
  4. {
  5. interest_calc = days360(row.Date_Paid,zoho.currentdate) / 365 * input.Loan_Interest_Rate * row.Cost_ex_VAT / 100;
  6. }
  7. row.Accumulated_Interest=interest_calc;
  8. }

  1. totalcost = 0;
  2. totalinterest = 0;
  3. purchase_price_interest = days360(input.Purchase_Date,zoho.currentdate) / 365 * input.Loan_Interest_Rate * input.Total_Price / 100;
  4. for each  row in Development_Costs
  5. {
  6. totalcost = totalcost + row.Cost_ex_VAT;
  7. totalinterest = totalinterest + row.Accumulated_Interest;
  8. }
  9. input.Total_Costs_to_Date = totalcost + input.Total_Price;
  10. input.Total_Interest_to_Date = totalinterest + purchase_price_interest;

Since those Total Cost and Total Interest fields are crucial for reporting, I was hoping to automate their update on daily basis, for which I thought Scheduled Workflow would be a perfect solution. But unfortunately I struggle to figure out how to refer to that particular "Update Financials" form in order to set the updates of both form and subform fields. Using the script above in Scheduled Workflow, if I just reference 

  1. for each  row in Development_Costs
it is understandably not defined, but changing to

  1. for each  row in Financial_Updates.Development_Costs
doesn't help either.

I would greatly appreciate any suggestions on the correct syntax to refer to particular forms/subforms/fields which hopefully will solve my issue.

Thanks in advance!