Filtering Database Query with Multiple Checkboxes

[Teodor]

You can use multiple checkboxes in order to filter your database queries.

In our example we already created a server action with database query which shows the products in a dynamic table. It shows all the products currently, but we want to be able to filter them by category:

First let’s add a form. Click the insert (in our case insert before) button, in order to place the checkboxes above the table on the page:

Open Forms category and add a From:

Then, click Insert Inside button, and select Multi Checkbox Form Group:

The multi checkbox group adds a few dummy checkboxes. We can easily edit them, remove them or add more if required. Select the first checkbox(1) in the app structure and add the value you need for it (2). This value will be used to filter the records:

Do the same for its label - select the label and edit its text:

Then, do the same thing for all of your checkboxes, adding appropriate values to them.

Now, open the server connect panel:

Open your server action, select $_GET and add click the Add New button:

Add an array:

And add a name for the array. We call it category:

Click your database query, and open the query builder to edit its options:

Under the Conditions tab, select the column you want to filter your query by:

Select the IN operator:

And click the dynamic data picker:

Select the array which we created under $_GET, then click the Data Formatter icon:

Right click the $_GET.category array and open the Text category:

Select the Split string option:

Then, click character and enter , (comma):

Click select:

We want to show all the results on page load, and only filter them if a checkbox is selected, so we need to add a condition which checks that. Click the dynamic data picker for the condition:

And select the category array as a condition expression:

This way the filter will be applied, only if the $_GET.category has a value i.e. when a checkbox is selected. Click OK:

Save your server action, close the server connect panel and go back to your page:

Select the server connect component in app structure and you will see the $_GET variable we added in server connect. Click the dynamic data picker, to assign a value to it:

Its value must be the value of the Checkbox Group:

And you are done, now the records will be filtered when you select any of the checkboxes:

---

   

---

[Hyperbytes]

Perfect Timing @Teodor, doing exactly that is on my to do list for next week! Thank you very much.

[updates]

Same for me!! Thank you @Teodor.
Very interesting the use of the array!!

[Freddy_Blockchain]

Very good example of using an array and split

[samrich]

@Teodor

This works great but I have a question. Let’s say one of the columns is a list of years. Now for the current year I can set the query to get NOW plus some formatting. But if I want to show past years, what’s the best way to do it? The “year” can vary so it wouldn’t work just putting a bunch of check boxes for each year.

Thanks for the help.

[mrbdrm]

@samrich use a select with the list of years

[samrich]

@mrbdrm Thanks for the help. That worked out perfectly.

[samrich]

@mrbdrm @Teodor

Well I spoke to soon, on a page that is similar to @Teodor 's original posts above. The problem I’m having is that there are two conditions in the Query Builder. The first one narrows the list of names by a group and is passed in as a {{$_GET.chapter_id}} when the page loads. Then the second condition is the one passed in from the form for the checkboxes, the “category” from Teodor’s example. The issue is that when the page loads for the first time, I get back all of the names from the system instead of just the ones for the passed group. It’s like the “category” is empty so it craps out on all of the $_GET values passed in.

[Hyperbytes]

did you remember to bind the variabe to the query connection?

[samrich]

@Hyperbytes, Yes, all binded.

It appears that when the page loads it is ignoring the “WHERE” portion of the SQL and bringing back all of the users. If you check both check boxes, the correct users are displayed. If both boxes are un-checked then you get the full list of users.

Is there a way to set the initial value of the array when the page loads so that the query is executed correctly?

[AdrianoLuiz]

use add group

[Hyperbytes]

Yes. @AdrianoLuiz is correct
You can use groups like this

[Djibril]

Hi,
Same issue for me. My query contains several tables and there are several conditions in addition to the filter condition. Any idea please ?

[sitestreet]

The part you want to pay attention to is here:

That should help you.

[Djibril]

The example works perfectly on a numeric type field. In my case, the field type is SET and it works if the record contains only one value. Thank you in advance for your help.

[Djibril]

Hi everyone, any idea to solve my issue ?

[psweb]

Any chance you could try give way more information and screenshots so I can try get a better idea of which filters are and are not working.

[Djibril]

Hi psweb,
Thanks for your reply.
The screenshot below is the representation of my table which has 2 fields:

• id: type smallint, ai
• Casques audios connecteurs: set type, the values are: Non applicable,Prise 2.5 mm,USB-C,1, Prise 6.35 mm, 1

12 records have been entered as you can see.

I want to filter the “Casques audios connecteurs” column. By using “IN” in the conditions, it displays only the records which have only one value. It ignores all records that contain more than one value in the “Headphones connector” field even if the value that is checked is one of them.

With “CONTAINS”, it displays all the records which contain the selected value.

But as soon as you check a 2nd value, it displays only the records which contain these 2 values and ignores the records which contain only one of these 2 values. And so on.