4 Replies Latest reply on Jan 18, 2010 2:06 PM by tessgold

    Unsophisticated user needs help finding most recent date

    tessgold

      Title

      Unsophisticated user needs help finding most recent date

      Post

      I hope someone will take pity on me. I have been using Filemaker for a long time, but as a user, not a database developer. So I am familiar with the basics and know how to create simple fields. But something that I thought would be simple has me stumped:

       

      I am using Filemaker Pro 9 and have a database that tracks, among other things, attendance at group meetings. This particular issue involves 2 tables, the Client Table and the Attendance Date Table. They are linked through a Client ID #. We have people who have as few as 1 visit and others with almost 200 visits.

       

      All I want to do is to be able to pull up a report that shows me which people have not attended in over a year. So I am trying to create a field that will show me the most recent visit for each client. I have tried using both the Min function and the Max function and also Last, but they all bring up the same date for each client, which is the first attendance date for that client.

       

      Is this a sorting issue, or is there something else that I'm missing?

       

      I would appreciate any help at all. Thank you. 

        • 1. Re: Unsophisticated user needs help finding most recent date
          ninja
            

          Hi tessgold,

           

          The Max() function is what you need.  Let's make sure you're setting it up correctly.

           

          In your "LastAttended" field, which is in your Client table, set it up to be a calculation field.

          Make sure the result is a date.

          set the calculation to be:

          Max ( Attendance:: Date)   ###Choose the Attendance:: Date field from the upper left pane ###

          Put the LastAttended field on your client layout. 

           

          Find a client that has attended multiple times and look at thier record in the client layout...is it correct?

          Look at a couple other clients too.

           

          If they are not correct, please describe what you see for the Max() result, and what dates the client attended.

           

          Note: extra spaces added after colons to avoid smileys

          • 2. Re: Unsophisticated user needs help finding most recent date
            tessgold
               Thank you, Ninja, so much. I am now beating my head against the wall. In shame. It works like a charm. The only thing I was doing wrong was creating the LastAttendance field in the Attendance table, rather than in the Client table. I really appreciate your help.
            • 3. Re: Unsophisticated user needs help finding most recent date
              LaRetta_1
                

              Another possible way, which I believe would be faster, would be to perform a find from the Client table.  It would be:

               

              Enter Find Mode [ uncheck pause ]

              Set Field [ Attendance::date ; "  ≥  " & Date ( Month ( Get ( CurrentDate ) ) ; Day ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate )  ) - 1 ) ]

              Set Error Capture [ On ]

              Perform Find [ ]

              Show Omitted Only

               

              ... add the proper error trapping of course.  This will show you all clients who have attended within one year from today.  Then when you Show Omitted, you will have a found set of clients who haven't attended within one year from today.  You don't need to have the attendance field on the client layout to perform the search if you use a script and DO use your Attendance::Date field for searching instead of a calculation field in Clients because the attendance date will index in the attendance table. :smileyhappy:

              • 4. Re: Unsophisticated user needs help finding most recent date
                tessgold
                  

                Thank you LaRetta, I'll try this too. I'm just so happy to have even one solution that I can use tomorrow! To have two is almost too much to have hoped for.