Erp-baq send list parameter to a baq

[Chan213419 (Jeremy Chance)]

Good morning!

I have used this strategy, more or less, to pass a parameter to a BAQ from a layer to populate a grid. The parameter comes from a MultiSelect Job search. My issue is, it only sends/returns 1 of the selected Jobs:

My ExecutionParamter has to be flawed:

"ExecutionParameter": [
	{
		"IsEmpty": false,
		"ParameterID": "JobNum",
		"ParameterValue": "{searchResult.JobNum}",
		"ValueType": "nvarchar"
	}
]

I am thinking I have to build a delimited string using the dataview-condition component to loop through the dataview searchResult. Is this the best way to get each selection and pass it to the Item List parameter in my BAQ? Is there an example out there that I missed? I apologize if this has been asked already; I looked I swear but couldn’t find a post involving Item List parameters.

[nikba (Nik)]

Hi there! If you set it up correctly, you will automatically get a character separated list from your search.

Parameters say [“JobNum”]

if you don’t like default separator (~), you can play with different options in the Row Delimiter

Hope this helps

[Chan213419 (Jeremy Chance)]

Thank you so much!

[Chan213419 (Jeremy Chance)]

Followup question for me and others who may get into this:

My search multi-select works as shown in the textbox:

When I select one item, my BAQ (that has a list parameter setup)

Runs and returns a row without issue:

When I select multiple JobNums, it fails. I get no records:

My Execution Parameters:

{
	"ExecutionSetting": [
		{
			"Name": "PageSize",
			"Value": 100
		},
		{
			"Name": "PageNum",
			"Value": 1
		},
		{
			"Name": "NeedTotal",
			"Value": false
		},
		{
			"Name": "Select",
			"Value": "[JobHead_JobNum]"
		}
	],
	"ExecutionParameter": [
		{
			"IsEmpty": false,
			"ParameterID": "JobNum",
			"ParameterValue": "{TransView.Jobs}",
			"ValueType": "nvarchar"
		}
	]
}

I am thinking I’m missing an execution parameter, or that my execution parameters are wrong. I’d be grateful to chew on any input.

[utaylor (Utah Taylor)]

@Chan213419

When I select multiple JobNums

When you select multiple jobs from a quick search or something similar? is that what you’re saying?

[Chan213419 (Jeremy Chance)]

Correct, I apologize for not being clear there. I click a button that opens a multi-select search. If I choose one JobNum, it works. If I choose more than 1 it completely fails (no rows populated) even though I’m seemingly passing in the value with the ~ delimiter. I’m on the verge of just building a function

[utaylor (Utah Taylor)]

I had that the other day and I may have given up… maybe… I forget. I’d have to reference something I know that uses a search. I’ll get back to you next week if it’s not already solved by someone here.

[jbooker (Josh Booker)]

Are we sure BAQs IN clause takes a tilde separated list? I’d try comma delimiter like SQL expects. If that doesn’t work, there’s also a client-side ‘function’ action that converts list values for passing into BAQs. convertDelimtedListToSql . Oh and JobNum is char so sql expects quotes as well. The function does that too I believe.

[nikba (Nik)]

I added a function call after the search-value-set that has input/output as string then used a simple code to “stringify” my output

var jobs = input.Split(',');

for (int i = 0; i < jobs.Length; i++)
{
  string p = jobs\[i\].Trim();
  output += "\\"" + p + "\\"";
  if (i < jobs.Length - 1) output += ",";
}

then update the TransView.Jobs field one more time with the actionResult.output and
this is what I get (ps. also changed the row delimiter in search-value-set to comma “,”)

[jbooker (Josh Booker)]

FYI: SQL IN() is single quote.

Here’s an example of how to do it with ui function action:

args:

[
	"TransView.searchResult",
	"C",
	"~"
]

result:

PS - there’s a bug that might getcha if your JobNums contatin only numbers:
IN in Where Clause in Kinetic doesn't work properly - #33 by jbooker

[nikba (Nik)]

Cool Function, even if I wasn’t able to make it work - did not realize that existed! Yeah, and sorry about the double quotes, did not test that all the way through. Been too much cloud work lately and too little SQL

and this should make it single quote

var jobs = input.Split(‘,’);

for (int i = 0; i < jobs.Length; i++)
{
string p = jobs[i].Trim();
output += “‘” + p + “’”;
if (i < jobs.Length - 1) output += “,”;
}

[Chan213419 (Jeremy Chance)]

Excellent question! I ran a trace and there is no list. Instead, the payload shows an an ExecutionParameter for each job selection:

