3 Replies Latest reply on Dec 30, 2009 4:35 PM by philmodjunk

    Can this be accomplished in Filemaker Pro?



      Can this be accomplished in Filemaker Pro?


      Hi folks.  I'm considering buying Filemaker Pro.  I'm an experienced Access developer.  I know the two products are very different, but since I like using my Mac, and need to develop some personal databases, I'm willing to learn Filemaker's way of doing things.


      Here's my question: suppose i have a table of songs, and a table of practice dates, where the relationship is one to many.  i.e., one song, many practice dates.  


      my goal is to be able to pull up a list of songs i have not practiced for 2 weeks.   I.e., where the difference between today's date, and the maximum of the practice dates for a song, is greater than 14.  


      final result would be a list of several songs that have not been practiced for 2 weeks (or more.)


      could this be stored as a report that i could run daily to get dynamic results?  i.e., if i practice a song on monday, and enter that record, it would not appear again until two mondays later.  similarly, new songs would crop up daily as the time between practices hit 14.


      thanks for any input.   

        • 1. Re: Can this be accomplished in Filemaker Pro?

          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.



          • 2. Re: Can this be accomplished in Filemaker Pro?

            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?  

            • 3. Re: Can this be accomplished in Filemaker Pro?

              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.