5 Replies Latest reply on Dec 3, 2013 7:15 AM by stevegleason

    Using a date range to limit line item aggregate summary information

    stevegleason

      As part of a larger project, I am trying to create a dynamic dashboard layout to show client activity within a user-specified range of time, and I am having trouble getting summary information to show accurately within each line of the portal.

       

      There are three TOs involved:


      A GLOBAL TO set set the date ranges.


      global_SHIFTLINEITEMS lists each billable activity, with an associated job number, client, and revenue generated. The relationship is defined by the date of the shiftlineitems record falling within the starting and ending range specified in the global table.


      global_shiftitems_CLIENTS is used to aggregate the unique jobs to a "rolled up" list of client activity.


      The layout is based on the GLOBAL TO – so I can display records from many different clients at the same time.

       

      A portal on that page shows the related records from the global_shiftitems_CLIENTS TO.

      The portal contains a field for the client name, and what I would hope would be a count of the related records in the period and the amount of revenue within the period.


      What works:

      The client list generates dynamically, only listing the clients who had activity within the specified time period.

       

      What doesn't:

       

      The aggregation within each line of the portal. I can use the "one line portal" method to have a portal listing the combined revenue for every item in the period below the main portal.


      But within the portal, I can only get that aggregate information (for ALL clients within the set time period - the same summary totals are listed in each line) using a summary field (s_revenue) from the base SHIFTLINEITEMS table.


      or an aggregate field (from the global_shiftitems_CLIENTS TO) that IS specific to the client, but it ignores the parameters of the set time period and lists all records ever entered: Unstored, from global_shiftlineitems_CLIENTS, = Sum ( global_SHIFTLINEITEMS::Revenue)

       

       

      Other things I've tried:


      Trying to use the GetSummary isn't working either. I have tried doing this from the context of each table:


      In the base ShiftLineItems table:

      c_revenue_in_date_range: unstored, from global_SHIFTLINEITEMS, = GetSummary (s_revenue; _kf_ClientCode)

      Result: empty field


      In the base Clients table:

      c_revenue_in_date_range: unstored, from global_shiftlineitems_CLIENTS, GetSummary ( global_SHIFTLINEITEMS::s_Revenue ;__kp_ClientCode )
      (Note: i tried using the breakfield from each table, with no results). Result: empty field

       

      Also in the base Clients table, i tried the Sum calculation:

      c_sum_revenue_in_date_range: unstored, from global_shiftlineitems_CLIENTS, Sum ( global_SHIFTLINEITEMS::Revenue)
      Result: empty field


      I know that cross tab reports - within a Portal - are a stretch. But I would really like to end up with this format instead of a list report. I'm missing something, but I can't figure it out. I've tried various self-joins, too. But I'm at the point where I feel like I am not doing anything to increase my understanding, but just flailing hoping that something works. I would appreciate feedback.



       

        • 1. Re: Using a date range to limit line item aggregate summary information
          Stephen Huston

          This sounds like a perfect place to replace  your relationships to the global table with an ExecuteSQL query to return the Sum of everything within the date range of the globals from the target table. And it should resolve much faster than related Sum totals aggregating across  relationships.

          • 2. Re: Using a date range to limit line item aggregate summary information
            stevegleason

            Hi Stephen,

             

            I'm trying to follow the basic schema outlined by Beverly Voth in her article at filemakerhacks.com (http://www.filemakerhacks.com/?p=5950)

             

            I have an unstored calculation called "c_ESQL_revenue_in_date_range" on the Clients table (in the context of the TO - though I dont know that this is relevant with an executeSQL command

             

            Let (

            [ $query = "SELECT SUM ( Revenue ) FROM global_SHIFTLINEITEMS

            WHERE global_SHIFTLINEITEMS.date >= ?

            AND global_SHIFTLINEITEMS.date <= ?

            AND global_SHIFTLINEITEMS._kf_ClientCode = ? "

            ; $result = ExecuteSQL( $query ; ""; "" ; GLOBAL::zSelectedStartWeekDate ;GLOBAL::zSelectedEndWeekDate ;__kp_ClientCode )

            ] ; $result

            )

             

            The logic is:

            get the line item revenue amount (not a summary field - just a simple calculation) if the record falls within the date range specified by the global date range start and end filters AND the client code (name) matches.

             

            It isn't working. I'm getting the dreaded question marks. Do I need to have a way to "escape out" the leading underscores that I use in field names?

            • 3. Re: Using a date range to limit line item aggregate summary information
              Stephen Huston

              I recall there are some issues with spaces and leading underscores for field-name references in SQL, but there are more competent SQL references to help. And they are free resources:

               

              Take a look at Beverly Voth's

              The-Missing-FM-12-ExecuteSQL-Reference.pdf

              [PDF]

              The Missing FM 12 ExecuteSQL Reference - filemakerhacks

               

              and SeedCode's SQL Explorer tool, which helps you test and write clean queries at

              http://www.seedcode.com/filemaker-sql-explorer/

              • 4. Re: Using a date range to limit line item aggregate summary information
                stevegleason

                Solved.

                 

                This format worked for aggregating all line item detail for a client:

                 

                ExecuteSQL("select sum(L.Cost) from Clients C join JobLineItems L on C.ClientName_kp = L.ClientName_kf

                where C.ClientName_kp= ?"; ""; ""; ClientName_kp)

                 

                in order to add a date range filter, this was expanded to:

                 

                ExecuteSQL("select sum(L.Cost) from Clients C join JobLineItems L on C.ClientName_kp = L.ClientName_kf

                where C.ClientName_kp= ? AND L.ShiftDate>=? AND L.ShiftDate<=?"; ""; ""; ClientName_kp;Global::z_StartingDate;Global::z_EndingDate)

                 

                Note: Originally, the "L.ShiftDate" field was named "L.Date" (or just "Date" in the table's field definition). This resulted in the dreaded question marks. Apparently, the word "Date" is reserved, and it messed up the calculation. Simply renaming the field, and the references to the field in the formula, cleared up the error.

                 

                I will use this method to provide a dashboard, aggregating each client's activity within a user-specified date range. When a client name is highlighted, an additional portal will display jobs for that client with activity within the date range. If a job is highlighted in that portal, a third portal will show line item detail for the job.