Sunday, March 31, 2019

Multiple Select for Name field on a Saved Search

The multi-select functionality for the Name field on Saved Searches is currently not possible and is filed as Enhancement# 175473 and 229380. There are instances when users have a list of names that they want to be included on the Saved Search results.

To illustrate, assume that a user wants to have a Saved Search for Items included on the list: Test, Hold, Check and Prepare. It is not possible to use Name is Test || Hold || Check || Prepare. It is also not possible to use Formula(Text) : {name} is Test || Hold || Check || Prepare.

The solution is to manually add one line for each selection. This is possible by adding the following fields on the Criteria tab and checking the Use Expressions box:

(           Name is Test        OR

Name is Hold        OR

Name is Check      OR

Name is Prepare              )


Note that when a user has hundreds or thousands of names on the list then this is no longer plausible as this requires a lot of manual steps.

If a user has a list of Items in a file (e.g. Excel) then the most convenient way is to simply copy and paste this on the Criteria. Using the same scenario, let us say a user wants to have an Item Saved Search for all records included on a predefined excel file/list. This is accomplished through these steps:

A.    Convert name column in excel to 'name' (assume name is on column A):

1.    Open the file

2.    Go to column B (or next to the name column)

3.    Enter formula for the next column: CONCATENATE("'", A2, "'",",",1,",") where A2 is the cell reference for name

4.    Copy the formula up to the end of the table

OUTPUT: Column with the format: 'name',1, (e.g. 'Test',1, instead of Test)

B.    Create the Saved Search

1.    Navigate to Transactions>Management>Saved Searches>New

2.    Select Item

3.    On the Criteria tab add Formula(Numeric)

4.    On the formula popup box, go to the Formula field

5.    Add DECODE({name},

6.    Copy the 'name',1, column created in step A

7.    Paste the values in the Formula field

8.    On the last line add 0)

9.    Set Formula(Numeric) field to equal to

10.  Set Value = 1

11.  Hit Set

12.  Hit Preview or Save and Run

To check, Formula(Numeric) in this case should say DECODE({name},'Test',1,'Hold',1,'Check ',1,'Prepare',1,0).

The results will only show four Items on this scenario: Test, Hold, Check and Prepare. Users can then add columns in the Results tab for additional information that they want to include on the table.

No comments:

Post a Comment