4 Replies Latest reply on Apr 14, 2016 1:32 PM by siplus

    Grouping Time Dependent Records

    Joberlander

      I am trying to create a list that shows a weekly summary of places visited, which is obviously time dependent, but I only get the records for the places by the day -- not a summary of week. I have a daily summary which works fine, but the weekly does not. Here is the script that I use:

      Set Variable [ $$current_date ; Value: Get ( CurrentDate)]

      Set Field [ Consultants::g_performance start date ; $$current_date ]

      Set Field [ Consultant::g_performance end date ; $$current date - 7 ]

      Show All Records

      Sort Records [ Restore ; No Dialog ]

      Find Matching Records [ Constrain ; Consultant::Last in Person Contact ]

      Any suggestions/ contributions are much appreciated

        • 1. Re: Grouping Time Dependent Records
          siplus

          You could smartly involve calculating WeekOfYear ( date ) in your reporting, taking the starting day of the visit, and group by that.

          • 2. Re: Grouping Time Dependent Records
            erolst

            Joberlander wrote:

            Any suggestions/ contributions are much appreciated

             

            Here is one: it doesn't make sense (and is most inefficient) to first Show all Records, then sort them, then constrain them.

             

            Imagine this sequence of events for a total record set of 100,000, where frequently you are only interested in sets of ~50 …

             

            Also, why set a global variable with value that you then write into globals? If you need these globals for .g. a relationship, forgo the variable, write directly. Also, avoid global variables unless you want to use beyond the script (mostly, you don't want to).

             

            Anyway, to find records for the last 7 days try:

             

            Enter Find Mode

            Go to Layout [ Consultants ( Consultants ) ]

            Set Field [ Consultants::dateField ; Get ( CurrentDate ) - 7 & ".." ]

            # [ other Set Field[]s as necessary to add more AND requests ]

            Set Error Capture [ on ]

            Perform Find

            If [ not Get ( FoundCount ) ]

            Show Custom Dialog [ error ; … ]

            Go to Layout

            End If

            # [ search successful … ]

            Sort [ … ]

            1 of 1 people found this helpful
            • 3. Re: Grouping Time Dependent Records
              JeffYurka

              I had a similar problem where I wanted to chart weekly data. week of the year wont work when you go to the next year.

              I created this calc:

               

              Left(GetAsNumber ( TheDate ) / 7;6)

               

              This will provide a number for the week that can be used even when the year changes.

              The number stays the same for week.

               

              -Jeff

              • 4. Re: Grouping Time Dependent Records
                siplus

                Actually what we use is WeekOfYearFiscal