Complex filtering of tables

App Building
, ,
1

Hi,

I am trying to set filters of a table using two multiselect lists: multiselect1 and multiselect2. So i am using setfilters(filters,filterStackType). However, I am not sure how to indicate that some of the filters should use "OR" and others should use "AND". Specifically, filters within each list should be "OR" and it should be "AND between the two lists.

Thanks

2

@ofedrigo Welcome to the forum!
Any time you are filtering it's best to use AND between the two fields you are selecting from since you want to allow the user to be as granular in their filtering as much as possible.
If you need help please post some of the code you are writing so the forum can help you get to a solution quicker...

3

Thanks. here is some code where table1 is my table I want to filter. This table includes a column called "status" and a column called "tags". I want to filter this table using values for these two columns trhough two multiselect lists (multiselect1 and multiselect2).

table1.clearFilters();
var all_statuses=multiselect1.value;
var all_tags=multiselect2.value;
var filters_statuses=[];
for (var i=0;i<all_statuses.length;i++) {filters_statuses.push({columnName: "status", filterValue: all_statuses[i], operator: "equals"}); }
var filters_tags=[];
for (var i=0;i<alltags.length;i++) {filters_tags.push({columnName: "tags", filterValue: alltags[i], operator: "equals"}); }

table1.setFilters(filters_statuses,"or");
table1.setFilters(filters_tags,"or");

But what I want is to filter the data for which column "status" is equal to any value of filters_statuses AND column "tags" is equal to any value of filters_tags. Does it make sense?

4

Yes it makes sense but I don’t have the ability to write it out at the moment so if you can wait until early tomorrow or maybe someone else can post before I am able to do so

1 Like
5

The easiest way to go about this, in my opinion, is to write a Query JSON with SQL against the table you want to filter on:

SELECT * FROM {{formatDataAsArray(yourTable.value)}}
WHERE (status = ANY({{yourStatusMultiSelectField.value}}) OR {{yourStatusMultiSelectField.value == ''}} )  
AND (tags = ANY({{yourTagsMultiSelectField.value}}) OR {{yourTagsMultiSelectField.value == ''}})
2 Likes
6

Thank you. That works. Is it a little slower than using table filters?

7

Well you will want to set that query as Run when inputs are changed so it is instantaneous... so no I don't think so (IMO)

1 Like
8

Hi @ScottR @ofedrigo ,
I'm new to retool and I'm working on an app, I need help on how to implement the custom filters using multiselect lists. I'm using the New Table component as the table which I want to filter with any or all of the values of all the multiselect list components.

I will be awaiting your response. Thanks.

9

Hey @Nasirullah!

@Alexi has a helpful post on how to implement filters in the new table here, let me know if that helps!

10