On this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.
As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):
CREATE VIEW uv_AZRCRV_CustomerItemLink AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
SOP102.SOPNUMBE
,SOP102.SOPTYPE
,SOP102.LNITMSEQ
,SOP101.CUSTNMBR
,SOP102.ITEMNMBR
FROM
SOP10200 AS SOP102 WITH (NOLOCK)
INNER JOIN
SOP10100 AS SOP101 WITH (NOLOCK)
ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
INNER JOIN
SOP60300 AS SOP603 WITH (NOLOCK)
ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO
GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
GO