Insert UD Table Records from a BPM

UPDATE - Thanks to helpful replies and some experimentation, I have a working POC, I added this as a Reply to the my post. Thanks!!!

Hi,

When I save a Sales Order and set the ReadyToProcess flag, I want to loop through the Order Lines, and for each order line, I want to insert a record in UD04 with Key1=OrderDtl_OrderNum and Key2=OrderLine_OrderLine.

We are thinking that a BPM with a C# Action is appropriate, rather than a Trigger or Stored Procedure.

I think we have our looping logic understood but we don’t know the syntax to insert the row. Since it is a UD Table, we are thinking it is find to insert directly.

If there is a straight forward, prescribed way that is safer like calling GetNewUD04, that’s great, but not sure how to begin on that front.

Thanks!

This example should get you pointed in the right direction. I believe you could also do this without using custom code but this is just my preference.

/* Find current record in dataset */
using (var txScope = IceContext.CreateDefaultTransactionScope())
{    
foreach (var recOrderHed in (from row in ttOrderHed 
    select row))
        {
        /* Find all lines for Order */
        foreach (var recOrdDetail in (from row in Db.OrderDtl where row.Company == Session.CompanyID && row.OrderNum == recOrderHed.OrderNum select row))
            {
                 /* Update UD Record */
                         UD01 newRow = new UD01();
                         Db.UD01.Insert(newRow);
                         newRow.Company = Session.CompanyID;
                         newRow.Key1 = recOrdDetail.OrderNum.ToString();
                         newRow.Key2 = recOrdDetail.OrderLine.ToString();
                         newRow.Key3 = Guid.NewGuid().ToString();
                         newRow.ShortChar01 = "Example";
            }
        }

Db.Validate();
txScope.Complete();

}
2 Likes

This will work for UD Tables , however note that you are bypassing the Epicor logic with this and using EntityFramework (the equivalent of doing an Insert directly into the Table). This is normally considered bad practice (with the Exception of UD Tables) you should use the Business Objects to do all your inserts / updates (if possible)
Here is the equivalent code using the Business Objects, you’ll have to bring in the reference to UD02 Contract in your BPM.

using(var UD02svc = Ice.Assemblies.ServiceRenderer.GetService<UD02SvcContract>(Db))
{
UD02Tableset ds = new UD02Tableset();
UD02svc.GetaNewUD02(ref ds);
ds.UD02[0].Key1 ="XX";
ds.UD02[0].Key2 = "YY";
ds.UD02[0].Key3 = "ZZ";
UD02svc.Update(ref ds);
}

Again what @danbedwards shared is perfectly valid for UD Tables, but if you want to insert / create anything else you should use the business objects.

And in your original post you mention Trigger / Stored procedure… Those should NEVER be used (specially triggers) and Stored Procedures should always be read only (if used)

5 Likes

Below is an example of a solution that is coming together. Thanks all!!!

BPM Method Directive Post SalesOrder.Update
Add conditions wherever I need them
Invoke BO Method Ice.UD04.GetNewUD04
Set Field for added row Key1
Set Field for added row Key2
Invoke BO Method Ice.UD04.Update
image


image
image

image

1 Like

Will this code work from a Data Directive?

For our WebOrders I use a Data Directive to set the Plant (didn’t know what BOs were hit from a WebOrder so this was the way I knew would work on a tight deadline). However if we need to fulfill out of both plants then I’d have to create a separate Release. Since the DD doesn’t have the Invoke BO Method module then I was curious if the code would still work.

Alternatively if someone knows how to trace the BOs hit when a WebOrder gets passed in from Magento, then I could do my logic in a PreProc and Invoke the BO for a new Rel in a PostProc. Open to either.

A post was split to a new message: Add a record to a UD table from a front end customization

