M3 Search Examples

M3 has created a Knowledge base article on the search syntax for Search in M3 13.3 and 13.4. M3 Infor Enterprise Search is a great tool which can greatly improve your workflow when used in the way it has been designed to work.

Depending on how you are using Enterprise Search (IES) today, you might have encountered a few instances where it has not retrieved the desired result.

This can in many cases be addressed by altering your search query to be better suited for the Lucene Search Engine which IES is built upon.

In this blog post I’ll do a quick overview of some of the queries from the examples in the Knowledge based article as a introduction but I recommend you to read the full article as it contains even more examples. I just wanted to take some of the material and the references so that it is easier to find if you search for tips on this blog, which I know a lot of our readers do.

Please note that if you are new to Enterprise Search there are  available courses and How-to videos at the Sales Portal and the Infor Campus.

Simple search

This is the simplest form of search with entry of full words. Note that search is always case insensitive. Searching for cheese would give you the following result:
cheese

You can enter multiple words in the search field but those values have to be displayed in the list. The default behaviour when using multiple words in search is applying AND for all words. This means that they have to exist in one or more columns. Using OR means one of them must exist in one of the columns which will result on more hits. Use parentheses to combine blocks of AND and OR. Use wild card to enter the first part of a word. Or a ‘?’ to replace a single character. Note that you can’t replace blank space.

cheese2

Examples:
cheese OR juice
cheese OR (juice 011215) - Combine with ()
chee* - starts with chee
chee*jui?e - ? replace a single character

To limit the search to a specific column, right click on the column and select ‘Add to search’. It will add a column prefix to the search.

addtosearch


ITDS:Cheese

Dates and numeric values are entered in the same date format as the user has in MNS150, the user master. Dates can be entered with 6 or 8 digits.

Advanced search

Intervals
Intervals are done using the following two formats [ x TO y] or { x TO y } or any combination of them. Usage of [] means that the range should be included in the search where as {} means that the range should be excluded.

Examples

STQT:[100 TO 480]
STQT:[* to 0} - find all items with negative quantity

Not conditions and use of ?
Not conditions are a bit special since they must start with another query and then the NOT condition can be introduced. The tip if there is no such natural condition is to start with the company number and then add the NOT search. The company number will search the entire table. An example query in MMS002 of all items that have not been part of a stock take in this year will look like this:

CONO:790 NOT INDT:YEAR(0)

Note the YEAR(0) function that will translate to an interval covering the to and from dates of the current year. There are a number of functions that can be used to create useful Macros that can be saved to favorites, canvas, as menu option or in toolbox area.

Creative usage of the ? allows for a simple search for all records with supplier number. This since ? only finds records with a character in the position. Blanks are omitted. Query SUNO:?* gives you all records with a supplier number in this case. For numeric fields you use the NOT statement like CONO:780 NOT STQT :0.
SUNO:?*
CONO:780 NOT STQT :0

Fuzzy search
The ~ is used for a fuzzy search that is useful for finding spelling errors and similar texts. An example would be a query like ‘min*~’ which would produce hits even if there is no exact match on anything starting with min, for example mild would also be a hit in this case. Not that it cannot be used for numeric values.

The full article covers more examples!

M3 IES reserved word and functions

SortBy:x,y,!z Sorts search result in x, y z hierarchy. The ! Prior field name perform a descending sorting (NCR 6670 and 6671)
SearchFields:x;y Limits search to listed fields x, y etc. Increases performance and can eliminate false hits
YEAR(x) Replaces with date range of year relative to current year x=-1 equals last year etc
MONTH(x) Same as YEAR but for month
WEEK(x) Same as YEAR but for week
DATE(x) Similar to YEAR but replaces with specific date
<xxxx> List & list header based replacements to query string. (NCR 6813)

All below are described in NCR 6220
Using the value within you can use the following replacement variables.

<USID>   Replaced by logged on user id
<CONO> Replaced by logged on company number
<DIVI>     Replaced by logged on Division. Note – same as <CurrentDivison>
<FACI>    Replaced by default facility from user master (MNS150)
<CUNO> Replaced by customer no from user master
<LNCD>  Replaced by language code from user master
<TIZO>    Replaced by time zone from user master
<WHLO> Replaced by warehouse from user master
<DEPT>   Replaced by department from user master
<MNVR> Replaced by menu version from user master
<DFMN> Replaced by menu name from user master
<CurrentLanguage> Replaced by currently used language in M3 (in case of switched language during logged in session)
<CurrentDivision> Current division. Note that this is the same as DIVI.

Reserved word for key search only

  • <QRY> Replaced by entered query in the search string
  • MAXJOIN:x Used to change number of records used in the key search join (default set in IES local management pages) (NCR 4351)
  • NOOPT Used when multiple key searches done to same table (like find items with attribute x, y but NOT z or items existing in warehouse a, b and c (NCR 4349)

M3 IES Stop words

Stop words in IES has the meaning of words that are not indexed and are automatically removed from the search query. Examples of stop words are “and”, “or” “etc” and other similar types. IES, by default has a set of English based stop words which apart from above examples also includes “a”, “by”, “is”, “as” plus about 20 other words. Unfortunately in many cases when searching ERP type of data you might want to search for “factor a”, the Swedish word for ice “is” etc.

The work around for this is to add a new stop word in the IES admin pages. Make sure it is something never search for like “lksdhja7789”. By doing that the default stop words are not used anymore. Please note that affected tables like MITMAS, MITLAD, OCUSMA, CIDMAS etc need to be re-indexed after this has been done.

Read more examples in the KB.

One thought on “M3 Search Examples

  1. Darius

    Hi, i;m not sure if I am posting in the right place. I want to created a macro that will automatically copy data from an excel file to PDS001. Any help? Where do I start? I don’t know how to link the application excel to M3.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s