Showing records with no child record in a summary part

[Djvc]

Hi all -

In our Filemaker database, we have an "Appointments" table and an "Appointment Services" table (being one or more services linked to an Appointment). I am working on a print layout to list all the Appointments on a given date, with a sublist of the Appointment Services for each appointment (shown beneath each Appointment).

I have set the layout to display records from the Appointment Services table (in the Body part), and I have a leading subsummary part which lists the appointment details from the "Appointments" table (when the records are correctly sorted). This works fine, except that some Appointments don't have any Appointment Services connected with them, and I would also like these Appointments to be listed in the printout (they don't show up currently, as I would expect, given that the layout is displaying Appointment Services, not Appointments). I have thought of the following options but would appreciate some help as to the best route to approach this. It's similar to a situation where you might want to show a list of all invoices, each with zero or more line items (as it stands, invoices with no line items would not show up).

1. I could change the layout to display records from Appointments instead - all Appointments would then be listed, however I don't think I could show multiple Appointment Services under each (only the first one)?

2. I could create a script to loop through the found Appointments, checking for any with no Appointment Services assigned, and create a record in the Appointment Services table for each of these appointments - it would be a 'dummy' Appointment Service (perhaps with a title "No Services")

I would prefer not to use option 2 above, as it seems a bit excessive - usually services will be added at some point, after the report is printed, I just want the report to show all Appointments, i.e. with none, one or more services. Is there another way to achieve this?

Thanks very much.

Edited December 28, 2022 by Djvc

[comment]

5 hours ago, Djvc said:

I could change the layout to show records from Appointments, but then I don't think I could show multiple Appointment Services under each

Actually, you could. You could use either a portal or a calculation field using the List() function. However, you would have to set a maximum number of rows for the portal or a maximum height for the field - and hope that the number of child records does not exceed that limit. And you might experience problems when the portal or the field spans a page break.

5 hours ago, Djvc said:

I would prefer not to use option 2 above

And rightly so. 

5 hours ago, Djvc said:

Is there another way to achieve this?

There isn't really a good solution for this, but your best option is probably to use a reporting table and a technique commonly known as "virtual list". In a nutshell, you create a variable containing a line for each row in your report - parent and child alike -  and the table uses calculation fields to parse the variable into individual records and fields. But this requires quite a lot of work and some learning to get it right. Do a search here and on the web to get some tutorials and examples.