[Multiple Selection Dropdown Widget (Reactive And Mobile)] pass multiple selected value from dropdown in IN clause
Application Type
Reactive

Hi,
I'm trying to pass the selected items from dropdown, convert them into list using split() in js and them pass the list into SQL IN clause to get data from entity.

On passing input as "C4C","CaptureMail" in SQL for testing purpose gives error. so I don't know what to do. Unfortunately, on converting them into list gives me error as below.

Dropdown:

Error:

JS :
$parameters.selectedProject = (document.querySelector('#b4-CurrentSelectedItems').textContent).split(",");

SQL :

SELECT {allappsdata}.[IssueType], Count({allappsdata}.[IssueType]) FROM {allappsdata} WHERE {allappsdata}.[Impactedsystem] IN (@impactedsystem) GROUP BY {allappsdata}.[IssueType]

Also it is not possible to test SQL when I use IN clause.


Apologies, but I cannot upload an OML as I have private data.

Hi Aman,

You don't need to use javascript for that. I'm attaching a full example on how to achieve this.

Take a look at the SQL query in the "GetSelectedItems" data action to see how to correctly configure the input parameter and set its test value:


MultiSelectDemo.oml

Hi,

Thank you for the reply. can you please share OML again ? On downloading, the file name comes as download and size is 0kb

Here it is. The download did work on my side though.

MultiSelectDemo.oml

Hi Aurelio,

Thank you for jumping in so quickly with an excellent example .oml to help Aman.

Great work!

Kind Regards,
Chris.

Hi Aurelio,

I'm getting below error when I'm trying it in my application. Even yours is giving me error if I try to change something, If i don't change anything it works perfectly. 

Regards,
Aman

Hello,

What exactly do you mean by "change something"? What are you changing?

Hi,

I moved the condition in the same line as WHERE keyword.

You're probably missing the value for the "SelectedIds" input parameter in the "Test Inputs" tab, as shown in one of the screenshots in my first reply.

If the {TestData}.[Id] column holds string values, you want @SelectedIds = "'a','b','c','d'" (notice each item is a SQL string - has single quotes around it).

If the {TestData}.[Id] column holds integer values, you want @SelectedIds = "1,2,3" (notice each item is an integer)

Hope this solves your issue.

Solution

To answer your question on how to get the returned list (here so others can benefit)

To get the returned list you need to define an OnChange handler (Screen Action). It's in this action that you perform any logic you want to that uses the results of the list.

Hi @Aurelio Junior ,

Thank you for the help. It worked perfectly. As you said, I missed the test inputs, I didn't understand why I needed to pass the test inputs. Should the SQL work regardless of the inputs?


Anyways, Marking yours and Chris's answer as solution.

Regards,
Aman Devrath

Hi Aman,

Happy to hear all's working now.

When you set a parameter's "Expand Inline" property to Yes, you're specifying that the parameter's value is going to be a part of your SQL command. So if you ommit the Test Value when testing the query, you'll end up with an incomplete SQL statement and, thus, an incorrect syntax error.

To add to the above mentioned answer gave by Chris.
I assigned MenuItems (of MultiSelect_ReactAndMobOnChange action) to itself and then assigned the value of MenuItems to a local variable - MenuItemsVar - of MultiSelect_ReactAndMob List type. 

Regards,
Aman Devrath

Glad we could help.

Good luck with your developing!

Solution

To answer your question on how to get the returned list (here so others can benefit)

To get the returned list you need to define an OnChange handler (Screen Action). It's in this action that you perform any logic you want to that uses the results of the list.

Hi Aman,

You don't need to use javascript for that. I'm attaching a full example on how to achieve this.

Take a look at the SQL query in the "GetSelectedItems" data action to see how to correctly configure the input parameter and set its test value:


MultiSelectDemo.oml

Hi,

Thank you for the reply. can you please share OML again ? On downloading, the file name comes as download and size is 0kb

Here it is. The download did work on my side though.

MultiSelectDemo.oml

Hi Aurelio,

Thank you for jumping in so quickly with an excellent example .oml to help Aman.

Great work!

Kind Regards,
Chris.

Hi Aurelio,

I'm getting below error when I'm trying it in my application. Even yours is giving me error if I try to change something, If i don't change anything it works perfectly. 

Regards,
Aman

Hello,

What exactly do you mean by "change something"? What are you changing?

Hi,

I moved the condition in the same line as WHERE keyword.

You're probably missing the value for the "SelectedIds" input parameter in the "Test Inputs" tab, as shown in one of the screenshots in my first reply.

If the {TestData}.[Id] column holds string values, you want @SelectedIds = "'a','b','c','d'" (notice each item is a SQL string - has single quotes around it).

If the {TestData}.[Id] column holds integer values, you want @SelectedIds = "1,2,3" (notice each item is an integer)

Hope this solves your issue.

Solution

To answer your question on how to get the returned list (here so others can benefit)

To get the returned list you need to define an OnChange handler (Screen Action). It's in this action that you perform any logic you want to that uses the results of the list.

Hi @Aurelio Junior ,

Thank you for the help. It worked perfectly. As you said, I missed the test inputs, I didn't understand why I needed to pass the test inputs. Should the SQL work regardless of the inputs?


Anyways, Marking yours and Chris's answer as solution.

Regards,
Aman Devrath

Hi Aman,

Happy to hear all's working now.

When you set a parameter's "Expand Inline" property to Yes, you're specifying that the parameter's value is going to be a part of your SQL command. So if you ommit the Test Value when testing the query, you'll end up with an incomplete SQL statement and, thus, an incorrect syntax error.

To add to the above mentioned answer gave by Chris.
I assigned MenuItems (of MultiSelect_ReactAndMobOnChange action) to itself and then assigned the value of MenuItems to a local variable - MenuItemsVar - of MultiSelect_ReactAndMob List type. 

Regards,
Aman Devrath

Glad we could help.

Good luck with your developing!

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.