ok… we need to see your code. I cannot tell where you are getting this error.
RowMod doesnt exist in the DB, only in the Data\Tablesets - like the incoming ttTables for a directive.
@timshuwy I’m sorry, I’m not sure how to view the code. I have been using the Workflow Designer widgets.
@Chris_Conn What I entered into the RowMod mapping was "A,U".Contains(ttOrderDtl.RowMod). Since that’s calling out the ttTable, shouldn’t that work?
After a lot of digging and talking to my company’s Epicor consultant, I finally figured out how to do this using custom code. I’m wondering if you guys can help me fix one last issue I’m coming across…
One of the fields is a calculated field to determine the revenue impact (Updated DocTotalPrice - Original DocTotalPrice). My code syntax is okay, but for some reason that field is not calculating (blank field when I look at the UD table). Do you have any idea why?
EDIT: Forgot to mention that when I look at the log, it’s skipping Step 4 (Original retrieved) entirely.
> Action<string>WriteLine = (s) => //Create file to log when BPM runs
> {
> //return; //Remove comment to stop logging BPM runs
> string filename = @"\\ST-PRD-EPCAPP-0\EpicorData\Reports\Manager\Log_OrderChangesBPM.txt";
> System.IO.StreamWriter obj = new System.IO.StreamWriter(filename, System.IO.File.Exists(filename));
> obj.WriteLine(s);
> obj.Close();
> obj = null;
> };
>
> WriteLine("BPM START");
> foreach(var OrderDtl in (from tt in ttOrderDtl where tt.RowMod == "U" select tt)) //Fetch updated data from OrderDtl table
> {
> WriteLine("1... OrderDtl retrieved");
> foreach(var Customer in (from tt in Db.Customer where tt.Company == OrderDtl.Company && tt.CustNum == OrderDtl.CustNum select tt)) //Fetch data from Customer table
> {
> WriteLine("2... Customer retrieved");
> Ice.Tables.UD11 newrow = new Ice.Tables.UD11(); //Create new row in UD table
> newrow.Company = Session.CompanyID; //Company is always required
> newrow.Key1 = OrderDtl.OrderNum.ToString();
> newrow.Key2 = OrderDtl.OrderLine.ToString();
> newrow.Key3 = string.Format("{0:yyyyMMdd_hhmmss.fff}", DateTime.Now); //Needed to create truly unique key
> WriteLine("3... key generated");
> newrow.Character01 = (string)OrderDtl["ChangeNotes_c"];
> newrow.Number01 = OrderDtl.OrderNum;
> newrow.Number02 = OrderDtl.OrderLine;
> newrow.Number03 = OrderDtl.DocDspUnitPrice;
> newrow.Number04 = OrderDtl.SellingQuantity;
> newrow.Date01 = OrderDtl.RequestDate;
> newrow.Date02 = OrderDtl.ChangeDate;
> newrow.ShortChar01 = Customer.Name;
> newrow.ShortChar02 = (string)OrderDtl["ChangeType_c"];
> newrow.ShortChar03 = (string)OrderDtl["ChangeReason_c"];
> newrow.ShortChar04 = OrderDtl.ChangedBy;
> foreach(var Original in (from tt in ttOrderDtl where tt.RowMod == "" select tt)) //Fetch original data from OrderDtl table
> {
> WriteLine("4... Original retrieved");
> newrow.Number05 = OrderDtl.DocTotalPrice - Original.DocTotalPrice; //Calculate revenue impact
> }
> WriteLine("5... data populated");
> Db.UD11.Insert(newrow);
> WriteLine("6... row inserted");
> Db.SaveChanges();
> }
> OrderDtl["ChangeType_c"] = ""; //Clear field for next entry
> OrderDtl["ChangeReason_c"] = ""; //Clear field for next entry
> OrderDtl["ChangeNotes_c"] = ""; //Clear field for next entry
> WriteLine("7... custom fields reset");
> }
> WriteLine("BPM END");Step 4 is looping all UNCHANGED OrderLine records. Are there any? Perhaps a WriteLine of ttOrderDtl.Count() right before that would be helpful to see how many records exists in that table.
I did see one thing that could be causing the problem… you have a foreach when retrieving the original rows… there could be many unchanged rows in the dataset… you should be applying an additional filter to only look at the original row of your current OrderLine.
I also saw that you are retrieving the ENTIRE customer record so that you can get the customer name. It is always best practice to only return the fields you want.
I took this on as an exercize to convert your queries into simpler lambda statements also… check this (UNTESTED) code out.
Action<string> WriteLine = (s) => {
//Create file to log when BPM runs
//return; //Remove comment to stop logging BPM runs
string filename = @"\\ST-PRD-EPCAPP-0\EpicorData\Reports\Manager\Log_OrderChangesBPM.txt";
System.IO.StreamWriter obj = new System.IO.StreamWriter(filename, System.IO.File.Exists(filename));
obj.WriteLine(s);
obj.Close();
obj = null;
};
WriteLine("BPM START");
var(ModifiedODtlRows = ttOrderDtl.Where(x => x.RowMod == "U"));
foreach (var OrderDtl in ModifiedODtlRows) {
//Fetch updated data from OrderDtl table
WriteLine("1... OrderDtl retrieved");
//foreach (var Customer in (from tt in Db.Customer where tt.Company == OrderDtl.Company && tt.CustNum == OrderDtl.CustNum select tt)) //Fetch data from Customer table
string customerName = Db.Customer.Where(x => x.Company == OrderDtl.Company && x.CustNum == OrderDtl.CustNum).Select(x => x.Name).FirstOrDefault() ?? "Not Found";
WriteLine("2... Customer retrieved");
Ice.Tables.UD11 newrow = new Ice.Tables.UD11(); //Create new row in UD table
newrow.Company = Session.CompanyID; //Company is always required
newrow.Key1 = OrderDtl.OrderNum.ToString();
newrow.Key2 = OrderDtl.OrderLine.ToString();
newrow.Key3 = string.Format("{0:yyyyMMdd_hhmmss.fff}", DateTime.Now); //Needed to create truly unique key
WriteLine("3... key generated");
newrow.Character01 = (string) OrderDtl["ChangeNotes_c"];
newrow.Number01 = OrderDtl.OrderNum;
newrow.Number02 = OrderDtl.OrderLine;
newrow.Number03 = OrderDtl.DocDspUnitPrice;
newrow.Number04 = OrderDtl.SellingQuantity;
newrow.Date01 = OrderDtl.RequestDate;
newrow.Date02 = OrderDtl.ChangeDate;
newrow.ShortChar01 = Customer.Name;
newrow.ShortChar02 = (string) OrderDtl["ChangeType_c"];
newrow.ShortChar03 = (string) OrderDtl["ChangeReason_c"];
newrow.ShortChar04 = OrderDtl.ChangedBy;
var Original = ttOrderDtl.Where(x => x.RowMod == "" &&
x.OrderNum == OrderDtl.OrderNum &&
x.OrderLine == OrderDtl.OrderLine).FirstOrDefault();
if (Original != null) {
WriteLine("4... Original retrieved");
newrow.Number05 = OrderDtl.DocTotalPrice - Original.DocTotalPrice; //Calculate revenue impact
}
WriteLine("5... data populated");
Db.UD11.Insert(newrow);
WriteLine("6... row inserted");
Db.SaveChanges();
OrderDtl["ChangeType_c"] = ""; //Clear field for next entry
OrderDtl["ChangeReason_c"] = ""; //Clear field for next entry
OrderDtl["ChangeNotes_c"] = ""; //Clear field for next entry
WriteLine("7... custom fields reset");
}
WriteLine("BPM END");
@timshuwy Hey Tim, thanks for the code! Unfortunately, it gave me a slew of errors:
CS0103: The name ‘var’ does not exist in the current context
CS0103: The name ‘ModifiedODtlRows’ does not exist in the current context
CS0103: The name ‘ModifiedODtlRows’ does not exist in the current context
CS0120: An object reference is required for the non-static field, method, or property ‘Customer.Name’
@Chris_Conn The specific field I’m trying to retrieve the original data for will almost always be changed after the record is updated… if tt.RowMod == "" doesn’t fetch the original data before the change, what code would do that for me?
Think this was stated before.
The foreach loop will update the newrow.Number05 potentially multiple times.
Have you tried to just query the db.OrderDtl table instead of the tt?
Something like this.
var currentOrderDtl = Db.OrderDtl.Where(r => r.Company == OrderDtl.Company && r.OrderNum == OrderDtl.OrderNum && r.OrderLine = OrderDtl.OrderLine)
.Select(r => new {r.DocTotalPrice} )
.FirstOrDefault()
if (currentOrderDtl != null)
{
WriteLine("4... Original retrieved");
newrow.Number05 = OrderDtl.DocTotalPrice - currentOrderDtl.DocTotalPrice; //Calculate revenue impact
}
Sorry… this line:
var(ModifiedODtlRows = ttOrderDtl.Where(x => x.RowMod == "U"));
was incorrectly typed… it had too many parenthesis it should have been:
var ModifiedODtlRows = ttOrderDtl.Where(x => x.RowMod == "U");
Also, the line:
newrow.ShortChar01 = Customer.Name;
should have been changed to:
newrow.ShortChar01 = customerName;
Below is the corrected code set again:
Action<string> WriteLine = (s) => {
//Create file to log when BPM runs
//return; //Remove comment to stop logging BPM runs
string filename = @"\\ST-PRD-EPCAPP-0\EpicorData\Reports\Manager\Log_OrderChangesBPM.txt";
System.IO.StreamWriter obj = new System.IO.StreamWriter(filename, System.IO.File.Exists(filename));
obj.WriteLine(s);
obj.Close();
obj = null;
};
WriteLine("BPM START");
var ModifiedODtlRows = ttOrderDtl.Where(x => x.RowMod == "U");
foreach (var OrderDtl in ModifiedODtlRows) {
//Fetch updated data from OrderDtl table
WriteLine("1... OrderDtl retrieved");
//foreach (var Customer in (from tt in Db.Customer where tt.Company == OrderDtl.Company && tt.CustNum == OrderDtl.CustNum select tt)) //Fetch data from Customer table
string customerName = Db.Customer.Where(x => x.Company == OrderDtl.Company && x.CustNum == OrderDtl.CustNum).Select(x => x.Name).FirstOrDefault() ?? "Not Found";
WriteLine("2... Customer retrieved");
Ice.Tables.UD11 newrow = new Ice.Tables.UD11(); //Create new row in UD table
newrow.Company = Session.CompanyID; //Company is always required
newrow.Key1 = OrderDtl.OrderNum.ToString();
newrow.Key2 = OrderDtl.OrderLine.ToString();
newrow.Key3 = string.Format("{0:yyyyMMdd_hhmmss.fff}", DateTime.Now); //Needed to create truly unique key
WriteLine("3... key generated");
newrow.Character01 = (string) OrderDtl["ChangeNotes_c"];
newrow.Number01 = OrderDtl.OrderNum;
newrow.Number02 = OrderDtl.OrderLine;
newrow.Number03 = OrderDtl.DocDspUnitPrice;
newrow.Number04 = OrderDtl.SellingQuantity;
newrow.Date01 = OrderDtl.RequestDate;
newrow.Date02 = OrderDtl.ChangeDate;
newrow.ShortChar01 = customerName;
newrow.ShortChar02 = (string) OrderDtl["ChangeType_c"];
newrow.ShortChar03 = (string) OrderDtl["ChangeReason_c"];
newrow.ShortChar04 = OrderDtl.ChangedBy;
var Original = ttOrderDtl.Where(x => x.RowMod == "" &&
x.OrderNum == OrderDtl.OrderNum &&
x.OrderLine == OrderDtl.OrderLine).FirstOrDefault();
if (Original != null) {
WriteLine("4... Original retrieved");
newrow.Number05 = OrderDtl.DocTotalPrice - Original.DocTotalPrice; //Calculate revenue impact
}
WriteLine("5... data populated");
Db.UD11.Insert(newrow);
WriteLine("6... row inserted");
Db.SaveChanges();
OrderDtl["ChangeType_c"] = ""; //Clear field for next entry
OrderDtl["ChangeReason_c"] = ""; //Clear field for next entry
OrderDtl["ChangeNotes_c"] = ""; //Clear field for next entry
WriteLine("7... custom fields reset");
}
WriteLine("BPM END");
@timshuwy Thank you for all of your help! Those changes did fix the code. I ran it for another test though, and I’m still getting the same issue… everything is writing to the UD table except Number05, and when looking at the BPM log, it’s still skipping step 4 entirely.
@kells step #4 will not work if you have a Condition before your Custom Code Widget. Do you have a Condition Widget before?
@hkeric.wci Amazing - yes, I do have a condition widget! I have it running only if one of the three fields is changed.
Is there a way for me to move those conditions into the custom code so that it will work?
You can just change this to
var Original = Db.OrderDtl.Where(x => x.Company == OrderDtl.Company &&
x.OrderNum == OrderDtl.OrderNum &&
x.OrderLine == OrderDtl.OrderLine).FirstOrDefault();
That way you are getting the original from the Database and then you can leave your condition in place.
More reading related:
@hkeric.wci Hey Haso, thank you so much for your help! I changed what you mentioned to the following…
var Original = Db.OrderDtl.Where(x => x.Company == OrderDtl.Company && x.OrderNum == OrderDtl.OrderNum && x.OrderLine == OrderDtl.OrderLine).FirstOrDefault();
if (Original != null)
{
WriteLine("4... Original retrieved");
newrow.Number05 = OrderDtl.DocTotalPrice - Original.DocTotalPrice; //Calculate revenue impact
}
… and now I’m running into another error:
CS1061: ‘OrderDtl’ does not contain a definition for ‘DocTotalPrice’ and no extension method ‘DocTotalPrice’ accepting a first argument of type ‘OrderDtl’ could bbe found (are you missing a using directive or an assembly reference?)
I’m not understanding why switching to Db would cause this error, when the code has found DocTotalPrice just fine up until this point. I really appreciate your help and giving that additional reading - I’m very new to C# and trying to learn as I go so I can solve these problems in the future!
OrderDtl.DocTotalPrice this OrderDtl one is actually your ttOrderDtl while the Original is from the Db… There probably isnt a DocTotalPrice column.
If you type in OrderDtl. (OrderDtl period) and then hold down CTRL and press Spacebar it should list all the columns available, see if there is a better one.
That worked! I switched from DocTotalPrice to DocExtPiceDtl.
@hkeric.wci @timshuwy @Chris_Conn @knash I just want to thank you all, so much, for helping me out with this. I really learned a lot, and I think it’ll help my organization tremendously. I’m just going to summarize what worked in case anyone stumbles upon this huge thread in the future.
WORKFLOW DESIGN
CUSTOM CODE
Action<string>WriteLine = (s) => //Create file to log when BPM runs
{
//return; //Remove comment to stop logging BPM runs
string filename = @"\\ST-PRD-EPCAPP-0\EpicorData\Reports\Manager\Log_OrderChangesBPM.txt";
System.IO.StreamWriter obj = new System.IO.StreamWriter(filename, System.IO.File.Exists(filename));
obj.WriteLine(s);
obj.Close();
obj = null;
};
WriteLine("BPM START");
var ModifiedOrderDtlRows = ttOrderDtl.Where(x => x.RowMod == "U");
foreach(var OrderDtl in ModifiedOrderDtlRows) //Fetch updated data from OrderDtl table
{
WriteLine("1... OrderDtl retrieved");
string customerName = Db.Customer.Where(x => x.Company == OrderDtl.Company && x.CustNum == OrderDtl.CustNum).Select(x => x.Name).FirstOrDefault() ?? "Not Found"; //Fetch data from Customer table
WriteLine("2... Customer retrieved");
Ice.Tables.UD11 newrow = new Ice.Tables.UD11(); //Create new row in UD table
newrow.Company = Session.CompanyID; //Company is always required
newrow.Key1 = OrderDtl.OrderNum.ToString();
newrow.Key2 = OrderDtl.OrderLine.ToString();
newrow.Key3 = string.Format("{0:yyyyMMdd_hhmmss.fff}", DateTime.Now); //Needed to create truly unique key
WriteLine("3... key generated");
newrow.Character01 = (string)OrderDtl["ChangeNotes_c"];
newrow.Number01 = OrderDtl.OrderNum;
newrow.Number02 = OrderDtl.OrderLine;
newrow.Number03 = OrderDtl.DocDspUnitPrice;
newrow.Number04 = OrderDtl.SellingQuantity;
newrow.Date01 = OrderDtl.RequestDate;
newrow.Date02 = OrderDtl.ChangeDate;
newrow.ShortChar01 = customerName;
newrow.ShortChar02 = (string)OrderDtl["ChangeType_c"];
newrow.ShortChar03 = (string)OrderDtl["ChangeReason_c"];
newrow.ShortChar04 = OrderDtl.ChangedBy;
var Original = Db.OrderDtl.Where(x => x.Company == OrderDtl.Company && x.OrderNum == OrderDtl.OrderNum && x.OrderLine == OrderDtl.OrderLine).FirstOrDefault();
if (Original != null)
{
WriteLine("4... Original retrieved");
newrow.Number05 = OrderDtl.DocExtPriceDtl - Original.DocExtPriceDtl; //Calculate revenue impact
}
WriteLine("5... data populated");
Db.UD11.Insert(newrow);
WriteLine("6... row inserted");
Db.SaveChanges();
OrderDtl["ChangeType_c"] = ""; //Clear field for next entry
OrderDtl["ChangeReason_c"] = ""; //Clear field for next entry
OrderDtl["ChangeNotes_c"] = ""; //Clear field for next entry
WriteLine("7... custom fields reset");
}
WriteLine("BPM END");
2 Likes