3 Replies Latest reply on Jul 14, 2017 8:09 AM by philmodjunk

    Sub Summary Report with one to one table related fields


      Hey FM Community,


      I am trying to create a report that simply displays the totals for each ticket or pass by if they are checked in or not. I can do this successfully for all in Table A, but when i add related (one to one) fields it wont give me the sub summary total for those.


      Table A (People): [id_people], [CheckedIn], [TicketType1],[TicketType2], [and other contact info]

      -----People includes my breakfield [CheckedIn], a simple 0 or 1. And also includes several number fields [TicketType1] i want to report on totals of check in vs not.


      Table B (ParkingPasses): [id_people], [Pass1], [Pass2]

      ---- I have several number fields that are different types of passes


      I want to include these fields on the same report, so i can print them on one page, but i cant figure out how to do it. 

      1) Is it possible to put multiple related fields from different one to one tables on the same report?

      2) I suppose i should, but do I have to, im on a time crunch, convert all these tickets/passes into their own one-to-many table? so instead of: ticketQTY1, ticketQty2, ticketQty3 convert to: id, ticketType, TicketQty. I would prefer to keep them flat, unti l have more time to revisit this.


      ideally i would move the Table A [TicketTypes] Fields to their own table, another 1-to-1 relationship, or better yet i would make it a one-to-many.----note I know these tickets and passes should be broken down into line items, but my data is being imported/new records are coming from a spreadsheet where each ticket is in its own column, scripting the import process to convert into line items is beyond me right now. so for now im keeping it flat).


      Thank You


        • 1. Re: Sub Summary Report with one to one table related fields
          Jens Teich

          It might be helpful if you show us the relevant part of the relationship graph.

          • 2. Re: Sub Summary Report with one to one table related fields

            Some things that may get you started:

            1. related fields just put on the layout will only show the first child record (by sort order in the relationship, or by creation order if not sorted)


            So, yes, you can put many related fields on the layout. If they are all one-one, then you should have no problem even with different relationships.


            2. Summaries can be tricky with related fields.

                 a. if the calculation is for Count(relatedTable::field) in the parent table, it will show only the count for the relationship.

                 b. if the summary field is in the child and placed in a portal, it will honor the relationship and even any filters on that portal. This is handy for filtered portals! Sometimes this is just a single row portal showing the summary field.


            3. The summary field in the child records can be used in a standard list view sub-summary report (also showing related parent fields, if desired). It relies on the sort of that report.


            4. The GetSummary() is another way to get a value without the sub-summary report in children. But it does use the summary field and sort fields to get the correct values.


            If you have questions on any of these, let us know (along with those screen shots!)


            • 3. Re: Sub Summary Report with one to one table related fields

              The use of multiple fields for the same data Ticket 1 Ticket2... Pass1, Pass2 suggests you may other issues with your data model that complicate your report.