help with FMS

[keith_taylor2]

I am trying to do what I thought was a simple formatted search on a user defined field in the item master

I want to return a price based on the following query. The query returns a correct value if I enter an itemcode so I know the data is correct.

SELECT price from itm1 (nolock) where itemcode = $[oitm.itemcode] and itm1.pricelist = '1'

I also want to do the same thing with LastPurPrc from the oitm to another user defined field.

Can anyone help to what must be a simple thing?

[former_member583013 · Accepted solution]

Keith Taylor

I would wonder why you would need to use a formatted search and user field to get the price of an Item which is already available when you select the Pricelist from the dropdown.

Please let me know if you have a different reason?

Suda

[keith_taylor2 · Accepted solution]

Client really wants to use the Boyum tool and just display these two prices instead of using the drop down. Boyum suggested I create the two user defined fields, use a formatted search for each, refresh on item change and then client can use Boyum tool to display the two fields somewhere on the main screens.

I realize both prices are in the drop down but client wants them immediately available on a screen.

Please tell me there is a way to make the FMS work on 2 new fields

[former_member583013 · Accepted solution]

If that is the case then YES, you can use 2 formatted searches in the 2 user fields.

SELECT T0.PRICE FROM [DBO\].[ITM1\] T0 (nolock) WHERE T0.PRICELIST = 1 AND T0.ITEMCODE = $\[OITM.ITEMCODE]

Link the to the UDF and set it to AutoRefresh on Item Description. (Refresh Regularly)

Depending on your need you can change the Pricelist number shown in the Query above.

Suda

[keith_taylor2 · Accepted solution]

Thank you Suda - I was able to accomplish both

[former_member204969]

This query could work as an FS, but you could not set it auto refresh when the price in the price list changes. You have no such possibility.

You could activate it only according to the last purchase price.

What is you purpose for the FS?

[keith_taylor2]

I would like to refresh when the item changes