Skip to Content

Formatted Search

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Feb 09 at 06:39 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08 at 03:01 PM

    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]

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08 at 04:15 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 09 at 12:54 PM

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

    Add comment
    10|10000 characters needed characters exceeded