I couldn’t find a delimited list being sent. Will the JSON Editor allow things like constants to build a list to loop through adding an ExecutionParamer for each? So far, I’ve had no luck.

[jbooker (Josh Booker)]

That trace is from your setup described in you first post? Meaning that what happens when you exec BAQ {TransView.Jobs} ? Or that’s a trace from the working test like after providing a list in BAQ designer.

[Chan213419 (Jeremy Chance)]

Another good question Trace came from running my BAQ in BAQ Studio/Designer.

[jbooker (Josh Booker)]

Huh. And it worked? You got the expected multi-job results? I’m suprised, just never seen the same parameter name/value provided twice.

If that’s indeed how ‘list’ type params are handled, I think you should change the param type to simple string, keep the IN() criteria and use one of the methods to concat a valid IN string above.

I’m not aware of an easy way to make it ‘expand’ a list into many params like BAQ designer is doing. I’m sure there’s a way but I don’t know it.

[Chan213419 (Jeremy Chance)]

My BAQ works for sure, but the layer that’s using it does not. Same issue as before. Selecting one JobNum works, but if you select 2 or more it fails.

On the BAQ criteria: If I use the IN operation, my paramter must be a list type:

Looks like I can’t change it to a string.

[jbooker (Josh Booker)]

Okay then I was wrong. I’m pretty sure the IN operator can take a single-quoted, comma delimited list via one of the option outlined above.

I’m not sure tho whether this is being complicated by the fact that you’re using a param and thus forced to call via rest-erp rather than baq action. But I’m curious whether one of the ConstantList options (non-parameter) makes a difference.

Don’t have time to test it or find a working example at the moment but I guess my approach would be:

1. figure what works for passing to IN() without using parameters then
2. figure out how to make that work with parmeters

like make param optional and use Where from UI instead like so:

{
	"ExecutionSetting": [
		{
			"Name": "PageSize",
			"Value": 100
		},
		{
			"Name": "PageNum",
			"Value": 1
		},
		{
			"Name": "NeedTotal",
			"Value": false
		},
		{
			"Name": "Select",
			"Value": "[JobHead_JobNum]"
		},
        {
            "Name": "Where",
            "Value": "JobNum IN({TransView.Jobs})"
        }
	],
	"ExecutionParameter": [
		{
			"IsEmpty": true,
			"ParameterID": "JobNum",
			"ParameterValue": "",
			"ValueType": "nvarchar"
		}
	]
}

[utaylor (Utah Taylor)]

Looking forward to y’all solving this because I think I had to us filters in the SSRS report to do it and I couldn’t get the parameter to pass, like I passed the list of jobs to filter01 in the report style criteria.

[Chan213419 (Jeremy Chance)]

Well, sorry to disappoint, but I just created a function instead. Unfortunately, time is my enemy today . I created a function that runs the same BAQ that has the pesky list parameter:

After the user selects the jobs they want to see, I pass their selections to my function using {TransView.Jobs} as populated from search-value-set that @nikba showed me, using the Row Delimiter ,

The function runs my BAQ, as shown below and returns the dataset (oDs):

CallService<DynamicQuerySvcContract>(dqSvc =>
{     
  Ice.Tablesets.QueryExecutionTableset QEts = dqSvc.GetQueryExecutionParametersByID("JobGrid");
  QEts.ExecutionParameter.Clear();
  
  var payload = new {
      JobsList = this.pJobsList
  };

  var JobsList = payload.JobsList.Split(',');
  foreach (var job in JobsList)
  {
      QEts.ExecutionParameter.Add(new ExecutionParameterRow 
      {
        ParameterID = "JobNum",
        ParameterValue = job.Trim().ToString()
      });
  }
      
  DataSet qeDS = dqSvc.ExecuteByID("JobGrid", QEts);
      
  if (qeDS.Tables[0].Rows.Count>0)
  {
    this.oDs = qeDS; 
  }        
});

I then pass the result to the dataview my grid is bound to:

Probably not the solution hoped for, but this direction does work. The function adds an ExecutionParameter for each jobnum the user selects, just like my trace showed when I manually ran the BAQ. Perhaps you can create a loop in the JSON editor and create an ExecutionParameter for each selection? I don’t know, but if you can do that, I’d definitely go that route over doing it this way. I just didn’t have time. If I shake something else out, I’ll be sure to update.

Edit: thank you everyone for your help, time, and input on this. Lots of smart humans about

[utaylor (Utah Taylor)]

wow, definitely had to do what you had to do… care to share the function?