[Amy Clark]

Hello all!

I’ve been trying to add the Vendor Item Number to a report which is currently based on the ReceivingsLineItems view.  The goal of the report is to get a list of receivings with unit cost by item per site (of which we have 3). However, my user wants to include the vendor item number used on the PO/Receiving.

If I attempt any join (left outer, equals etc.) in either of the Builders it is causing a return of duplicate rows (on some RCT#s rows are in triplicate).

So I copied the script from the view to create a new one and came up with the attached, which was successful. However, I’m lost as to where in the script I add in POP30310  VNDITNUM.

Any suggestions?

Thank you in advance!!

——————————
Amy Clark
Jondo, Ltd.
Yorba Linda CA
——————————

[Steve Erbach]

Amy,

Ah! One of the famous friendly-name GP Views!

The View combines records from the working tables and the history tables. VNDITNUM is found in both POP10310 and POP30310. The VNDITNUM field needs to be added in the same relative locations in the two parts of the View in order for the UNION that combines them to work. I’d try:

...
, rtrim(['Purchasing Receipt Line'].[ITEMNMBR]) AS 'Item Number'
, rtrim(['Purchasing Receipt Line'].[ITEMDESC]) AS 'Item Description'
, RTRIM(['Purchasing Receipt Line'].[VNDITNUM] AS 'Vendor Item Number'
, ['Purchasing Receipt Line Quantities'].[QTYSHPPD] AS 'QTY Shipped'
, ['Purchasing Receipt Line Quantities'].[QTYINVCD] AS 'QTY Invoiced'
...
UNION ALL
...
, rtrim(['Purchasing Receipt Line History'].[ITEMNMBR]) AS 'Item Number'
, rtrim(['Purchasing Receipt Line History'].[ITEMDESC]) AS 'Item Description'
, RTRIM(['Purchasing Receipt Line History'].[VNDITNUM] AS 'Vendor Item Number'
, ['Purchasing Receipt Line Quantities'].[QTYSHPPD] AS 'QTY Shipped'
, ['Purchasing Receipt Line Quantities'].[QTYINVCD] AS 'QTY Invoiced'
...

Insert the two bolded lines at those spots in the query and the output should work.

Regards,

——————————
“Sparkly” Steve Erbach – Business Analyst & MS Dynamics Platform Administrator
WOW Logistics Company – Appleton, WI
VP for Communication, GPUG WI (Milwaukee) Chapter
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 (16.00.0439) / MR 2012 CU15
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: @serbach
——————————
Welcome to Dynamics!
You do not have permission.
Call Steve. (hat tip: Lou Spevack)
——————————
——————————————-

[Steve Erbach]

Amy,

I forgot the closing parentheses after [VNDITNUM] in both places! Sorry.

Regards,

——————————
“Sparkly” Steve Erbach – Business Analyst & MS Dynamics Platform Administrator
WOW Logistics Company – Appleton, WI
VP for Communication, GPUG WI (Milwaukee) Chapter
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 (16.00.0439) / MR 2012 CU15
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: @serbach
——————————
Welcome to Dynamics!
You do not have permission.
Call Steve. (hat tip: Lou Spevack)
——————————
——————————————-

[Amy Clark]

Excellent! only needed to modify slightly – was missing a closing “)”

Thank you so much, Steve!!!

——————————
Amy Clark
Jondo, Ltd.
Yorba Linda CA
——————————
——————————————-