Filter derived table based on the id associated with the user entered value

[jbernal]

We have a derived table that I would like to filter based on user's input on the front end explore. The main goal is for the filter to improve the number of records scanned and thus performance. However, the improved performance only happens if the user is filtering in the front end by an indexed field like id but our users don't know ids. They work with user facing identifiers like integer codes or string names.

Is there anyway to grab the user entered code/name, cross reference to that value's id, and then pass *that* into the derived table as a filter instead of the raw code/name value? That would achieve the goal of filtering the derived query by an indexed field which improves performance.

[GavinW]

Perhaps you could use a user attribute to filter the data behind the scenes? Users wouldn't need to do anything on the front end. You could use a user attribute with an access_filter, always_filter, or sql_always_where. There's an example of the latter here.

[jbernal]

Thanks for the reply Gavin. We already do this for certain user attributes that should *always* be the case and can vary from user to user. For this use case I'm asking about these would be values that the user might decide to filter in their regular day-to-day usage. It's not a predetermined set of ids that they're *always* going to have auto-filtered.

[GavinW]

Perhaps the always_filter would be something to investigate? This would start the user with a filter to a specific subset of the data, but they could then modify this filter at will if they need to see a different subset?

You are probably already aware of templated filters - I think that here we could also pick up the value from an 'always_filter' that is populated by a user attribute, and then use it to filter a derived table at runtime.

[jbernal]

Appreciate the suggestions but I don't think these are as dynamic as what we're looking for. An always_filter would help the initial load be faster which is great but as soon as the user overrides it with some other value (which as described above would be a code/name value, not an ID) then the value passed into the derived table would no longer be an indexed value and it would not help with reducing run time as well as an ID would.