Compare old row value in Data Directive FOREACH

Kinetic ERP
1

Is there a way within a FOREACH section of a Standard Data Directive BPM to reference the old row value for compare?

In this case, the BPM is for RcvDtl (Receipt lines), and the goal is to only select lines where either the old received qty differs from the new received qty OR the old Received flag status differs from the new Received flag status.

I don’t see how to 1) reference the OLD values, and 2) use the OLD values for comparison (e.g., as part of a WHERE). I tried JOINing to the Db.RcvDtl table but that never resulted in detecting a change, so it seemed the Db entries were already updated with the new values. Should I instead JOIN again to ttRcvDtl but require RowMod to be ‘U’ on the main table and RowMod to be ‘’ on the second table?

The code block is set to Synchronous, if that matters.

foreach (var ttRcvDtl_iterator in 
          (from ttRcvDtl_Row in ttRcvDtl
            join POH in Db.POHeader on
                  new {ttRcvDtl_Row.Company, ttRcvDtl_Row.PONum} equals
                  new {POH.Company, POH.PONum}
            join Ven in Db.Vendor on
                  new {POH.Company, POH.VendorNum} equals
                  new {Ven.Company, Ven.VendorNum}
            join RcvH in Db.RcvHead on
                  new {ttRcvDtl_Row.Company, ttRcvDtl_Row.VendorNum, ttRcvDtl_Row.PurPoint, ttRcvDtl_Row.PackSlip} equals
                  new {RcvH.Company, RcvH.VendorNum, RcvH.PurPoint, RcvH.PackSlip}
//            join RcvD in Db.RcvDtl on
//                  new {ttRcvDtl_Row.Company, ttRcvDtl_Row.VendorNum, ttRcvDtl_Row.PurPoint, ttRcvDtl_Row.PackSlip, ttRcvDtl_Row.PackLine} equals
//                  new {RcvD.Company, RcvD.VendorNum, RcvD.PurPoint, RcvD.PackSlip, RcvD.PackLine}
//            where (string.Equals(ttRcvDtl_Row.RowMod, IceRow.ROWSTATE_UPDATED, StringComparison.OrdinalIgnoreCase) || 
//                   string.Equals(ttRcvDtl_Row.RowMod, IceRow.ROWSTATE_ADDED, StringComparison.OrdinalIgnoreCase) ||
//                   string.Equals(ttRcvDtl_Row.RowMod, IceRow.ROWSTATE_DELETED, StringComparison.OrdinalIgnoreCase)) 
//                   &&
//                   (System.Convert.ToInt32(ttRcvDtl_Row.OurQty) != System.Convert.ToInt32(RcvD.OurQty) ||
//                    ttRcvDtl_Row.Received != RcvD.Received)
                  
            select new {ttRcvDtl_Row.PackSlip,
                        ttRcvDtl_Row.PackLine,
                        ttRcvDtl_Row.PONum,
                        ttRcvDtl_Row.POLine,
                        ttRcvDtl_Row.PORelNum,
                        ttRcvDtl_Row.PartNum,
                        ttRcvDtl_Row.PartDescription,
                        ttRcvDtl_Row.OurQty,
                        ttRcvDtl_Row.Received,
                        ttRcvDtl_Row.RowMod,
                        Ven.Name,
                        Ven.VendorID,
                        RcvH.EntryPerson,
                        POH.BuyerID}))
2

That’s correct, the standard data directive fires after the DB transaction is completed and the database row is updated.

The other thing is that a standard DD only looks at one record, so using foreach isn’t necessarily required. For updates, you’ll only have an updated ttRcvDtl_Row.RowMod == "U" / ttRcvDtl_Row.Updated() and unchanged ttRcvDtl_Row.RowMod == "" / ttRcvDtl_Row.Unchanged()

So to compare the old and new values for OurQty and Received, you could do something like this:

var oldRcvDtl = ttRcvDtl.Where( x => x.Unchanged() ).Select( s => new {s.Received, s.OurQty, s.SysRowId} ).FirstOrDefault();

var newRcvDtl = ttRcvDtl.Where( x => x.Updated() ).Select( s => new {s.Received, s.OurQty, s.SysRowId} ).FirstOrDefault();

if ( oldRcvDtl == null ) return; // Null handling if no unchanged row
if ( newRcvDtl == null ) return; // Null handling if no updated row

if ( oldRcvDtl.SysRowId != newRcvDtl.SysRowId ) return; // This makes sure you're looking at the same row of data. Should never hit as Standard Data Directives won't have multiple rows in the temp table.

bool ReceivedFlagChanged = oldRcvDtl.Received != newRcvDtl.Received;

bool ReceivedQtyChanged = oldRcvDtl.OurQty!= newRcvDtl.OurQty;
3

a standard DD only looks at one record, so using foreach isn’t necessarily required

Hmm, I am using the output to send email notifications of the received lines, and I have had one instance where one email contained multiple received lines on the same packslip. So, the dataset had more than one row…or was it just a fluke that it two updates occurred so closely together that the foreach caught them together?

I’ll try your method. Thanks!

But

4

I wouldn’t think that the two updates happening close together would be included on the same email, so I guess I might be wrong on that. I always thought you had to use In-Transaction if you have multiple row updates that you want grouped together.

5

Can someone confirm whether or not a Standard Data Directive can have multiple row updates?

6
2 Likes