How to Limit Employee List to Active Only (for Material Queue / Manager)

Kinetic ERP
, ,
1

I got a request to hide all inactive employees from the employee lists within the Material Request Queue and Material Queue Manager, by default. Epicor is thwarting all of my attempts!

TL;DR - Named Search, Quick Search, then BPM on GetList whereClause…

Attempt 1: Named Search
I created a BAQ to filter the list, created the Named Search, and set the options to Default, Auto Execute, and Return All Rows. This works great for me, but named searches seem to be user-specific, so every employee would need to create their own. I can’t seem to deploy it across the system.

image

Attempt 2: Quick Search
I created a QuickSearch based off the BAQ, but it needs extra clicks to run when I search, but it does run in testing the quicksearc. I wasn’t able to get the QuickSearch to show on the list either in Employee Maintenance, let alone MRQ.

image

image

image

Attempt 3: BPM to filter GetList
This time I figured I could prevent GetList from returning the inactive employees in the search window, similar to how we hide inactive shipvia’s and sales reps using @timshuwy’s How to Inactivate tips.

I made the BPM. It took a few tries to get the nomenclature correct (use “EmpStatus like ‘A’” in the whereClause). This also works to limit the list in the search box, but now it breaks the employee selection in MRQ!

When I select a material row, then Actions>Assign, and double click an employee name from the list, it defaults to a different person (always the same one)! If I disable the BPM, double-clicking the employee name properly assigns it to the employee I selected.

Comparing a trace with the BPM disabled and enabled, I see it’s calling the EmpBasicImpl BO, running the GetList, putting the employee I selected in the parameter whereClause (EmpID 783), but then returning a EmpID (0080)! EmpID 0080 is the first record in the dataset, for what it’s worth.

<tracePacket>
  <businessObject>Erp.Proxy.BO.EmpBasicImpl</businessObject>
  <methodName>GetList</methodName>
  <appServerUri>https://masked</appServerUri>
  <returnType>Erp.BO.EmpBasicListDataSet</returnType>
  <localTime>1/13/2023 17:26:19:3577465 PM</localTime>
  <threadID>1</threadID>
  <correlationId>9232b9cf-d26e-4f8c-aa91-390ca346b79b</correlationId>
  <executionTime total="110" roundTrip="101" channel="0" bpm="0" bpmDataForm="0" other="9" />
  <retries>0</retries>
  <parameters>
    <parameter name="whereClause" type="System.String"><![CDATA[(EmpID = '783') BY Name]]></parameter>

Why is it returning a different employee? There’s nothing else in the trace after that. Maybe it’s actually returning the right employee, but displaying the wrong one? More to test Monday…

***Is there a better way to accomplish hiding the inactive employees without breaking Epicor? ***

2

you stated that you used a quicksearch, but it took extra clicks… did you select the checkbox on the quicksearch to make this the default search? if you do that, it should eliminate extra steps.

2 Likes
3

Tim, the extra clicks were to run the search (in test mode, if that matters). It loads up with the single radio button selected already, but then you still need to click on ‘Search’ afterwards. It didn’t default with the records displayed like the Named Search does.

Our folks want it to work like it is now, but not show the inactive employees without doing more clicks, or me getting passwords to remote into 50 workstations to build a named search for each user.

4

Are you replacing the where clause in the GetList, or adding to where clause in the GetList?

You need to be appending/adding, because other things use that where clause.

To be clear, if you are replacing it, then this:

<parameter name="whereClause" type="System.String"><![CDATA[(EmpID = '783') BY Name]]></parameter>

is really (in effect) now this on every call to GetList

<parameter name="whereClause" type="System.String"><![CDATA[(EmpStatus LIKE 'A') BY Name]]></parameter>

And depending on where you use this technique, you might should be checking other criteria as to whether you should be modifying it at all.

1 Like
5

Hi Kevin,

I thought setting the whereClause argument to ‘A’ was like a filter. I’m not sure on the apeending vs replacing part. I’m a low coder that copies the best from you guys (and attempts to retain as much as you guys discuss - and learn a ton from you all!) :slight_smile:

Here’s the super simple BPM:

image

image

image

6

In your expression, try including the existing whereClause value and add to it. Don’t forget the space before AND.

