Calculations on Selected Related Records / Portal Records
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:
- Comps data is extracted into the child table "Comps".
- Each comp is compared to the Subject Property in the parent table "Property Analyzer".
- An "Adjustment" field calculation is performed that calculates the adjustment for each comp based on size, # of beds & baths, etc.
- 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.
- 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.