Infor M3's Standard Search feature using CMS021 & CMS022
As the name suggests this functionality is for searching a keyword/s from an interactive program. If you have observed MMS001 top right corner there would be a text field with a place holder as "search".
This Infor Enterprise Search (IES) works like a Google search engine.
IES is used to search/filter the data from the tables involved in the program, there are different types of search functions in M3 Business Engines as mentioned below.
- Simple search
- Advanced search
- Search including reserved words
Let's see one by one how the search has to be made and how M3BE responds to it.
Simple Search
This is the simplest form of searching and uses the entry of full words which are case sensitive. All columns are searched, and the words found may be from multiple columns.
How to use Search box
- In the Search box, specify the word to be searched for in full and in the correct case. For example, searching for 'cheese' returns all instances of 'cheese' in any column.
- Using AND with multiple words means that all the words must exist in one or more columns on a line. For example, searching for 'cheese' and 'juice' returns all lines where both words exist in any column on a line.
- Using OR with multiple words means that one of the words must exist in one of the columns. For example, searching for 'cheese OR juice' returns all lines where either word exists in one of the columns.
- Use ( ) to combine blocks of AND and OR. For example, 'cheese OR (juice 011215)' will return all lines where either 'cheese' exists or 'juice 011215' exists.
- You can use these wildcards in the Search box:
- '*' - replaces more than one character. For example, 'chee*' returns all instances containing 'chee' such as 'cheese', 'cheeses', 'large cheeses', etc.
- '?' - replaces a single character in a specific position. For example, 'jui?e' returns instances of 'juice'.
- You can limit the search to a single column, right-click the column header and select 'Add to search'. Specify the search word.
- You can use field names in the search query to search in fields that are not displayed as a column. For
- example, 'chee* STAT:20' returns instances containing 'chee' and which also have the status of '20', even though the status is not displayed in a column.
- You can search for specific dates, using the same date format as defined for the user in the user master. For example, 'LMDT: 112515' returns all lines with '112515' in the date column in that format.
- You can also use the decimal point format used is controlled by the setting on the user master. For example, '.', ',' etc.
Advanced search
Advanced search can be divided into the following types
- Ranges
- Dates
- Wildcards
- Sort search results
- Key search
Ranges
You can search for ranges of data by using brackets as follows:
- [x TO y] means the values are included. For example, '[100 TO 480]' returns all instances in the selected column containing values from 100 to 480
- {x TO y} means the values are excluded. For example, '{100 TO 480}' returns all instances in the selected column outside the range 100 to 480
Dates
You can use date functions to filter the data, these date macros are provided by Infor
For example, 'INDT:YEAR(-1)'.
The DATE macro can be combined with ranges to create your own range. For example, 'LMDT:[DATE(-14) TO DATE(0)] returns all transactions within the last two weeks.
When searching with NOT, this must be preceded by another query. For example, 'No:780 NOT INDT:YEAR(0)'.
Date macros are very useful with saved searches as they give a quick link to, for example, last month's orders. They can be saved to Favorites, Canvas, as a menu option, or in the toolbox area, so they can be easily accessed.
- YEAR(x)
- MONTH(x)
- WEEK(x)
- DATE(x)
For example, 'INDT:YEAR(-1)'.
The DATE macro can be combined with ranges to create your own range. For example, 'LMDT:[DATE(-14) TO DATE(0)] returns all transactions within the last two weeks.
When searching with NOT, this must be preceded by another query. For example, 'No:780 NOT INDT:YEAR(0)'.
Date macros are very useful with saved searches as they give a quick link to, for example, last month's orders. They can be saved to Favorites, Canvas, as a menu option, or in the toolbox area, so they can be easily accessed.
Wildcards
- '?' - replaces a single character in a specific position and can be used together with other wildcards. For example, 'SUNO:?*'.\
- For numeric fields, you can use the NOT statement. For example nb mkll?>e, 'NOT STQT:0'
- '~' – can be used in alphabetic characters, not numeric characters and is a fuzzy search. This means that the characters do not need to exactly match those in the query
Sorting search results
- Sorting search results allows you to free sort any field in the main table of the function, apart from fields containing multiple words such as a description. A search finding results in a related table, set up in 'Related Search. Open' (CMS022) are not sorted
- A search which finds results in both related tables and the main table presents hits for the main table first in the selected sorting method, followed by hits from the related tables which are not sorted
- This sort is done by using the keyword 'SortBy:x,a,!y' where '!' performs a descending order
- You can save sorting in the toolbox area which enables you to re-sort the list without having to use a separate index
For example, you could re-sort results for the current company number using 'CONO:xxx', where 'xxx' is replaced by the currently used M3 BE company number.
Searching multiple words
- Searching multiple words across several columns can easily return false results as it returns all instances of the search in all fields, in all columns. For example, 'chees* 10'
- By using the keyword 'SearchFields:x;y;z', you can search in multiple columns but only in the specified fields
For example, 'SearchFields:ITDS,FUDS chees* 10'.
Key search
- Searching with the help of key search simplifies complex queries so they are easier to use. It also allows searching in other tables without the need for matching keys, as required by the related search setup in (CMS022)
For example, you can define a query which, when specified, the preset part in the key search is automatically appended.
Available key searches are displayed by using Ctrl+F.
Note: A key search is performed on both the main table and related tables (defined in (CMS022) or related tables through 'Related Tables. Connect' (CMS011)).
Search including reserved words
While forming a search query we can use the reserved words to take inputs dynamically based on the user, below are the list of reserved words available in Infor M3.
There are few reserved words which are applicable only on key search discussed earlier in this post.
Search query and key search rules
- There are some rules you need to follow while performing search operation, these rules are used to set '*' and '~'
- <QRY>* - Automatically appends a wild card to each word in the specified query. For example, the query 'johsmit' is set to 'joh* smit*' before the search is performed
- <QRY>~' - Automatically appends a tilde, or fuzzy search, to each word in the specified query
- For example, the query 'joh smit' is set to 'joh~ smit~' before the search is performed. Fuzzy searches should not be used with numeric values due to the nature of this type of search
Exception
There are few exceptions to the search query as mentioned below
- Query inside "" - When specifying a query enclosed by double quotation marks "", wildcards are not added to the search as the search engine treats all characters contained within "" as normal characters
- That is, "joh*" only returns results for 'joh*' including the '*' character, and not 'john' etc
- Short words - (With effect from M3 IES version 11 patch 5 onwards). Short words containing fewer than the number of characters set in the IES local administration page are not appended with '*' or '~'. For example, if the setting is three characters, which is the default, then 'jo', '10' and 'xl' would not appended with '*' or '~'. This is to improve system performance
How to enable search functionality to a Customized Interactive program?
- You can enable search function on any interactive program, but the master table to be used for search must be indexed otherwise search wouldn't work
- You can create a record in CMS021 by providing the program name on which search has to be enabled and the Table and its logical index on which the search has to be made
- You can even create a Related search by using option 11, where the system takes you to CMS022
You can store the search results by clicking on the result and you can come back at any time and use the same result for a quick review. All saved queries can be seen under favourites by just a click system would open the program with the data filtered.
Advantages of Infor Enterprise Search
- You can use IES as selection criteria with AND or OR keywords in the search query
- You can use this to filter data on any column from the main table or related table
- You can even use this to generate reports using export to excel functionality
- You can check the data you wish at any time by saving the search query
Source: inforxtreme
That's it, come back here and tell us about the before-and-after. I bet you'll have something to say!
Please let me know your thoughts in the comment section below!
If you enjoyed this post, I'd be very grateful if you'd help it spread by emailing it to a friend, or sharing it on Twitter or LinkedIn.
In our next post let us see about Text panel (Header and Lines) and how it is linked to the Master file.
Thanks for reading!
--Saiteja Saadi
Please let me know your thoughts in the comment section below!
If you enjoyed this post, I'd be very grateful if you'd help it spread by emailing it to a friend, or sharing it on Twitter or LinkedIn.
In our next post let us see about Text panel (Header and Lines) and how it is linked to the Master file.
Thanks for reading!
--Saiteja Saadi
Comments
Post a Comment