Query doesnt work well for "Customer"

[Former Member]

Dear All,

i have this query for automatic creation of BP code which is used as FMS at the BP bode in BP master data. However, this query works fine when the cardtype is "supplier" but once it is changed to customer, it does not generate any code! Can anyone please correct this query and let me know why is this query not working for the customer?

Declare @A Varchar(10)

Set @A=$[OCRD.Cardtype]

SELECT Top(1)

Case

when @A='s' then 'VE' +

cast((select max(cast(substring(( T0.CardCode ),3,3) as int))+1 from OCRD T0 where cardtype='s')as varchar(10))

--when @A='c' or @A='l'

--then 'CU' +

--cast((select max(cast(substring(( T0.CardCode ),3,5) as int))+1 from OCRD T0 where cardtype='c'or cardtype='l')as varchar(10))

end

from OCRD T0

Thanks in advance,

Joseph Antony

[former_member204969 · Accepted solution]

I suggest this one for partner code generation. (Sudau2019s SQL may have problem with generating code for leads.)

If $[OCRD.CardType]='S'
Select 'VE'+isnull(right(str(max(substring(c.CardCode,2,10))+1000000001),5),'00001')
  From OCRD c
  Where c.CardCode like 'VE[0-9][0-9][0-9][0-9][0-9]'
Else 
Select'CU'+isnull(right(str(max(substring(c.CardCode,2,10))+1000000001),5),'00001')
  From OCRD c
  Where c.CardCode like 'CU[0-9][0-9][0-9][0-9][0-9]'

It generate 5 digits, you can change it to less or more.

[former_member583013 · Accepted solution]

Istvan,

I would like to learn SQL from you....

Could you please tell me how my SQL would not work for LEADS?

Thank you

Suda

[Former Member · Accepted solution]

Hi Istvan,

Your query is working fine for the customer and vendor, but when i select card type as lead, its still generating the code for the customer. Can you please help by correcting this part too.?

Thanks,

Joseph

[Former Member]

Thank you all for worthful suggestions. Really appreciate the efforts, Thank you all.

[former_member204969]

To Joseph:

The process generate the same type code for a lead as a customer, because the later will probably become a customer. And your code also would generate this type.

To Suda:

Your code could generate the same number for a lead as an already existing customer, because it selects the maximum from the equal type partners, and the leads and customers use the same number range.

[former_member583013]

Istvan,

Please read Joseph's original requirement and then judge my query.

Suda

[former_member204969]

To Suda:

If there is customer with CU002 and a lead with CU001 then for the next lead your code would generate the duplicate CU002. (And it supposes the existence of the first codes.)

Istvá

[former_member583013]

Joseph,

How are your trigerring this FMS. Is it set to Auto Refresh / manual by Pressing Shift+F2

Check this Query

SELECT CASE WHEN $[OCRD.CardType] = 'S' THEN 'VE' ELSE 'CU' END + 
CAST(MAX(substring(T0.CardCode,3,3))+1 AS VARCHAR)
FROM [dbo].[OCRD] T0
WHERE T0.CardType = $[OCRD.CardType]

NOTE: There is no need for Declaring variables or such a convoluted SQL.

Suda

[Former Member]

Hi Suda,

to answer your question, i'm not using auto refresh instead pressing Shift + F2 and also suda, the query that you have suggested is giving internal error.

Thanks,

Joseph

[former_member583013]

I have tested the query and it works just fine. Please make sure you do not have non-numeric BP Codes when you filter SubStrong(CardCode,3,3)

Suda

[Former Member]

How many BP in total by your projection? The formula gave too little room to grow. If you will only have maximum of 1000 customers for example, your query may work.

Thanks,

Gordon

[former_member204969]

Probably there are already customers in the table for who the substring(( T0.CardCode ),3,5) expression is not numeric. You should exclude these from the select.