Formatted Search Problem

[Former Member]

Hi All,

SAP Business One 2007 A SP01 PL05

I am having a small problem with a formatted search that is not displaying the correct values on only some items.

I have created a UDF with valid values of F, R, and O and it is called Division. All my item codes are 7 digits long for example B123ABC. I have also created profit centers as follows B123ABCF and B123ABCR.

My formatted search is on the profit center field and it is set to auto refresh when exiting altered column Division and display saved user defined values. The query is

SELECT $[$38.1] + $[$38.U_Division]

and that should select the itemcode and the division value and insert it into the profit center field which will select the correct profit center.

This works but there are a few items where it does not work for argument sakes if I select item B123ABC and my Division is R it gives me B123ABCF in the profit center and I cannot workout why this is.

I have removed the item and added it again, changed the formatted search to update on a different column but it has not worked.

Any suggestions on how I can get this to work correctly?

Regards,

Quinn

[former_member204969 · Accepted solution]

Are you sure the code of the UDF is u2018Ru2019 and not only its description?

You can try to execute the FMS code so that you can see the replaced values:

1. Go to the document with the problematic item and R divison.

2. Activate the FMS query with the mouse from the top menu Tools / Queries / User queries selecting it.

3. Then a window will open showing the query with the substituted values and you can see what causes the problem.

[Former Member · Accepted solution]

Hi Istvan,

Thank you for your reply.

I did what you suggested and when I click on the user defined queries I get an error.

This is the error I get Character number is greater than allowed [2000-1]

This strange as it does work on some but not on these and I have made sure that all my profit center codes are 8 characters and my item codes are 7 and my division is 1 which comes to 8 and should work fine.

Am I doing something wrong here?

[former_member204969 · Accepted solution]

What do you see in the query window (with the replaced values)?

Show (copy) here and we can examine.

Edited by: István K#rös on Jan 28, 2010 2:28 PM

[former_member583013 · Accepted solution]

You could use RTRIM to remove any spaces in the ItemCode. Check this one.

SELECT RTRIM($[$38.1.0]) + $[$38.U_Division]

[Former Member · Accepted solution]

Hi Suda,

Thanks so much for your help.

The RTRIM is all I needed to sort out the problem.

Everybody else thank you for trying as well.

[Former Member]

Hi Quinn,

Try this,

DECLARE @a nvarchar (10)
SET @a= $[$38.1.0]
SELECT @a+(SELECT $[$38.U_Division])

OR

SELECT $[$38.1.0]+$[$38.U_Division]

Regards,

Madhan.

[Former Member]

Hi Madhan,

Thanks for both those queries.

I have tried both of them and it is still producing the same results. It works fine on most of the items but there are just a few where it is not working.

Do you perhaps have any other suggestions?

Thanks for the help so far I really appreciate it.

[Former Member]

Perhaps a bit more information might help.

The exact itemcodes that I am having problems with are B162ADU and B207ABF

When in the profit center column if I click on the magnifying glass I get B162ADU with a space after the U so that leads me to believe that the formatted search is not working correctly.