SageForum for UK versions of Sage 50- Help and support.

Hi, my boss likes to run the 'SO_LIST' report of the months sales orders by product, and he wants me to remove any cancelled orders so that they don't show up on the report to head office. We already zero the cancelled orders, so that the balance doesn't count, but he'd like them not to show at all.

I'm trying to do this by applying a filter to omit any orders with 'cancel' in the allocated column of the SO list.

I'm using 'Report - Filters - include rows where.....

NOT Contains(SALES_ORDER.ALLOCATED_STATUS, "CANCEL")

but this doesn't work.

I've also looked at using ALLOCATED_STATUS_CODE but this seems to be looking for a numeric value. I assume 0 means unallocated, 1 allocated, blahblah, but I can't find any info on which code means what.

Any ideas?

Cheers
Love the site, possibly the best Sage resource I've come across.

EDIT - I've just noticed that when I try to apply a regular filter to the SO list using

Join---------- Field-------------------------------Condition-------------Value
Where--------Order Allocated Status-----------Is Equal to------------XXX

The value field is a dropdown, thus pointing to it being a selection box (perhaps Part, Full, Cancel would be expected) but is filled with only blank lines.
Looks like a fault in Sage, perhaps?


Oh, Sage v21, by the way, all latest updates applied.

The really easy way to do this is to take advantage of the filter in the UI.

So in the UI do a filter to hide all the cancelled orders, then run the report, because you have filtered on screen you will only get the ones you need.

A little side note, why zero cancelled orders, sounds like pointless work to me.

Coming back to the original question.

The reason why it doesn't do what you expect is because you do not expect it to be Case Sensitive.

"Cancelled" does not contain "CANCEL" and so is not filtered out.

You could convert the value to uppercase first like this.

NOT Contains(ToUpper(SALES_ORDER.ALLOCATED_STATUS), "CANCEL")

This is by no means the only way, you could use the status number, you could force it lowercase and match to "cancel" and so on.

Hi, yea, pre-filtering the list is what I mentioned didn't work in my update, , , in that when i do

filter - where - order allocated status - is not equal to -

the next field is a dropdown with only blank lines in it. (see image) I would expect it to say 'full, part, cancelled' etc. but it doesn't, it just has a blank dropdown with what looks like three lines in it.


(Also, the MD is not going to run a filter before applying a report, that would be far too much to ask!)

Anyway, the string to upper method works! I never would have thought of that, since when looking at the SO list it says CANCEL in uppercase, so I was assuming it would match that.

EDIT - oh, yea, we zero the orders because the Boss sends a report of 'orders received', which gives a balance of the orders for the month. If you simply cancel an order, the value of that order still counts in the total. Now, however, we won't need to zero them, since I can now omit them.

Cheers.

You would need to use ORDER_STATUS_CODE on screen.

ahhhh, and there's the list of the 10 allocation/despatch codes. Brilliant, thanks.