7 Replies Latest reply on Jun 7, 2013 4:22 PM by JerryAPowers

    Ideas on how to simplify calculation

    JerryAPowers

      Title

      Ideas on how to simplify calculation

      Post

           I have a very long calculation in my database that appears that it could be simplified with some sort of loop counter and wlldcard parameter with the field names. However, I cannot seem to figure out the coding to make this work. If you can think of a better way to create this calculation it would be greatly appreciated. Additionally, I am thinking that I should create a custom function for this calculation since there are MANY fields that also utilize this calculation. I have hit the character limits for calculations when I duplicate this long calculation in the formula and add additional calculation for each particular field.

           Thank you for your help!

           Jerry

            

           Following is my current calculation:

           Round(Average (
           If ( Comps in ARV = "Yes"; Average (
           If (Comp 1 Select = "Yes";
           ((Comp 1 Purchase Price + If(Number Bedrooms<1 or Comp 1 Beds<1;0;((Number Bedrooms-Comp 1 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 1 Baths<1;0;((Number Baths-Comp 1 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 1 Lot Area<1;0;((Lot Area-Comp 1 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 1 Square Footage));
           If (Comp 2 Select = "Yes";
           ((Comp 2 Purchase Price + If(Number Bedrooms<1 or Comp 2 Beds<1;0;((Number Bedrooms-Comp 2 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 2 Baths<1;0;((Number Baths-Comp 2 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 2 Lot Area<1;0;((Lot Area-Comp 2 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 2 Square Footage));
           If (Comp 3 Select = "Yes";
           ((Comp 3 Purchase Price + If(Number Bedrooms<1 or Comp 3 Beds<1;0;((Number Bedrooms-Comp 3 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 3 Baths<1;0;((Number Baths-Comp 3 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 3 Lot Area<1;0;((Lot Area-Comp 3 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 3 Square Footage));
           If (Comp 4 Select = "Yes";
           ((Comp 4 Purchase Price + If(Number Bedrooms<1 or Comp 4 Beds<1;0;((Number Bedrooms-Comp 4 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 4 Baths<1;0;((Number Baths-Comp 4 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 4 Lot Area<1;0;((Lot Area-Comp 4 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 4 Square Footage));
           If (Comp 5 Select = "Yes";
           ((Comp 5 Purchase Price + If(Number Bedrooms<1 or Comp 5 Beds<1;0;((Number Bedrooms-Comp 5 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 5 Baths<1;0;((Number Baths-Comp 5 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 5 Lot Area<1;0;((Lot Area-Comp 5 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 5 Square Footage));
           If (Comp 6 Select = "Yes";
           ((Comp 6 Purchase Price + If(Number Bedrooms<1 or Comp 6 Beds<1;0;((Number Bedrooms-Comp 6 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 6 Baths<1;0;((Number Baths-Comp 6 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 6 Lot Area<1;0;((Lot Area-Comp 6 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 6 Square Footage));
           If (Comp 7 Select = "Yes";
           ((Comp 7 Purchase Price + If(Number Bedrooms<1 or Comp 7 Beds<1;0;((Number Bedrooms-Comp 7 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 7 Baths<1;0;((Number Baths-Comp 7 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 7 Lot Area<1;0;((Lot Area-Comp 7 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 7 Square Footage));
           If (Comp 8 Select = "Yes";
           ((Comp 8 Purchase Price + If(Number Bedrooms<1 or Comp 8 Beds<1;0;((Number Bedrooms-Comp 8 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 8 Baths<1;0;((Number Baths-Comp 8 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 8 Lot Area<1;0;((Lot Area-Comp 8 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 8 Square Footage));
           If (Comp 9 Select = "Yes";
           ((Comp 9 Purchase Price + If(Number Bedrooms<1 or Comp 9 Beds<1;0;((Number Bedrooms-Comp 9 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 9 Baths<1;0;((Number Baths-Comp 9 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 9 Lot Area<1;0;((Lot Area-Comp 9 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 9 Square Footage));
           If (Comp 10 Select = "Yes";
           ((Comp 10 Purchase Price + If(Number Bedrooms<1 or Comp 10 Beds<1;0;((Number Bedrooms-Comp 10 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 10 Baths<1;0;((Number Baths-Comp 10 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 10 Lot Area<1;0;((Lot Area-Comp 10 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 10 Square Footage));
           If (Comp 11 Select = "Yes";
           ((Comp 11 Purchase Price + If(Number Bedrooms<1 or Comp 11 Beds<1;0;((Number Bedrooms-Comp 11 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 11 Baths<1;0;((Number Baths-Comp 11 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 11 Lot Area<1;0;((Lot Area-Comp 11 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 11 Square Footage));
           If (Comp 12 Select = "Yes";
           ((Comp 12 Purchase Price + If(Number Bedrooms<1 or Comp 12 Beds<1;0;((Number Bedrooms-Comp 12 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 12 Baths<1;0;((Number Baths-Comp 12 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 12 Lot Area<1;0;((Lot Area-Comp 12 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 12 Square Footage));
           If (Comp 13 Select = "Yes";
           ((Comp 13 Purchase Price + If(Number Bedrooms<1 or Comp 13 Beds<1;0;((Number Bedrooms-Comp 13 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 13 Baths<1;0;((Number Baths-Comp 13 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 13 Lot Area<1;0;((Lot Area-Comp 13 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 13 Square Footage));
           If (Comp 14 Select = "Yes";
           ((Comp 14 Purchase Price + If(Number Bedrooms<1 or Comp 14 Beds<1;0;((Number Bedrooms-Comp 14 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 14 Baths<1;0;((Number Baths-Comp 14 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 14 Lot Area<1;0;((Lot Area-Comp 14 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 14 Square Footage));
           If (Comp 15 Select = "Yes";
           ((Comp 15 Purchase Price + If(Number Bedrooms<1 or Comp 15 Beds<1;0;((Number Bedrooms-Comp 15 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 15 Baths<1;0;((Number Baths-Comp 15 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 15 Lot Area<1;0;((Lot Area-Comp 15 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 15 Square Footage));
           If (Comp 16 Select = "Yes";
           ((Comp 16 Purchase Price + If(Number Bedrooms<1 or Comp 16 Beds<1;0;((Number Bedrooms-Comp 16 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 16 Baths<1;0;((Number Baths-Comp 16 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 16 Lot Area<1;0;((Lot Area-Comp 16 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 16 Square Footage));
           If (Comp 17 Select = "Yes";
           ((Comp 17 Purchase Price + If(Number Bedrooms<1 or Comp 17 Beds<1;0;((Number Bedrooms-Comp 17 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 17 Baths<1;0;((Number Baths-Comp 17 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 17 Lot Area<1;0;((Lot Area-Comp 17 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 17 Square Footage));
           If (Comp 18 Select = "Yes";
           ((Comp 18 Purchase Price + If(Number Bedrooms<1 or Comp 18 Beds<1;0;((Number Bedrooms-Comp 18 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 18 Baths<1;0;((Number Baths-Comp 18 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 18 Lot Area<1;0;((Lot Area-Comp 18 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 18 Square Footage));
           If (Comp 19 Select = "Yes";
           ((Comp 19 Purchase Price + If(Number Bedrooms<1 or Comp 19 Beds<1;0;((Number Bedrooms-Comp 19 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 19 Baths<1;0;((Number Baths-Comp 19 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 19 Lot Area<1;0;((Lot Area-Comp 19 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 19 Square Footage));
           If (Comp 20 Select = "Yes";
           ((Comp 20 Purchase Price + If(Number Bedrooms<1 or Comp 20 Beds<1;0;((Number Bedrooms-Comp 20 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 20 Baths<1;0;((Number Baths-Comp 20 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 20 Lot Area<1;0;((Lot Area-Comp 20 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 20 Square Footage));
           If (Comp 21 Select = "Yes";
           ((Comp 21 Purchase Price + If(Number Bedrooms<1 or Comp 21 Beds<1;0;((Number Bedrooms-Comp 21 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 21 Baths<1;0;((Number Baths-Comp 21 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 21 Lot Area<1;0;((Lot Area-Comp 21 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 21 Square Footage));
           If (Comp 22 Select = "Yes";
           ((Comp 22 Purchase Price + If(Number Bedrooms<1 or Comp 22 Beds<1;0;((Number Bedrooms-Comp 22 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 22 Baths<1;0;((Number Baths-Comp 22 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 22 Lot Area<1;0;((Lot Area-Comp 22 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 22 Square Footage));
           If (Comp 23 Select = "Yes";
           ((Comp 23 Purchase Price + If(Number Bedrooms<1 or Comp 23 Beds<1;0;((Number Bedrooms-Comp 23 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 23 Baths<1;0;((Number Baths-Comp 23 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 23 Lot Area<1;0;((Lot Area-Comp 23 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 23 Square Footage));
           If (Comp 24 Select = "Yes";
           ((Comp 24 Purchase Price + If(Number Bedrooms<1 or Comp 24 Beds<1;0;((Number Bedrooms-Comp 24 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 24 Baths<1;0;((Number Baths-Comp 24 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 24 Lot Area<1;0;((Lot Area-Comp 24 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 24 Square Footage));
           If (Comp 25 Select = "Yes";
           ((Comp 25 Purchase Price + If(Number Bedrooms<1 or Comp 25 Beds<1;0;((Number Bedrooms-Comp 25 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 25 Baths<1;0;((Number Baths-Comp 25 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 25 Lot Area<1;0;((Lot Area-Comp 25 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 25 Square Footage)))
           * Square Footage);
           If ( RealQuest in ARV = "Yes";Realquest Valuation);
           If ( HomeGain in ARV = "Yes";Homegain Valuation);
           If ( Zillow in ARV = "Yes";Zillow Valuation))/Defaults::Sales Price Rounding;0)*Defaults::Sales Price Rounding

        • 1. Re: Ideas on how to simplify calculation
          schamblee

               I read your other post too.  I don't really think you need the if statement for each comp.  If you have a comp table then you could have 1 to whatever amount of comps you would ever need.  With your current design, if you ever needed 30 comps you would have to redesign your app / calculation.

               Yes, if you have filemaker advance you could make a custom function, but I think a calculation field would be fine.

               Take a look at the following link about planning your databases.

          http://help.filemaker.com/app/answers/detail/a_id/3247/~/relational-database-design-101-(part-2-of-3)

                

                

                

          • 2. Re: Ideas on how to simplify calculation
            JerryAPowers

                 I have my database set up as a relational database as instructed on your link - I now have a separate "Comps" table from my "PropertyAnalyzer" table.

                 Please let me know if there is a better way to construct this calculation. The reason why I have the "If" statements is because we typically do not want to include the value of all 25 retrieved comps - we only want to include the comps that are considered and ranked to be close to the same characteristics of our subject property. Therefore, I have included a "Select" field for each of the 25 comps that is automatically selected or de-selected based on the "Rank" of the comp. The automatic calculation of the "Select" field is performed when the comp data is retrieved. However, the user can select or de-select any comp to include in the "Comp Value" of the subject property. Therefore, there has to be some way that the above calculation know which comps to include in calculating the value of the subject property.

                 My initial question centers around the fact that since I have 25 sets of repeating formulas, wouldn't it be better to figure out how to create a loop, make the comp number a variable, and have that variable increase by 1 with each pass?

                 I have to get the length of this calculation reduced - it is slowing down the performance of my solution! When a new field calculation requires the above calculation and the calculation exceeds the filemaker character limits, I have no other choice but to refer back to this calculation in that calculation. As you are aware, this causes filemaker to go into a sort of calculation loop everytime a field is modified - not good for performance!

            • 3. Re: Ideas on how to simplify calculation
              RickWhitelaw

                   Your script indicates a design problem to me. Posting your relationship graph would be as useful as the script itself. With proper relationships you shouldn't need to specify a Comp number in your script. You may not even need to Loop!

              • 4. Re: Ideas on how to simplify calculation
                JerryAPowers

                     Rick,

                     Thank you for your reply. I have attached an image of my relationship graph - the entire calculation is shown above.

                     Thank you for your help!

                • 5. Re: Ideas on how to simplify calculation
                  schamblee

                       In filemaker, you could have the calculation on all your comps and set the field to do not store - calculate as needed.  It will not slow anything down, at least not anything noticable and will be much faster than a script with a loop.   You can perform a find, based on your rank system which can include  any number of records. 

                  • 6. Re: Ideas on how to simplify calculation
                    philmodjunk

                         And in which table is the calculation that you have posted defined? Keep in mind that you know your business model and work flow while we do not and this can hinder our understanding of how this calculation should work.

                    • 7. Re: Ideas on how to simplify calculation
                      JerryAPowers

                           S Chamblee - I have the calculation on all my comps set to Do Not Store - Calculate as needed. Where the solution slows down is when I have to write a calculation that refers to the calculation above - therefore, that calculation has to wait until the above calculation is completed. I would guess that there are at least 12 calculations that are structured in this manner. This is why I need to figure out how to streamline the above calculation. Currently, with the way that these other 12 calculations are constructed, Filemaker must calculate each of these calculations in a linear manner - one after each other, rather than calculating them simultaneously - which is what would happen if I could simply copy the above formula to new calculations that build upon the above calculation. I had the same poor performance issue about 2 years ago when I figured out that this is what was causing it. I fixed it once by embedding a shorter version (only 4 comps) of the above calculation into the other calculations. Now that I have expanded to 25 comps the calculation to too long to embed into other calculations to build upon.

                           Using Find based on the rank system is a good solution for a completely automated system. While this is what I am attempting to do with this solution, it removes the option to allow the user to select which "comps" they want to include in the "after-repair-value" of the subject property.

                           Phil - The calculation from above is defined in my main table - "Property Analyzer." The majority of the "comps" fields are currently in the "Comps" table.