Sometimes in a FileMaker Pro database, a need arises to relate only to records within a specific range of criteria. Take for example a calorie/weight database. Imagine if you only want to display the weight figures for the past 7 days.
You set up the relationship to the weight table so that it has two criteria instead of just one. In the “Edit Relationship” dialog, you can click on “Add” to add a second connection.
So, in this specific example, assuming that you are displaying all data from a table called “Dates” with a relationship to another table called “Current Weight”. You could use the Date field in the “Current Weight” table (called “wDate”) to relate to two different date fields in the Date table.
One of the dates in the date table (”dToday”) is just a simple date field. The other date in the date table (”dTodayRange”) is a calculation field. Its calculation looks like this:
dDate - 7
Double click on the box in the relationship line to open the “Edit Relationship” dialog, and then get creative with ≥ and ≤ instead of just using an =.
By setting the relationship to be less-than-or-equal to the current date, and greater-than-or-equal to the date from 7 days ago, you’ve just set up a range that will always have the 7 most recent dates in it. The end result can live in a portal that always shows the most recent data, no matter what the date is.
