1 Reply Latest reply on May 14, 2014 9:37 PM by erolst

    SQL Sum Needed for Summary Report but Slow to Update

    bradleyboggs

      Thank you in advance for any help you can provide!

       

      Currently, I have a Daily "PO Log" report, which lists the PO's we've received on a daily basis. This report is called through a find of "Enter Date" = "Get(CurrentDate)", then sorts by region and gives a sub-summary total for each region, and then provides 2 totals at the bottom - a Daily Total which is a summary total of all the orders listed, and then a Monthly total.

       

      When i first made this report as I was just starting out, the only way I could get this to work was by making a calculation field called "MonthExt" with this "If (Month (EntDate) = Month(Get(CurrentDate)) and Year (EntDate) = Year(Get(CurrentDate)); ExtPOCost; "" )", then having a separate calculation field called "MonthlyTotal" with this: "ExecuteSQL ( "SELECT Sum ( MonthExt ) FROM Orders" ; ""; "")"

       

      Referring to this post as I did it: http://forums.filemaker.com/posts/2d245a1d51

       

      I had to do it this way, because if I made the "MonthlyTotal" field a summary field or a calculation field with a SUM in it, it would give me the total for the newest record, but not the total for all the month's records.

       

      When I first made this using the SQL method, it worked fine. The math continues to add up correctly. The only caveat, is that now that the database is growing since we put it into place in October, this SQL sum is a bit sluggish and doesn't always update immediately (often fairly quickly, sometimes up to 5 or 10 minutes after the final order is entered it still hasn't updated).

       

      I know I could set this to evaluate manually with scripts/triggers, but that doesn't seem like a great solution either.

       

      Fool hardedly I tried to "filter" the records at the relationship level with different TO's (as you would with filtering portals at the relationship level) so that it wouldn't have to evaluate the full record set, but I'm not using portals for this data since it's a report and it doesn't work without portals.

       

      If you have any tips, I'm all ears! Thank you again if you made it this far!

       

      -Bradley

       


        • 1. Re: SQL Sum Needed for Summary Report but Slow to Update
          erolst

          bradleyboggs wrote:

          Fool hardedly I tried to "filter" the records at the relationship level with different TO's (as you would with filtering portals at the relationship level) so that it wouldn't have to evaluate the full record set, but I'm not using portals for this data since it's a report and it doesn't work without portals.

           

          Defining a relationship doesn't mean you need a portal to put it to use … and basically, only a single relationship is required …

           

          In the Orders table, define a calculation field, type text, as Year ( EntDate ) & "|" & Month ( EntDate ), and use it as match for a selfjoin relationship. Then create a calculation field Sum ( Orders_selfJoin::ExtPOCost ) and put it in the Grand Total part.

           

          Actually, you don't even need that calculation field; simply use a summary field for ExtPOCost, say, sExtPOCost. The sExtPOCost field from the TO which your report is based on will show the daily summary; put the same field via the selfjoin relationship on the layout (i.e. Orders_selfJoin::sExtPOCost), and you'll see the monthly sum.

           

          If you want to use SQL, then, instead of creating an unstored calculation field and target that (which is probably causing the increasing sluggishness, at least in part), directly query the date field (which is stored), like

           

          Let ( [

            cd = Get ( CurrentDate ) ;

            m = Month ( cd ) ;

            y = Year ( cd )

            ] ;

            ExecuteSQL ( "

              SELECT Sum ( ExtPOCost )

              FROM Orders

              WHERE EntDate BETWEEN ? AND ?

              " ; "" ; "" ; Date ( m ; 1 ; y ) ; Date ( m + 1 ; 0 ; y )

              )

          )