Skip to Content
0

SQL FMS to populate a unique Address ID in CRD1

Apr 13 at 07:52 PM

63

avatar image

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]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Marli Schutte Apr 17 at 03:15 PM
0

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
Share
10 |10000 characters needed characters left characters exceeded
Abdul Mannan Apr 15 at 08:39 AM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Apr 16 at 06:36 AM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Agustin Marcos Cividanes Apr 16 at 08:20 AM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Agustin Marcos Cividanes Apr 16 at 08:32 AM
1

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

Show 1 Share
10 |10000 characters needed characters left 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

0
Nagarajan K Apr 13 at 11:14 PM
0

Hi,

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

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded