Getting IN() function to work from multiselect table with Azure/SQL server


Can anyone tell me how to get IN() to work from a multiselect table? I assume it's something to do with the format of JSON object verses what the server is expecting?

I have the following query:

 dbo.GetResourcesOnEvents.EventID, dbo.GetResourcesOnEvents.ResourceName
  dbo.GetResourcesOnEvents.EventID IN {{formatDataAsObject(}};

And the multiselect table SelectedEvents returns an array of integers!

Hovering on the green JS code is showing me the two EventIDs that I have selected in the table (top right of the screen).

But I get no results (nothing showing in the listbox (on the left - SelectedResources)

If I use = 7823 directly in the query - I get the desired results!

See the list view showing Len and Kim.

Here is my data.

Hi @jclutterbuck, have you tried mapping it instead of using formatDataAsObject?


{{ => x.EventID)}}

If that does not work, can you follow this tip from Justin/Brad: Using (IN) with a list of values from a Retool component or state

Lemme know how you go


To select multiple rows from a table and pass it properly into a SQL statement you need to use string_split in addition to formatDataAsObject:

To reduce the amount of SQL every time you want to do an IN statement consider creating a table function on your DB like so

Hi @jocen

Thanks for your reply. I don;t want to take up your time, but if you had a couple of minutes to explain what that does I'd be grateful. I understand the map function but x => x.EventID?

(I did manage to get it to work with the answer below)


Thanks @Nicolas

This works!

I'm so grateful. I'm still learning (aren't we all) and some of the SQL in your solution will take me a while to understand how it works. but for now I can continue with my project.



Glad it worked out for you, the code can be reduced to below:

The reason why I had the select * from () was because I simplified the trimming, convert and string_split into the function above so I can call it like:

business unit in (select * from dbo.f_array(########) )

@retool the JS script solution above was also provided to me when I was struggling with filtering. For heavy SQL only users like myself I feel that a new value can be added to components that would concatenate selected text into one value, separated out by a comma.

So in my example above I selected 10094, 10148, the value that needs to be passed into sql for the IN filtering to work is: '10094,10148'.

I don't see this as a SQL injection, but just another value that can be used in filtering. This simplification would simplify SQL queries that require multiple IN clauses, which I have many apps and queries with.

I see. That explains quite a bit, thanks.

I can see the advantage if Retool was to provide this as a standard value of a component.

Thanks again.