I am having trouble with strange behavior in my report. I have two subsummaries and then details under that works most of the time but then, surprisingly, doesn't. I've checked each table individually to make sure that the data is indeed there.
What I'm trying to do is divide the report by shoot dates, then within that date, the scenes to be shot and under the scene, the special equipment needed. And mostly, this is working.
My structure is this:
JOIN TABLE (EQUIP_JOIN) which includes a Scene ID foreign key which relates to the SCENE LIST (MAIN) table's main ID and an equipment ID foreign key which relates to the the EQUIPMENT (EQUIPLIST) table's main ID
There is a SHOOT DAY JOIN (SHOOT_DAYS) table that has the SCENE ID foreign key from the SCENE LIST (MAIN) table's main ID and a Date ID Foreign key from the DATEVALUES table's ID.
After a lot of trial and error, everything was working except that I noticed if there was a date that followed directly a date and they both had the same scenes scheduled, the second date just wouldn't print. And then occasionally a scene or two didn't show up under the date - even though it was definitely in the SHOOT_DAY table.
The date subsummary is based on the DateValues table Date field since there is only one instance of each date there. The scene subsummary is based on a "Full Scene" field (MAIN table) which is a calculated field joining the "prefix" field and the "scene" field since there can be a scene 10 and a scene A10.
I have sorted on the DateValues table Date field and then sorted on the scene, prefix and then full scene field since the scene field is numerical and the full scene field is a text field.
I am using Filemaker Pro 12. I've attached a diagram of the table structure. This is my first attempt with Filemaker although I have used other databases before. And I'm not using the DataValues 2 instance at all - that was a failed idea! :)
I am probably doing something very stupid.
Thanks for any help. I've been going round and round on this.