Hi, I am trying to write query for sales order information, this query would provide all information related to sales order, I am finding difficulty with writing query for Pack no, this is what my results are, based on the query I have written its deriving duplicate pack number.
Is there a way to combine sales order and line number in the query
Thank you in advance for your time
Are you just asking for 1 field that shows Order-line example being your first line 5-2?
It would just be a calculated field where you say
OrderDtl.OrderNum + ‘-’ + OrderDtl.LineNum
That is if you are using the Order Detail table. Sub out the fields with what you are actually using.
Thanks Nick, Do I have to add a User define field in the data table in order to derive pack number from the shipdtl table
Not sure I fully understand.
Are you just trying to show any packing slips related to that order and line?
Yes, I’m trying to show all the packing slip for that sales order and line
Discussed (And resolved) at Length on this thread.
2 Likes
Thanks Jose, I checked that thread, it provides solution to query from single field, i.e deriving PO to Job, but am looking for sales order and line no combination to derive packing slip, I had no issue in deriving invoice number, but am finding it difficult to program for combination.
other option I was trying was to create a UD - Userdefined filed with combination of Sales order and Line no and then using it in the query.
Make a calculated field in your BAQ. There is an editor for this that will show you all the fields and and most of the functions) you can use to make the new column (i.e. - calculated field)
my guess the dups are due to how the tables are being joined.
OrdDtl has ordernum, order line
ShipDtl has ordernum, orderline, and orderrelnum
multiple release orders would cause dups for PackNum, Pack line
How are you joining the tables? Posting the query phrase will help you get more assistance.
What are the Displayed columns in SubQuery4? And are they grouped?
Your BAQ is listing all packs associated to that ordernum. The list appears to be correct. Since you have multiple lines with the same order, you are seeing “duplicates” .
You may want to add the orderline in your join so that you know the packs associated to each order line vs. the order. I don’t know your business case, but multiple lines for us means different parts. Having it broken down by line would be more beneficial.
Hi Ken, I’m trying to write baq for sales order information, its a one stop query for sale order, in this query I am deriving Pack no info, pack no are combination of SO and Line number,
Where should I join the order line so as to remove duplicate information
btw thanks for your time and interest in this issue
Here’s one I’ve used recently. The subquery:
Note: the “Distinct” will filter out duplicates.
Note: your connections will be different, but you’ll need ‘FOR XMD PATH(’’)’ on the end of the last criterion expression. That’s two single quotes inside the parentheses.
REVERSE(stuff(REVERSE(REPLACE(REPLACE(({SubQuery4}), ‘</Calculated_DetailPlantList>’, ‘’), ‘<Calculated_DetailPlantList>’,’’)), 1, 1, ‘’))
The reverse/stuff/replace removes the trailing comma.
Disclaimer: I’ve stolen all this from other, smarter folks.
See if this helps.
Joe
Thanks Joe, I’ll try it out