Populate variable with query results

This is similar but not exactly the same as a problem I had earlier. At least I can’t figure out how to apply that solution to this problem.

I need to be able to evaluate the Ship To territory of an order so that I can use a BPM to set the correct value in the SalesRepCode1 field. The default behavior of Epicor is that the SalesRepCode1 field is populated based on the territory of the customer. We need it populated based on the territory of the Ship To and the classID of the part.

For example,

We have territories DEF, PQR and XYZ. These are geographic.
We have two classIDs, A and B.
We do not mix part classes on orders – an order will contain A parts or B parts, but not both.

I have the piece of the BPM that identifies the ClassID. That is done using a “number of rows in the specified query is not less than 1” condition. So I know that I am dealing with an “A” order or with a “B” order.

I need someplace to store the SalesTerritoryID so that I can parse it and populate the correct SalesRepCode1.

The logic would be something like this: if the classID is A, then populate SalesRepCode1 with the value that corresponds to the first character of the territoryID (e.g., D, P or X), else (because classID is B), populate SalesRepCode1 with the value the corresponds to the second character of the territoryID (e.g., E, Q or Y).

Once I can hold the value in a variable I can do all the parsing and comparing. I am struggling with how to populate the variable itself.

And… no custom programming allowed. Strictly BPM boxes in the designer…


Hmm…I get that you don’t want custom programming, but I think this would be way easier if you did allow at least the setting of a variable by a c# expression using the Set Argument/Variable widget.

and I haven’t been fully caffeinated this morning yet either, so bear with me, but I also assume you know how to create local variables within the BPM? That would give you a place to store your data and then parse out to set your field

I, too, am not yet fully caffeinated. Let me look at the link you sent and see if my little brain can handle it.

If you are willing to help me on this little adventure, I would appreciate it.

I have created a variable ShipToTerr.
I attempted to use the SetArgument/Variable as follows:

Set the ShipToTerr argument/variable to the specified expression.

My expression (based on your example) is this:

(char?)(from row in Db.ShipTo where row.Company == callContextClient.CurrentCompany && row.CustNum == custNum && row.ShipToNum == ShipToNum select row.TerritoryID).FirstOrDefault() ?? 0

When I check syntax, I get two error messages, both about the name not being in the current context. The two names are CustNum and ShipToNum.

Both those fields are in ttOrderHed.

So maybe I don’t understand current context?

Any ideas?

You should be able to do this with a widget if I am understanding correctly

Got that far. The issue was when I pasted the C# code block into the editor and then checked syntax, I got the errors. So there something wrong the code, but I don’t know what.


Is that the code you are using? If so, you cant do that. That would be suitable if you were doing this in a custom code block.

If you need to look something up from the Db based on some info you have, you are going to need to go a different route. This might include saving the info you use to look up in variables, then calling an adapter. Another option, depending on version is to use a query to help find the relevant info.

1 Like

OK, I may be out of my depth here – I am not a programmer (nor do I play one on TV…)

If I can use a query to populate the variable, that would be ideal. I had not gone down the Update Table by Query route because I don’t want to update a table; I wanted to populate a variable. Unless the variable table (is there such a thing?) is what I would be updating?

You said the end goal was to set the Sales Rep. That’s the thing in the table you need to update (based on the logic you described)


You’ll need to make sure a Workforce entry exits for each SalesRep code your BPM can derive.

Ah. OK, so maybe I need to dump all the logic in to that one calc.

I was going in small steps:

  1. Identify ClassID
  2. Identify SalesTerrID
  3. Parse SalesTerrID and then, based on ClassID, use substring of SalesTerrID to populate RepCode (probably hard-coding if… then statements for now; ultimately would like something more table-based).

Yep. Our workforce records are fine. Not really worried about that part.

The small steps are fine (actually, they’re great when starting out and trouble shooting)

If you want to avoid code altogether, you can do it with a bunch of condition widgets.

Here’s a partial example.


Lots of steps not shown. I was just trying to illustrate the use of Condition widgets

One more question… Why are you using the part’s ClassID? You should use ProductGroup.

PartClass is more for the inventory side of things, while Product Group is for the sales end.

Plus you can get the PrdGrp right from the OrderDtl, without having to derive it from the Part table based on the line’s partnum.

1 Like

Thanks so much for the example. This is sort of the small step path I was headed down until I couldn’t easily figure out what the Territory was (which started this whole chain).

RE. ClassID vs. Prod Group: We have a ton of product groups and there is no easy way to map them back to the Sales Rep unless I add a field to the Prod Group table (something we have not yet done). So ClassID it is for now. ClassID maps very nicely to the SalesReps.


Clarification - you CAN use this code pattern in a “set argument/variable” widget or a “set field” widget. At least we can on-premise. As long as it’s a single C# expression everything is fine.

@skhayatt As long as it isn’t blocked on cloud versions, the following should work (although I note you’re now using a different approach):

(string)(from row in Db.ShipTo where row.Company == callContextClient.CurrentCompany && row.CustNum == custNum && row.ShipToNum == ShipToNum select row.TerritoryID).FirstOrDefault() ?? string.Empty

This code is expecting a string to be returned, so a char variable won’t work. Next, strings are nullable so adding a ? to the variable type won’t compile (that signals the nullable version of a data type, which strings don’t have). Finally, the part after ?? is the fallback assignment if the first part doesn’t return anything, so it needs to be of the same data type, and your zero is an integer instead of a string.

1 Like

Thanks for the explanation – I even understand it. I am still getting both “does not exist in current context” errors when I check syntax. CustNum and ShipToNum are the offenders. This is a post-processing on SalesOrder.MasterUpdate.

We are on-premise as well. And got pulled off on something else yesterday so am now back on this. I want to try and figure it out because I think it could be useful in general. What is the “current context?” I would have thought the business object (so, Sales Order), but maybe not?

I am going to try to hunt down that phrase in the various BPM docs, but if you have an easy explanation I will happily take it.

That will be because those are variables. If they aren’t already existing in this context or you haven’t created and set them, then they won’t be there for this expression to use.

I’m out of the office now so can’t look closer for you, I’m afraid. But down the left of the widget you can see what things ARE there for you to use - that might get you started. Your CurrentCompany variable is one of them.

That’s helpful. Thanks! Didn’t know I had to define them; I thought they already existed. Onward…

1 Like

I have been doing the same thing on some BPMs… disclaimer is that you dont want to set multiple variables this way because it does a separate query for each variable… but for only one, it is fine…
Also, I have been personally rewriting all my queries into Lamda expressions, because I find them easier to read, and many times, shorter expressions… here is how I would have written the same expression (but there are many ways to do this :wink:

Db.ShipTo.Where(R=>R.Company == callContextClient.CurrentCompany && R.CustNum == custNum && R.ShipToNum == ShipToNum).Select(R=>R.TerritoryID).FirstOrDefault() ?? ""
1 Like

Oh, 100% agree it has a fairly exact use case. A previous post of mine was brought into this discussion, so I’m simply hoping to avoid misunderstandings in putting it together. Lambda is nice (and compact) too, just I’ve found this form easier to explain within
our team.

1 Like