Record alternate result for multi select for SQL

[JoeColvile]

Hi all,

My title is not the most concise but what I am doing is building an app which acts as a front end for an SQL script executed in power automate. I have got most of it working however I am having an issue with multi-select fields. I have two of them, the example I will give is a vendor filter. As the user might want to filter on multiple vendors and not just single selection they can select multiple. These selections are sourced from a database table. 

What I want is:

- User selects all the vendors they want (e.g. ven1, ven2, ven3) then when they click run I want that to input to the SQL query in power automate as ('ven1'),('ven2'),('ven3') so that it can be processed.

Does any one have any insight into how I can do this?

Thanks in advance,

Joe

[Aki78]

You could put a button next to your vendors dropdown that adds the dropdown.selected.value in to a context variable array- this way you can add as many vendors from the dropdown as you like...  and then call the context variable in to your 'run function'.

[v-albai-msft · Accepted solution]

Hi @JoeColvile ,

From your description, I assume that you want to get a result format like ('ven1'),('ven2'),('ven3')

If yes, you can try this solution. I will explain why I use this solution in below.

1. When I select 4 selectins in my combo box, the result shows as a table:

2. I can use Concatenate function to change its format:

ForAll(ComboBox6.SelectedItems,Concatenate("('",Value,"')"))

3. Then I can use Concat function to convert above result into a string:

Concat(ForAll(ComboBox6.SelectedItems,Concatenate("('",Value,"')")),Value&",")

4. Format above result to remove last ","  (based on your actual situation, maybe you need to change the last number to "-2"):

Left( Concat(ForAll(ComboBox6.SelectedItems,Concatenate("('",Value,"')")),Value&","), 
Len( Concat(ForAll(ComboBox6.SelectedItems,Concatenate("('",Value,"')")),Value&",") ) - 1 )

This time we already get the result, we can also save the result into variable so that we can directly use this variable in other formula. Set OnChange property of your combo box to below:

Set(varVendor,
Left( Concat(ForAll(ComboBox6.SelectedItems,Concatenate("('",Value,"')")),Value&","), 
Len( Concat(ForAll(ComboBox6.SelectedItems,Concatenate("('",Value,"')")),Value&",") ) - 1 ))

Best regards,

Allen

[JoeColvile]

Hi,

This solution looks like it would work very well. I will let you know how I go.

Thanks,

J