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

Filter on purchase order report (so only pulls through certain suppliers)

Sage 50 general help forum - Free help and support for all general issues
Post Reply
gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Filter on purchase order report (so only pulls through certain suppliers)

Post by gazmoz17 » 30 Oct 2018, 14:12

Hi,
I use an existing sage purch order report: Purchase Oder Not Delivered or Part Delivered, Filename PO_TBDEL.

I want to put a filter on the report so it only runs for 4 suppliers. However, I don’t know how to write the expression? Any help much appreciated?
Don’t know if alternatively I need to do it as a sectional filter. Only display rows if…
Current report Filter: Remaining > 0
I thought it would be something along the lines of:

PURCHASE_ORDER.NAME IS JOE BLOGGS LTD OR PURCHASE_ORDER.NAME IS SUCH AND SUCH LTD

Many Thanks in advance

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

Re: Filter on purchase order report (so only pulls through certain suppliers)

Post by brucedenney » 30 Oct 2018, 14:25

The name on the account is not a good choice as it can change. You want to use the ACCOUNT_REF field as this does not change.

Here is the sort of expression you would need if you were to apply a filter to the report.

HOWEVER

I would suggest leaving the report as it is. Instead use a Filter on the POP screen to only show you the suppliers you are interested in, then use the standard report. This way if you want to add another supplier you can do it in the filter, if you want to use this "group" of supplier with other reports you can.
Login to access the files attached to this post.
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Filter on purchase order report (so only pulls through certain suppliers)

Post by gazmoz17 » 08 Nov 2018, 10:30

Hi Bruce,

Apologies for the late response been busy with general day to day but this would make a massive diff to our control.

Can you please confirm what POP screen is?...sorry for being thick

Many Thanks
Gareth

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

Re: Filter on purchase order report (so only pulls through certain suppliers)

Post by brucedenney » 08 Nov 2018, 11:58

POP = Purchase Order
Untitled.png
Login to access the files attached to this post.
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Filter on purchase order report (so only pulls through certain suppliers)

Post by gazmoz17 » 08 Nov 2018, 12:30

HI Bruce,

Thanks for POP :roll: silly question by me ha!.

Makes sense what you said using ACC code as that wont change.

Managed to get it to work with the below expression :D :

Remaining > 0 AND (PURCHASE_ORDER.ACCOUNT_REF = "POLSPAIN" OR PURCHASE_ORDER.ACCOUNT_REF = "POLYNTFR" OR PURCHASE_ORDER.ACCOUNT_REF = "POLYNTIT" OR PURCHASE_ORDER.ACCOUNT_REF = PURCHASE_ORDER.ACCOUNT_REF = "POLYNT")

However, is there a way I can get it to only show POLYNT acc ref outstanding purchase orders for purchase orders that only contain certain prods?

Certain POLYNT prods SKU numbers: 1100
1103
1104
1105
1110
1120
3070
3100
1104
1111

Ive tried the below and it doesnt work:

Remaining > 0 AND ((PURCHASE_ORDER.ACCOUNT_REF = "POLSPAIN" OR PURCHASE_ORDER.ACCOUNT_REF = "POLYNTFR" OR PURCHASE_ORDER.ACCOUNT_REF = "POLYNTIT" OR PURCHASE_ORDER.ACCOUNT_REF) OR (PURCHASE_ORDER.ACCOUNT_REF = "POLYNT" AND POP_ITEM.STOCK_CODE = “1100” OR “1103” OR “1104” OR “1105” OR “1110” OR “1120” OR “3070” OR “3100” OR “1104” OR “1111”))

Reading the sage help forum now but struggling a bit trying to reverse engineer expressions from other reports to get the method.

Any help much appreciated

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Filter on purchase order report (so only pulls through certain suppliers)

Post by gazmoz17 » 12 Nov 2018, 08:31

Hi, any ideas guys?

Many Thanks

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

Re: Filter on purchase order report (so only pulls through certain suppliers)

Post by brucedenney » 15 Nov 2018, 11:42

You could use a filter in the report to exclude the data you do not want, however it is unclear how you are defining the data you do not want.

In general you want to make things as simple as you can, having a single stock category to filter on rather than multiple stock codes makes for a much easier expression for you to get your head round or for someone later on to get their head round.

Whilst doing long complicated expressions work, I suggest you should try and simplify things as much as you can.
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.

Post Reply

Who is online

Users browsing this forum: Ahrefs [Bot] and 11 guests