1 Reply Latest reply on Jul 9, 2012 6:15 AM by philmodjunk

    Report filter by Date



      Report filter by Date



      I have a table like this,

      PersonName [Text]

      CreatedDate [Date]

      ItemBought [Number]

      ItemReturned [Number]

      RunningBalance [Number] - This field stores the cumulative (ItemBought - ItemReturned) from the begining up until the current record, for this PersonName. This field is to be filled in by script


      Suppose there are 5 records as below:

      PersonName     |     CreatedDate     |    ItemBought    |    ItemReturned

             A                     1/1/2012                       3                        4

             A                     3/1/2012                       5                        1

             B                     3/1/2012                       3                        6

             A                     5/1/2012                       7                        8

             B                     2/1/2012                     10                       15


      The report generated have to be grouped by PersonName, and sorted by PersonName and CreatedDate, and filtered by date range. If the date range is 1/1/2012 to 5/1/2012, the report is as below


      PersonName      TotalBalance  

                                             CreatedDate     |    ItemBought    |    ItemReturned     |     RunningBalance

             A                      2

                                               1/1/2012                    3                           4                              -1 <--- (3 - 4)

                                               3/1/2012                    5                           1                               3 <--- (-1 + (5 - 1))

                                               5/1/2012                    7                           8                               2  <--- (3 + (7 - 8)) (is TotalBalance)

             B                     -8

                                               2/1/2012                  10                         15                              -5 <--- (10 - 15)

                                               3/1/2012                   3                           6                               -8 <--- (-5 + (3 - 6)) (is TotalBalance)


      If the date range is 3/1/2012 to 5/1/2012, the report is as below:


      PersonName       TotalBalance  

                                               CreatedDate     |    ItemBought    |    ItemReturned      |      RunningBalance

             A                       2 (still the same)

                                                3/1/2012                    5                          1                               3

                                                5/1/2012                    7                          8                               2

             B                      -8(still the same)

                                                3/1/2012                    3                          6                              -8


      If the date range is 1/1/2012 to 1/1/2012, the report is as below:


      PersonName      TotalBalance  

                                              CreatedDate     |    ItemBought    |    ItemReturned     |    RunningBalance

              A                     2 (still the same)

                                               1/1/2012                    3                         4                              -1

              B                    -8 (still the same) (still display PersonName B even no record of B matches the date range)



      I need some help in displaying PersonName when that person has no record matches in the date range.

        • 1. Re: Report filter by Date

          This was a simple report to do in FilMaker until you added that last requirement, that you want to list all names, even when there are no records for that name in the specified date range. That little detail really complicates the report compared to just listing the persons who have transactions in the specified date range. Left Outer Joins aren't something FileMaker can do in a list view type report like this.

          Do you have a related table where you have one record for each person? Seems like something useful to have for such a database. If you do, you could base your report on that table of persons with a filtered portal (or if you are using A version older than FileMaker 11), or a filtered relationship  to display the individual transactions. For printing purposes, you'd need to size the portal to be many rows tall and set it to slide up, resize enclosing part.

          The alternative, which would be rather messy, is to add one blank record for every person to your table and perform your date range based find with a second, request that includes \these blank records in order to provide place holders for the persons with no transactions in the specified date range. Excluding these additional records for those persons that do have transactions would take a bit of processing of your found set after performing the find.

          Key facts about sliding layout objects:

          1. It's only visible in preview mode and when you print/save as PDF...
          2. All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
          3. Objects in headers and footers will not slide.
          4. Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
          5. Fields will slide up only if Top, alignment is specified for it and will slide left only if left alignment is specified.
          6. Consistent side borders are difficult to achieve with sliding fields.