Number of Rows in the <x> Query... not working correctly

Epicor ERP 10
1

RelatedOrderRelease_Demo.bpm (41.4 KB)
I have simplified an example of an issue I am seeing with a BPM. I am using the Number of Rows in the < x > query… condition to determine if a Sales Order Line has more than one release. Here is what is happening:

I have setup a Pre-Processing BPM on the Erp.SalesOrder.MasterUpdate Method…

image
image.png761×575 23.3 KB

The design looks like this:
image

At Insights this year, I learned NOT to create joins between ttTables and ErpTables for performance reasons. Instead, the best practice is to put the ttTable on grid so that you can access the values on the ttTable for Table Critera on the Erp Table like I am doing here…

image
image.png809×648 29.7 KB

As you can see, am using the criteria from the ttOrderDtl table to find the related Erp.OrderRel records.

I have a message for the TRUE result…

image
image.png538×615 27.5 KB

And a message for the FALSE result…

image
image.png537×618 24.6 KB

So, to review: the TRUE condition means that there is one release, and the FALSE condition means there is more than one release.

I create a sales order with one line and one release…

image
image.png864×542 18.6 KB

When I make any change to the line, the BPM is triggered and the FALSE result message appears, indicating that there is more than one release.

image
image.png831×663 22.5 KB

There is only one release, so the TRUE result message should be shown.

I have attached a copy of the BPM if you want to test this yourself.

Thanks!

2

well, you might be counting rows that are both modified and unmodified since you are doing this as a pre-process… In order to filter that down, you need to check RowMod (Try addint a criteria ttOrderDtl.RowMod <> “”). Also you might want to display the rowmod (temporarily) in your popup message.

Also, the join between the tt record and regular database (see other discussion) problem… in order to elimnate the problem, you have to completely REMOVE the TTRecord from the query… this would not work for this particular query since the tt records COULD have multiple lines that changed. I am still working out the recommendation for when there are multiple modified records.

2 Likes
3

One other warning… when you first create a sales order detail record, there is no release. It gets created by a background process. so the first time you save, nothing will be there (zero records), BUT it will be there after you save.

4

Tim,

After a few distractions, I finally got back to this.

You are correct, sir, it was the unmodified row that was causing the issue. As soon as I put the constraint on the row mod value on the ttOrderDtl table, the query works as expected.

image
image.png720×566 14.8 KB

This is not intuitive as the two tables are not connected in the diagram and the “target” table, ERP.OrderRel, does not have a queryable RowMod value. I suppose that using the ttOrderDtl criteria to limit the tt rows is somehow affecting the row count produced by the table criteria.

In any case, I now have my query working. Thanks so much for you input on this!

Michael

5

Tim,

Thanks for this as well. I did run into other issues regarding the first commit of the Order Line due to the pending release, but I was able to work around it.

Michael

6

in this case, what you should do is:

  1. make the ttOrderDtl table the number 1 table (instead of #2)
  2. filter the ttorderDtl table and only return the rows that are modified or added
  3. LINK the ttOrderDtl to OrderRel (See other discussions on linking tt to non-tt tables… this is a slight problem but it will probably work fine in your case).