Show Most Recent Data

[oscarbrown4]

Hi,

I am relatively new to FileMaker Pro. We have a database that manages all our figures built in Access and it's my task to "move" it into FileMaker format. We have 17 different centers, each with a "Center ID Number", and every Monday they send in data from the past week with a "Week Ending Date." All the data is stored in a "Weekly Figures" table. I want a layout where all I see is the 17 records (one from each center) with data from the most recent "Week Ending Date"... I'm thinking Max(Week Ending Date) maybe? And then next week when the centers submit their new numbers, the layout shows those numbers. I hope that makes sense. Any suggestions would be greatly appreciated. Have a great day!

Edited March 17, 2007 by Guest

[Digital Life]

You can do a find in a date field using this type of entry in find mode:

3/1/07...3/7/07

this will find everything with a date in the date field of 3/1/07 to 3/7/07 inclusive.

If you have the field on the layout you can use the insert command to insert such a range. If not then you have to get a little tricker.

Set up global Date fields called 'gStartDate' and 'gEndDate' to hold the beginning and end of the date range you want to search for.

Create a calculation field which is 'Numeric' and which is equal to the Record date field. Call it say cDate.

Then in find mode set the 'cDate' field to this:

GetAsNumber(Find Date::gStart Date) & "..." & GetAsNumber(Find Date::gEnd Date)

Then perform the find. That should find all records with a date in the range specified whether the date field is on the layout or not. Hope this helps. JJ

[aldipalo]

I took a little different approach and set up a script, which uses a variable, and is triggered by a button that will find this weeks sales and go to the report. Take a look at the attached file. Go to Sales Order layout and click Weekly Sales Report button.

HTH

Al

Sales_Orders.zip