10 Replies Latest reply on May 11, 2012 10:34 AM by philmodjunk

    Getting subtotals in a portal

    StefanÖstlind

      Title

      Getting subtotals in a portal

      Post

      Hi,

      I need to make a table where I get the subtotal of delivered units in a portal in my bookings layout. Let me explain.

      I make one booking to deliver units to a client of different sizes.

      I then source these units from 2 suppliers.

      I have one table for bookings and one for supplied units where the supplied units is a table of supplier code and no of units. I join each delivery of units to a booking reference in the bookings table.

      In the bookings layout I then want to look at a booking and see how many units each supplier has contributed with in total. I only manage a running total for each supplier or a total for all suppliers.

      Very grateful for any help here.

        • 1. Re: Getting subtotals in a portal
          StefanÖstlind

          Hm. Got a step on the way. I used the GetSummary ( Number of Cartons ; Grower Code ) function and named the field Subtotal. However, when I insert the Subtotal field in my portal, it repeats the subtotal for each Grower (supplier) for each registered record.

          • 2. Re: Getting subtotals in a portal
            philmodjunk

            Can you spell out the relationships you have between your two tables?

            How do you link a record in Bookings to a record in DeliveredUnits?

            I would assume that one record in Bookings documents a single delivery (but of more than one item) to a given customer?

            How do you list the multiple items delivered for a given record in Bookings?

            It sounds to me like you need an additional table for listing the items delivered for a given bookings record, but that's a guess based on very limited information.

            • 3. Re: Getting subtotals in a portal
              StefanÖstlind

              Table 1 Bookings

              Booking ref

              Table 2 Delivery notes

              Booking ref

              Delivery note id

              Table 3 Delivery specifications

              Delivery note Id

              Supplier

              Units

              Size of units

              Layout booking

              Booking ref: A123

              Supplier Total number of units

              John       600

              Gary      1200

              Total 1800

              Comments

              I start in table 1 creating a booking, getting a ref. Then I creat a delivery note in table 2 and then in table three I specify every delivery. Here I have 5 different sizes and each supplier can supply any size.

              In the layout, I only want the total number of units per supplier, not every size of every unit.

              • 4. Re: Getting subtotals in a portal
                StefanÖstlind

                I can't make this work so what I have created instead is a separate layout where I get all subtotals. I would like to have only these subtotals directly in a portal in the bookings layout though.

                • 5. Re: Getting subtotals in a portal
                  philmodjunk

                  Correct me if I am wrong, but you appear to have these relationships:

                  Bookings---<DeliveryNotes-----<Delivery Specifications   (---< means "one to many")

                  Bookings::BookingRef = DeliveryNotes::BookingRef

                  DeliveryNotes::DeliveryNoteID = DeliverySpecifications::DeliveryNoteID

                  Where BookingRef uniquely identifies a specific record in Bookings and DeliveryNoteID uniquely identifies a record in DeliveryNotes. I'm drawing that inference from the field and table names, but could be wrong--so please confirm or correct my conclusions here.

                  If I have your relationships correct, what is the purpose of DeliveryNotes? It would seem that Delivery Specifications could link directly to Bookings.

                  On the other hand, maybe you have:

                  Bookings---<DeliveryNotes>-----Delivery Specifications

                  Where DeliveryNoteID uniquely identifies a specific record in Delivery Specifications.

                  This info is crucial to producing what you need as it tells me where the needed data resides and what possible relationships might be added in order to produce the results that you want.

                  • 6. Re: Getting subtotals in a portal
                    StefanÖstlind

                    Hi,

                    Booking ref in Table one is unique and one of the Records in Table 1 needs to be chosen when creating a record in Table 2. Delivery Note Id in Table 2 is also unique and has to be chosen in Table 3.

                    The only reason for Table 2 to be used is a workflow issue. i could have done without it but for ease of registration I chose to have an extra table.

                    Workflow:

                     

                    1 On order planning: Create a booking ref in Table 1

                    2 On supplier planning: Create a Delivery Note ID

                    3 On supplier delivery: Register units delivered

                     

                    • 7. Re: Getting subtotals in a portal
                      philmodjunk

                      So for a given booking record, you might have this data in Delivery Specifications:

                      23 Units of Type A by Supplier Acme Inc.
                      5 Units of Type B by Supplier Acme Inc.
                      6 Units of Type A by Pinnacle
                      7 Units of Type B by Pinnacle

                      and you sant to see this in your portal?

                      28 units Acme
                      13 units Pinnacle

                      If that's what you want to see, you'll need to add a table of suppliers with some creative relationship design to set up a filtered relationship to match to this data by Delivery Note ID and Supplier ID

                      • 8. Re: Getting subtotals in a portal
                        StefanÖstlind

                        Hmm.. OK. I was hoping it was an easier way right in the portal setup that I missed. Will probably just go with a separate layout since I am not sure how to organize the relationship design.

                        Thx for taking the time.

                        • 9. Re: Getting subtotals in a portal
                          StefanÖstlind

                          Oh, and by the way, you understood perfectly what it is I wanted to get as a result.

                           

                          • 10. Re: Getting subtotals in a portal
                            philmodjunk

                            A summary report of this is much easier to set up. Portals are limited to one record per row, you can't have one row represent multiple records. Thus, to get the results described, you end up with an intermediate table where you can create one record for each row of the portal, but use a relationship to match to and total up the individual records. the use of the extra Delivery Notes table, further complicates that approach.