Hi Matt. Below is the code we use to insert rows into UD04 as a Post Process on SalesOrder.MasterUpdate. We get an order for multiple items, and we decide where we want to build the order based on the product mix. I had help with this, and the comments are a little clumsy, but hopefully you can make sense of it.

image
image

CODE
using(var UD04svc = Ice.Assemblies.ServiceRenderer.GetService<UD04SvcContract>(Db))

foreach (var CurrentOrder in ttOrderHed) // Identify the Order that we have in ttOrderHed, should only ever be one OrderHed

{

bool addMetaRow = true; /Initialize our variables/

string orderNumChar = CurrentOrder.OrderNum.ToString();

string headerMfgLocation = "";

bool mainAlways = false; // Are ANY of the lines on the order MA?

bool mainDefault = false;// Are ANY of the lines on the order MD?

bool hnvrAlways = false;// Are ANY of the lines on the order HA?

bool hnvrDefault = false; // Are ANY of the lines on the order HD?

bool hnvrCB = false; //01-26-19 If HNVR, then set the checkbox

// Now find all the lines on the order that we are working with, that don’t have ‘None’ for a location and put them into a list (ToList)

var CurrentOrderLinesList= (from row in Db.OrderDtl where row.Company == callContextClient.CurrentCompany && row.OrderNum == CurrentOrder.OrderNum && row.MfdLocation_c != "None" select row).ToList();

// Evaluate one line at a time, and set the Order Level Variables above based on what you find on each line

foreach (var CurrentOrderLine in CurrentOrderLinesList)

{

if (CurrentOrderLine.MfdLocation_c == "MA") {

mainAlways = true;

}

else if (CurrentOrderLine.MfdLocation_c == "MD") {

mainDefault = true;

}

else if (CurrentOrderLine.MfdLocation_c == "HA") {

hnvrAlways = true;

}

else if (CurrentOrderLine.MfdLocation_c == "HD") {

hnvrDefault = true;

}

}

// Depending which Order Level Variables are true, decide how to set headerMfgLocation

if (!mainAlways && !mainDefault && !hnvrAlways && !hnvrDefault) {

headerMfgLocation = “NONE”;//whenever we find a match, we set the location, and then stop, no more ELSE IF’s after that

}

else if (mainAlways && hnvrAlways) {

headerMfgLocation = "CONFLICT"; // This is not allowed by policy - no orders should have a mix of HA/MA

}

else if (mainAlways) {

headerMfgLocation = "MAIN";// if it has any MA and the rest are HD or MD, we make in MAIN

}

else if (hnvrAlways) {

headerMfgLocation = "HNVR"; // if it has any HA and the rest are HD or MD, we make in Hanover

hnvrCB = true; //01-26-19

}

else if (!mainDefault && hnvrDefault) {

headerMfgLocation = "HD";

}

else {

headerMfgLocation = “MD”;// if we didn’t find a match yet, then it will do this. The final Else is “MD”

}

// Now only if we have a headerMfgLocation other than MAIN or NONE do we want to proceed with creating rows in UD04 if they don’t exist yet, update them if they d

//if (headerMfgLocation != "MAIN" && headerMfgLocation != "NONE") {

// we are moving the line above to evaluate when ADDING a new line - we always want to update existing lines

// loop through each line in the order (we already have the list from before)

foreach (var CurrentOrderLine in CurrentOrderLinesList) {

bool addDtlRow = true;

string orderLineChar = CurrentOrderLine.OrderLine.ToString();

// create a database scope - this will tell the subsequent Db.Validate and txScope.Complete what table it is updating

using (System.Transactions.TransactionScope ourScope = IceDataContext.CreateDefaultTransactionScope()) // part of Synch Location logic below

{

// look to see if if the Row exists in UD04 for the Current Order Line, and loop through - 1 row or none

foreach(var ud04ExistingRow in (from ud04 in Db.UD04 where ud04.Company == callContextClient.CurrentCompany && ud04.Key1 == orderNumChar && ud04.Key2 == orderLineChar && ud04.Key3 == "0" select ud04))

{

// We found it, so we don’t want to add it, but we want to make sure the SC01 is up to date

addDtlRow = false;

//START - Synch the Location if already present:

// since it was already there, let’s perform the actual update if it doesn’t match the latest re-calc

var ud04ExistingRowForUpdate = ud04ExistingRow;

if (ud04ExistingRowForUpdate.ShortChar01 != headerMfgLocation) {

ud04ExistingRowForUpdate.ShortChar01 = headerMfgLocation;

Db.Validate();

ourScope.Complete();

}

// END -Synch the Location if already present

}

}

// if we didn’t find a ud04 row, add one

if (addDtlRow)

{

if (headerMfgLocation != "MAIN" && headerMfgLocation != "NONE") {

// if we don’t have any existing rows, we don’t want to write them if MAIN or NONE (to keep UD04 as clean as possible)

UD04Tableset ds = new UD04Tableset();

UD04svc.GetaNewUD04(ref ds);

ds.UD04[0].Key1 = orderNumChar;

ds.UD04[0].Key2 = orderLineChar;

ds.UD04[0].Key3 = "0";

ds.UD04[0].ShortChar01 = headerMfgLocation;

ds.UD04[0].CheckBox08 = hnvrCB; //01-26-19

UD04svc.Update(ref ds);

}

}

}

//}

// see comment above - we are moving the line above to evaluate when ADDING a new line - we always want to update existing lines

}

