Method Directive ChangeJobHeadJobReleased

Epicor ERP 10
, , , ,
1

In Job Entry there is a Field “Planner:”
image

I have been requested to modify my current Post-Processing Method Directive to have the Engineering notification email sent to the person who is in this box. I have been trying to pull the UserID using the persons name, being its their first initial and last name I feel like this would be the easiest. However, I cannot figure out the best way to accomplish this. I cannot use Custom Code. Does anyone have any suggestions? I thought maybe using invoke BO.SysUserFile, but I couldn’t get it to work properly. I tried Fill table by query, but that again didn’t seem to work. I know for a fact its the User (myself) fault it isn’t working. So if anyone could help point me in the right direction, that would be amazing!!!

2

Have you used Field Help to determine the source of the control? The userID may actually be stored in that field. There are many places in the UI where the “code” or ID of the value is what is stored in the field, but the UI displays that code’s description or name.

Edit

That field does store the ID, in JobHead.PersonID

BTW - the email for that person is setup in the Person Maintenance. In Person.EMailAddress

So you want the record from the Person table where Person.PersonID == JobHead.PersonID

3

I tested that already, I pulled that field and had it show up in a Message box. Unfortunately, it is not the field value I am looking for.
image

4

You said you can’t use code. Because your installation prevents it? Or because your not comfortable using it?

5

Not all the users have their emails set up in person maintenance. I guess I could see if I could get them updated.

It is a little bit of both. Honestly, I try to avoid Custom code because I only have access to it in Pilot. I haven’t been able to convince Corporate IT to give me access in Epicor Live. It’s really annoying, but all the changes I want to make I have to do them in Pilot. Then once I can prove it works the way we/I want it to, I have to write work instructions for Corporate IT to have them do it or have them give me temporary access to do it myself. Thus I try to keep it to the tools I do have access to in Epicor Live, so I don’t have to wait weeks/months to get IT to approve and decide how they want to handle it.

6

First off, it’s possible to create a Person entry that has no matching user account. This person could then be selected in Job Entry for the Planner dropdown. Your BPM would would never find a matching user for that planner.

As far as “custom code” goes, you can get the email address without using an Execute Custom Code widget.

In your BPM:

  1. Make a variable of type string, like:
    image

  2. Add a Set Argument/Variable widget:
    image

  3. Select the variable you created:
    image

  4. In the expression, set it to:

Db.Person.Where( r =>r.Company == callContextClient.CurrentCompany  && r.PersonID == dsJobHeadRow.PersonID).Select( r =>r.EMailAddress).DefaultIfEmpty("").FirstOrDefault()

Now you can use that variable anywhere in your BPM.

No custom code :wink:
(as long as no one looks inside that Set Variable widget)

image
image782×414 18.7 KB

1 Like
7

You are wonderful!!! I have been trying to figure out how to properly use expressions in the ‘Set Argument/Variable’ widget.
I am so thankful I stumbled across this site, with people like you that are will to help. Thank you so much!!

8

Full credit goes to @josecgomez

There’s a full thread with lots of good info about doing such a thing:

https://www.epiusers.help/t/simple-one-liner-c-to-retrieve-data-from-a-table/44627

1 Like
9

That’s awesome @ckrusen… thanks!

10

So if you were trying to pull the first UserID from the change log, would it be something like:
Db.ChgLog.Where( r =>r.Company == callContextClient.CurrentCompany && r.UserID.Select)

Or would you need to call out the Table
Db.ChgLog.Where(r=>r.Company == callContextClient.CurrentCompany && r.TableName == “JobHead”.Select (r=>r.UserID).DefaultIfEmpty("").FirstOrDefault()

11

UserID is a field in ChgLog. The FirstOrDefault refers to the record being selected.

You would want

Db.ChgLog.Where(r=>r.Company == callContextClient.CurrentCompany)
.Select(r=>r.UserID).DefaultIfEmpty("").FirstOrDefault()

That would return the UserID of the first record in the ChgLog table.

Now if you wanted the first record from the ChgLog specific to the JobHead table …

Db.ChgLog.Where(r=>r.Company == callContextClient.CurrentCompany && r.TableName == "JobHead").Select (r=>r.UserID).DefaultIfEmpty("").FirstOrDefault()

Note: you were missing a ) between "JobHead" and .Select. And be careful copying code from this site as quotes often get changed to fancy quotes. Can yo tell the difference in the following:

“Hello World!” "Hello World!"

12

Thank you for the warning, I wouldn’t have thought that would cause issues. However, I usually don’t copy/paste code because I like to type it out so I can really understand what is going on. To me it is like reading at that point. If I just copy/paste then I don’t really have the best chance of understanding what the code is doing and why. Also, I can see the difference but don’t really understand why it results in ‘Fancy’ quotes.

As for the code, do I need to tell it which change log like per the JobNum? The reason I ask is because I am pulling a ‘blank/empty’ value when there are numerous rows in the Change log.

image
image1100×599 39.7 KB

13

