AnsweredAssumed Answered

Calculations on Selected Related Records / Portal Records

Question asked by JerryAPowers on Jun 20, 2013
Latest reply on Jun 21, 2013 by philmodjunk

Title

Calculations on Selected Related Records / Portal Records

Post

     I need a calculation that will average just the user-selected records from a related table. I know that I can sum or average ALL of the related records, but the IF statement doesn't work in conjuction with those function for related records.

     I have created a calculation field in the child table that provides a result if the box is selected in the portal. However, because the resulting field "Average Valuation" is used in about 20 other field calculations, I need this calculation to not reference any other calculation fields to ensure that I don't have a calculation looping problem that I currently have - it is really slowing down my app!

     In a nutshell, this is what I am doing:

         
  1.           Comps data is extracted into the child table "Comps".
  2.      
  3.           Each comp is compared to the Subject Property in the parent table "Property Analyzer".
  4.      
  5.           An "Adjustment" field calculation is performed that calculates the adjustment for each comp based on size, # of beds & baths, etc.
  6.      
  7.           An "Adjusted Price per sqft" field calculation adds the Comp Purchase Price to the Comp Adjustment and then is divided by the Comp square footage.
  8.      
  9.           When the Selection Box is activated either by calculation or user-selection, a third field "Select Comp" is populated with the "Adjusted Price per sqft" field contents.

     I have this currently working and it does calculate the right amount. However, to achieve this I have the following calculation:

     Round((( Average (Comps::Select_Comp) * Square Footage ) * Defaults::Sales Price Modifier);0) / Defaults::Sales Price Modifier

     Is there a way to utilze IF statements across related records?

     In a closely related question, how can I write a script that will select or deselect all properties when the user selects the checkbox above the comps? I've tried SetField (Comps::Select; "Yes") but it only seems to work on the first record.

      

      

Comps_Screen.JPG

Outcomes