Hi Jose I tried using your block code to update Part unit price te sintaxis is OK but is not updating the unit price take a look:

any clue ?

thank you

Hi Octavio I’m not really sure what you are referring to here, this code isn’t referenced anywhere in this post that is over a year old

If you are trying to do something different please start a new topic

Hi,

I’ve been trying for a while to make it work but I can’t figure it out… I know this is an old post but I`m still trying to get answer here since this is the same base subject.

I`m in a data directive of JobHead (In-transaction).

I`m using the code proposed by Jose adapted to my context:

But I always get that error when running it (Trigger when JobHead.JobRelease 0 to 1):
Error_1

What am I doing wrong?

Someone got tips for me?

Thank you in advance!

Best regards,
Alexandre

Hi Alexandre,

My BPM is a Post-Process Method Directive on SalesOrder.MasterUpdate. I have a pre-process that Enables the Post Process if they check a box on the Sales Order Header, then it inserts one row per Sales Order Line into UD06

I had to add a Reference to Ice.Contracts.BO.UD06 see screen shot. There is a loop in there that I’m not using ‘not using this…’ but I left it.

image001.png

The entirety of the code is below:

// 2020-09-22 remove N15 StickNum, that field is not populated

// RickWelling 2020-08-12 - Bring in TrimDec and StickLengthDec from OD in case I need to write it back

// RickWelling 2020-07-30 - Adapted from the UD04 Program, this builds UD06 if the RecutSched_c flag is true when the order is released to production

using(var UD06svc = Ice.Assemblies.ServiceRenderer.GetService<UD06SvcContract>(Db))

foreach (var CurrentOrder in ttOrderHed) // Identify the Order that we have in ttOrderHed, should only ever be one OrderHed

{

bool addMetaRow = true; /*Initialize our variables*/

string orderNumChar = CurrentOrder.OrderNum.ToString();

string NotUsingThis = "NotUsingThis"; //Carry over from UD04 build, not relevent here

// Now find all the lines on the order that we are working with, that are Flagged RecutSched_c = true and put them into a list (ToList)

var CurrentOrderLinesList= (from row in Db.OrderDtl where row.Company == callContextClient.CurrentCompany && row.OrderNum == CurrentOrder.OrderNum && row.RecutSched_c select row).ToList();

// loop through each line in the order (we already have the list from before)

foreach (var CurrentOrderLine in CurrentOrderLinesList) {

bool addDtlRow = true;

string orderLineChar = CurrentOrderLine.OrderLine.ToString();

string orderLineCutType = CurrentOrderLine.ShortChar06;

string orderLineRecutter = CurrentOrderLine.ShortChar07;

string orderLineTrim = CurrentOrderLine.ShortChar08;

string orderLineStickLength = CurrentOrderLine.ShortChar09;

int orderNumInt = CurrentOrderLine.OrderNum;

int orderLineInt = CurrentOrderLine.OrderLine;

decimal orderLineKnifeQty = CurrentOrderLine.Number10;

decimal orderLineSticksM = CurrentOrderLine.Number12;

decimal orderLineCut = CurrentOrderLine.Number14;

decimal orderLineTrimDec = CurrentOrderLine.TrimDec_c;

decimal orderLineStickLengthDec = CurrentOrderLine.StickLengthDec_c;

// create a database scope - this will tell the subsequent Db.Validate and txScope.Complete what table it is updating

using (System.Transactions.TransactionScope ourScope = IceDataContext.CreateDefaultTransactionScope()) // part of Synch existing row logic below

{

// look to see if if the Row exists in UD06 for the Current Order Line, and loop through - 1 row or none

foreach(var UD06ExistingRow in (from UD06 in Db.UD06 where UD06.Company == callContextClient.CurrentCompany && UD06.Key1 == orderNumChar && UD06.Key2 == orderLineChar && UD06.Key3 == "0" select UD06))

{

// We found it, so we don't want to add it, but we want to make sure the SC01 is up to date

addDtlRow = false;

//START - Synch the data if already present:

var UD06ExistingRowForUpdate = UD06ExistingRow;

if (UD06ExistingRowForUpdate.ShortChar01 != orderLineRecutter) {

UD06ExistingRowForUpdate.ShortChar01 = orderLineRecutter;

Db.Validate();

ourScope.Complete();

}

// END -Synch the data if already present

}

}

// if we didn't find a UD06 row, add one

if (addDtlRow)

{

if (NotUsingThis == "NotUsingThis") {

UD06Tableset ds = new UD06Tableset();

UD06svc.GetaNewUD06(ref ds);

ds.UD06[0].Key1 = orderNumChar;

ds.UD06[0].Key2 = orderLineChar;

ds.UD06[0].Key3 = "0";

ds.UD06[0].ShortChar06 = orderLineCutType;

ds.UD06[0].ShortChar07 = orderLineRecutter;

ds.UD06[0].ShortChar08 = orderLineTrim;

ds.UD06[0].ShortChar09 = orderLineStickLength;

ds.UD06[0].Number08 = orderLineTrimDec;

ds.UD06[0].Number09 = orderLineStickLengthDec;

ds.UD06[0].Number10 = orderLineKnifeQty;

ds.UD06[0].Number12 = orderLineSticksM;

ds.UD06[0].Number14 = orderLineCut;

UD06svc.Update(ref ds);

}

}

}

}
1 Like

Hi Alexandre,

My BPM is a Post-Process Method Directive on SalesOrder.MasterUpdate. I have a pre-process that Enables the Post Process if they check a box on the Sales Order Header, then it inserts one row per Sales Order Line into UD06

I had to add a Reference to Ice.Contracts.BO.UD06 see screen shot. There is a loop in the code below that I’m not using ‘not using this…’ but I left it.

The entirety of the code is below:

// 2020-09-22 remove N15 StickNum, that field is not populated
// RickWelling 2020-08-12 - Bring in TrimDec and StickLengthDec from OD in case I need to write it back
// RickWelling 2020-07-30 - Adapted from the UD04 Program, this builds UD06 if the RecutSched_c flag is true when the order is released to production
using(var UD06svc = Ice.Assemblies.ServiceRenderer.GetService(Db))
foreach (var CurrentOrder in ttOrderHed) // Identify the Order that we have in ttOrderHed, should only ever be one OrderHed
{
bool addMetaRow = true; /Initialize our variables/
string orderNumChar = CurrentOrder.OrderNum.ToString();
string NotUsingThis = “NotUsingThis”; //Carry over from UD04 build, not relevent here

// Now find all the lines on the order that we are working with, that are Flagged RecutSched_c = true and put them into a list (ToList)
var CurrentOrderLinesList= (from row in Db.OrderDtl where row.Company == callContextClient.CurrentCompany && row.OrderNum == CurrentOrder.OrderNum && row.RecutSched_c select row).ToList();

// loop through each line in the order (we already have the list from before)
foreach (var CurrentOrderLine in CurrentOrderLinesList) {
bool addDtlRow = true;
string orderLineChar = CurrentOrderLine.OrderLine.ToString();
string orderLineCutType = CurrentOrderLine.ShortChar06;
string orderLineRecutter = CurrentOrderLine.ShortChar07;
string orderLineTrim = CurrentOrderLine.ShortChar08;
string orderLineStickLength = CurrentOrderLine.ShortChar09;
int orderNumInt = CurrentOrderLine.OrderNum;
int orderLineInt = CurrentOrderLine.OrderLine;
decimal orderLineKnifeQty = CurrentOrderLine.Number10;
decimal orderLineSticksM = CurrentOrderLine.Number12;
decimal orderLineCut = CurrentOrderLine.Number14;
decimal orderLineTrimDec = CurrentOrderLine.TrimDec_c;
decimal orderLineStickLengthDec = CurrentOrderLine.StickLengthDec_c;

// create a database scope - this will tell the subsequent Db.Validate and txScope.Complete what table it is updating
using (System.Transactions.TransactionScope ourScope = IceDataContext.CreateDefaultTransactionScope()) // part of Synch existing row logic below
{
// look to see if if the Row exists in UD06 for the Current Order Line, and loop through - 1 row or none
foreach(var UD06ExistingRow in (from UD06 in Db.UD06 where UD06.Company == callContextClient.CurrentCompany && UD06.Key1 == orderNumChar && UD06.Key2 == orderLineChar && UD06.Key3 == “0” select UD06))
{
// We found it, so we don’t want to add it, but we want to make sure the SC01 is up to date
addDtlRow = false;
//START - Synch the data if already present:
var UD06ExistingRowForUpdate = UD06ExistingRow;
if (UD06ExistingRowForUpdate.ShortChar01 != orderLineRecutter) {
UD06ExistingRowForUpdate.ShortChar01 = orderLineRecutter;
Db.Validate();
ourScope.Complete();
}
// END -Synch the data if already present
}
}
// if we didn’t find a UD06 row, add one
if (addDtlRow)
{
if (NotUsingThis == “NotUsingThis”) {
UD06Tableset ds = new UD06Tableset();
UD06svc.GetaNewUD06(ref ds);
ds.UD06[0].Key1 = orderNumChar;
ds.UD06[0].Key2 = orderLineChar;
ds.UD06[0].Key3 = “0”;
ds.UD06[0].ShortChar06 = orderLineCutType;
ds.UD06[0].ShortChar07 = orderLineRecutter;
ds.UD06[0].ShortChar08 = orderLineTrim;
ds.UD06[0].ShortChar09 = orderLineStickLength;
ds.UD06[0].Number08 = orderLineTrimDec;
ds.UD06[0].Number09 = orderLineStickLengthDec;
ds.UD06[0].Number10 = orderLineKnifeQty;
ds.UD06[0].Number12 = orderLineSticksM;
ds.UD06[0].Number14 = orderLineCut;
UD06svc.Update(ref ds);
}
}
}

}

Hi @RickW,

I’m sorry for the late answer…

But thank you! It works. I recreate my DD BPM into Method Directive and now, it creates all my row without any issue.

Best regards,
Alexandre

I’m Glad!!! Have a great day.