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.
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.
Table 1 Bookings
Table 2 Delivery notes
Delivery note id
Table 3 Delivery specifications
Delivery note Id
Size of units
Booking ref: A123
Supplier Total number of units
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.
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.
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:
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.
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.
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
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
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.
Oh, and by the way, you understood perfectly what it is I wanted to get as a result.
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.