1 Reply Latest reply on Nov 24, 2009 12:18 PM by philmodjunk

    Any Way to Filter Dates Within Records

    Romba

      Title

      Any Way to Filter Dates Within Records

      Post

      I need to report client activity on a quarterly basis. Our non-profit organization uses FMP 8.5; an update may be forthcoming but not before the next fiscal year – tight budget. I am relatively new to FMP, but have prior experience with Access and dBASE. I use a PC with Vista to deliver a system to the Apple Eaters at the Volunteer Center…

       

      The Aftercare Client table includes the following fifty-two (52) fields:

       

                  CaseMgr1            ...2,3  ...13

                  CareDate1           ...2,3  ...13

                  Notes1                 ...2,3  ...13

                  CareProvided1    ...2,3  ...13   This field is a Checkbox with 15 possible Values, e.g.:

       

                           Clothing

                           Food

                           Hygiene Products

                           Shelter Referral

       

      I need to account for each value within CareProvided, (this I am doing by the two-step process of summing PatternCount calculations. So far so good (if laborious!).

       

      Filtering records using the most recent CareDate (MRV) and beginning and end of quarter dates, as in:

       

                     BegQtr   <=   MRV   <=   EndQtr

       

      does not do it… Every record needs to be evaluated since:

       

      Any appointment date falls before BeqQtr, must be excluded. CareDate, CaseMgr and CareProvided for that particular date should properly have been reported in the previous quarter.

       

      If the MRV occurs after EndQtr, the relevant fields inside the quarter’s range are erroneously excluded from the totals, AND any relevant fields preceding the BegQtr should be excluded.

       

      It seems Access (and yes, even dBASE) allowed storage of relevant records in a separate table joined by the main record’s serial number. Didn’t that allow the CareDates themselves to then be filtered for analyses? Does FMP contain a similar process to permit reliable automated analyses of this data?

       

      Thanks in advance for any evaluation and guidance…

        • 1. Re: Any Way to Filter Dates Within Records
          philmodjunk
            

          "It seems Access (and yes, even dBASE) allowed storage of relevant records in a separate table joined by the main record’s serial number. Didn’t that allow the CareDates themselves to then be filtered for analyses? Does FMP contain a similar process to permit reliable automated analyses of this data?"

           

          Yes! and the fact that you have not done so is what is complicating your databse design.

           

          In Manage | Database, you can define new tables and relational links between them.

          You can use a Portal like you would an Access Subform or subreport to list records in a related table.

           

          Look up Portal in the help system to learn more. It should be a familiar concept given your experience with other database apps.