Custom input filter for query don't pass filter onChange?

We have a custom text input box which is used in a query to filter the query. Basically, the query has the following: {{textInput1.value}}. This works to properly filter the query. The problem is that the query is run on each onChange of the value of the text input. This obviously is not performant, as tons of queries are run as the user changes the value in the Input box. How can we disable onchange and only run the query when the value has been submitted? We tried to do this via a form, but the form value was still available in query onchange. What value do you use instead of a .value to just use the submitted value?

BTW, I read this post, Search table when text input submitted, clear search when text input is deleted - but this seems like a ridiculously complicated approach to this issue and I am really hoping this is not the suggested solution.

1 Like

Welcome to the forum!

It really depends - if the field is in a form then why not run the query when the form is submitted?

Screenshot would help the forum help you

1 Like

Yes, we run the query when the form is submitted, but the value is still available in the query onChange. There doesn't seem to be any value you can put into {{}} that is determined based only on the value in the submitted form. So for example, {{}} (a form value) will still trigger the query, even onChange of the textInput2 in the form. What we are looking for is: something like isSubmitted property, so we can test if the value is submitted, or just not run the form onChange.

And maybe, I'm not clear here. We have a table1. It is filled based on query1. But query1 has a filter. The filter is based on an input field. The problem is the query1 filter will always run onChange of the input. There is no way to seemingly control that. This will overload the database, of course, as queries run on every onChange.

In case, anyone else finds this question, the only way to use server-side pagination for a table and at the same time have filters, is to create 2 queries, as suggested in this topic Search table when text input submitted, clear search when text input is deleted

The reason is that, as far as I can tell, Retool will always evaluate all input values onChange and there does not seem to be anyway to evaluate input values onSubmit. So, if you want to have a table with server-side pagination and custom filters, you must unfortunately create 2 of the exact same queries to display that table. Query1 controls the initial display for pagination and runs onChange (if you don't have onChange here, the pagination will never work). Query2 controls the filters and runs when triggered and the input values must be set with Actions to trigger Query 2 upon submit. Then in the table itself, you need to control the data source via some logic like so: {{ textInput1.value.length ? : }}

I consider this kind of a hack, as Retool should somehow allow evaluation of input values onSubmit and not just onChange. That way, you would be able to use one query that runs onChange, but allow an evaluation of the textInput.value to be something like text.Input1.isSubmitted ? textInput.value : null

And why do you need server side pagination? Well client side only works for very small datasets. Once you get to any reasonable table size, client side is not practical to use, as it must grab all your data to work properly. This is obviously not viable for most situations.

EDIT: The above solution still does NOT work. Because when you put {{textInput.value}} into Query2 and set Query2 to only run manually when triggered and create the trigger in the Input field to trigger Query2 onsubmit, Query2 will still run when the textInput is changed onChange! IMHO, this is a serious bug in Retool and it's surprising there are not more posts on this. You can't use this to create custom filters at all, as all the queries will always run onChange of the input value, overloading the database! There is NO way to evaluate an inputValue only onSubmit, even though this is basic Javascript. Pretty much the only way I see how to do this is to have all the queries that table depends on to run on a trigger only and then you need to manually run the non-filter query to get all the results. This works, but is a bad UI.

Hi @ddsgadget

I'm wondering if you can solve this by maintaining a snapshot of the form data upon submission in a variable.

Here are the steps I've taken to solve this:

1) Create a variable that will store the form's value on submit (default value will be empty)

2) Set the form's submit event to update the variable with the text value

3) Create my paginated table that will be filtered based on the variable's value

Please let me know if you run into any issues with this approach! Happy to take another look

Thanks. Yes, we were actually doing something similar to this. Using variables is a great option. Haven't finished building it yet, but will update, if this works.

1 Like