7 Replies Latest reply on Nov 7, 2014 7:11 AM by lmnieves

    Summarizing Data by Product Code within a Start and End Date

    lmnieves

      Hello! I am currently developing a database that needs to summarize the weight of certain products, by its product code, for a selected time period. The time period would be selected by the user, and a summary should be shown in the layout. I tried to create a self relationship to filter by start and end data, thus bringing all the records created within that period and then using another self-relationship to filter by product code. The summary shall only show one line per product code, but totalizing the shipping weight for the period selected. I tried to use the guidelines suggested in http://filemakerhacks.com/2011/10/18/summary-report-in-a-filtered-portal/. No luck at all.

       

      Is there any other way to do this without scripting? The layout shall be iteractive to allow the end user to pick the dates he wants to evaluated the shipping weights and the layout should show the results right away.

       

      Thanks for your advise!!!

       

      lmnieves

        • 1. Re: Summarizing Data by Product Code within a Start and End Date
          mikebeargie

          Don't use self joins. Use a list view of your data, perform a find to constrain the data to your target date range. This way any summary type fields you place on the layout will only summarize based on your current found set.

           

          Next, you can add a subsummary part to group the records by product code on the layout, and the summary fields in the subsummary part will total for that group.

           

          Lastly, look into and learn the ExecuteSQL() function, you might be able to easily calculate the total you need without having to do layout or relationship work at all.

          • 2. Re: Summarizing Data by Product Code within a Start and End Date
            lmnieves

            Thanks Mike! I did your first recommendation and worked great. Still, I would like to have a way to avoid scripting for finding / sorting the data. I will try your second recommendation too. I would like to have some other options in the layout that the list type limits by design. But definitively your suggestion helped me to get this one working and I can use this for the first solution. I found a document titled: The Missing FM 12 ExecuteSQL Reference (http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/) that talks about the second recommendation. I will look for a similar guide in this forum too.

             

            Thanks again!!!

            • 3. Re: Summarizing Data by Product Code within a Start and End Date
              mikebeargie

              That articles pretty much the defacto source for info on ExecuteSQL(). It also mentions some important tools you can use, like Seedcode's SQL Explorer tool.

               

              Beverly Voth who wrote that article regularly frequents this forum as well, so feel free to ask questions if you get stuck on ExecuteSQL.

              1 of 1 people found this helpful
              • 5. Re: Summarizing Data by Product Code within a Start and End Date
                lmnieves

                I reviewed some (A LOT) of articles related to the ExecuteSQL function. After mashing and milling all the data on them, I could not get a correct answer (any indeed) to a "simple" calculation. For practicing, I was trying to get the total weight for a certain product in a period of time. Although the list layout worked for this purpose, it was no the solution I wanted. Then, following Mike's recommendation I started to learn about this funtion. This is what I conclude about it: it takes a LOT of time to understand how to work with it, but IT'S THE BEST THING EVER!!!

                 

                First of all, it is LIGHTNING FAST. But I will not go into all overwhelming details. The issue that creates a lot of "problems" is how this function handles dates. I saw some custom functions to go over this issue, but I was lucky enough to discover that by using the dynamic parameters, the solution is straight forward. At first, I was trying to get a data for a certain date using more or less the following instruction:

                 

                ExecuteSQL ("SELECT SUM (Weight_Net)
                FROM  Transactions
                WHERE In_Date = g_Start_Date"


                ; "" ;""  ) 

                 

                --- (many others were tried before this one... all of them returned the same result, except when using a hardcoded date in the format "YYYY+MM+DD" literally with the "+" signs) ---

                 

                where g_Start_Date is a global field in a separate table for globals only. Transactions is the table where the weight are stored. The code above sends a "?" to the field where the calculation is stored. I figuerd out that the problems were related to how the function handles dates. After several (LOTS) attemps, I modified the function to:

                 

                ExecuteSQL ("SELECT SUM (Weight_Net)
                FROM  Transactions
                WHERE In_Date = ? AND Product_Code = ? "


                ; "" ;"" ;g_SQL_Start_Date ; g_Product_Code ) 

                 

                Here, I replaced the global date field "g_Start_Date" with a dynamic parameter "?". Also added another parameter to reduce the scope of the calculation to a single product code in the field "Product_Code". Both fields In_Date and Product_Code are located in the Transactions table. By using the dynamic parameters, the function successfully dealed with the dates. I still need to work more with this function, but for now, I was able to solve this issue. Now I can use the calculation to summarize data at LIGHTNING FAST speed. I hope that the readers benefit from this.

                • 6. Re: Summarizing Data by Product Code within a Start and End Date
                  mikebeargie

                  Great job figuring that out on your own. Definitely always use the ? variables to handle dates. That confused a lot of developers when esql() first came out.

                   

                  Also, I’d recommend at looking into making your calc failsafe. Currently you have:

                   

                  ExecuteSQL ("SELECT SUM (Weight_Net)

                  FROM  Transactions

                  WHERE In_Date = ? AND Product_Code = ? "

                   

                  ; "" ;"" ;g_SQL_Start_Date ; g_Product_Code )

                  which will break if you change either the table name or one of the field names.

                   

                  You can use a tool like SQL Explorer ( http://www.seedcode.com/filemaker-sql-explorer/ ) to learn how to prepare your statements so that they are safer.

                  1 of 1 people found this helpful
                  • 7. Re: Summarizing Data by Product Code within a Start and End Date
                    lmnieves

                    Thanks again Mike! You've been a salvation for this SQLed soul. I will definitively do your recommendation in the final version. I read some recommendation to prevent the break and will incorporate them ASAP.