Trying to Copy data from OrderDtl.QuoteNum to UD field OrderHed.QuoteNum_c and show on Order Entry Screen on load

,

Hello,

Been working on this for a bit with some help but I am stuck.

I have been asked to create a UD Field in the OrderHed table to hold the QuoteNum if the order was generated from a quote.

I see that the OrderDtl holds the QuoteNum if this happens.

They want to be able to modify this field if needed, otherwise I would just put the field on the Order Entry Summary screen and be done with it.
I created the UD Field OrderHed.QuoteNum_c (string) and tried to write custom code to copy the field from OrderDtl.QuoteNum to OrderHed.QuoteNum_c.

I put in the method directive: Erp.BO.SalesOrder.GetByID. Is this the best place to have this?

I have tried it both as pre-processing and post-processing.

It will copy the data and show in the database but it does not show on the screen without a manual refresh after initial load.

I need help geting it to show the data copied on initial load on the oOrder Entry Summary screen.

Any ideas? My code is below (Thanks to Jason for getting me this far).

foreach (var H in Db.OrderHed.Where(H => H.Company == CompanyID && H.OrderNum == orderNum && H.QuoteNum_c == "")) { H.QuoteNum_c = Db.OrderDtl.Where(D => D.Company == CompanyID && D.OrderNum == orderNum && D.QuoteNum != 0).Select(D => D.QuoteNum).DefaultIfEmpty(0).First().ToString(); }

Any help is truly appreciated.

Why not just use a DD to populate the field on Order Creation. Tie it to OrderDtl.Update and you can do most of it with widgets. You will need something similar to this to update the OrderHed table.
This snippet is syncing the SalesRepList on creation.

            // Sync Sales Reps code block
            string sCompany=Session.CompanyID;
            string SalesRepList="";
            string msg = "";
            var ttOrderDtlRow = Epicor.Customization.Bpm.EnumerableExtensions.GetSingleRow(ttOrderDtl, "ttOrderDtl");

            this.iOrderNum = (ttOrderDtlRow.OrderNum);

            var RepRows = (from od in Db.OrderDtl.With(LockHint.NoLock)
              join sr in Db.QSalesRP.With(LockHint.NoLock)
                on new { od.Company , od.QuoteNum } equals new { sr.Company , sr.QuoteNum }
                  into qsr
              from sr in qsr.DefaultIfEmpty()
              where od.Company == sCompany
                && od.OrderNum == this.iOrderNum
              orderby sr.PrimeRep descending
                select new { sr.SalesRepCode } )
              .ToList();
   
            if (RepRows.Count > 0) 
              {
              foreach (var repCode in RepRows) 
                {
                SalesRepList+=repCode.SalesRepCode +"~";
                if (sCompany=="EZ") // Added to only place Primary Sales Rep Code for Company 'EZ'
                  {
                  break;
                  }
                }
              SalesRepList=(SalesRepList.Length<2)?"":SalesRepList.Substring(0,SalesRepList.Length-1);
              this.sSalesRepList = SalesRepList;  
              using (var txScope = IceContext.CreateDefaultTransactionScope())
                {
                foreach(var hoh in (from row 
                  in Db.OrderHed.With(LockHint.UpdLock) 
                    where row.Company == Session.CompanyID && row.OrderNum == this.iOrderNum
                    select row))
                      {
                      if (hoh.SalesRepList != this.sSalesRepList) 
                        {
                        hoh.SalesRepList=this.sSalesRepList ;
                        //Debug Message
                        msg = "Company: "+Session.CompanyID+"\n"+"Syncing SalesRepList with Quote. \nFrom: "+hoh.SalesRepList+"\nTo: "+SalesRepList+"\n"+"QuoteNum: "+this.iQuoteNum+"\n"+"OrderNum: "+this.iOrderNum+"\n";
                        }
                      }
                Db.Validate();
                txScope.Complete();
                }
              }
            this.sMsg=msg;

1 Like

@CSmith

I need the UD Field because they want the ability to modify the text.

I thought DD would only run on updates. Will they work on the first screen load?

Yes

I edited my post above and provided snipped from DD as that is a simpler way to manage this BPM on the Orders table since the other way would require creating 2 separate Method Directives and is better option IMO instead of using a form customization as you wanting this to happen when the Order gets created from a Quote.

1 Like

Technically would work BEFORE the first screen load as the Order record is created.

So GetByID should be generating a TableSet on the server with all the records from the Order(OrderHed/OrderDtl/etc) and then sending this back to the UI.

You should have access to this TableSet in the PostProcessing BPM, you need to get the OrderHed from this tableset and populate the QuoteNum_c field.

Right now you are just updating the database, but it will not be returned to the client so you cannot see it, this is why it shows in the next refresh because this time the TableSet gets the value from the DB.

2 Likes

What would the tableset be called?

I tried this and it worked, I don’t usually work with bpms so I thought it would be named salesOrderTableSet, turns out it is resultHolder, who would have thought.
Replace the fields I used with what you need, I just used random ones.

