Skip to Content
0

Formatted Search

Feb 08 at 02:32 PM

41

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Johan Hakkesteegt Feb 09 at 06:39 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Hrvoje Dolenec Feb 08 at 03:01 PM
0

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]

Share
10 |10000 characters needed characters left characters exceeded
Lance Paton Feb 08 at 04:15 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Lance Paton Feb 09 at 12:54 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded