9 Replies Latest reply on Oct 27, 2013 10:09 PM by JohnColburn

    Subsummary Report, Parent, Child, Sliding Portals

    JohnColburn

      Title

      Subsummary Report, Parent, Child, Sliding Portals

      Post

           I have two tables:

           Parent table: Items,   Child table: Options, linked via the page_full field.

           For every item or option that had a value of 1 in the tagged field I wanted the report to show the item and immediately below show any options that shared the same page_full field. I set it up with the option table showing in a portal and this worked fine unless there were no options tagged and then the report would be completely blank.

           After 4 hours my head is spinning. Any suggestions would be appreciated.

           John

        • 1. Re: Subsummary Report, Parent, Child, Sliding Portals
          philmodjunk

               You'll need to describe what you want in more detail. A "tagged field" in which table? Parent or Child? A page_full field in what table?

               Best guess is that if you are dealing with this relationship:

               Parent-----<Child

               Your report should be a list view layout based on the Child table with fields from Parent included in Header, Footer, grand summary or sub summary layout parts.

               Go To related Records, a scripted find, Sorting the records can all be used to control which Child records will be included in the report and how this data is presented in that report. This process can be scripted.

          • 2. Re: Subsummary Report, Parent, Child, Sliding Portals
            JohnColburn
                                                                                                                                                                                                                                                                                                                                                                             
                      Parent and child table
                                ITEMS table                     OPTIONS table
                                page_full (keyfield)                     page_full
                                sku                     sku
                                description                     description
                                cost                     cost
                                tagged                     tagged

                 The user goes thru the list and will "tag" those items and/or options that they would like listed in the report. This puts a value of "1" in the tagged field(s).

                 I will try your suggestion to base the report on the options table. One question: If a item is tagged but there are no matching options tagged, would the item still show up in the header?

                 Thank you, as always for your help.

                  

                  

            • 3. Re: Subsummary Report, Parent, Child, Sliding Portals
              philmodjunk

                   I don't wee why you have sku, description and cost in both tables. Nor why tagged would be defined in the items table.

                   What is the nature of this relationship? Your last post appears to indicate that Page_full is the key field, but can you describe in detail how your relationship is supposed to work here? Is it truly a one to many from parent to child--which is the most common relationship when those terms are used to describe both tables? (And that is what I meant by my notation: Parent-----<Child.)

              • 4. Re: Subsummary Report, Parent, Child, Sliding Portals
                JohnColburn

                     This is a database of furniture. Below is an example of one item and some of the options that go with it.

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                    Table                     Page_full                     SKU                     Description                     Cost                     tagged
                                    ITEMS                     23a                     ECA140BT                     Nightstand with drawer                     400                      
                                    Options                     23a                     ECAORT                     reverse top on nightstand                     0                      
                                    Options                     23a                     ECO2T                     Two-tone                     29                      
                                    Options                     23a                     MEONL                     Nightlight                     30                      

                     Thanks

                • 5. Re: Subsummary Report, Parent, Child, Sliding Portals
                  philmodjunk

                       What is the purpose of Page_Full in this relationship?

                       What does a value of "1 in the tagged field" represent?

                       I would have expected a relationship that matched a SKU in Items to a new field, ParentSKU, in options or, if an option can be listed for more than one item, a join table would be used to link items and options in a many to many relationship.

                       And it is very possible that the records in items and options could be listed in a single common table with a self join relationship used to link items to options.

                        

                  • 6. Re: Subsummary Report, Parent, Child, Sliding Portals
                    JohnColburn

                         Page_full represents the catalog page where the item is found. Tagged field just reflects that that particular item or option should be added to the report. Perhaps I should rethink combining the tables into one table. That may solve my report issues. I will play with this suggestion today.

                          

                         Thanks,

                    • 7. Re: Subsummary Report, Parent, Child, Sliding Portals
                      philmodjunk

                           Does that mean that you have only one item on a page?

                           I strongly recommend defining an auto-entered serial number field in Items for uniquely identifying each record.That can then directly or indirectly link to options for that item. (link directly if every option can be linked to one and only one item, link indirectly via a join table if a given option can be linked to more than one item.)

                           I'd use a different field for the catalog page as that sounds like a value that could change from time to time.

                           Does the fact that you have a tag field in both items and options mean that you can select an item or options for that item to include in a given report?

                           (And please note that if these are temporary selection fields just for the 'report of the moment', two users tagging different records will end up with the records tagged by both users in many cases.)

                      • 8. Re: Subsummary Report, Parent, Child, Sliding Portals
                        JohnColburn

                             Sorry for taking so long to get back. Day jobs can so get in the way. :)

                             I do have an auto-entered serial number. I should have included it in my list of fields. Thanks for the tip on the tag field. Although I don't anticipate having the users access the database over a network, I should still take that into consideration.

                             I did take your advice and combine the items and options tables. This version seems to be stable now.

                             Thanks again for the advice.

                             John

                        • 9. Re: Subsummary Report, Parent, Child, Sliding Portals
                          JohnColburn

                               Oops, didn't mean to pick my own post as best answer.