Grouping and subtotaling line items in portal

[mark_anderson_us]

Hi Al

I wonder if you can help me. I have a portal that is displaying line itme from a related table. eg.

Qty....Item....Unit Price....Total Price

1......Dog.....20.00.........20.00

1......Dog.....20.00.........20.00

1......Cat.....10.00.........10.00

1......Cat.....10.00.........10.00

1......Cat.....10.00.........10.00

What i want to do is display the following instead

Qty....Item....Unit Price....Total Price

2......Dog.....20.00.........40.00

3......Cat.....10.00.........30.00

I'm completely lost. If I make a report with subtotals in the related table, it's fione, but I cannot get the summary to appear on my main form (with fields from the parent record.)

Can anyopne help me out/point me in the right direction?

Thanks

Mark

[-Queue-]

You can't summarize like this in a portal. You would need another table that contains only one instance of each Item and Qty and Total Price calculations of the related Item.

[mark_anderson_us]

Thanks queue. Looks like I've been pulling my hair out for nothing. Can you give me a hint on the easiest way to create the summary table?

[-Queue-]

Here's a sample that assumes you already have an Items table (which you should anyway) It performs the calculations in the Items table, so that a portal isn't necessary. If you want to display this information in another table, however, you could create a Cartesian Join (use the X operator to link any two fields between the tables) and base your portal on that relationship.

SummaryTable.zip

[mark_anderson_us]

thanks a lot queue. that's exaclty waht I want EXCEPT, I need it as part of a form (see attached screen dump). In the second portal, I need to get the summarized values. i have a primary key in the related table, which is a concatenation of worktype and material and that's what i need to subtotal

So the last 3 visible lines should be grouped intoa single entry with Qty 3 and total costs.

Any suggestions?

Regards

mark

[-Queue-]

I'm having a difficult time visualizing this. Could you attach a stripped-down sample of your file, with a few sample records I can play with?

[mark_anderson_us]

Hi Queue

So attached is my DB. Basically, I want to summarize the job costing (subtotaled by "key"). If you look at job number 1234, you wil see (on the job summaries layout) that "Creo PTP" "regular or origial work" is listed 5 times, but I want a count of how many listings and the subtotal of cost

Regards

mark

JobCosting.zip

[-Queue-]

I've added a table to hold a record for each Job Number, Material, and Work Type. Records are created when your Import Costs script runs. I've also added serial numbers to the JobCostings table and the new table, a key self-relationship based on Job Number_Material_Work Type and calculation for determining whether the key is the first instance of its kind in the JobCostings table, another relationship from the new table to JobCostings based on the same type of key, and Qty and Charge fields in the new table that are a Sum of related fields in the JobCostings table.

I only ran an update (Import Costs, disabling the import script temporarily) on Job 1234. But it seemed to work. You appeared to be importing into the JobSummaries table, so I moved the Go to Layout [JobCosting] before the import, so that you'll be importing into JobCosting instead. I also changed the Go to Record/Request/Page [by calculation] to [First]. The calculation you had would always try to go to the 0th record if the Job Number were not empty. This ends up being 1 anyway. So it's easiest just to hard code it. However, I don't believe you even need that step. When an import runs, it should leave you on the first record afterward. That's how it works in 5.5. Perhaps 7 is different, but I haven't tested it.

JobCosting.zip

[mark_anderson_us]

Thanks a lot queue. You're a star. It solved my problem perfectly.

Regards

mark