8 Replies Latest reply on Mar 6, 2013 12:37 PM by Pstone

    Problem: Sub-Summary of related field not picking up correctly

    Pstone

      Title

      Problem: Sub-Summary of related field not picking up correctly

      Post

           So here's my delima. 

           I have a Table-A layout that has some Table-A fields on it and some Table-B fields on it which get populated by a relationship between a Table-A field into Table-B. This way when you choose say, a model # based on the values in Table-B's Model field, the Table-B fields on the layout pop up with the Model Name, Category and Manufacturer.

           Here's what I want to do. Sort by Table-B::Category (the only field on the layout) and then do a simple Count of those records.

           I have setup a layout sub-summary based on Table-B::Category since that's the only field being used to represent that

           Consequently, I have setup in Table-B a field that simply is: Summary = Count of Category as again, this is the field being shown.

           I do a sort of Table-B:Category which correctly bunches things up together by Category.

           However, the Count is not working.

           I'm doing this also with a Table-A:Field, same method as above but everything is contained in Table-A and it works great.

           So I am wondering... does Sub-Summary NOT WORK across tables like this? Do I need to do some bizarre Relational ghost table or something?

           Thanks everyone!

            

        • 1. Re: Problem: Sub-Summary of related field not picking up correctly
          philmodjunk

               It's not the Sub summary layout part, it's the summary field. The value returned by a summary field depends on the context in which it is used. When you reference a summary field in a related table, you get a summary value based on the set of all related records. When you put a summary field from the layout's table in the sub summary layout part, you get a sub total based on your current found set and how your sort order groups the records.

          • 2. Re: Problem: Sub-Summary of related field not picking up correctly
            Pstone

                 Right - so I sort by Table B::Category and sure enough, the layout sorts by that Table B::Category.

                 In the Sub-Summary section, it's setup based on Table B::Category (what's being sorted) and the count value is taken from a Summary COUNT value that lives in Table B as a Summary Count Of "Category" as that's the field that's being displayed on the layout as "Table B::Category"

                 This is what fails.

                 If all fields lived in Table A, it works fine as I do successfuly have this working with a Model # that lives as a field in Table A but the second I try and engage the Summary Count on a related table, the sub-summary doesn't count.

                 I'm wondering if I put on my layout the related Table B fields in a PORTAL if that will somehow allow it to work. 

            • 3. Re: Problem: Sub-Summary of related field not picking up correctly
              philmodjunk

                   Please read my last post. I spelled out to you how to get the needed values to appear.

              • 4. Re: Problem: Sub-Summary of related field not picking up correctly
                Pstone

                     Yea Phil - Sorry but I've read that and I'm pretty sure I'm doing that. Using a portal doesn't fix it btw.

                     Perhaps a picture is worth 1000 words. Here's all the info on setup - Again, when everything was in table-a, it works fine when sorting by a local Table-A field.

                     When I engage a sort with an item from Table-B that IS on this layout, the correct Category sub-summary happens correctly at every change in Category but lists the "CountOfCategory" count at always 1 leading me to believe counts of related fields do not work for some reason.

                • 5. Re: Problem: Sub-Summary of related field not picking up correctly
                  philmodjunk

                       Apologies.

                       I've been answering very similar questions today on two different threads. In the other thread, I spelled this out in detail and thought I had in this one instead. blush

                       What I thought I had posted here:

                       Define a calculation field in Table A that computes the count of your related records in table B. This can use an aggregate function such as the count function or it can simply reference a summary field from Table B.

                       Set up your summary field in Table A to summarize this added calculation field. Put it in your sub summary layout part.

                  • 6. Re: Problem: Sub-Summary of related field not picking up correctly
                    Pstone

                         Whoa... that didn't work but It led the path to make it work. Thank you!

                         How to make it work:

                         You do in fact in Table A make a field called say, CountOfCategorys which is a calculation of Table B::Category

                         However, placing that in the Sub-summary doesn't work - it just kicks the subsummary to happen every line with the answer of one.

                         To get it to work:

                         Add another field in Table-A called CountOfCategorysNum which is a Summary field of the above CountOfCategorys and place THAT  in the subsummary.

                         Keep everything else the same and IT WORKS!

                         I thought I tried this before but perhaps I had the subsummary setup wrong. However, this is pulling the relational field as that calculation into Table-A and then you can do a Summary on that as you can't choose anything but the table you're in when doing a Summary so while not a bug, a work-around. 

                         Thanks for helping kick this around Phil!

                    • 7. Re: Problem: Sub-Summary of related field not picking up correctly
                      philmodjunk

                           Better read my post again. wink

                           I did tell you:

                           

                                Set up your summary field in Table A to summarize this added calculation field. Put it in your sub summary layout part.

                      • 8. Re: Problem: Sub-Summary of related field not picking up correctly
                        Pstone

                             Haha - crap - you're right - I'll have a talk with my right brain about getting too creative :)

                             Thanks again!