Tablerecords List Filter made Easy using a single Simple Query

Tablerecords List Filter made Easy using a single Simple Query

  
Hi Guys,

I often have a situation in which I want to either include or exclude a (part of a) WHERE clause based on a variable value (in the example this is a boolean).
I used to do this with separate Simple Queries or even with Advanced Queries and building up my own WHERE clause in a text variable.

No I've thought of a little "trick" that keeps a clear design and only needs a single Simple Query.
This trick I'd like to share with the community:

In the example I'm showing a company list and enable a quick filter for "valid companies" based on a checkbox that has a RefreshTable set in the Onchange.



This is what the used RefreshTable action looks like (note the single Simple Query)


The real trick is in the Query part:


The "COMPANY3.LmeId <> NullIdentifier()" part will ONLY be executed if the FilterNoSwordCode variable is set to True.

Using this 'trick' you can easily filter out e.g. "Invalid statusses" or show records that are both valid and invalid without the use of:
  • An advanced query
  • Multiple simple queries
  • A separate "List" assignment

Besides that it gives a clear view in the simple query screen of what the possible queries are that can be executed.

Hope this helps you as well as it is helping me.

Cheers,

Eric

Hello Eric

That's an expected behavior of the Agile Platform, since when you set a simple query input parameter as Search Parameter, all conditions that use that parameter will not be included in the query, if the value of that parameter is NULL or empty. In the case of boolean values, the default null value is false.

So when you're valid checkbox is unchecked, the FilterNoSwordCode input parameter is false, and thus the entire query condition 'FilterNoSwordCode = True and (Company3.Lmeid <> NullIdentifier())' will not be included in the query.

Since you're query condition is a AND, I would suggest to split that condition into 2 conditions, isolating the usage of FilterNoSwordCode, and avoiding such problems.

Hope this information is helpfull.

Cheers

Miguel João
Hi Miguel,

I'm afraid you haven't quite understood the 'story' above. :)
I'm explaining a way to enable or disable pieces of a query with a boolean input parameter by using the "Is search parameter" option.

Cheers,

Eric
Hi Eric

Ups !! i thought I had seen a question mark somewhere. :)

Sorry, my bad.

Good post by the way.

Cheers

Miguel João
Hi Miguel,

No problem :); the form is mostly used for questions (The header says "Ask Outsystems / Ask the Community")
hence it's logic that you consider my posting a question / problem situation.
Since there is a community perhaps it's an Idea to create a separate forum section with Tips & Tricks that can be shared.

I think that can help to create a higher level of thinking and handling issues for the O.S. developer community.

Cheers,

Eric