Formatted Search

[former_member556346]

Hi,

I am trying to create a formatted search for the Country of Origin when a sales order is being input.

I have a list of certain items that are made in the UK and for our Canadian customers need to specify if the item is US or UK made.

On item master Data I have created a property both "Made in the UK" and "Made in the US".

Can anyone suggest how I can get a formatted search to return "Made in the UK" or "Made in the US"?

[Johan_H · Accepted solution]

Hi Lance,

Using properties for your purpose is not optimal, and it is not necessary. On the General tab page of the Item Master Data screen you can find the Manufacturer field (OITM.FirmCode). This field allows you to pick a country of origin (where it was manufactured). Please use this field. The advantage is that you have an unlimited number of countries (consider the future, when your company starts selling items from more countries), and your properties remain available for use as... well, properties.

The formatted search query can then simply be:

SELECT OITM.FirmCode FROM OITM WHERE ItemCode = $[RDR1.ItemCode]

or if you insist on fancy foot work:

SELECT 'Made in ' + OITM.FirmCode FROM OITM WHERE ItemCode = $[RDR1.ItemCode]

or if you insist on fancy foot work in designer shoes:

SELECT CASE 
        WHEN OITM.FirmCode = 'GB' THEN 'Made in the UK'
        WHEN OITM.FirmCode = 'US' THEN 'Made in the US'
        ELSE 'Made Elsewhere'
       END
FROM OITM WHERE ItemCode = $[RDR1.ItemCode]

Regards,

Johan