AnsweredAssumed Answered

Any Way to Filter Dates Within Records

Question asked by Romba on Nov 24, 2009
Latest reply on Nov 24, 2009 by philmodjunk


Any Way to Filter Dates Within Records


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.:




                     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…