Skip to Content

SQL FMS to populate a unique Address ID in CRD1

Experts,

For integration into another system, I need to have unique ID's for all Addresses on the Business Partner. I created a UDF and populated this with a FMS with the OCRD.CardCode - sequential number - Ship/Bill to (C00002-3-B). This FMS works for current BP's that have addresses entered already, but for a BP with no Address it will not populate the first one.

Your help would be greatly appreciated,

Marli

SELECT top 1 Concat(T0.[CardCode],'-', sub.num+1,'-',T1.[AdresType]) 
FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] and T1.AdresType = $[CRD1.AdresType] left join (select (SELECT count( T0.[CardCode])
FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] and T1.AdresType = $[CRD1.AdresType]
WHERE T0.[CardCode] = $[$5.0]) as num, $[$5.0] as cc) sub on T0.[CardCode] = sub.cc
WHERE T0.[CardCode] = $[$5.0]

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    Apr 17 at 03:15 PM

    Hi all,

    I found that this FMS worked and solved the first record not populating.

    SELECT TOP 1 res FROM 
    (SELECT
    	Concat($[$5.0],'-', sub.num+1,'-(',$[CRD1.AdresType],')') as res
    	FROM 
    		OCRD T0  
    	INNER JOIN CRD1 T1 
    	ON 
    		T0.[CardCode] = T1.[CardCode] and 
    		T1.AdresType = $[CRD1.AdresType] 
    	left join 
    		(select (SELECT count( T0.[CardCode])
    	FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] and T1.AdresType = $[CRD1.AdresType]
    WHERE T0.[CardCode] = $[$5.0]) as num, $[$5.0] as cc) sub on T0.[CardCode] = sub.cc
    WHERE T0.[CardCode] = $[$5.0]
    UNION
    SELECT Concat($[$5.0],'-',1,'-(',$[CRD1.AdresType],')') as res ) A  ORDER BY res DESC
    
    Add comment
    10|10000 characters needed characters exceeded

  • Apr 13 at 11:14 PM

    Hi,

    FMS will work only on current forms and will not take one another screen.

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 15 at 08:39 AM

    You need to add If Condition in your query for BP without address and with address

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 16 at 06:36 AM

    Hi Marli,

    Please use this query to get a unique identifier for your FMS:

    SELECT CAST(DATEPART(YEAR, GETDATE()) AS NVARCHAR)
         + CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR)
         + CAST(DATEPART(DAY, GETDATE()) AS NVARCHAR)
         + CAST(DATEPART(HOUR, GETDATE()) AS NVARCHAR)
         + CAST(DATEPART(MINUTE, GETDATE()) AS NVARCHAR)
         + CAST(DATEPART(SECOND, GETDATE()) AS NVARCHAR)
         + CAST(DATEPART(NANOSECOND, GETDATE()) AS NVARCHAR)

    Optionally you may use the MS SQL Server Management Studio to create a Scalar-valued function in the master database, for even easier reusability in the future. In that case you could use the following syntax:

    SELECT master.dbo.YourScalarValuedFunction()

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 16 at 08:20 AM

    Hi

    try this SQL statement:

    if (select count(*) from crd1 where cardcode = $[$5.0] and adresType = $[CRD1.AdresType]) = 0
    begin
    	SELECT $[$5.0] + '- 0 -' + $[CRD1.AdresType]
    end
    else
    SELECT T0.[CardCode] + '-' + cast((sub.num+1) as nvarchar(5)) + '-' + T1.[AdresType] 
    FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] and T1.AdresType = $[CRD1.AdresType]
    left join (select (SELECT isnull(count( T0.[CardCode]),0)
    FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] and T1.AdresType = $[CRD1.AdresType]
    WHERE T0.[CardCode] = $[$5.0]) as num, $[$5.0] as cc) sub on T0.[CardCode] = sub.cc
    WHERE T0.[CardCode] = $[$5.0]
    

    Kind regards

    Agustín

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 16 at 08:32 AM

    Hi

    try this SQL statement:

    if (select count(*) from crd1 where cardcode = $[$5.0] and adresType = $[CRD1.AdresType]) = 0
    begin
    SELECT $[$5.0] + '- 0 -' + $[CRD1.AdresType]
    end
    else
    SELECT T0.[CardCode] + '-' + cast((sub.num+1) as nvarchar(5)) + '-' + T1.[AdresType] 
    FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] and T1.AdresType = $[CRD1.AdresType]
    left join (select (SELECT isnull(count( T0.[CardCode]),0)
    FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] and T1.AdresType = $[CRD1.AdresType]
    WHERE T0.[CardCode] = $[$5.0]) as num, $[$5.0] as cc) sub on T0.[CardCode] = sub.cc
    WHERE T0.[CardCode] = $[$5.0]
    

    Kind regards

    Agustín Marcos Cividanes

    Add comment
    10|10000 characters needed characters exceeded

    • Agustin,

      Thank you! When I tried this one (and previous) it gave me a selection to choose from if there was more than one Bill to or Ship To address.

      I appreciate your assistance.

      Marli