Project Checklist - How to Set BPM Email to Assigned Person

[askulte (Andris Skulte)]

Hi guys,

I’ve almost got my BPM complete - Goal is to send an email when two project checklist tasks are complete to the person assigned to the “MASTER AGREEMENT” task.

I’m stuck on how to get the Person.EmailAddress field into the Send Email widget since the Person table isn’t part of the Project data set. I could link the ProjectTask.PersonID field to the Person.PersonID field, but not sure how so I can set the email variable.

Looking at the different threads here, as well as Insights notes, it all seems pretty complicated, but I’m sure it’s not… (and yes, I’ll be adding this to my personal BPM cookbook for future reference!).

Thanks for the help!

[ckrusen (Calvin Krusen)]

create a variable (or two) to hold the info from the Person table. Use a Set Arg/Var widget(s) with the expression using @josecgomez’s “Simple one-liner”:

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

note that expression should not have the string MyData = at the beginning, the ; at the end, and obviously updated for your fields and params.

edit

First make a variable to hold ttProjectTask.PersonID, say ProjPersonID

Then for the expression to set the variable to hold the emailAdd, you’ll want something like

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

[askulte (Andris Skulte)]

Calvin - Thanks! YES, it worked! That tip (and thread) is golden.

Here’s what I used for the PersonID:

Db.ProjectTask.Where( r =>r.Company == callContextClient.CurrentCompany && r.ProjectID == ttProjectTaskRow.ProjectID && r.Description == "MASTER AGREEMENT" ).Select( r =>r.PersonID).FirstOrDefault()

And for the Email Address (note, it errored first time since I used r.EmailAddress, instead of r.EMailAddress):

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

Is it bad that I named my variable PersonID, which is the same as Person.PersonID?

And if it helps anyone else, I needed the .FirstOrDefault() at the end, otherwise it would error out with "Result of expression cannot be assigned to variable of ‘System.String’ type.

I like the .DefaultIfEmpty("") tip - I’ll add that too. Will that cause it to error out if email is set to synchronous (and drive someone to let IT know that the person needs an email)?

[ckrusen (Calvin Krusen)]

A couple of things…

It’s a bad idea to link directly to a ttTable (ttProjectTaskRow in your example). Because, depending on the tables, that can force A LOT of records to be retrieved from the DB. There’s an extensive thread on the topic on this site.

You had to remove the .DefaultIfEmpty("") from the first expression because ProjectTask.PersonID is probably an integer. It might have worked if you used .DefaultIfEmpty(-1). Then you could have tested your variable PersonID in a condition to determine if it was found. I’m not sure what the value of the first LINQ expression returns if no matching record was found. It might try to return Null. In which case, you variable would have had to have been declared as integer?

edit

just curious as to why you needed the first expression. Isn’t PersonID already available in in ttProjectTask? The expression could have simply been:
ttProjectTask.PersonID

[askulte (Andris Skulte)]

@ckrusen

It’s a bad idea to link directly to a ttTable (ttProjectTaskRow in your example

Whoops! I missed that. I had set ProjectID to a variable, but still called the temp table. Fixed!

Also, no error if the person was not set or the email is “”. Epicor sent it to my ‘cc’ anyways, so I put in a condition block to stop it.

Thanks for all the help! Here’s the end result:

[askulte (Andris Skulte)]

@ckrusen

just curious as to why you needed the first expression. Isn’t PersonID already available in in ttProjectTask? The expression could have simply been:
ttProjectTask.PersonID

The person I need is the one that completed the “MASTER AGREEMENT” checklist task, so it may not be the record in the ttProjectTask row.

[askulte (Andris Skulte)]

@ckrusen

It’s a bad idea to link directly to a ttTable (ttProjectTaskRow in your example…)

Thinking more about this… If the ttTable is one of the ‘where’ criteria, why would Epicor bring in a full table’s worth of records to evaluate, compared to using a variable that you defined earlier? Both go in the where clause, so I would think Epicor’s db cost would be similar.

I’d think this case would be treated differently than which way you join in a BAQ query (within the BPM)
ttTable(1) joining to dbTable(2) is OK vs dbTable(1) joinging to ttTable (2) is bad.

[ckrusen (Calvin Krusen)]

You’re probably right on that. You’re using a value from a record (which is in a relatively small dataset), not the whole dataset itself.

[RishiRaj (RsihiRaj)]

Hello @askulte @ckrusen ,

I’ve done the same steps, but with some different condition. my flow looks like this.

Still I’m unable to send the mail to the assigned person.
Can you tell what might had went wrong.

for PersonID and MailID argument, I’ve used your code.

Any help would be a great help.

Thanks.
Rishi

[askulte (Andris Skulte)]

Rishi - Add a pop-up message before the send-email to show that the email address variable that got set properly, and add yourself as a hard-coded person on the email to see what the message is sending (or not!). That should help troubleshoot where it’s going wrong…

[RishiRaj (RsihiRaj)]

@askulte , we’ve tried doing it. and it’s showing the below error.

Can you throw some light on it.?

Thanks

[askulte (Andris Skulte)]

Put message boxes after every step in the BPM. That makes it easy to step through it, one block at a time. When does that error appear? I’m guessing one of the variables isn’t set correctly.