Optional user input for filters

[shilton]

I want to create a filter (or select) with optional user inputs. The issue is the optional nature of the user input.

I can easily create a filter where the user must put in a value. The issue is where I have several parameters and I want to make some of them optional.

For example I would like to filter a maintenance job database on one or more of the following parameters: Job assigned to (person name), site (name of location), Open (job open or closed, ie Yes / No), Start Date (input begin date range for due date of job), and End Date (input end date for due date of job).

If I force all to be input filter (or select) works fine. If I leave them optional it is an issue. How can I build a filter condition on the fields with data in them and leave out from the filter where there is no user input?

[Suvrutt_Gurjar]

Hi @shilton,

If I have understood your requirement correctly, please explore following approach.

In general , I believe you may use default TRUE or FALSE conditions for those optional inputs, where the inputs are blank sometimes.

For example, IF (ISBLANK([Job Status]), TRUE, ([Job Status]= “Open”))

In general , I believe you may have a TRUE as default condition where the filter has many AND conditions. FALSE may be good as default for where filter has OR conditions.

You may wish to share more details if you are looking at something else.

Edit: of course default TRUE or FALSE will depend on results you are wishing to have in addition to AND , OR conditions.

[shilton]

Hi,

I have used IF statements where it is just one optional item (exactly as you have suggested). However, this becomes very complicated very fast with multiple optional input parameters.

Looking at potential input fields:

• Job assigned to (person name) - could be blank or a name
• site (name of location) - could be blank or a site name
• Open (job open or closed, ie Yes / No) - could be blank or have data
• Start Date (input begin date range for due date of job) - could be blank or have a date
• End Date (input end date for due date of job) - could be blank or have a date

If I have my maths right it is 32 different possible combinations each requiring a full filter statement in each of the nested IF positions. I suspect I would never get it right and I suspect there is also a maximum length allowed in the expression areas that I would exceed.

To build a nested if of all possible combinations of input being blank (or not blank)

[Suvrutt_Gurjar]

Hi @shilton ,

Thank you. My suggestion is to use those IF() statements inside the SELECT() itself, for example

SELECT ( Jobs [ Jobs Name], IF (ISBLANK([Job Status]), FALSE, OR([Job Status]= “Open”, [Jobs Status]=”Closed”)))

So I believe you may need 5 such complex statements at the minimum within the SELECT() expression.

Edit: However I totally agree that the expression could become complex as one tries to build more flexibility in terms of OR and AND combinations. I believe the expression is bound to become more complex in proportion to number of arguements and filter condition combinations involving OR and AND etc.

[Steve]

Bah. Not a problem.

FILTER(
  "Jobs",
  AND(
    OR(ISBLANK([Job assigned to]], ([_THISROW].[Job assigned to] = [Job assigned to])),
    OR(ISBLANK([site]], ([_THISROW].[site] = [site])),
    OR(ISBLANK([Open]], ([_THISROW].[Open] = [Open])),
    OR(ISBLANK([Start Date]], ([_THISROW].[Start Date] <= [due date of job])),
    OR(ISBLANK([End Date]], ([_THISROW].[End Date] >= [due date of job])),
    ...
  )
)

I doubt you’ll run into an expression size limit if there are only 32 criteria. Although, from a user experience-perspective, does it make sense to offer that many options?

Note that this is an expensive operation and could potentially cripple your app. Can I ask you to elaborate on how you intend to implement the filter beyond merely finding the matching jobs? How do you intend to present the options to the user? How do you intend to present the results?

[Rafael_ANEIC-PY]

uh, this thread sprung back to life on its own and to the front of the questions section somehow, weird.

I just had a crazy idea about it tho, what if you create a slice for each filter condition, where each slice would run against each other in order, in a way where if the required criteria field is empty, it is TRUE.

So for example, lets say i have 3 fields with connected slices, could it work like this…?

Slice 1: check for name criteria, if it is blank, TRUE, return all rows from the original table
Slice 2: check for site name criteria, if it is blank, TRUE, return all rows left from the slice 1
Slice 3: check for start date criteria, if it is blank, TRUE, return all rows left from the slice 2

And so on, could it work?

Can you slice a slice?

[Steve]

Rafael_ANEIC-PY:

Can you slice a slice?

Nope, but one slice can reference another.