Summary report with sub summary parts can do this but not rows in a portal.
You can set up a related "summary" table with one record for item A, one Record for Item B and use a relationship to your table of items sold to compute summary totals. You might even be able to use your Products table for this purpose. To set up the relationship for your portal, consider using the X operator to match to all summary or Products records, then use a portal filter, if needed to filter out records you don't want listed in the portal (such as items with 0 total sales).
I can get a related table showing all records, but can not get the "summary" table like you mentioned showing only one record for item 1, one record for item B, etc. How can i get this?
You'd need a relationship such as:
ReportLayout'sTable::anyfield X Products::anyfield
Products::ProductID = SalesItems::ProductID
This is the most basic relationship, but you may find you need to define global fields in Products to match to fields in SalesItems in order to limit the records being totaled up. (All sales items for a given date or range of dates, for example.)
In regards to the global field:
I am putting the products portal on a customers tabel based layout.
So each customer has it's own portal of items ordered.
I only have one table for products. So i'm relating customers to products but then i don't have another salesitem table to join it to.
Shoudl i just do a self join of products to itself? If so, do you recommend any type of global field?
You can select a "box" in Manage | Database | Relationships and then click the duplicate button (two green plus signs). This creates a duplicate "occurrence" of an existing table. It's not a new table, just a new way to refer to an existing one. You can use such added table occurrences to make additional relationships between your tables.
Given your relationships you might set up this arrangement:
I'm guessing you may have an invoices table in there, but it's not strictly ncessary that you do.
ProductsSummary would be a new occurrence of your products table. ItemsOrderedByProductId would be an added occurrence of ItemsOrdered.
Your portal would be to ProductsSummary and the intervening occurrence of ItemsOrdered will "filter" the related records down to just those ordered by the current client. No X operator needed in this example. The match fields in both links to ProductsSummary would be your product ID field and your calculation field would use the sum function to sum a field in ItemsOrderedByProductID.
I'm still unable to get the portal to group the items together. I've created a mock up of the database and i'm hoping from this you might be able to help me further. *Fingers Crossed* How can i send it to you?
I have the filter on the portal and everything just as you've explained. I'm hoping that i'm just missing something simple.
Thank you so much for your help!
The portal does not group anything. It's a portal toa different table where you have one record for every group. Since you have one record for every group, this portal can than display the totals you want. Have you created such a table. Have you created the needed records in it?
My relationships look like this:
Customers - Order Tickets - Products - New Self Join Products per your above post
Customers:Customer Name - Orders: For Customer ; Orders: Product ID - Products: Product ID ; Products: Product ID - Products Self Join: Product ID
I want a portal on a customers layout to list teh products they have ordered with the totals of each products only. Right now, i can get a portal to list every item, but not group them into the summary field that i have.
Portal on Customers Layout is pulling from Products (Filtered via Products Self Join)
To which table occurrence is the portal?
The portal is the products table occurance with the filter portal records checkbox marked; filtered by products self join.
I did make a mock up of the database if you would like it.
Apologies for my first post today. I was recalling a different thread on the same subject and didn't review previous posts carefully enough. A portal to products should list one row for every product purchased by that customer and leave out any that have never been purchased by that customer. Do you see that in the portal?
I think I see the problem and owe you a second apology for not forseeing this complication. You are getting totals for all sales for each product, not the sales for just the current customer, correct?
We'll need a relationship that filters out records from other customers. Unfortunately, this requires a set up and scripted support I would prefer to avoid, but it does work.
Make your relationship between Products and items sold like this:
ProductsSummary::ProductID = ItemsOrderedByProductID::ProductID AND
ProductsSummary::gInvoiceIDList = ItemsOrderedByProductID::InvoiceID
gInvoiceIDList is a global text field and a script would be required to update it's value each time you access a different Customer record:
Set Field [Products::gInvoiceIDList ; List (Invoices::InvoiceID) ]
I know you have offered to share the file. You can do so by uploading a copy to a file sharing site and then posting the download link here. You can save a clone of the file so that there is no sensitive data present in the copy you upload. I've been attempting to make sure this was really necessary first.
FINALLY GOT IT!
Thanks SOO much Phil!