This can all be done in filemaker.
When you find yourself wishing you had a subform or subreport, use filemaker's portal.
When you need a query that uses an inequality, you'd define this in Manage | Database | Relationships instead of in an SQL query's WHERE clause.
You'd use script triggers in place of events.
Presumably, your Practice Dates table has a date field, I'll call it PracticeDate to store the practice date. Define a calculation field, PracticeDue, set to return date:
PracticeDate - 14
Use this to define a relationship such as...
PracticeDates:: PracticeDue > Songs::LastPracticeDate
A portal on a PracticeDates layout that refers to Songs will list all Song records with a LastPracticeDate 14 or more days earlier than the current record's practice date.
You can assign a button to update the LastPracticeDate field for a given song record by placing the button in the portal's row and giving it the following script step:
Set Field [Songs::LastPracticeDate; Get(currentdate)]
That fits what you describe. However, if you want to record a log that tracks each time a song is practiced, in MS Access or Filemaker, you'd need a join table linking songs and practice dates.
Thanks very much for your quick reply!
I see what you are saying; it does differ from my original concept, in that i was envisioning a record of each practice; i.e., a one to many relationship between a songs table and a "practices" table.
Your idea is a good one, though, too and does address the task i mentioned. But for greater flexibility, i would like to have the one to many...and i understand from my Access experience that it would require a relationship.
my question now is: assuming the structure i've described, is there a way to construct a report that would pull the maximum (i.e., most recent) practice record for each song (in SQL this would use the MAX function in a GROUP BY query) and then compare that to today's date, and list the ones where that maximum date is at least 2 weeks old? and to save that report so it could be rerun daily or whenever some practice time was available.
i guess what it boils down to is, do you have the GROUP BY functionality along with MAX, MIN, SUM, COUNT and so forth, which are standard SQL? not necessarily exposed in the same way, but functionally the same?
Yes you have those concepts available, you just won't see it in a SQL expression.
Check out Summary Report in the help system.
A sorted summary report with a find to exclude records that don't match your criteria should produce the affect you want. You can script this approach to automate report creation and "save" the results by preserving the criteria needed for the report.