My team and I are working through our E9-10.2 conversion. We’re nearly finished with our BPM Method Directives. However, we’ve run into an “immovable object” of sorts. We need to have calculations of a table’s fields done on the fly in this query builder. As an example, we’ll need to find the difference between two decimal fields in JobProd and compare them to a ttPartTran field.
Easy, right?
Wrong–at least for us so far. We’ve figured out that we can utilize BPM Workflow Designer’s “Variable” objects in a “Set Argument/Variable” object, where we can utilize the Method Directive’s associated tables and fields to fill the Variable with a calculated value. We can then reference this Variable in the Query Builder.
However, we cannot access an unrelated table’s fields in these “Set Argument/Variable” objects.
You can set the value of an argument/variable to any C# expression, which means you can access other tables within it. As an example, where “custID” in this expression is a directive variable (but could be anything else):
(int?)(from row in Db.Customer where row.Company == callContextClient.CurrentCompany && row.CustID == custID select row.CustNum).FirstOrDefault() ?? 0
This, in one of our BPMs, is used to set a custNum variable for use elsewhere.
That might actually be the ticket to what I need–I just want to verify that this will work as I expect it to.
I’m going to set a variable that I’ll call “TestDecimal” with the following code (modified from yours!):
(decimal?)(from row in Db.JobProd where row.Company == ttPartTranRow.Company && row.JobNum == ttPartTranRow.JobNum select row.ProdQty).FirstOrDefault() ?? 0
(This substitutes the part of the ABL code: JobProd.ProdQty)
So long as the conditions (row.Company == ttPartTranRow.Company, row.JobNum == ttPartTranRow.JobNum) are reflected in the “Compose Query” builder as well, can I expect the comparison to the “TestDecimal” variable to work as designed in the query builder?
Yes, that’s what we do. Obviously a single variable only works for something that’s common to all rows if there are multiple rows you’re looking at.
By the way, on an unrelated topic, you can expect someone to pick you up on joining a tt table to a database one in a query. It’s horribly inefficient and can slow a BPM down massively. If you need to query Db tables then using tt values or variables in criteria instead of joining is the way to go if you can.
That’s fantastic. Thank you for your help and reminding me that I can use any sort of C# query there (and it’ll flow as expected with a matching query from the query builder)!
That’s a bit upsetting to hear that the join operation between a temp table and a DB one will slow it down considerably… but I appreciate you letting me know. I will be looking into that next. Thanks again!
