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

Omit cancelled orders from sales order summary.

Report design issues and solutions.
Post Reply
samsamuel
User
User
Posts: 13
Joined: 03 Feb 2015, 15:07
Sage Version: v2015 21 UK/Europe/Africa

Omit cancelled orders from sales order summary.

Post by samsamuel » 25 Feb 2015, 16:23

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4616
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Omit cancelled orders from sales order summary.

Post by brucedenney » 26 Feb 2015, 10:21

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.
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.

samsamuel
User
User
Posts: 13
Joined: 03 Feb 2015, 15:07
Sage Version: v2015 21 UK/Europe/Africa

Re: Omit cancelled orders from sales order summary.

Post by samsamuel » 26 Feb 2015, 14:58

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.
Login to access the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4616
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Omit cancelled orders from sales order summary.

Post by brucedenney » 26 Feb 2015, 16:06

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.

samsamuel
User
User
Posts: 13
Joined: 03 Feb 2015, 15:07
Sage Version: v2015 21 UK/Europe/Africa

Re: Omit cancelled orders from sales order summary.

Post by samsamuel » 27 Feb 2015, 09:11

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

Post Reply

Who is online

Users browsing this forum: No registered users and 8 guests