4 Replies Latest reply on Dec 7, 2011 9:08 AM by philmodjunk

    counting related records by date



      counting related records by date


      I have two related tables:   Client and Appointments, related by the field ClientID.  In the appointment table, each client will have multiple appointments over several months.  I need to count the number of  remaining appointments after the current date, so that when I run an appointment list for today, I can see the number of apointments after today.  How to do this?


        • 1. Re: counting related records by date

           OK, so I tried creating a field in the appointments table "afterToday" with the calculation If(Appt_Date > Get (CurrentDate), "x";""), so it puts an "x" in the field if the appointment is after today.  Then in the Client table the field "remainingAppts" with the calculation  Count (Appts::afterToday).  This seems to work just fine, though I don't know if it's the best way to do it.

          My concern is that with over 3000 appointments in that table, will the calculation slow things down?


          • 2. Re: counting related records by date

            Given that your calculation field must be unstored if it is to update correctly with each new day, it could slow you down and you don't need it.

            To see all appointments after a specified date (could be today or any date you want to specify):

            Set up a layout based on your appointments table, not clients.

            Perform a find on this layout for all records where the date is greater than or equal to the specified date. Sort them by client, then by date.

            If you only want to see appointments for a specific client, include the ID number of the specified client as criteria in your find.

            To view all future appointments for a specified client, you could also use Go To Related Records from the Client layout to pull up all appointment records for that client, then use constrain found set to filter out all past appointment records. Sort the resulting found set by appointment and you are done. (And the client name and other info from the related client table can be included on this layout.)

            • 3. Re: counting related records by date

               I already have a layout to view the appointments as you describe.  I am just wanting to calculate remaining appointments and have that number show up on the list report that we print each day for the day's appointments.

              • 4. Re: counting related records by date

                This would be a list of clients with a number after each one?

                Use the layout I have described, but make these changes to it:

                Remove the body layout part. Replace it with a sub summary part "when sorted by Client ID".

                Put the fields you need, such as their name, from Clients in this sub summar part.

                Add a summary field to your appointments table that 'counts' the ClientID field and put it in this sub summary part.

                Perform the find I described via a script or manually on a different layout based on the same table occurrence where you can get access to the appointment date field. Then sort the records by ClientID. (If you want to list them in alphabetical order, sort the records by Client Name, then also by ClientID.)

                This will produce a list of clients with upcoming appointments with a count of how many listed with each client name.