BAQ - filter dynamic

Epicor ERP 10
1

Hello,
I have a jobmtl baq. i need do filter in the partnum in the baq.but the filter is dynamic.
it looks like select * from jobmtl where partnum = ‘009-001’ or partnum = ‘008-002’, etc. the partnum is based on what user select. i did not find such way in the baq. so, what i did is call baq and then filter in the dataview.rowfilter. it works but it is slow.
is it any way to improve that.

thanks,

Eddy

2

On the menu in the baq designer, define parameters.

Use those like@parametername

3

thanks,
in the baq, how i can put the filter. the filter option only contains “start with, equal, etc” . what I need is part IN(‘xxxxx’,‘yyyy’) how we can manipulate OR in the filter in the baq and it must be dynamic.
input parameter is no issue. I did not find keyword IN in the baq.

regards,

Eddy

4

Could be as simple as just restructure your query to not need the “in” keyword.
Might need a subquery.

Are there only two parameters needed, is it always “or” ?

Can you describe in more detail exactly what you are trying to accomplish?

5

I’m on 2022.2.8, I have the “IN” keyword in my operations list.
I assume it was there in 10.2.600.

6

yes, the epicor version is 10.2.600

7

I finally figured out what you are trying to do.

In the BAQ define go to Actions, Define Parameters.
Select New, Type your parameter name, and choose Editor Type–>Item List.

Save.

In the criteria section at the bottom on the query you want, add a criteria for partnum.
Then select Operation IN, and “specified list parameter”, click the blue “specified list”
section, and choose your parameter.

Save your BAQ and run it. It will give you a prompt to add your criteria to the list.

p1
p11029×632 62.4 KB

p2
p2956×244 58.7 KB

p3

p4

8

thanks so much.

i will try that

9

by the way,
in the code behind, how i can pass the value to the paremeter if it is defined as list.

thanks,

Eddy

10

How are you calling it? In a dashboard with refresh, or custom code and the dynamic query adapter?

11

My list parameter is called “Params” (“@Params”)

You add rows to the Query Execution DataSet or TableSet, with the
ParameterID of “Params”, and the ParameterValue(s) with your values.

Smart Client:

//Client Code

DynamicQueryAdapter dQ = new DynamicQueryAdapter(UD01Form);

dQ.BOConnect();

QueryExecutionDataSet qeDS = dQ.GetQueryExecutionParametersByID("Kev_t1");
qeDS.ExecutionParameter.Clear();
qeDS.ExecutionParameter.AddExecutionParameterRow("Params", "D" , "nvarchar", false, Guid.Empty,"A");
qeDS.ExecutionParameter.AddExecutionParameterRow("Params", "E" , "nvarchar", false, Guid.Empty,"A");


dQ.ExecuteByID("Kev_t1", qeDS);

string retString = JsonConvert.SerializeObject(dQ.QueryResults.Tables["Results"], Formatting.Indented);

//Clipboard.SetText(retString);
MessageBox.Show(retString);

dQ.Dispose();

//Returns
[
  {
    "ABCCode_ABCCode": "E",
    "RowMod": null,
    "RowIdent": "d8bda19f-6caf-459f-9b8e-d1193e104d08",
    "SysRowID": "d8bda19f-6caf-459f-9b8e-d1193e104d08"
  },
  {
    "ABCCode_ABCCode": "D",
    "RowMod": null,
    "RowIdent": "cb73d97e-4617-4e1c-87a9-731a89ec9ea8",
    "SysRowID": "cb73d97e-4617-4e1c-87a9-731a89ec9ea8"
  }
]

Calling from BPM:

//BPM Code
string retString = "";

QueryExecutionTableset qeTS = dq.GetQueryExecutionParametersByID("Kev_t1");

qeTS.ExecutionParameter.Add(new ExecutionParameterRow {
    ParameterID = "Params",
    ParameterValue = "D"
});
  
qeTS.ExecutionParameter.Add(new ExecutionParameterRow {
  ParameterID = "Params",
  ParameterValue = "E"
});
  
DataSet retDS = dq.ExecuteByID("Kev_t1", qeTS);
    
retString += JsonConvert.SerializeObject(retDS, Formatting.Indented);
    
InfoMessage.Publish(retString);
    
dq.Dispose();

{
  "Results": [
    {
      "ABCCode_ABCCode": "E",
      "RowMod": null,
      "RowIdent": "0a009fac-0144-4a7a-ab7e-ad4c1ba2c787",
      "SysRowID": "0a009fac-0144-4a7a-ab7e-ad4c1ba2c787"
    },
    {
      "ABCCode_ABCCode": "D",
      "RowMod": null,
      "RowIdent": "abf8b301-6fe7-46ac-9d86-cc53a13312da",
      "SysRowID": "abf8b301-6fe7-46ac-9d86-cc53a13312da"
    }
  ],
  "Errors": [],
  "ExecutionInfo": [
    {
      "Name": "ExecutionTime",
      "Value": "4.4276"
    }
  ]
}
1 Like
12

thanks so so much.

13

No problem, I love a challenge.

If your problem is solved, remember to check the little box that says “Solution”.