var orderLine = this.resultHolder.Original.OrderDtl.FirstOrDefault();
var orderHed = this.resultHolder.Original.OrderHed.FirstOrDefault();
if(orderLine != null && orderHed != null)
{
  orderHed.CCStreetAddr = orderLine.PartNum;
}

This will only update what is returned to show in the UI, you can also add a condition to do nothing if your UD field already comes populated from the DB.

1 Like

@Jonathan Curious, would this methodology have less impact than a DD?

I think the most efficient way would be to do it just after an order is created from a quote, problem is I believe there are 2 o 3 different ways to do this, so it would be multiple bpms to cover all scenarios.

I think the overall impact for any approach would be negligible considering it would be at max only a couple of queries, with a DD on OrderDtl you would always need to query OrderHed, and in the case of the GetByID you already have the records available to check and only run additional queries if necessary.

But it also depends on what you are trying to achieve, in this case if you want to see the data lets say in a dashboard, you would need to load every order in Sales Order in order for the bpm to sync the data, this is very inefficient, in this case the DD would be a way better solution.

1 Like

Because of the multiple ways to do this I decided on a DD, but I could see the GetByID working as well but it would seem to trigger every time a record gets accessed versus just on added row(s). Just thought this might have less impact, but I see how it would get triggered every time a Sales Order is accessed pretty much same as DD then with this methodology.

@Jonathan
Hello,

I pasted your code into my method directive 'Erp.BO.SalesOrder.GetByID / Post-Processing and tried to change the orderHed.CCStreetAddr to my UD Field ‘OrderHed.QuoteNum_c’ but it did not recognize it.

Any ideas on how to reference a UD Field from there?

I am not familiar with the technical side of UD fields, for the most part they are always there.

I mean if you can use the QuoteNum_c in a control EpiBinding and you can see the value from the DB then it must be returned to the UI somehow, which means it should be part of the returned TableSet from GetByID.

Try OrderHed[“QuoteNum_c”] and see if that works.

I did what you said and it is adding the quotenum to the UD field on the screen but it is not putting that QuoteNum in the UD Field in the database at that time.

If I make a change to something on the screen and then save it, it does show up in the database at that time.

I am running a query against the database each step to see when it saves to the database.

Is there a way to have it save the QuoteNum to the UD field on load so that if they open the Order it will already be saved to the databse also?

A scenario I see is if they open the order and the UD field is not saved to the database, then when they print a sales order acknowledgement, it will not show my UD field on there.

BTW, I already added the UD field to the SO ack report and it works fine.

Let me know if I am not making sense please.

Ah I see, the code that I pasted was only to have the UD field on the screen, nothing else.
You already had the DB part working so I didn’t add it, you need to put what you already had which saved the UD field to the DB and also what I pasted to show it on the UI the first time(without refresh).

Looks like th code I have causes an error ‘Row has been modified by another user and couldn’t be updated.’

I have to figure out why that is happening, then I can implement your code on top of that.

Any ideas? (Code below)

foreach (var H in Db.OrderHed.Where(H => H.Company == CompanyID && H.OrderNum == orderNum && H.QuoteNum_c == “”))
{
H.QuoteNum_c = Db.OrderDtl.Where(D => D.Company == CompanyID && D.OrderNum == orderNum && D.QuoteNum != 0).Select(D => D.QuoteNum).DefaultIfEmpty(0).First().ToString();
}

I don’t know if the entire bpm is executed inside a transaction scope, so this might not work, but after you update OrderHed you need to run Db.Validate(H), then you need to take the new SysRevID from H and assign it to OrderHed as in my previous code.

The reason is that the record that is in the GetByID result is not the same because you modified it, so the SysRevID in the DB is now different than what is on the UI, any attempt to modify this record will result in this error.

So we need to sync the new SysRevID so the UI knows it has the latest and can do changes.

This is where I think you want to trigger off added rows in a DD like I suggested above. It will show on the screen and be updated when the new screen loads. Did not test fully but pretty sure you can use these widgets and then use the following code snippet.

// Set QuoteNum_c field
            var ttOrderDtlRow = Epicor.Customization.Bpm.EnumerableExtensions.GetSingleRow(ttOrderDtl, "ttOrderDtl");

            this.iOrderNum = (ttOrderDtlRow.OrderNum);
            this.iQuoteNum = (ttOrderDtlRow.QuoteNum);

              using (var txScope = IceContext.CreateDefaultTransactionScope())
                {
                foreach(var oh in Db.OrderHed.With(LockHint.UpdLock).Where(oh => oh.Company == Session.CompanyID && oh.OrderNum == this.iOrderNum))
                      { oh.QuoteNum_c = this.iQuoteNum.ToString(); }
                Db.Validate();
                txScope.Complete();
                }

@CSmith
This looks great except I am unsure on how your first condition checks how a UD field in the OrderHed table has a value?