The code sample I gave retrieves one record. So it is useful for finding a specific record, by adding more constraints to the WHERE clause part.

Adding more conditions should allow yo eventually find the record you’re looking for. Use the Data Dictionary to view the key fields of table ChgLog. Those are the ones you’ll want to include in your expression. You can also use the Field help (like you have shown) to verify the field names.

Off the top of my head, I’d guess the fields you need in the where clause are: Company, TableName, Date, Key1 (these might not be the exact field names).

14

I am not sure what tells you that a field is a “key field” in the Data Dictionary Viewer.

image
image1077×478 35.6 KB

Also, I am not sure how to call the DateStampedOn by the earliest date or first date. I thought of using Min or less then, but I cannot find any examples on how to write that. Anyways, this is what I have tried and what it has been returning. Did I write something wrong?

I have tried this:
Db.ChgLog.Where(r=>r.Company == callContextClient.CurrentCompany && r.TableName == “JobHead” && r.SchemaName == “ERP” && r.Key1 == “JobNum:”+ dsJobHeadRow.JobNum).Select (r=>r.UserID).FirstOrDefault()

It returns nothing.

I also tried this:
Db.ChgLog.Where(r=>r.Company == callContextClient.CurrentCompany && r.TableName == “JobHead” && r.SchemaName == “ERP” && r.Key1 == “JobNum:”+ dsJobHeadRow.JobNum).Select (r=>r.UserID).DefaultIfEmpty(“Not Working”).FirstOrDefault()

This returned: “Not Working”

I have tried:
Db.ChgLog.Where(r=>r.Company == callContextClient.CurrentCompany && r.TableName == “JobHead” && r.Key1 == “JobNum:”+ dsJobHeadRow.JobNum).Select (r=>r.UserID).FirstOrDefault()

Again, returns an empty notification.

15

The key fields will be in the index table on the main sheet of the Data Dict.
image

For ChgLog, they are:

  • Company
  • Identifier
  • SchemaName
  • TableName
  • Key1
  • Key2
  • Key3
  • DateStampedOn
  • LogNum
  • UserID

You might not need to use all of those fields, But if you do, it guarantees to specify a single record.

You clause of r.Key1 == "JobNum:"+ dsJobHeadRow.JobNum might need some tweaking. First, there might be a space between the colon and the jobnum. Second, you might need to convert the JobNum from an Int to a string before combining it with that text constant (JobNum: )

The phrase that returned, “Not Working” means that no record was found (meaning none matched the criteria)

Make a BAQ of the ChgLog table to see the exact contents of the ChgLog table - The ChgLog viewer might be tweaking the data for easier viewing. You could also try your criteria in the BAQ.

To avoid having to make an exact match of the Key1 field, you can use a Contains() in the where clause.

Db.ChgLog.Where(r=>r.Company == callContextClient.CurrentCompany 
  && r.TableName == "JobHead" 
  && r.SchemaName == "ERP" 
  && r.Key1.Contains(dsJobHeadRow.JobNum.ToString()))
  .Select (r=>r.UserID).DefaultIfEmpty("Nothing Found").FirstOrDefault()
16

@sire - It turns out the fields in the ChgLog table are nothing like what shows in the viewr. Here’s what the viewer looks like (for OrderDtl)

image
image700×190 5.02 KB

Note that “Key 1” has way more info in it than what the ChgLog table actually has.

image
image922×191 5.89 KB

So try just matching Key1 to the JobNum
... && r.Key1 == dsJobHeadRow.JobNum) ...

17

Mr. Krusen, you did it again!!! You are amazing! Honestly, thank you so much for your help and working through this with me. You have no idea how much I appreciate it. This has saved me from hours or days of trouble shooting this by myself and a lot of frustration. Thank you! :slight_smile:

Now I just have to figure out how to pull the first log/earliest date.

18

The .FirstOrDefault() part will return the first record of the matching set. To find the earliest record for your criteria, add an OrderBy().

I’ve never used one, But it looks like the syntax would be
.OrderBy(r.LogNum)

Perhaps something like:

Db.ChgLog.Where(r=>r.Company == callContextClient.CurrentCompany 
  && r.TableName == "JobHead" 
  && r.SchemaName == "ERP" 
  && r.Key1.Contains(dsJobHeadRow.JobNum.ToString()))
  .OrderBy(r.LogNum).Select (r=>r.UserID).DefaultIfEmpty("Nothing Found").FirstOrDefault()

Haven’t actually tried that, so the OrderBy might actually belong in a different spot in the syntax.

19

I actually got it by setting the ChgLog ‘DateStampedOn’ = JobHead ‘CreateDate’. Here is my final code below:

Db.ChgLog.Where(r=>r.Company == callContextClient.CurrentCompany
&& r.TableName == “JobHead”
&& r.SchemaName == “ERP”
&& r.DateStampedOn == dsJobHeadRow.CreateDate
&& r.Key1 == dsJobHeadRow.JobNum).Select (r=>r.UserID).DefaultIfEmpty(“Nothing Found”).FirstOrDefault()

:grin:

1 Like