8 Replies Latest reply on Apr 25, 2012 7:40 AM by MattLeach

    Reporting last activity

    MattLeach

      I have a database with two main tables, Clients and Appointments.

       

      I'm being asked to create a report that will list all clients that have not had an appointment within the last 3 months. I'm not really sure how to even begin setting this up.

       

      In the appointment table there if a field for the date of the appointment. Any guidance would be greatly appreciated. Thanks

        • 1. Re: Reporting last activity
          Mike_Mitchell

          How about:

           

          Enter Find Mode [ ]

          Set Field [ Appointments::Date = GetAsText ( Date ( Month ( Get ( CurrentDate ) - 3 ) ; Day ( Get ( CurrentDate )) ; Year ( Get ( CurrentDate )))) & "..." & GetAsText ( Get ( CurrentDate )) ]

          Perform Find

           

          At least for a starting point. You'd probably want to use the Let function to keep from parsing out the Get ( CurrentDate ) function multiple times, but you get the idea.

           

          HTH

           

          Mike

          • 2. Re: Reporting last activity

            Hi Mike,

             

            But this would not include Clients without an Appointment at all.  I would instead reverse the find, searching for all Clients who have appointments within the timeframe and then Show Omitted.  Also, it wasn't mentioned and I know you know this - it can be ran from Clients - no need to go to the Appointments table.  The date in Appointments is stored so the search will be fast.

             

            Also no need to wrap with GetAsText() since you are setting a date field.

            1 of 1 people found this helpful
            • 3. Re: Reporting last activity
              Mike_Mitchell

              Grr ... I meant to say "Omit". You're right. I had it that way when I was writing it as a simple Find request and it got lost in the transition to a script step. What you really want is to Find [Omit Records] (insert calculation above). That will omit all records that do not have a date within that range.

               

              And yes, you can run this from either context; but I agree that it should be run from the Clients context since you're looking for clients, not appointments.

               

              Good tip on the GetAsText. Used to be, if you just used the Date function, it would give you the raw number value if you parsed that out in a calculation with the range characters (...). Seems they've fixed it.

               

              Thanks.

               

              Mike

              • 4. Re: Reporting last activity
                MattLeach

                Using the data viewer i put together your example but i'm not sure i'm doing something correct. The calculation i am attempting is:

                 

                Let (

                  [

                     cdate = Get ( CurrentDate )

                  ];

                 

                 

                  Date ( Month ( cdate - 3 ) ; Day ( cdate ) ; Year ( cdate )) & "..." & cdate

                )

                 

                According to the data viewer, this evaluates to:

                 

                4/25/2012...4/25/2012

                 

                 

                One thing i probably should have mentioned before but i'm not sure if it has any effect on the calculation would be the date format. This database uses a date format of dd\mm\yyyy on all date fields. Would that have any ill effects on this search?

                • 5. Re: Reporting last activity
                  Mike_Mitchell

                  Syntax error.    :-)

                   

                  Month ( cdate - 3 ) gives you the month of the current date, minus three days. What you want is the month of the current date, minus 3. Try:

                   

                  Month ( cdate ) - 3

                   

                  Mike

                   

                  P.S. Date format is irrelevant; that's why we use the Date function. It tells FileMaker we're dealing with dates; it'll handle figuring out the particulars. Gotta love it.     

                  • 6. Re: Reporting last activity
                    MattLeach

                    Apparently one cup of coffee this morning wasn't enough, DOH! Made the change and not it is evaluating properly.

                     

                    As stated above, this shows all records with appointments in that time frame. Where does the Omit come into play. I've never used the omit function before so i want to make sure i do it properly.

                     

                    It seems using the Omit Record script step locks me to one record which does have an appointment in the range. Would i use the Omit Multiple Records step?

                    • 7. Re: Reporting last activity
                      Mike_Mitchell

                      Sorry. Probably should have given you that little detail.

                       

                      You'll need to set a Find request in your Enter Find Mode script step and set the Omit flag there. That way, FileMaker has something to modify.

                       

                       

                      find.png

                       

                       

                      Oops! Guess I need another Diet Coke ...         

                       

                      Mike

                      1 of 1 people found this helpful
                      • 8. Re: Reporting last activity
                        MattLeach

                        Thanks for all of your help! Greatly appreciated.