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:
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.
Hi Aurelio,
Thank you for jumping in so quickly with an excellent example .oml to help Aman.
Great work!
Kind Regards,Chris.
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?
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.
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
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.
Glad we could help.
Good luck with your developing!