Also problems in automatic generation of business partner no.

[michael_benner]

Hello,

I've studied all the entries in the forum concerning automatic BP no. generation, but I am getting

the internal error 8180 with the follwoing script:

if $[ocrd.cardtype] ='S'

begin

select CAST ((MAX (CAST (T0.CardCode AS Numeric)) + 1 ) AS VARCHAR) from [dbo].[ocrd] T0 where T0.cardtype = 'S'

end

else

begin

select CAST ((MAX (CAST (T0.CardCode AS Numeric)) + 1 ) AS VARCHAR) from [dbo].[ocrd] T0 where T0.cardtype != 'S'

end

Our GP Code starts with 2701000000 for customer and leads and 2702000000 for suppliers. Related companies have external numbers like 0009000100 with leading zeros.

Can anyone help ?

Thanks in advance.

Michael

[michael_benner]

Hello,

finally I've asked a B1 consultant and got the followiong query as solution:

SELECT CAST((MAX(CAST(T0.CardCode as numeric)) + 1) AS varchar) FROM [dbo].[OCRD] T0 WHERE T0.CardType IN ((SELECT CASE WHEN $[OCRD.CardType] <> 'S' THEN 'C' ELSE 'S' END) UNION (SELECT CASE WHEN $[OCRD.CardType] <> 'S' THEN 'L' ELSE 'S' END)) AND T0.CardCode <> 'a'

Michael Benner

[Former Member]

Hi,

Only if you can guarantee all your BP codes are number, you can run this FMS.

You may check forum to find quite a few threads discussing it.

Better treat the lead identifier as letters.

Thanks,

Gordon