5 Replies Latest reply on Jul 17, 2014 5:55 AM by erolst

    Catalog layout grouped by category field, or...?

    dnfmptn

      Hello and thanks for reading.

      I set up an inventory database, with each item its own record. Each record has three category fields since each item can belong to up to three categories.

      In Layout, I set up a subsummary part sorted by short description and the inventory looks good, it shows one of each record but it's the whole inventory rolled together.

      I'd like to have the Layout produce a pdf and printed catalog, organized by these categories.

      ie, first pages are Widgets, then Thingamabobs, then Whatchamakallits and so on.

      Unfortunately I'm now discovering that when you use a subsummary it can only be based on one field. I tried multiple subsummary parts thinking I could get each grouping that way but it's not working.

      What's the proper way to do this? Do I need another table with categories, instead of three fields per record?

      I'd like to resolve this asap as we have a big trade show starting Tuesday...

      Dave.

        • 1. Re: Catalog layout grouped by category field, or...?
          erolst

          dnfmptn wrote:

          Unfortunately I'm now discovering that when you use a subsummary it can only be based on one field

           

          No, actually you can, but your structure is wrong. Here's a very simple example: if Item #1 is in cat A and B, and item #2 in cat B and C, you'd want a summary that shows your items in 3 groups (A:#1, B:#1,#2 , C:#2) – but you only have 2 items!

           

          What you want to do is to use the categories in your three fields to be one field – and the way to do this is (as you already have suspected) to create a child table for your Items table, where each record is a combination of itemID and a category. Then create your summary in a layout based on that table.

           

          Coming back to the example above: in this child table you'd have 4 records (1/A, 1/B, 2/B, 2/C), which you could summarize into 3 groups by category.

          • 2. Re: Catalog layout grouped by category field, or...?
            dnfmptn

            erolst; thanks for the quick and succinct reply. You've got the idea correctly. even if an item in the inventory is in 2 or 3 categories, yes it will appear in the catalog that many times. Some customers may only look at a section they're interested in and then miss an item that was in another category.

            Follow up question though, if the subsummary is based on a table with only itemID and category, then what data will fill in the Subsummary Part? I would just open up the part and plunk in the related item fields?

            • 3. Re: Catalog layout grouped by category field, or...?
              erolst

              dnfmptn wrote:

              Follow up question though, if the subsummary is based on a table with only itemID and category, then what data will fill in the Subsummary Part? I would just open up the part and plunk in the related item fields?

               

              Yes, the basic idea of a relational database is to normalize your data, use IDs and display data via relationships, instead of having one big, “flat” table and copying data.

               

              But be aware that the name field from the related Items table belongs into the body, and the category field (native to the child table) goes into the sub-summary, since that's your break/sort field.

              1 of 1 people found this helpful
              • 4. Re: Catalog layout grouped by category field, or...?
                dnfmptn

                Thanks erolst; I'm going to have to revisit this after the trade show. tried setting up another table for categories in a copy of the db and it wasn't working as expected. We'll produce smaller individual category catalogs based on the main one with all items which now happily has a 'cover sheet'!

                dave

                • 5. Re: Catalog layout grouped by category field, or...?
                  erolst

                  Maybe this is on time for the show: here's a sample file that shows how to

                   

                  a) maintain a join table, and

                   

                  b) create a report based on that join table (including complete page numbering …)

                  1 of 1 people found this helpful