6 Replies Latest reply on Jan 2, 2013 12:29 PM by LeahRiley

    Grayed Out Subtotal Options

    LeahRiley

      Title

      Grayed Out Subtotal Options

      Post

           My question is regarding calculating a subtotal in a layout that includes fields from two different tables.  

           I have no issues sorting by category and then using the Trailing Subtotals to see the Average, Count, etc of a field within the parent table for that layout.  However, when I then include fields from other tables linked through a database relationship (primary key), the subtotal options are all grayed out.  This is true for data in a different table or in a completely different file.

           I have included a screen shot of the issue.  Note that this is not a problem for any field that is part of the parent table.  I can also go to the parent table of the linked fields and sort and subtotal appropriately.

           Any help would be much appreciated!

      Screen_shot_2012-12-28_at_3.55.25_PM.png

        • 1. Re: Grayed Out Subtotal Options
          philmodjunk

               You cannot use this method to compute subtotals for values from a related table.

               You'll need to describe your tables and how the two are related before we can suggest a way to compute the sub total.

               I also suggest that you investigate creating a list view layout with sub summary layout parts as this can provide a nicer looking result for computing and displaying aggregate values such as subtotals, averages, etc.

          • 2. Re: Grayed Out Subtotal Options
            LeahRiley

                 Thank you for the help.  

                 My tables are connected with a simple primary key.  Both tables have a numeric field called "#" and Table1::# is linked to Table2::#.  Very basic.  The relationship allows for two way editing.  

                 Any suggestions regarding making a subtotal would be greatly appreciated.

                 Thanks.

            • 3. Re: Grayed Out Subtotal Options
              philmodjunk

                   But is this a one to many relationship or one to one? Could there be many records in table 2 linked to table 1?

              • 4. Re: Grayed Out Subtotal Options
                LeahRiley

                     One to many.

                     Each record in Table 1 has a unique #, increasing numerically.  I'll call this Table "Material".

                     Table 2 is the "Device" Table.  I may make 5 devices with the same Material found in Table 1. 

                     From this relationship I can build a larger table which combined by Materials with my Devices.

                • 5. Re: Grayed Out Subtotal Options
                  philmodjunk

                       It would be simpler to compute the total from a layout base on table 2 rather than table 1. In such a layout, a summary field can correctly compute totals and sub totals. (Sub summary layout parts may be needed.)

                       From Table 1, define a calculation field as Sum ( Table2::fieldYouWantToTotal)

                       And now you have a field that you can reference with a summary field to get totals from the context of table 1.

                       Note: if you just want the total of related records in Table 2 for each record in Table 1, you can define a summary field in Table 2 and put it on your table 1 layout to show that total.

                  • 6. Re: Grayed Out Subtotal Options
                    LeahRiley

                         Thanks for the help.  I'll give that a try and let you know how it comes out.