How To: Filter a BAQ with lots of random criteria, including "all"

Off Topic
1

I’ve mentioned this in other posts, but I never had a good example to share, but now I do! This is part of something I am working on today.

Here I am using Parameters, but this logic works for general criteria, too.

I’m making a dashboard to show jobs. I plan to give the user a parameter pop up like this (well, the App Studio version of it). Yes, truly optional parameters are possible in the BAQ (skip if empty, etc.), but they have drawbacks. This is nice because it actually says “optional” in the field.

But…

  1. What if they leave the “Optional” string in the boxes?
  2. How do I handle a dropdown or radio button for the sites AND allow the user to see all sites if desired?

image

OK, from the top.

Here are the tables. Basically it’s just JobHead. But to explain the rest:

  • As the comment says, I added PartPlant as an easy way to block a user that has no access to some sites.
  • The Project table is there only to add the Project Description
  • The criteria is as shown; it made the BAQ run faster. Yes, it really does.

image

Here is the parameter setup.

  • All are Mandatory and none are “Skip if empty”
  • Did you know that you can set default values in the grid, even if they look uneditable in the Detail tab? True story.
    • Also did you know that the default value likes to delete itself any time you edit the parameters? Good times.

image
image1029×285 18.7 KB

image
image803×400 10.1 KB

And here is how I handle those parameters.

  • Each case statement results in a 1 or zero depending on the input
    • For the first 3
      • If it’s blank, I take that to mean “don’t filter by this” – therefore all rows pass this test.
      • If they left the word “Optional” in the input box, I also take that to mean “don’t filter by this”
      • If they enter anything else, filter the corresponding field to that value
    • For the site, I use a similar logic – ‘All’ results in 1, otherwise it needs a match
  • Then I multiply the case statements together
    • If all case statements return 1, then 1 x 1 x 1 x 1 = 1 – which means true
    • But if any case statements equal zero, the whole thing results in false

image
image670×793 29.5 KB

And finally, I filter the whole SubQuery on that calculated field.

(If you use this as a SubQuery inside another SubQuery, you could put the criteria there, of course.)

image

Here’s the BAQ (2022.2.25) if you want to see it.

JobHeaderWithParams.baq (27.3 KB)

1 Like
2

Here is another example of this. This is a bit simpler. If the setup of a part is “wrong” (per our policies) then display that row – AND an error message explaining why.

It’s actually much easier than it sounds.