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.
Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
Omit cancelled orders from sales order summary.
- brucedenney
- Site Admin
- Posts: 4616
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Omit cancelled orders from sales order summary.
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.
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.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Omit cancelled orders from sales order summary.
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.
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.
Login to access the files attached to this post.
- brucedenney
- Site Admin
- Posts: 4616
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Omit cancelled orders from sales order summary.
You would need to use ORDER_STATUS_CODE on screen.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Omit cancelled orders from sales order summary.
ahhhh, and there's the list of the 10 allocation/despatch codes. Brilliant, thanks.
Who is online
Users browsing this forum: No registered users and 8 guests