9 Replies Latest reply on Oct 2, 2013 1:31 PM by philmodjunk

    Report problems

    JamesDeakins

      Title

      Report problems

      Post

           Hi,

           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.

      tableStructure-2.png

        • 1. Re: Report problems
          philmodjunk

               And on which of these table occurrences is your report layout based? (And I can't make out much detail from your screen shot...)

          • 2. Re: Report problems
            JamesDeakins

                 I based the report layout on Equip_Join because I have "many" instances of equipment for each scene. It didn't seem to work the other way.

            • 3. Re: Report problems
              JamesDeakins

                   I've tried drawing out the structure by hand to see if that would be more readable.

              • 4. Re: Report problems
                philmodjunk

                     Your relationships don't support the structure of the report that you want. Your list of equipment is linked to a specific Scene record in "main table". There's nothing here that links specific equipment to specific dates on your shooting schedule.

                • 5. Re: Report problems
                  JamesDeakins

                       I tried before to have a field in the Equip_Join table that linked to dates. But then I felt like I was "tearing the records apart" because when I input the records, I had the join table as a portal to a form with the Main table. So I would get to the scene and there might be more records for equipment and yet, perhaps only two shoot days so there would be records of equipment with nothing in date field. It's funny because my report almost works. Most everything is there - there are only a couple of places where information is missing.

                       What structure would you suggest? I'm sorry if I'm being dense.

                  • 6. Re: Report problems
                    philmodjunk

                         If you want distinct lists of equipment for each shooting day, then each record in EquipmentList must be linked to a specific date. If the same piece of equipment is used on more than one date, it must be listed twice in EquipmentLIst, once for each date. Otherwise, your database has no way to determine which equipment to list under each shooting date sub heading in your report.

                         Please note that with your current structure, you could base your report on Shoot_Days with a portal to EquipmentList to list equipment, but you'll see exactly the same equipment listed for each day that you shoot a given scene. You'll need to decide if that is a workable alternative.

                    • 7. Re: Report problems
                      JamesDeakins

                           Hi Phil,

                           I had come to that conclusion and was beginning to re-enter all the information into the Equip_Join table which is definitely a laborious process! You mentioned using a portal and basing the report on Shoot_Days but aren't portals less elegant for printed reports because they leave extra space if there are only three pieces of special equipment vs 10?

                           Also, I do want the report to list, under each date, each scene to be shot and under each scene the equipment. So there may be be repetition of equipment under the shoot days if there are scenes that day that use the same equipment (and since we try to shoot similar scenes per day, that will most probably be the case) but no repetition of equipment under the individual scenes, right?

                           I guess my only option at this point is to manually re-enter the equipment into the Equip_Join table and add a shoot date to each record. I seem to have done a lot of double work on this! The pain of learning, I guess.

                           I did have one other question that came up. When you create an instance of a table, if the original table gets updated with new records, will those records all be in the other instance of the table automatically?

                           Thank you very much for your timely help. I appreciate it a great deal.

                      • 8. Re: Report problems
                        philmodjunk
                             

                                  aren't portals less elegant for printed reports because they leave extra space if there are only three pieces of special equipment vs 10?

                             Portals do have limitations and thus I try to avoid using them in reports but sometimes they remain the best option. For printed/previewed/saved as PDF reports, you can set a portal to slide up and to resize the enclosing part to reduce the portal down to just the minimum number of rows needed to display the data.

                             But keep in mind that using your original design, this would list exactly the same equipment for each day that you shoot the same scene. If that's workable, then I'd go with the portal option if it is at all possible to do so. IF not, then what you would seem to need here is a "star join" between three tales instead of two, though I am not really sure that you actually need the DateValues table--there's no obvious reason for that table in what you have shown so far.

                             So either you need equip Join set up as a join table that links to Main, Date Values AND EquipList or you might keep it the way it is, but require that a date field contain a date for each record in the join table.

                        • 9. Re: Report problems
                          philmodjunk
                               

                                    When you create an instance of a table, if the original table gets updated with new records, will those records all be in the other instance of the table automatically?

                               Well your "instance of a table" is what we call a Tutorial: What are Table Occurrences?. Both Table Occurrences will point to the same Data Source table and you can see the name of this table if you point your mouse at the arrow in the upper left corner of the table occurrence box. These are really just two different "labels" in your relationship tab that refer to exactly the same table and thus any change to the data will be automatically accessible via either table occurrence reference.