Creating a Query to Change Price

[Former Member]

Hi,

I have created a query to change the price in the Sales Order that looks like:

SELECT CASE A WHEN COND1 THEN 120.0 ELSE $[$38.17] END FOR BROWSE

This means, when A <> COND1 I want the existing price ($[$38.17])to be maintained (no modification). And, I want to modify the price to 120.0 only when A=COND1.

However, the above query results in the price getting modified to its own value when A <> COND1 and sales order identifying this as a field value change; and, the OK button changes to Update.

To avoid this, is there a way to write a query such that price field is left untouched when A <> COND1?

Thanks.

[Former Member · Accepted solution]

Hi Satish,

I think I found a solution to this problem. It might look as a tedious solution, but I think it will work. Try the following:

declare @Val varchar(10)
select @val = CASE A WHEN COND1 THEN 120.0 end
if @val is not null
select @val

I have managed with this to get the result I think you are looking for. Certain documents will update and on others it will only select a result if the value is not null.

Hope it helps,

Adele

[Former Member · Accepted solution]

Hi Adele,

Thanks for your suggestion. I tried it; however, when I used the logic on sales order, the price field updates with the value fetched by the SELECT statement. So, if the output of SELECT statement is null, the price also becomes null.

Thanks.

[Former Member · Accepted solution]

Hi Satish,

I have tested this and it works for the price as well. The result of the query is not null, but rather there is no result. I have tested like this: if the item number is 'A1000' then it must update, otherwise not. The select that shows the actual result in the end is part of an if statement. If that condition is not met then the query has no result (not a null result)!

[code]DECLARE @Val varchar(10)

SELECT @val = case $[$38.1.0]

WHEN 'A1000' then 120 end

IF @val is not null

SELECT @val[/code]

Hope it helps,

Adele

[Former Member · Accepted solution]

Hi Adele,

Thanks for your reply.

Yes, when the If condition is not met there is no result (it is not null). I have cut-and-paste your code onto my query and attached it to the price field in sales order form. The result is that only for the specified item, the price is shown as 120 and for the rest it is blank on the sales order form. I am using 2004B Patch 40.

Thanks.

[Former Member · Accepted solution]

Hi Satish,

If you remove the formatted search is the prices added correctly then? Do you do an automatic refresh on the field? I have only tested with a "manual" formatted search by pressing Shift+F2 to test. Will test later and give you my result.

Hope it helps,

Adele

Message was edited by: Adele le Roux

OK, I see what you mean with the value is blank. It overwrites the value if you have Automatically refresh on. If you do a manual refresh it works 100%. Sorry for the confusion