You should probably check if whereClause is blank or not, before doing this. If it is, you wouldn’t want the ‘AND’.

whereClause + " AND EmpStatus = ‘A’"

7

whereClause + iif(whereClause = "", "", " AND ") + "EmpStatus = 'A' "

2 Likes
8

@andrew.johnson - Shoot, that passed the syntax check in Method Maintenance, but errored out when called in Material Request Queue. If I OK the error, it returned the full list of employees (Inactive & Terminated).

Message: Incorrect syntax near the keyword 'BY'.
9

Did you use your ShowMsg block to view the whereClause value upon entry into the BPM?

Edit

The fact that the GetList method is called makes me think that you can select more than one employee - and hence the need to use a method that returns a “list” over one that returns a single employee.

You might need to make your BPM act in two different ways. The first for when it gets the list of employees to display, and the other for when you select a subset of that list.

Try checking for the whereClause being empty (as in indicator that it is the first fetch of employees). If it is, then set it to your “filter”. If its not empty, then it probably is for the second use. In this case don’t touch the whereClause

10

Good idea on the ShowMsg - I just connected it before and after the set argument, and it does not pop up, ever! Yet the search results are clearly filtering out the I and T status (just showing A’s).

I did get an error on the iif “does not exist in the current context” when I tried that.

image

And when I reverted back to the basic “EmpStatus like ‘A’” with message boxes, I get this error (which goes away if I don’t include message boxes, after a restart only). And now it seems to work fine! I dunno… Epicor confuses me sometimes.

image

Thanks for the edit explanation. That makes sense.

11

Sorry about the example with the IIF() I rushed it and meant it top be a “guide” not a verbatim solution.

As for the message, you did connect it back into the flow path, and then save the BPM (I would often close the BPM editor (saving it, bu then forget to save the BPM itself)

IIF() might not even be supported. Try a (CASE WHEN ... THEN...) expression I THINK this might work
whereClause + (CASE WHEN LEN(whereClause) = 0 THEN "EmpStatus LIKE 'A' ")

Tha should only add the EmpStatus filter when it’s empty

12

So everything works like you want now?

  1. List of employees is limits to those who are active
  2. Double clicking an employee from that list selects that employ and not a different one
13

You ended up with this:

(EmpID = '783') BY Name AND EmpStatus LIKE 'A'

Should be more like

(EmpID = '783' AND EmpStatus LIKE 'A') BY Name

May not be exact, need to see what the exact whereclause looked like in the BPM, not
sure if it looks exactly like the trace (parenthesis etc)

14

Good point Kevin. Appending the existing whereClause to the end is probably safer than having it at the beginning of the expression.

I remembered that I did this exact thing for the Employee Search in Time and Expense Entry. I used a Custom Code block in the Erp.BO.EmpBasic.GetListForTE (Pre-Processing), with the following code:

whereClause = "EmpStatus <> 'I' AND " + whereClause;
15

You get this worked out?

16

Try


whereClause = string.Concat(whereClause, string.IsNullOrEmpty(whereClause) ? "" : " AND ", "EmpStatus = 'A' ");
17

Instead of pre-processing create a post-processing then enter below code in code widget

result.EmpBasicList.RemoveAll(e => e.EmpStatus != “A”);
2 Likes
18

Seems so obvious now. :hammer: :hammer: :hammer:

19

Thanks @tkoch! That almost worked perfectly (once I fixed the curly quotes “ with regular quotes ").

What’s different between that in the POST and the “EmpStatus like ‘A’” in the PRE?

What’s happening now, is that it doesn’t return 100 records at a time anymore (26 on the initial search), and clicking ‘Next 100’ takes me to 34 records, then 36, 37, etc… I’ve never seen that happen before. Options>Return All Rows returns the full list of 305 active emps.

image
image543×866 29.5 KB

FYI - I did get the Pre-BPM working, but it would cause the Material Request Queue ‘Selected By’ to be the first EmpNum in the system, regardless of who selected it. @tkoch’s method fixes that.

Here’s what that looked like:

"EmpStatus like 'A'"

image

image

20

The Base BPM is returning pages of records, in 100 record blocks, and the post is removing them.

74 removed on first page, 66 removed on second, etc.