I need to have a calc field that counts the number of appointments that use the same PrimeApptIDX. I thought the count function pointed to the "ApptTimeStart" would do the trick but I always get 1 as the result irrespective of the number of start times there are. How should I resolve this? I need to have the number of performances of of any given show by a given performer.

Hi.

As with many things in FileMaker, this all depends on where you want to see this count: in what context.

But I can make a couple suggestions.

If you'd like a report this is most easily done by creating a Summary field in the Appointments table (CCCalAppts): one that counts PrimeApptIDX. Then create a report layout with two summary parts: the first summarizing by Performer (use a field IN the appointments table which refers to your performer), and the second by Show (again, use a field IN the appointments table which refers to your show). Place your summary field in each part. Then sort your found set in the same order as your summary parts (first by the local performer field, then by the field referring to your show) and you'll get the total appointments for each performer, and then see that total broken out by show. You can use the found set to report for a specific kind of appointment, or a specific date range.

If you'd like to see this count when you're looking at a given appointment you'd do it differently. In this case you'd make a self-join relationship on your graph between AppointmentsDaily and another instance of itself (called something like AppointmentsDaily2). This relationship would match two sets of fields: match the appointments performer to itself, and match the show to itself. Then a new calc field in CCCalAppts defined as Count ( AppointmentsDaily2:: PrimeApptIDX ) will show the total number of times that performer has a record for that show. (Note that you'd need to define this calculation from the context of AppointmentsDaily: you do that at the top of the calculation screen.)

Depending on your needs you may want to add a third relationship match linking PrimeApptIDX to itself, but make the operator here ≠ (not equals) instead of equals. This will prevent each appointment from showing as a duplicate of itself.

Hope that helps.

I don't want to bother you but I have tried a great number of times to implement your second suggestion for the calc field in CCCalAppts. I don't have any trouble following your instructions however my calc fields interminably comes up with the result of "1" irrespective of the number of events with the same PrimeApptIDX. Before i go nuts trying to resolve this I beg to suggest that either you forgot to indicate a step in the process or I'm doing something wrong. I have checked and rechecked it many times. The only difference between my setup and your indications is that I don't actually have a "performer" field, so the relationship graph just matches "show" to "show" but I would have thought that that you have no incidence on how the setup works. it would be greatly appreciated if you could spare a few minutes to take another look at this problem.

< Hi Geoffrey, thanks for reposting this! >

Well it is hard to know what might be going wrong from what you describe. The way I'd test this is to expand the Edit Appointment layout and add a portal to AppointmentsDaily2 on to that screen. That portal should show you all the records matching your self-join relationship and the number of rows should be the value of your Count() calc. I imagine there is something in your relationship that is causing each appointment to only be related to itself.

Let me know if that helps,

Dear John,

I did as you suggested and the calc field in the portal generally returns "1" in the first row, "0" in th second row and nothing in the third row irrespective of whether there are 1, 2 or 18 events with the same PrimeApptIDX. But what is really strange is that I added the PrimeApptIDX field to the portal just to verify what would appear and generally the PrimeApptIDX of the same event appeared in the same number of rows that displayed a result. Once however it displayed a PrimeApptIDX number that had nothing to do with event. Very odd. Any other ideas on how we can resolve this ? Sorry if I appear to be a dummy.

Geoff

Well, no two records should have the same PrimeApptIDX. In order to test this you'd also put the Count() field outside the portal: all we're using the portal for is to test to see if your self join relationship is retuning the right number of records.

Why don't you email me the file and I'll take a look? You can send it to john at seedcode.com

(I'm stepping out for a couple hrs but will look at it when I get back.)