3 Replies Latest reply on Jun 21, 2013 10:05 AM by philmodjunk

    Calculations on Selected Related Records / Portal Records



      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:

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




        • 2. Re: Calculations on Selected Related Records / Portal Records

               Phil - Thank you for your response!

               I looked at the referenced thread and I don't think that completely applies. I have a solution that gives me the average price per square foot of selected comps. However, I guess that my question can come down to a more basic topic:


               When constructing calculations, does referencing other calculations slow down the application by causing a calculation loop? I have always understood that it is best to not reference other calculations within a calculation. If the answer is no then I am good and I will have to figure out what is causing the slow response with my app.


               Including a reference to a calculated field in a calculation appears to present the following problem -

          1.           I have a field that calculates the Maximum Allowable Offer (MAO) for a subject property.
          3.           To calculate the MAO, I must know the Average Valuation of the subject property based on the comps.
          5.           If I construct a formula that says: Average Valuation minus All Costs minus Desired Profit equals MAO, then filemaker must wait for the Average Valuation formula to compute before it can calculate the MAO.
          7.           However, filemaker will attempt to calculate the MAO before the Average Valuation calculation is completed, causing a calculation loop.

               Am I thinking correctly with this reasoning? Or is there a way to tell filemaker not to calculate a field until a parent field is calculated? I have tried selecting the "Do no evaluate if all reference fields are empty" and it doesn't seem to make any difference.


               The reason why I don't think that the above thread doesn't completely apply is because once the user selects the row a calculation has to occur to determine the "adjusted value" of the comp. If it was simply a case of selecting the row and calculating a sum or average of those rows it appears that the thread would apply. However, I could be completely wrong and have been known to be wrong on many occassions!!

               Thank you!

          • 3. Re: Calculations on Selected Related Records / Portal Records

                 Using one calculation to refer to a different calculation field does not automatically mean that you have a "loop". In most cases, there won't be any problems doing that, though there are some calcualtions in some contexts that can result in a performance penalty.

                 I do think the thread I recommended does indeed apply. You want to compute an average from a set of related records, but only for some related records, not all. That cannot be done (except with ExecuteSQL) so you have to use one of the method described in that link to selectively average your records:

                 Define a relationship that only matches to selected records

                 Use a summary field inside a filtered portal

                 Use Execute SQL