Newbie: Find by previous month, qtr, yr, etc...

[Shrubman]

Hello folks,

I'm happy to have now largely set up my new (first) database in FM. Now that the data is in there I am trying to set up some reports. Often we need to return only results in the past month, quarter or other calculated date ranges. How do I set up a search like that without manually choosing the dates?

For that matter, does anyone have an elegant solution to choose from a list of generic date blocks (ageing?)when loading a report?

Thanks,

Evan

[RalphL]

There are a number of tricks that you can do with dates. Use the Get ( CurrentDate) function as a starting point. Use the Day, Month & Year functions to get these data. I often use a YearMonth calculate number field = 100 * Year + Month. The first & last day of the month can be obtained by using the Date Function. First = Date (Month (CurrentDate) ; 1; Year (CurrentDate)) & Last = Date ( Month (CurrentDate) + 1; 0; Year (CurrentDate)). FileMaker will take care of Month & Year when current month is December.

I use a separate table for ageing reports. The script deletes all records in this table and then imports the records to be included. Age is based on the report date which is the creation date of the records. It then checks and omits all with 0 balance. It then sorts by customer. Then goes to report layout and the preview mode. It uses relationships to calculate the totals for both customer and grand total.

[RalphL]

I updated my sample file for ageing reports to FMP 7. The data is still from 2001. The sript for importing the data has to be completed. The report script needs to have the cleanup script added.