E10 DynamicQuery Multiple Parameters or ValueSetItems


(Andrew Saldivar) #1

In E9, I successfully used a routine demo’ed by @josecgomez.sixs (a while ago) that instantiated the dQuery and then looped the AddQueryValueSetItemsRow() method in order to get multiple entries (Order Numbers) from the user before executing the query - which returned a DataSet containing all the selected Orders.

The BAQ used the criteria: OrderHed.OrderNum IN specified Constant list.

This worked great, but I can’t get it to work under E10. It looks like the prevailing thinking (now) is to use Parameters via the AddExecutionParameterRow() method instead?

Problem is, I can’t figure out how to include multiple parameters - The BAQ criteria doesn’t allow “IN” with a Parameter list - only “=” or “Begin”, as in another great demo video (thanks Jose.)

My users are able to select multiple Sales Orders from a grid. I have no problem accessing that list and creating the necessary loop to add the selected order numbers. Does anyone have some guidance on turning that list into the appropriately formatted QueryExecutionDataSet or QueryValueSetItems DataSet?

Thanks in advance!


(Jose C Gomez) #2

You can use IN with a List parameter in 10… then (this is kinda contradictory) but you add the same aparameter for each instance of the “IN” when you execute the DynamcQuery in code.


(Andrew Saldivar) #3

Thanks Jose. I see I can use “IN” with a “Constant List”. But the option for Parameter list is not available.
Are you suggesting I use the constant list and then loop the adds to build a ?!? what exactly?


(Jose C Gomez) #4

What version of 10?


(Andrew Saldivar) #5

10.2.200.3 (should have started w/that info. Sorry)


(Haso Keric) #6

I plan to use List via Code, need to figure it out yet. I traced the Parameter value down to being a semi-colon seperated list behind the scenes:

123;My Value 2;MyVal3 - need to play around a bit more with the logging to figure it out.


(Jose C Gomez) #7

This is 10.1.500 so 10.2 should definitely have it it… You are not SaaS MT are you? :tired_face:
image


(Jose C Gomez) #8

It’s in Snippet @hasokeric just added it last week


(Haso Keric) #9

The question is how do you pass it in via

ds.ExecutionParameter.AddExecutionParameterRow("InvoiceNum", InvoiceNum, "int", false, Guid.Empty, "A");

(Andrew Saldivar) #10

The list IS there (now I remember), but I can’t create a parameter to then reference in code.
ExecutionParameter.AddExecutionParameterRow("listOrders",...

That “listOrder” is the parameter name (in the BAQ) that I can create for a single parameter. But when I select the “list parameter”, do I create a “Parameter List” somewhere? Is this what you’re looking for @hasokeric?


(Jose C Gomez) #11

As silly as this may seem it is as simple as adding the same param multiple times

 foreach(var item in list) //Where list is collection
          {
               ds.ExecutionParameter.AddExecutionParameterRow("inList", item, "nvarchar", false, Guid.Empty, "A");
          }

(Andrew Saldivar) #12

That “inList” is where I’m having some difficulty. I can’t create that parameter name! I’m sure I’m doing something stupid, but the parameter exists - it just isn’t visible to be selected…

image


(Jose C Gomez) #13

Param needs to be an item list


(Haso Keric) #14

Next thing you have to do is click Define…

Then just create a nvarchar param and instead of Constant option pick the List and hit okay.


(Andrew Saldivar) #15

Wow. I knew I was doing something stupid. Thanks for the picture!
Gents, truly appreciated! Works like a charm!