Formatted Search not working..

[Former Member]

Hi SAP Gurus,

i have created a Formatted Search for Business Partner. below is the code.

select top 1 LEFT(cardcode,2)+  RIGHT(CAST(000 + (10001+ right(cardcode,4)) AS varchar(5)), 4)
  from OCRD 
  where CardType = $[OCRD.CARDTYPE] -- 's' 
    and CardCode LIKE $[%OCRD.CARDCODE.CHAR(1)%]
    order by CardCode desc

the out put of the query should be  : 'VU0010'

but bad luck its not working.. does any one know the proper query ..?

i appreciate your help.

Thanks and Regards,

kaviprashu

[Johan_H · Accepted solution]

Hi Kavi,

Try to move the % characters out of the variable:

and CardCode LIKE '%' + $[OCRD.CARDCODE.CHAR(1)] + '%'

Regards,

Johan

[Former Member · Accepted solution]

Hi,

Thanks Jogan, but its not working..

original code is as follows

select top 1 LEFT(cardcode,2)+  RIGHT(CAST(000 + (10001+ right(cardcode,4)) AS varchar(5)), 4)
  from OCRD 
  where CardType = 's'
    and CardCode like '%u%'
  order by CardCode desc

The above code return new Vendor code..

i appreciate your help.

Thanks and Regards,

kaviprashu

[Johan_H · Accepted solution]

Hi,

Does it work when you put the numbers in parenthesis (line 1) ?

select top 1 LEFT(cardcode,2) +  RIGHT(CAST('000' + ('10001' + right(cardcode,4)) AS varchar(5)), 4) 
 from OCRD   
where CardType = 's'  
          and CardCode like '%u%'  
order by CardCode desc 

Regards,

Johan

[Former Member · Accepted solution]

Hi,

Thanks Jogan, its not working..

old query

select top 1 cardcode, LEFT(cardcode,2)+  RIGHT(CAST(000 + (10001+ right(cardcode,4)) AS varchar(5)), 4)
  from OCRD 
  where CardType = 'c'
    and CardCode like '%v%'
    order by CardCode desc

result:

old value (from database)	new value   (to be added in db)
VS0055	VS0056

modified query with parenthesis

  select top 1 cardcode, LEFT(cardcode,2)+  RIGHT(CAST('000' + ('10001'+ right(cardcode,4)) AS varchar(5)), 4)
  from OCRD 
  where CardType = 'c'
    and CardCode like '%v%'
 order by CardCode desc

result

old value  (from database	new value   (to be added in db)
VS0055	VS0010

i appreciate your help.

Thanks and Regards,

kaviprashu

[Johan_H · Accepted solution]

Hi Kavi,

Is there a common patern to the CardCodes ? I.e. are they all made up of 2 letters and 4 digits ?

Regards,

Johan

[Former Member · Accepted solution]

Hi,

Thanks Johan, yes the format for CardCodes is fixed to 6 digits for eg VS0010

Thanks and Regards,

kaviprashu

[Johan_H · Accepted solution]

Okay, how about this:

select TOP 1
       RIGHT(SUBSTRING(CardCode, 1, 2)
             + CASE
                WHEN LEN(CAST(MAX(CAST(SUBSTRING(CardCode, 3, 4) AS INT)) + 1 AS NVARCHAR(15))) = 1 THEN '000'
                WHEN LEN(CAST(MAX(CAST(SUBSTRING(CardCode, 3, 4) AS INT)) + 1 AS NVARCHAR(15))) = 2 THEN '00'
                WHEN LEN(CAST(MAX(CAST(SUBSTRING(CardCode, 3, 4) AS INT)) + 1 AS NVARCHAR(15))) = 3 THEN '0'
                ELSE ''
               END
             + CAST(MAX(CAST(SUBSTRING(CardCode, 3, 4) AS INT)) + 1 AS NVARCHAR(15)), 6) AS CardCode
  from OCRD   
where CardType = 'S'
      and CardCode like 'h%'
group by CardCode
order by CardCode desc

[Former Member · Accepted solution]

Hi

Sorry for late reply i was on leave yesterday..

Thank you so much Johan, its working!! , but both queries are returning same result!, but your query are more accurate, hence i created an Formatted Search for Business Partner as follows.

select  TOP 1  
RIGHT(SUBSTRING(CardCode, 1, 2)  
+ CASE  
WHEN LEN(CAST(MAX(CAST(SUBSTRING(CardCode, 3, 4) AS INT)) + 1 AS NVARCHAR(15))) = 1 THEN '000'  
WHEN LEN(CAST(MAX(CAST(SUBSTRING(CardCode, 3, 4) AS INT)) + 1 AS NVARCHAR(15))) = 2 THEN '00'  
WHEN LEN(CAST(MAX(CAST(SUBSTRING(CardCode, 3, 4) AS INT)) + 1 AS NVARCHAR(15))) = 3 THEN '0'  
ELSE ''  
END  
+ CAST(MAX(CAST(SUBSTRING(CardCode, 3, 4) AS INT)) + 1 AS NVARCHAR(15)), 6) AS CardCode  
from OCRD     
where CardType = $[OCRD.CARDTYPE.CHARACTER]  
     and CardCode like '%' + SUBSTRING(  $[OCRD.CARDname.CHARACTER(1)] , 1, 1)  + '%' -- replaced CardCode with CardName, query execute on change of cardname
group by CardCode  
order by CardCode desc

Once again Thank you so much for helping me out regarding Formatted Search for Business Partner.

I appreciate your help..

Thanks and Regards,

kaviprashu

[Johan_H · Accepted solution]

You are quite welcome Kavi.

Could you please rate my last answer as the solution, so your question gets closed ?

Regards,

Johan

[Former Member · Accepted solution]

Hi,

Sure, Thanks Johan , but rating system in new SCN is slightly different from old SDN,

hence, i was wondering how can i rate 'Very Helpful Answer'..?

but Formatted Search on Business Partner didn't completed yet.. , i.e, in Business Partner, i am trying to update Business Partner Code on change of CardName or CardType,

in User-Defined Values Setup dialogbox,

we cannot select multiple values, i.e, BP Name, and BP Type  ,

Once again thank you so much..

i appreciate your help..

Thanks and Regards,

kaviprashu

[Johan_H · Accepted solution]

Hi,

Yes, in the new SCN we can only set it to helpful or correct. In the old SDN it was possible to also set a question to 'Very Helpful' and 'Closed Without Answer'.

As for your requirement (user defined search with multiple triggers), you are not he only one who needs this, and adding this functionality has been suggested to SAP.

You can encourage SAP to implement this idea by voting for it: https://cw.sdn.sap.com/cw/community/ideas/sme/b1

The same web site also has a forum for ideas for the new SCN: https://cw.sdn.sap.com/cw/community/ideas/community_and_services/new_scn

Having more options than just helpful and correct, has already been suggested to SAP as well.

Regards,

Johan

[Former Member · Accepted solution]

Hi,

Thanks Johan, ..

Thanks and Regards,

kaviprashu