Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Expression to pull alternate Supplier Part nos

Report design issues and solutions.
Post Reply
pauljj
User
User
Posts: 48
Joined: 11 Sep 2014, 11:38
Sage Version: v2018 24 UK/Europe/Africa

Expression to pull alternate Supplier Part nos

Post by pauljj » 12 Feb 2018, 08:17

<r>Hi Bruce, <br/>
<br/>
I've just been reading this post from a while back<br/>
<URL url="viewtopic.php?f=14&t=3073&hilit=ALTERNA ... <LINK_TEXT text="viewtopic.php?f=14&t=3073&hilit=ALTERNATE+SUPPLIER">viewtopic.php?f=14&t=3073&hilit=ALTERNA ... </URL><br/>
<br/>
and thought some of this could be what i need but would just like to check to see if my thinking is right.<br/>
<br/>
I've been wrangling with how to get around the fact that we can use alternate suppliers for the same product. <br/>
Sage 50 v24 windows platform.<br/>
<br/>
So, my setup is briefly, In the product record we use the PRODUCT CODE field for our own generated "Part number of material number if you will", the supplier account is our primary supplier, and the part no field is the suppliers part no.<br/>
In the web tab i have used webcat2 and 3, to list Alt Supp and Alt Part (if we have one)<br/>
I've managed to work the Purchase order we send out to show the suppliers part number and remove our product code. <br/>
<br/>
So if the following is true<br/>
PURCHASE_ORDER.ACCOUNT_REF = STOCK.WEB_CATEGORY_2 ? STOCK.WEB_CATEGORY_3 : STOCK.SUPPLIER_PART_NUMBER<br/>
<br/>
If the account we place the purchase order on is the same as the Alternate account then show the alternate part number, if not show the primary part number from the product record.<br/>
<br/>
I've used the "=" check, because their could be two states of the alternate STOCK.WEB_CATEGORY_2 field, as in, it's either filled in or it's blank.<br/>
If i'd have started with "<>" not equals, i would just switch the last two conditions around.<br/>
<br/>
So, like this it appears to work , however, there are cases where we can use upto 4 or 5 suppliers for the same part. Soooo, I thought i could utilise the Comment 1 field from the purchase order.<br/>
<br/>
So, we can order a Stock Item that's attached to a primary supplier and then resource that to say a 3rd alternate by putting their part number into the comment 1 line. the account used will be where we want to purchase from, we can pull "our product code" into the purchase order. We don't want to use the "WEB_CATEGORY" alternates, because say they have no stock. So, then i hit the expression issue.<br/>
<br/>
How do i construct the expression to go - Does your supplier purchase order match a known alternate in the product record ? - No - Then don't print the Primary stock code, ignore the alternate field and print the value in comment 1.<br/>
Also, bearing in mind that if the order has selected it's primary part no, ignore all the above and just print that.</r>

So my working expression looks like this so far
PURCHASE_ORDER.ACCOUNT_REF = STOCK.WEB_CATEGORY_2 ? STOCK.WEB_CATEGORY_3 : STOCK.WEB_CATEGORY_3 = "" ? POP_ITEM.COMMENT_1 : STOCK.SUPPLIER_PART_NUMBER

Now i know it's not quite right as i cannot pull through the primary part numbers when the first condition is False.
I'm looking into "or" as part of the solution but this would be my first time using something like this.
Any tips would be as always greatly appreciated.
Thanks
Paul

pauljj
User
User
Posts: 48
Joined: 11 Sep 2014, 11:38
Sage Version: v2018 24 UK/Europe/Africa

Re: Expression to pull alternate Supplier Part nos

Post by pauljj » 12 Feb 2018, 11:30

Just a little update - that expression seems to be working, but i'm getting mixed results on orders where the primary supplier and part number are being ordered, on some test orders for stock items, the correct part is showing, and then on another stock item it isnt (?)
the only real difference i can see is the part numbers of the ones that are showing begin with a number, and the others are alpha, i cant see how that has any bearing....scratches head

pauljj
User
User
Posts: 48
Joined: 11 Sep 2014, 11:38
Sage Version: v2018 24 UK/Europe/Africa

Re: Expression to pull alternate Supplier Part nos

Post by pauljj » 12 Feb 2018, 12:33

Think i worked out the logic

PURCHASE_ORDER.ACCOUNT_REF = STOCK.WEB_CATEGORY_2 ? STOCK.WEB_CATEGORY_3 : POP_ITEM.COMMENT_1 = "" ? STOCK.SUPPLIER_PART_NUMBER : POP_ITEM.COMMENT_1

So, because the 1st Alternate Supplier (Web Category fields) could be blank or filled with information, then the first part of the expression checks to see if there's a match (it could return a blank) if there is information there it will show that and no problem. 1st Alt selected.

The second part looks for the 2nd Alternate Supplier and if the Comment1 field is empty then this becomes the key to decide that after the first check say returns a blank, then really, both the 1st Alt Supplier and 2nd Alt Supplier are blank, therefore the system is ordering a primary part, so return the STOCK.SUPPLIER_PART_NUMBER.

The ELSE part of the expression will return the 2nd Alternate part number, if Part 1 is blank, and part 2 is not blank.

this now appears to be working, i just know that when it comes to GRN then the supplier part numbers on that screen could appear incorrect, so we are employing a preview report to show what was actually ordered, and use that to do all the usual checks and learn to "ignore" the GRN information.

I'll now need to look at the Supplier Invoices side of things ? and see if this method had an impact there too?

If you know there are any glaringly obvious errors with this setup please let me know. If it looks good then i hope others find it useful, although i have to say figuring this out was a bit like playing with time travel paradoxes :)

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest