BPM to Email Changes

I have a Standard Data Directive on OrderHed.Update that checks if the “Neeed By Date”, ShipVia, or FOB has changed from any to another. It is functional but I have received a request to improve the display format.

I would like to show the previous value and then the new value for each of these 3 fields (if any of them stay the same I would like to suppress that line). I am not sure how to do that email format, so I did a bunch of conditions that drop down to 8 different email formats (so if the data is not changing no line is shown for that field).

Sometimes (like when there is only a change on the need by date), the “Need By Date” from ttOrderHed shows a before and after separated by a semicolon (but it does this on the company, sales order number, serial number, order stage. I do not know why these fields are printing twice (I did not originally create this BPM).


If I change just the Need By Date, it doubles items.

If I change all 3 items, I lose the line return (showing individual lines for each item) and nothing shows as double:

The email widget shows the line returns:

What is going on? Am I doing something wrong?

@BKen Maybe this thread will help you.

I tried that for line returns and could not get any of that to work. Then I figured I would try something that I have used in word to give you a page break (Ctrl + Enter) in the email widget…and it worked (but not every time).

You can see ZERO difference in the email widget, but in the three lines in the email body went from this:

To this:
Then I changed a couple of the fields to something else and one of the line returns disappeared:

Then I added 3 spaces and the Ctrl+Enter at the end of the line and voila:

Now I am still working on how to reliably get the previous values for each. I have used a Fill Table by Query, but I am not getting the results I am expecting.

So if I only change the Need by date and have all the boxes checked, then I get before and after on the change.


But if more than 1 field is changed on the same save, then I do not get the before and after.

Does the tt lose the previous value if more than 1 parameter gets updated?

I’ve had numerous instance where the changed value showed up twice. It would be one thing if it was showing the old value and the new, but it is clearly showing the same value twice. Play with the filter checkboxes (Added, Deleted, Updated, Unchanged). Might be some combo of them that works better.

As for the dissappearing line breaks, what do you see if you put some characters after the field? Something like:

Date changed to <Need_By_Date/> ,

Note the comma at the end.

Does adding the text <br> in the email body create a line break, or just litterally show as "<br>" ?

And one thing I’d do to pretty it up is to use a Variable (string type) and set it based on the changed value. That way you could format it so that it doesn’t show the time.

This is what the Email Widget looks like:

This is what the email text looks like:

The < b-r > (without dash) shows up as < b-r > (without dash) and the comma and space+comma shows up as comma and space+comma, but the line return does not show up.

If I try 1 space and Ctrl+Enter, I do not see any line returns in the email, I see this:

If I try 2 spaces and Ctrl+Enter, I do not see any line returns in the email, I see this:

So back to 3 spaces and Ctrl+Enter, the line returns now show up in the email, this:

I am still stuck on getting a reliable way to show both the before and after values. For the Need By date change (the before and after only show up if the Need By date is the ONLY change on the save). For the ship via and FOB I am showing the Description (not the code) in the email (but I think the tt would only have the code value, not the description).

How do I pretty up the Date-Time to show just the date?

  1. Create a variable (of type string) in the BPM
  2. Use a Set Variable widget, and in the formula convert the datetime to a formatted date string.


Here’s a post showing how to create a variable and assign it a value. This post assumes the value isn’t even in the BPM’s dataset and fetches it from the DB. You shouldn’t need to do that. Just have to spend some time figuring out the formula / expression to convert the datetime to string of the formatted date.

So reading through the forums, I found a convenient way to convert the datetime into just a date:
This is the expression I used to convert my new “NeedByDate” to no longer show the time of midnight.

((DateTime) dsOrderHedRow.NeedByDate).ToString("MM-dd-yyyy")

Now I am trying to use a description rather than the code (the code is held in the Order Header table).
I have been able to achieve this on the ShipVia field, but I cannot on the FOB table.

Here is what I am doing on the expression field for the Ship Via:

PrevShipViaDesc = Db.ShipVia.Where(x=>x.Company == CompanyID && x.ShipViaCode == PrevShipViaCode).Select(x=>x.Description).FirstOrDefault()

However, when I try the same thing with the FOB database table, I get an error message:

PrevINCOTERMDesc = Db.FOB.Where(x=>x.Company == CompanyID && x.FOB == PrevINCOTERMCode).Select(x=>x.Description).FirstOrDefault()


The Erp.FOB table contains columns called Company, FOB, and Description, so I do not understand the error.

Entity Framework does not allow fields to have the same name as table. In such cases field has suffix1,
So you need to use FOB1 field name in your linq query


You are right, FOB1 worked. I changed it to:

PrevINCOTERMDesc = Db.FOB.Where(x=>x.Company == CompanyID && x.FOB1 == PrevINCOTERMCode).Select(x=>x.Description).FirstOrDefault()

Now it is time for me to share what I did (to help any other users trying to accomplish the same thing). A predecessor created a Standard Data Directive on OrderHed that triggered for a machine order (not a parts order) when the NeedByDate, ShipVia, or FOB changed from any to another. It would send an email that would provide the order number, machine serial number, the current values of the 3 fields that possibly changed, then the open order lines with a part number. It was rather basic and users (especially salespeople who were travelling) wanted more information to help them identify the Customer and what changes occurred.

After unsuccessfully trying to get the “before” value on the Standard Data Definition, I created a Pre-Processing Method Directive on SalesOrder.Update.

So I used 2 conditions. The first one checks some UD fields that identify this is a “Machine” order and not a parts order. The second condition checks that one of the three fields has changed from any to another:
Then I set the order number as a variable:
My predecessor had a custom code widget to get the salespersons email addresses (some minor tweaks were needed to get it to work in a Method directive compared to a Data Directive; like tt becomes ds.) That custom code looks like this:

//look at incoming SalesRepList (~ delimited) and get emails
var internalSalesEmails = new List<string>();

var salesRepString = ds.OrderHed.Select(r=>r.SalesRepList)

if(salesRepString != null)
  //split the string into a list of Sales Rep Codes on ttOrderHed
  var salesRepList = salesRepString.Split('~').ToList();
    var roleCodeList = Db.SalesRep.Where(r=>r.Company == Session.CompanyID 
                                         && r.RoleCode == "Agent")
    //add "Sales" work force ID to roleCodeList

    //reduce salesRepList down to just internal Sales by removing the roleCodeList values
    var internalSalesRepList = salesRepList.Except(roleCodeList).ToList();       
    // lookup email addresses
    foreach (string RepCode in internalSalesRepList)
      var tempEmailList = Db.SalesRep.Where(e=>e.Company == Session.CompanyID && e.SalesRepCode == RepCode)
      string tempEmailString = string.Join("",tempEmailList);
    //create email string 
    SalespersonEmail = string.Join(";",internalSalesEmails);

Then I filled a table by query (to grab the open lines, line number, and Machine part number):
The query looks like this:
With only the 3 display fields I needed:
Then I set the Previous values as variables, beginning with the NeedBy Date:
Then with Calvin’s help (and other tidbits gleaned from EpiUsers threads), Set the date (as another variable) without the time:
Then I set another variable for the New NeedBy Date (short version):

Then I set another variable for the Previous Ship Via Code:
The I set another variable for the Previous Ship Via Description:
Then I set another variable for the Previous INCOTERM (or FOB) Code:
Then, with Olga’s tip (notice the column as FOB1), I added the INCOTERM description:

Then I wanted to add some text that would be conditional (to identify the changed values and also show the unchanged values). I did this with more variables for NeedBy Date, ShipVia, and INCOTERM/FOB. Here is the expression for the NeedBy Date:
The ShipVia (the new values are carried in the ds. table):
The email then pulls the conditional variable in. The customer name gets pulled through on the ds.OrderHed table as does a formatted version of the ship to address. Then I show my conditional statements to clarify what changed (showing the previous value and the new value) or stating that the field did not change and showing what that value is. I also had to add three spaces then Ctrl+Enter to get my line returns to show properly in the email. I also showed in the message which user made the change to the order, what the order stage is, and then showing the order lines:

Here is what the email looks like:

Hopefully this will help other users.

I am open to critiques on how I could have done this better.


Thanks for sharing your detailed solution.

All too often the OP will just say, “I figured it out. Had to use a method directive.”

1 Like