Trying to sort report on a field pulled in from other table

johnsogj

I'm running a report over the sales line table and grouping outstanding open quantity by item. Currently the report sorts on item number. However, I am also pulling in the "Special Equipment Code' value from the item master. I'd like to sort/group all of the items by Special Equipment Code so that I can then display the total open sales quantity for each "special equipment code" value. Is this possible?

garak

Then you must redesign your report.
For example first DataItem is

-Item sorting by "Special Equipment Code" (if this key exist) group by "Special Equipment Code"
-- Sales Line linked with Item where SalesLine.Type = Item and No. = Item.No

Now you can create goup Header and group footer and create totals for your field (outstanding quantity).

Regards

johnsogj

thank you. Now the report runs over the item table and displays the total outstanding quantity for each item in the sales lien table if any outstanding quantity exists. if no open sales lines exist, the line is not displayed. I am also showing the "special equipment code" next to each item number that is displayed. (and the items displayed are sorted/grouped by special equip. code) however, I am now stuck on getting the total by special equipment code. any ideas? I used a group footer on the sales line table to total the open qty for each item. Do I do another group footer below that? do I do the group footer for the item table or the sales line table? thanks for your help

DaveT

Hi,

You will have to do a number of things:

1. select (create if needed) a key with special equipment code as the primary part in the item dataitem
2. put special equipment code in the grouptotalfields property
3. put the line of code currrreport.createtotals( "sales line"."outstanding quantity" ); in the onpredataitem trigger of the item dataitem
4. Add a groupfooter to the item section and just put in "sales line"."outstanding quantity"

The report will do the totalling for you and will even give you a grand total if you put it into the footer section of the item dataitem.

johnsogj

thanks. I was missing the CreateTotals piece.

garak

no problem.
So please set the Attribute: Solved in your forst post.

Regards