I would like a BPM to track deletions to table IntQueIn which tracks interface transactions. I saw a similar request for deletions on table InvcHead and tried to use the same sort of logic however it’s not working correctly. I’d appreciate any insight into what’s wrong with this BPM (I’m new to this). When I enable it the transactions don’t come through via the integrated table workbench.
what does the error message say ?
From the quick looks of it, I would recommend you wrap the whole UD09 inside a TransactionScope and lastly you need Db.Validate(UD09); or just Db.Validate(); to actually commit the Insert to the database.
Epicor Wraps Entity Frameworks SaveChanges() into Db.Validate();
using (var txScope = IceDataContext.CreateDefaultTransactionScope())
{
// Create New
Ice.Tables.UD10 UD10 = new Ice.Tables.UD10();
Db.UD10.Insert(UD10);
UD10.Company = Session.CompanyID;
UD10.Key1 = Convert.ToString(ttMtlQueueRow.MtlQueueSeq);
UD10.Key2 = ttMtlQueueRow.PONum == 0 ? Convert.ToString(ttMtlQueueRow.JobNum) : Convert.ToString(ttMtlQueueRow.PONum);
UD10.Date01 = ttMtlQueueRow.SysDate; /* Entry Date */
UD10.Date02 = ttMtlQueueRow.NeedByDate;
UD10.ShortChar04 = ttMtlQueueRow.PartNum;
UD10.ShortChar05 = ttMtlQueueRow.FromWhse;
UD10.ShortChar06 = ttMtlQueueRow.FromBinNum;
UD10.ShortChar07 = ttMtlQueueRow.ToWhse;
UD10.ShortChar08 = ttMtlQueueRow.ToBinNum;
UD10.Character01 = ttMtlQueueRow.PartDescription;
UD10.ShortChar18 = Session.UserID;
// Commit to Database
Db.Validate(UD10);
// Complete Transaction
txScope.Complete();
}
Also I would change it to…
Note .Deleted() and .ToList();
var ttIntQueIn_Rows = (from ttIntQueIn_Row in ttIntQueIn where ttIntQueIn_Row.Deleted() select ttIntQueIn_Row).ToList();
foreach (var row in ttIntQueIn_Rows)
{
// use row, in case they delete multiple and Epicor combines them into a batch
}
A full example where I am tracking MtlQueue
// Initialize Variables
string ACTION_NAME = string.Empty;
var mtlQueueRows =
from tmq in ttMtlQueue
where tmq.Added() || tmq.Updated() || tmq.Deleted()
select tmq;
foreach (var ttMtlQueueRow in mtlQueueRows)
{
if (string.Equals(ttMtlQueueRow.RowMod, IceRow.ROWSTATE_ADDED, StringComparison.OrdinalIgnoreCase))
{
ACTION_NAME = "ADD";
}
else if (string.Equals(ttMtlQueueRow.RowMod, IceRow.ROWSTATE_UPDATED, StringComparison.OrdinalIgnoreCase))
{
ACTION_NAME = "UPDATE";
}
else if (string.Equals(ttMtlQueueRow.RowMod, IceRow.ROWSTATE_DELETED, StringComparison.OrdinalIgnoreCase))
{
ACTION_NAME = "DELETE";
}
using (var txScope = IceDataContext.CreateDefaultTransactionScope())
{
Random r = new Random();
// Create New
Ice.Tables.UD10 UD10 = new Ice.Tables.UD10();
Db.UD10.Insert(UD10);
UD10.Company = Session.CompanyID;
UD10.Key1 = Convert.ToString(ttMtlQueueRow.MtlQueueSeq);
UD10.Key2 = ttMtlQueueRow.PONum == 0 ? Convert.ToString(ttMtlQueueRow.JobNum) : Convert.ToString(ttMtlQueueRow.PONum);
UD10.Key3 = Convert.ToString((int) ((TimeSpan)DateTime.Now.Subtract( DateTime.Today )).TotalSeconds + r.Next(5, 130) ); /* Keeps Rows Unique */
UD10.Key4 = "MtlQueue"; /* Target Table so we can use UD10 for other tables if needed */
UD10.Key5 = Convert.ToString(ttMtlQueueRow.SysTime); /* Keeps Rows Unique */
UD10.Date01 = ttMtlQueueRow.SysDate; /* Entry Date */
UD10.Date02 = ttMtlQueueRow.NeedByDate;
UD10.Date03 = DateTime.Today; /* Processed Date */
UD10.ShortChar01 = DateTime.Today.AddSeconds(ttMtlQueueRow.SysTime).ToString("HH:mm:ss"); /* Entry Time Human Readable */
UD10.ShortChar02 = DateTime.Today.AddSeconds(ttMtlQueueRow.NeedByTime).ToString("HH:mm:ss"); /* Convert to Human Readable */
UD10.ShortChar03 = DateTime.Now.ToString("HH:mm:ss"); /* Processed Time Human Readable */
UD10.ShortChar04 = ttMtlQueueRow.PartNum;
UD10.ShortChar05 = ttMtlQueueRow.FromWhse;
UD10.ShortChar06 = ttMtlQueueRow.FromBinNum;
UD10.ShortChar07 = ttMtlQueueRow.ToWhse;
UD10.ShortChar08 = ttMtlQueueRow.ToBinNum;
UD10.ShortChar09 = ttMtlQueueRow.LotNum;
UD10.ShortChar10 = ttMtlQueueRow.RequestedByEmpID;
UD10.ShortChar11 = ttMtlQueueRow.SelectedByEmpID;
UD10.ShortChar12 = ttMtlQueueRow.IUM;
UD10.ShortChar13 = ttMtlQueueRow.Reference;
UD10.ShortChar14 = ttMtlQueueRow.TranType;
UD10.Number01 = ttMtlQueueRow.Quantity;
UD10.ShortChar15 = ttMtlQueueRow.JobNum;
UD10.ShortChar16 = (string)ttMtlQueueRow["ShortChar02"]; /* Urgency Priority Level Y, B, R */
UD10.ShortChar17 = ttMtlQueueRow.PONum == 0 ? "Job" : "PO"; // Job or PO
UD10.Number03 = ttMtlQueueRow.Priority;
UD10.Character01 = ttMtlQueueRow.PartDescription;
UD10.ShortChar18 = Session.UserID; /* Processing UID */
UD10.ShortChar19 = Session.EmployeeID; /* Processing EmpID */
UD10.ShortChar20 = ACTION_NAME; /* Action Name */
UD10.Number20 = 500; /* IT VERSION NUMBER */
// Commit to Database
Db.Validate(UD10);
// Complete Transaction
txScope.Complete();
}
}
Thank you so much for your response and I’ve tried to put in some new code based on your suggestion but I get the errors shown below. I’m no programmer so would appreciate any input to correct this.
Paste me your code here in plain-text I can correct it for you.
Also when you paste it put a ```cs block around it
Like this:
OK here you go - thanks
var ttIntQueIn_Rows = (from ttIntQueIn_Row in ttIntQueIn where ttIntQueIn_Row.Deleted() select ttIntQueIn_Row).ToList();
foreach (var row in ttIntQueIn_Rows)
{
// use row, in case they delete multiple and Epicor combines them into a batch
}
using (var txScope = IceDataContext.CreateDefaultTransactionScope())
{
// Create New
Ice.Tables.UD10 UD10 = new Ice.Tables.UD10();
Db.UD10.Insert(UD10);
UD10.Company = Session.CompanyID;
UD10.ShortChar04 = ttIntQueIn_Row.IntQueID;
UD10.ShortChar05 = ttIntQueIn_Row.CreateDCDUserID;
UD10.ShortChar18 = Session.UserID;
// Commit to Database
Db.Validate(UD10);
// Complete Transaction
txScope.Complete();
}
Perfect, try this:
var ttIntQueIn_Rows = (from ttIntQueIn_Row in ttIntQueIn where ttIntQueIn_Row.Deleted() select ttIntQueIn_Row).ToList();
foreach (var row in ttIntQueIn_Rows)
{
using (var txScope = IceDataContext.CreateDefaultTransactionScope())
{
// Create New
Ice.Tables.UD10 UD10 = new Ice.Tables.UD10();
Db.UD10.Insert(UD10);
UD10.Company = Session.CompanyID;
UD10.ShortChar04 = row.IntQueID;
UD10.ShortChar05 = row.CreateDCDUserID;
UD10.ShortChar18 = Session.UserID;
// Commit to Database
Db.Validate(UD10);
// Complete Transaction
txScope.Complete();
}
}
Haso,
Thank you again. I no longer have any syntax errors and the BPM is enabled. However I am not getting any entries posted to UD10 table despite being able to see entries in table IntQueIn being added and then deleted through standard business processes. Any ideas at all? Am I just missing something simple.
var ttIntQueIn_Rows = (from ttIntQueIn_Row in ttIntQueIn where ttIntQueIn_Row.Deleted() select ttIntQueIn_Row).ToList();
foreach (var row in ttIntQueIn_Rows)
{
using (var txScope = IceDataContext.CreateDefaultTransactionScope())
{
// Create New
Ice.Tables.UD10 UD10 = new Ice.Tables.UD10();
Db.UD10.Insert(UD10);
UD10.Company = Session.CompanyID;
UD10.ShortChar04 = Convert.ToString(row.IntQueID);
UD10.ShortChar05 = Convert.ToString(row.CreateDCDUserID);
UD10.ShortChar18 = Session.UserID;
// Commit to Database
Db.Validate(UD10);
// Complete Transaction
txScope.Complete();
}
}
this is your issue mate,
you need to connect your arrows this way:
It worked!! Al & Haso I am incredibly grateful for your guidance. I owe you both a drink.
