Summarise data in a portal

[GailyD]

I apologise to anyone who has already tried to help me on this, but I am still tied in knots with what seems like a basic problem...

ORDER table - one order

ORDERED_ITEMS table - many items for one order

[there is also a Product table with one record for every variation of model + color, each defined with a unique Product_Code]

ORDERS has a form layout with a portal which should display the total of all related ORDERED_ITEMS summarised by model + color [ProductID].

Basically, if you have ordered 3 blue widgets and 1 yellow widget, that should appear as two rows in the portal.

At the moment the summary field is working but I am getting 4 portal rows - three showing "3 blue widgets" and one showing "1 yellow widget".

Question 1:

How do I relate the tables to make this work?

Question 2:

Which table should the Sum() field be in to work with the r'ship.

Thanks in advance for any advice... :

[bcooney]

I'm a bit confused. Why are you trying to summarize line items on the Order? Wouldn't it be two portal rows each with different Quantities?

[GailyD]

I'm a bit confused. Why are you trying to summarize line items on the Order? Wouldn't it be two portal rows each with different Quantities?

That is what I want, but not getting! The summary field showing quantity is working, but I am still getting a portal row for every single item...

[Fenton]

I have just had to do this, and, though it might seem a "basic" task, it is not so in terms of technique. It is basic if you're doing a subsummary report, for printing. But to show in a portal is an entirely different thing.

A portal shows related records. There is no getting around that. If you have 3 records of something and you only want to see 1 row for it, you are going to have to create a "mark" on the 1st record, or an ID that only shows up on the 1st record (the Order ID in this case). This is not terribly hard to do, with a self-relationship and an auto-enter calculation.

But you are also going to need to "maintain" that mark. That is, if a marked record is deleted, you're going to have to go look and see there's another that needs the mark. That means a script, as such a thing cannot happen any other way.

The self-relationship in this case would be on "color" AND widget ID AND Order ID. It would also be used in a Sum ( self-relationship::amount ) calculation to get the total for all the blue widgets on the order, so you could show that in the portal as the "amount" field.

You could then create a relationship from Orders to that "Order ID for 1st color|widget" field, and you'll get a "summarized" portal. Not a lot of fun, but doable.

Edited July 22, 2007 by Guest

[GailyD]

Thank you so much Fenton - I am secretly pleased it is not straightforward as I don't feel I have missed the obvious...

I will have a crack at what you have outlined and let you know..

Thanks again :clap:

[comment]

But you are also going to need to "maintain" that mark. That is, if a marked record is deleted, you're going to have to go look and see there's another that needs the mark. That means a script, as such a thing cannot happen any other way.

I believe there is a better way, a.k.a. "Ugo's method". See here, for example:

http://www.fmforums.com/forum/showpost.php?post/157908/

[GailyD]

Yes! I downloaded the example and can get it to do what I want to do... heres hoping I can translate it to my solution.

Thanks again everyone, it really is helping me over an enormous hurdle.

[GailyD]

It worked

It worked

It worked oh yeah it worked

Thanks again...

[Fenton]

Thanks comment, for reminding me of my own example file, and Ugo for the method. It allowed me to drop the "maintenance" steps from my project.

[bcooney]

Would someone please clarify for me why the summary was needed. Why not just a quantity in the OrderLineItems table? I could see if you are looking from Customers to OrderLineItems and wish to summarize.

[Fenton]

I can't speak for the original poster; and yeah, why not just a quantity field? But in my case the line items were from another data source, hundreds of phone calls, lots from the same phone#; there was no quantity field. The client wanted to see them summarized by phone#. And, not only that, they wanted this on a Title page, as well as in regular Subsummaries. Plus there were summaries of charges from 3 other tables, which had to be summarized by type, also on the Title page. Fortunately, there were not many types of those, so I knew the maximum size of the portals needed.

[bcooney]

Thanks, Fenton, for sharing in my curiosity as to why a simple quantity wasn't sufficient, especially since the OP mentions only Orders and OrderLineItems. Sometimes, the question requires a very sophisticated answer bcs they couldn't possibly have missed "quantity," right? But, sometimes novices do overlook what we consider the obvious, and I was thinking that this was the case. It would be nice to know what the OP thinks.

The technique will be kept for future reference, of course.