[Pam Palmer]

As I’ve mentioned before, I have enjoyed using Victoria Yudin’s SOP_POP_LINK sql https://victoriayudin.com/2009/02/04/sql-view-to-show-items-with-sop-pop-link/

I need to add a couple of fields to my search and am wondering how to incorporate it all.

SOP60100 has the two fields I want to add to my Smartlist (QTYONPO, QTYRECVD) but I do not know how to add them to the view or to the SmartList i’m using without crashing everything.

If anyone has some insight into the easiest way to do this, I would be grateful.

Currently using GP 2018

Thank you,
Pam

——————————
Pam Palmer
Project Mgr/Database Admin
Temple, Inc.
Decatur AL
——————————

[Aref Karam]

Hi Pam,

If you are using the same SQL view from Victoria Yudin’s site, you just need to change the SQL view query with this one:

SELECT SH.CUSTNMBR CustomerID, SH.CUSTNAME CustomerName,
S.SOPNUMBE OrderNumber, S.ITEMNMBR Item,
S.ITEMDESC ItemDescription, L.PONUMBER PONumber,
PH.VENDORID VendorID, PH.VENDNAME VendorName, L.QTYONPO, L.QTYRECVD

FROM
(SELECT SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC
FROM SOP10200
UNION
SELECT SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC
FROM SOP30300) S

INNER JOIN
SOP60100 L
ON S.SOPTYPE = L.SOPTYPE AND S.SOPNUMBE = L.SOPNUMBE
AND S.LNITMSEQ = L.LNITMSEQ

LEFT OUTER JOIN
(SELECT SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME
FROM SOP10100
UNION
SELECT SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME
FROM SOP30200) SH
ON SH.SOPTYPE = S.SOPTYPE AND SH.SOPNUMBE = S.SOPNUMBE

LEFT OUTER JOIN
(SELECT PONUMBER, VENDORID, VENDNAME
FROM POP10100
UNION
SELECT PONUMBER, VENDORID, VENDNAME
FROM POP30100) PH
ON PH.PONUMBER = L.PONUMBER

If your SQL view has the same name as Victoria’s then you can just run the below script:

Alter VIEW view_SOP_POP_Link
AS

/**
view_SOP_POP_Link
Created Feb 4m 2009 by Victoria Yudin – Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
Shows all SOP line items with SOP – POP link present
Updated on Sep 1, 2018 to fix join and unions
**/

SELECT SH.CUSTNMBR CustomerID, SH.CUSTNAME CustomerName,
S.SOPNUMBE OrderNumber, S.ITEMNMBR Item,
S.ITEMDESC ItemDescription, L.PONUMBER PONumber,
PH.VENDORID VendorID, PH.VENDNAME VendorName, L.QTYONPO, L.QTYRECVD

FROM
(SELECT SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC
FROM SOP10200
UNION
SELECT SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC
FROM SOP30300) S

INNER JOIN
SOP60100 L
ON S.SOPTYPE = L.SOPTYPE AND S.SOPNUMBE = L.SOPNUMBE
AND S.LNITMSEQ = L.LNITMSEQ

LEFT OUTER JOIN
(SELECT SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME
FROM SOP10100
UNION
SELECT SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME
FROM SOP30200) SH
ON SH.SOPTYPE = S.SOPTYPE AND SH.SOPNUMBE = S.SOPNUMBE

LEFT OUTER JOIN
(SELECT PONUMBER, VENDORID, VENDNAME
FROM POP10100
UNION
SELECT PONUMBER, VENDORID, VENDNAME
FROM POP30100) PH
ON PH.PONUMBER = L.PONUMBER

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_SOP_POP_Link TO DYNGRP

——————————
Aref Karam
Data Consultant
LTI Information Technology
Ann Arbor MI
——————————
——————————————-

[Pam Palmer]

That did the trick!  Thanks so much for the assist!

Pam?

——————————
Pam Palmer
Project Mgr/Database Admin
Temple, Inc.
Decatur AL
——————————
——————————————-