11 Replies Latest reply on Dec 21, 2011 5:05 PM by arronman

    Creating a report which includes records within a range of dates

    arronman

      Title

      Creating a report which includes records within a range of dates

      Post

      I've been trying to create a script triggered by a button where fields from records are selected according to a range of dates. My problem is that I have already created a report where only records that contain >0 in a number field are displayed. This works fine.

      I want to be able to create a new report which limits the above results to a range of dates

      I have tried without success to use:

      Go To Layouit[my above report)    -- (sorted by date with sub-totals)

      Enter Find Mode[]

      Insert Calculated Result [Select; Table::aDate; Table::Start Date & "..." & Table::End Date

      Perform Find[]

      -- where Start Date and End Date are Globals, and aDate is Date field (tried Indexed and not Indexed)

       

      Spent a lot of time going in circles ...

       

       

        • 1. Re: Creating a report which includes records within a range of dates
          Sorbsbuster

          Try:

          Go to Layout [YourLayout]

          Enter Find Mode []

          Set Field [ ThatNumberField ; ">0"]

          Set Field [ YourDateField ; gStartDate & "..." & gEndDate]

          Perform Find []

          There is no need to sort the records first.

          • 2. Re: Creating a report which includes records within a range of dates
            arronman

            Thanks for your suggestion Sorbuster, but I'm still having trouble. [I'm in a different time zone (Sydney Australia) and busy doing other stuff as well, hence the delay in responding]

             

            Error: "No records match this criteria"  Don't know why as many of the records actually do have [ThatNumberField ; ">0"] . This is the same error I was getting when using Insert Calculated Result

            Could it be because I'm attempting to display them in a report layout with a leading sub-summary by "date" (not the field I'm using to store the global start & end date results; ie YourDateField)?

             

            I just created another layout which loads all records, regardless of ThatNumberField value.  Also has aDate, gStartDate and gEndDate fields. So they load, but when the button with script:

            Go to Layout[YourLayout] *--actually the same layout: might this be the problem? --*

            Enter Find Mode []

            Set Field [ ThatNumberField ; ">0"]

            Set Field [aDate; gStartDate & "..." & gEndDate]

            Perform Find []

            Since the above I have tested with the gDates & button on a different layout, but still the same no records ...

            Any ideas would be most gratefully received.

             

             

            • 3. Re: Creating a report which includes records within a range of dates
              Sorbsbuster

              The layout you are looking at is based on the same table that the aDate field is defined in, yes?

              What happens when you do the find manually (both the >0 and the date range value)?  If you can't see the fields on the layout because it only has subsummary parts then temporarily show a body part with the 3 fields in it.

              If that doesn't work let me know.

              If it does work, then insert a 'Pause Script step just before the Perfrom Find step and see what search criteria are in the fields, and let me know.

              • 4. Re: Creating a report which includes records within a range of dates
                arronman

                Manual find works with >0, but not with date range.  I was putting 12/12/2011...14/12/2011 in aDate.  Is that correct? NB date field formatted dd/mm/yyyy.

                Tried putting aDate, gStartDate, gEndDate and script button all in body of report layout.  Still "No records match this criteria"

                ?????

                • 5. Re: Creating a report which includes records within a range of dates
                  arronman

                  Further info ...

                  aDate is Date Indexed (All), gDates Global (Validate only during data entry)

                  • 6. Re: Creating a report which includes records within a range of dates
                    Sorbsbuster

                    Don''t worry about the indexing at this stage, it will only make the search slower or faster - not make it function or not.

                    On a layout that is based on the same Table Occurance as aDate, have a body section with 3 fields:
                    - aDate
                    - gStartDate
                    - gEndDate

                    Enter find mode, set the date range in aDate and see what records it returns. If it returns no records, tab into the aDate field and press Control-I and see if the dates that you expect to see are listed.

                    If it returns the found set you expect, set the gStartDate and gEndDate fields, and run the script.  Include the Pause step before the Perform Find and check that the date range looks like you want.

                    Add the Number field to the body section.  Do the find manually - setting the same date range as before, and also setting >0 in the Number field.  How many records does it return?

                    • 7. Re: Creating a report which includes records within a range of dates
                      arronman

                      Weird!

                      On a layout that is based on the same Table Occurance as aDate, have a body section with 3 fields:
                      - aDate
                      - gStartDate
                      - gEndDate

                      Entered Find. Set 12/12/2011...14/12/2011 manually in aDate. Performed Find.  No results CMD-I (Mac) showed nothing in pop-up window.

                       

                      Ran script via button - could see 12/12/2011...14/12/2011 automatically entered in aDate.  No result ...

                      • 8. Re: Creating a report which includes records within a range of dates
                        Sorbsbuster

                        If you have clicked into the aDate field and Cmd-I brings up a list of nothing, then there is no data in the field.  Are you sure you haven't set the aDate field to be global also?

                        • 9. Re: Creating a report which includes records within a range of dates
                          arronman

                          As a test I created a blank file with only one layout, containing ThatNumberField, Date & aDate - both simple date fields, gStartDate & gEndDate.

                          No scripts, just 8 records with various dates number values.

                          Entering date range manually in the record date field works correctly Cmd-I shows the full range of dates, Find result correcly limits the records found.

                          Created a simple version of your scriptDoing same in aDate doesn't work, shows no data with Cmd-i:

                          Go to Layout [YourLayout]

                          Enter Find Mode []

                          Set Field [ YourDateField ; gStartDate & "..." & gEndDate]

                          Perform Find []

                          On run script you can see the correct data in aDate (my eg: 12/12/2011...14/12/2011), but still the no records found message and no data with Cmd-1

                           

                          All this after shutting FMP & rebooting Mac ...

                          • 10. Re: Creating a report which includes records within a range of dates
                            philmodjunk

                            I use this method all the time and it works. When you open Manage | Database | Fields, find gStartDate and gEndDate and double click them to check the field options, is global storage specified?

                            In manage | database | fields, do you see "date" specified as the field types?

                            • 11. Re: Creating a report which includes records within a range of dates
                              arronman

                              Oh boy! Discovered my problem: I thought aDate was some magical field which related esoterically to the date fields in my records. Doh! And I think I'm a pretty smart dude ... Maybe, but not with programming.

                              Changing script to point the set field to Date instead of aDate (containing no data, of course) works a charm.

                              Wasted only a zillion hours of my time and a little of Sorbsbuster and PhilModJunk's.

                              Thank you so much for your patience.