8 Replies Latest reply on Feb 8, 2017 2:38 PM by Doug Castell

    Goldmine Report Filters- Filtering by Date

    Apprentice

      We have a report we run and now want to have it run automatically once a week. I know how to use dialog fields to put in a date range, but now that we want it to run unattended, I need help with the report filter. How would I filter for the next week? My attempt below returns a field mismatch error.

       

      Cal->Ondate>=sys->date.AND.Cal->Ondate<=sys->date +7

       

      Also, is the a way to filter for Cal->ref contains or like "Review" or does it have to be Cal->ref= and write out the entire reference?

       

      Message was edited by: Jon Gearhart

        • 1. Re: Goldmine Report Filters- Filtering by Date
          Doug Castell Expert

          Try this for your filter:

           

          cal->ondate>=sys->date.and.double(cal->ondate)<=double(sys->date)+7
          

           

          Note that the report engine can't do the implied conversion on the fly to add a date value and a numeric value, so the explicit data type conversion is necessary.

           

          For a section filter (like, double-click the detail1 section heading and go to the filter area) you can do something like this to indicate a contains filter:

           

          "Review"$cal->ref
          

           

          Note that dBase is case-sensitive in its comparisons, so you might want to go with

           

          "REVIEW"$upper(cal->ref)
          

           

          to capture any/all instances of the word review in the reference (regardless of whether it's Review, review or ReViEw..

          1 of 1 people found this helpful
          • 2. Re: Goldmine Report Filters- Filtering by Date
            Apprentice

            Thanks, Doug. I knew I was formatting incorrectly. I'm just used to the GM filters where you can use GETDATE() + 7 and tried to follow a similar line of thinking. I knew about section filters and have used them, but I would never thought of using one for the contains filter. This will come in very handy.

             

            I was actually wondering if the date + 7 was even going to be doable in the report writer. I was thinking a work-around would be to set up a process to run the report weekly and just triggering a gm filter at the time the report ran.

            • 3. Re: Goldmine Report Filters- Filtering by Date
              Doug Castell Expert

              Yeah, the report writer, it's often helpful to remember, is a 3rd-party add-in to GoldMine.  As such, it's interpretation of dBase stuff can be slightly different than, say, the dBase expression tester. 

               

              Trivia: The same (reports) add-in was used by Quotewerks for it's quote template engine.

              1 of 1 people found this helpful
              • 4. Re: Goldmine Report Filters- Filtering by Date
                Apprentice

                I had no idea it was an add-in. I'd much rather use Crystal Reports, but when we try to run the reports in GM, it always asks for the sql server password. "We've" never asked our GM Partner about this, but I'm sure they would have it running in two shakes...(I have no say in this or it would have been working yeeeears ago. )

                • 5. Re: Goldmine Report Filters- Filtering by Date
                  Apprentice

                  Ok, I'm seeing a little problem here, and I know it's operator error.

                   

                  With this as my Report filter

                   

                  cal->rectype="A".and.(cal->ondate>=sys->date.and.double(cal->ondate)<=double(sys->date)+7)

                   

                  and my sort 1 header break field as cal->userid,

                   

                  I get 36 records across 6 pages separated by userid, as expected.

                   

                  If I add my detail section filter as

                   

                  "REVIEW"$upper(cal->ref)

                   

                  It now sees 36 records, but returns 5 blank pages and 1 page with 1 record, the one record that contains Review in the cal->ref field.

                   

                  Why am I getting the blank pages for the other users that now have 0 records matching the criteria?

                  • 6. Re: Goldmine Report Filters- Filtering by Date
                    Doug Castell Expert

                    because the break tells it to go to a new page each time a new userid is seen in the results, and then, on that new page, it filters out the results that don't have the REF term. 

                     

                    You probably just want to add the ref filter part to the whole report filter and avoid the data even getting to the break or detail 1 section filter.

                    1 of 1 people found this helpful
                    • 7. Re: Goldmine Report Filters- Filtering by Date
                      Apprentice

                      Ok, got ya. I misunderstood earlier when you showed how to filter the detail section. I thought you meant that part of my filter HAD to go in the detail section! Thanks for all your help, Doug!

                      • 8. Re: Goldmine Report Filters- Filtering by Date
                        Doug Castell Expert

                        Not at all.  In my original reply, I didn't figure you were breaking by anything, so in testing the expression, I just added it to the detail1 section of my test report...  Worked it my situation, not quite right for yours.  ;)