This one came up from a query a client had about linking a sales order transaction line to the customer item in SmartList Builder. The problem is that to join two tables together, you need all of the key fields to be on the same table; unfortunately, with the SOP Transaction table, this isn’t the case when you want to link to the customer item.
SOP10100 (Sales Transaction Work) holds the CUSTNMBR (Customer Number), but SOP10200 (Sales Transaction Amounts Work) holds the ITEMNMBR (Item Number) which are both needed to link to SOP60300 (Sales Customer Item Cross Reference) which holds the customer item number and description.
While there may be a way to do this in SmartList Builder I’ve not been able to work it out (other than using two calculated fields), it is easier, quicker and more reusable, to create a simple SQL View which returns the relevant information.
In this case the view I created works only for transactions which are at a status of work:
CREATE VIEW uv_PI_SOPCustomerItemLink
AS
SELECT
SOP101.CUSTNMBR
,SOP102.ITEMNMBR
,SOP603.CUSTITEMNMBR
,SOP603.CUSTITEMDESC
FROM
SOP10200 AS SOP102
INNER JOIN
SOP10100 AS SOP101
ON SOP101.SOPNUMBE = SOP102.SOPNUMBE
AND SOP101.SOPOwner = SOP102.SOPOwner
INNER JOIN
SOP60300 AS SOP603
ON SOP603.CUSTNMBR = SOP101.CUSTNMBR
AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO
GRANT SELECT ON uv_PI_SOPCustomerItemLink TO DYNGRP
GO
The SQL above includes the Grant statement used to add select permissions for the DYNGRP.