cancel
Showing results for 
Search instead for 
Did you mean: 

Formatted Search

0 Kudos

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"?

Accepted Solutions (1)

Accepted Solutions (1)

Johan_H
Active Contributor
0 Kudos

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

Answers (3)

Answers (3)

0 Kudos

Thank you Both, together with your suggestions I have got this populating what is required.

0 Kudos

I was trying to incorporate a query that IF QryGroup1 on OITM = Y Then it returns text of "Made in UK"

But I am just learning SQL and struggling to think of a query appropriate.

hdolenec
Contributor
0 Kudos

Depends on what kind of field is in item master data, but how about something like this? Set auto refresh on exiting column Item No.

SELECT T0."U_Origin" FROM OITM T0 WHERE T0."ItemCode" = $[$38.1.0]