BPM widget to run a query and store the result for further use

I have looked at all the widgets and can’t figure out how to run a query and store the result in a BPM variable that I have defined.

“Fill Table by Query” seems the closest, but I couldn’t make it work. I defined a variable “MyTable” and gave it the type DataSet, but it didn’t show up as a table I could use. It also isn’t clear to me how I could set a single variable value with this; presumably I would have to set a table with one element and then extract it in C#.

Set Argument / Variable has the opposite problem - I can find my BPM variable, but I only have the option to assign an expression, not a query. I do not want to use LINQ (too much work trying to translate my working reference SQL to LINQ, not as much to a query). But even if that were an option, I only see temp tables available and I need to access several tables not listed.

Set BPM Data Field doesn’t let me set my variable.

Set By Query wants to set a field in the query, which is not what I want. I just want to get values, not set anything.

Set Field wants to set a field in one of the temp tables, which is also not what I want.

Update Table by Query doesn’t list my variable.

I don’t see any other widgets that seem relevant.

I am definitely no expert, so I may be wrong.

You need to declare a variable like you did and make it either a dataset or tableset. Then you would use C# to fill it. Once you fill it, you will be able to access it in the other widgets.

Hopefully you can use one of the existing methods to fill your dataset instead of writing a LINQ query.

It sounds like there isn’t a way to fill it outside of C# then? Not sure what you mean by “use one of the existing methods”. Maybe create a BAQ and call it from on of the widgets somehow?

Not that I am aware of.

Depending on what data you are returning, there might already be a method that Epicor created to get it. If you have a BAQ, you could use the DynamicQueryAdapter.

I use the fill table by query widget to save/use query results in a bpm. Try using a tableset variable instead of dataset.

The problem I have with TableSet is that I can never figure out how to type it. I’ve got a query with four joins, that doesn’t map to the type of tableset it offers me or an assembly. I think somebody showed me once how to do this, but it was non-intuitive (the names were totally unrelated to what I was doing) and I don’t recall how it was done.

I see, finding a tableset that would intuitively link the bindings could definitely be a challenge. What I’ve done in the past is just create a tableset for one of the UD tables, then manually link the bindings in the fill table by query widget. Not too bad with a few fields.

Hey Michael,

This is from an Epicor Function but it’s identical to a BPM. It shows how to run the BAQ using widgets along with setting parameters. Once you have the result set, you can grab whatever fields you need from there.

2 Likes

Thanks, I’ll take a look at that.