Which report are you referring to ?

but possibly create a new expression on the layout called QTY_REMAINING using   GDN_Item.QTY_ON_ORDER - GDN_ITEM.QTY_DESPATCHED

It doesn't have the joins for SOP Items it only has GDN items. My expression should be 

SOP_ITEM.QTY_ORDER - (SOP_ITEM.QTY_DELIVERED+SOP_ITEM.QTY_DESPATCH)

i have got GDN_ITEM.QTY_ORDER and GDN_ITEM.QTY_DESPATCH 

is there anyways i could add a join on the lay out for SOP items. 

You can change the joins. I was only giving an example as you didn't state what 'report' you were referring to.

It is always helpful to other readers if you could state what report/layout you are trying to modify before asking the question which is in relation to the appropriate report/layout. Sometimes one's brain runs ahead when you're trying to get an answer to an issue

Its for a delivery note. its saved as a report 

one of them in here (Reports) ?

or in here (Layouts)

the one highlighted is the one we use. but my joins look like this 

I got this join suggestion by Googling the requirement but I couldn't make it work for me. You'll have to add the SOP_ITEM file to the joins.

So if you make a join between GDN_ITEM.UNIQUE_ID and SOP_ITEM.ITEMID you can access information out of the SOP_ITEM table for that GDN_ITEM record.

We dont really used SOP so I have limited data to play with (and it took forever to run on my few orders - would probably been best if I'd selected one order to print from).

We need Sage Darron Cockram  Andy Rickeard to point you in the right direction......

Ken Starnes said:

I got this join suggestion by Googling the requirement but I couldn't make it work for me

I'm not surprised you can't make it work as it is incorrect advice. GDN_ITEM.UNIQUE_ID is the unique Identifier of the record in the GDN_ITEM table so does not relate to the SOP_ITEM table so could not be used to base a join on.

To join the GDN_ITEM table to the SOP_ITEM table you would need to use both the ORDER_NUMBER and ITEM_NUMBER fields. With this done the calculation for remaining is then simply SOP_ITEM.QTY_ORDER - SOP_ITEM.QTY_DELIVERED.

BUT beware that if you have multiple dispatches for an order and needed to reprint an older GDN you will get the current amount remaining and not the amount that was remaining at the time the GDN was originally raised. For example you have an order for 10 items and issue a GDN for 5 of them day one, and another GDN for the remaining 5 on day two. When printed initially the 1st GDN would correctly show 5 remaining, and when printing the 2nd GDN it would correctly show the calculation as 0 remaining. However, if you reprinted the 1st GDN it would now also show as 0 remaining.

Not particularly easy to deal with this as the SOP_ITEM always represents the most up-to-date data. The only way I can think of to potentially handle 'historical printing' would be a much more complex approach of using sub reports to sum up the quantity dispatched from any earlier GDNs for the same order/item via linked criteria. That is getting in to some pretty advanced territory that even I would find challenging, so incorrect outstanding amounts for historical printing may be the sort of limitation that is best to just accept. Hopefully a pretty uncommon situation anyway.

Hope that helps.

Thanks Darron - am trying to help Katie out.
I've tried the join - it runs slow and I'm getting strange results on a completed order with nothing outstanding.......

Join types are both Inner as I assume that is the default.

What I get : -

Order is actually : -

The join won't be particularly quick as it is dealing with non-indexed fields unfortunately.

In terms of the strange results I can't replicate that from a brief test. What I would recommend doing to troubleshoot is to temporarily include the raw data fields to help see where things are going wrong. Fields I'd suggest including for now are SOP_ITEM.QTY_DESPATCH, SOP_ITEM.QTY_ORDER, SOP_ITEM.QTY_DELIVERED and SOP_ITEM.STOCK_CODE. This should help show if we have the joins right and that the underlying data is as we expect.