Jump to content
Sign in to follow this  
GailyD

Summarise data in a portal

Recommended Posts

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... :

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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:

Share this post


Link to post
Share on other sites

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/

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

It worked :yay:

It worked :yay:

It worked oh yeah it worked :yay:

Thanks again...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.