Solved: Optional multi-dimensional filter issue

wyotim · ‎02-07-2019

Good day PowerAppers!

While working on a new app I came across an issue that I wonder if anyone has a good solution for.

The problem: I am collecting a subset of a table based on four different filter dimensions (in truth, four filters and a search, but the search portion works fine). I want any or all of these filters to be optional such that if the user selects no filter, the most recently added items are shown (500 total as I am using the default settings for data row limits). If they select one or more filters, I want it to return up to 500 items based only on the filters being used.

My initial attempt was to use an If statement on each filter dimension to check if the filter is being used or not (see code below) but the blue squigglies reminded me that If statements can't be delegated and the filtering will be done on the first 500 items, not the whole table (which is why the Sort portion is the first layer of the whole nested statement: to make sure that 500 items is the most recent, not the earliest). Simply adding the filter components without the If statements means all the filters must be used to get a result, which obviously isn't an option.

Here is my code with the If statements bolded:

// Inform the user the filter is collecting data
UpdateContext({Working: true});
// Using the various filters, collect matching records
ClearCollect(
    colFilteredTickets,
    Search(
        Filter(
            // Sort the table so new records are first
            Sort(
                '[dbo].[Ticket]',
                ID,
                Descending
            ),
            // Check the date filter; date is converted into an Int value to bypass date filtering issues
            If(
                IsBlank(dteDateFilterTLS.SelectedDate),
                true,
                TicketDateInt = Value(
                    Text(
                        dteDateFilterTLS.SelectedDate,
                        "[$-en-US]yyyymmdd"
                    )
                )
            ), 
            // Check the company filter
            If(
                IsBlank(cmbCompanyFilterTLS.Selected.ID),
                true,
                CompanyID = cmbCompanyFilterTLS.Selected.ID
            ),
            // Check the location filter
            If(
                IsBlank(cmbLocationFilterTLS.Selected.ID),
                true,
                LocationID = cmbLocationFilterTLS.Selected.ID
            ),
            // Check the rig filter
            If(
                IsBlank(cmbRigFilterTLS.Selected.ID),
                true,
                RigNumber = Substitute(
                    cmbRigFilterTLS.Selected.EquipmentName,
                    "Rig ",
                    ""
                )
            )
        ),  
        // Search for the ticket number
        txtTicketNumberFilterTLS.Text,
        "TicketNumber"
    )
);
// Show the user a count of returned records
UpdateContext({Working: false})

Now, I could write a large If statement based on the combinations of filters being blank or not, which if my math skills are holding up means that I have 2^4 or 16 different permutations to account for. That's one big If statement!

Is there a more elegant way to do something like this?

wyotim · ‎03-04-2019

Alright, alright, alright! I finally sorted this thing out. Much thanks to @ericonline for helping me sort out the text side and @KickingApps for helping me sort the number/foreign key reference side!

What sealed the deal was the Laura Rogers video that you sent me @KickingApps, though in a roundabout sort of way. I tried the same method that she is using but realized that I didn't need the default value parts; I just need the Or statement with a slightly different approach than what she does. Instead of checking for the default value, I just check to see if the control is Blank(), which bypasses the need to search for that item if true and invokes the search if false. Also, I decided to keep the And-type structure for now, though I will fiddle with this to see how an Or-type structure would work.

The final code is as follows:

// Inform the user the filter is collecting data
UpdateContext({Working: true});
// Using the various filters, collect matching records
ClearCollect(
    colFilteredTickets,
    Filter(
        Sort(
            '[dbo].[Ticket]',
            ID,
            Descending
        ),
        txtTicketNumberFilterTLS.Text in TicketNumber,
        Substitute(
            cmbRigFilterTLS.Selected.EquipmentName,
            "Rig ",
            ""
        ) in RigNumber,
        TicketDateInt = Value(
            Text(
                dteDateFilterTLS.SelectedDate,
                "[$-en-US]yyyymmdd"
            )
        ) || dteDateFilterTLS.SelectedDate = Blank(),
        CompanyID = cmbCompanyFilterTLS.Selected.ID || cmbCompanyFilterTLS.Selected.ID = Blank(),
        LocationID = cmbLocationFilterTLS.Selected.ID || cmbLocationFilterTLS.Selected.ID = Blank()
    )
);
// Show the user a count of returned records
UpdateContext({Working: false})

While an If statement isn't delegatable, an Or statement does okay and works the same as my previous monster code. Thanks again for all the help on this. I greatly appreciate it!

And here is a heavily redacted screenshot of what I was unable to screenshare @KickingApps. The UI isn't final, but its getting along.

*edit for code comments that became irrelevant due to solving the issue*

View solution in original post

ericonline · ‎02-07-2019

Nice write up @wyotim. What is the data source?

wyotim · ‎02-07-2019

Oops! Should have mentioned that. Azure SQL DB is the data source.

ericonline · ‎02-07-2019

Here are a couple options. Not sure if they'll work for you, but might lead to a solution.

I have done something similar in the past (deleted the actual app during "cleanup", but the video is attached). Check out the 01:54 mark. The Filter() code is in there using a bunch of OR statements, might help.
1. **EDIT** unable to attach .mp4. PM me your email and I'll send it directly if you want.
If you can step users through filters sequentially, then try this. I was able to take a 60k record table down to 3 seconds of filtering (OnStart, Filter1 Select, Collect, Filter2 Select, Collect, Present) by doing this:
1. Create a SQL View of a distinct subset of data within the 500 limit (for tickets maybe something like "Reason Code" or "Reported By"
2. OnStart of the app, ClearCollect(colDistinctX, [dbo.mySQLView])
3. Set first Combobox/Dropdown Items property to: colDistinctX
4. Set first Combobox/Dropdown OnChange property to:
  1. ```
  ClearCollect(colNextFilter,
      Distinct(
          Filter(
              '[dbo].[entireSQLTABLE]',
              someColumn = ComboBox1.Selected.X
          ),
          nextColumn
      )
  )
```
5. Set second Combobox/Dropdown Items property to: colNextFilter
6. Repeat these steps to carve the entireSQLTable down to a digestable number of records (in your case 500)
7. Here is the forum post from last year if it helps.

Wish I could just show you the app, but its internal.

Hope something here helps, I know its longwinded!

wyotim · ‎02-07-2019

Wow! Thanks for the great response! I will see what I can come up with. The sequential step-through idea seems promising and the table I am using is about a tenth the size of yours and only grows by like 1,500 to 2,000 records a year (the joys of working for a smaller business!), so it should stay pretty manageable. And DM incoming with my email. Thanks again @ericonline! Always a pleasure!

wyotim · ‎02-07-2019

@ericonlineWhile an awesome idea, I realized that the sequential step-through idea unfortunately won't work in this case. I am going to tuck that one away for later though as it is a fantastic approach. And much appreciation for the video with the Or sequence too. Again, a great approach but one that won't work for this project unfortunately.

Due to this particular item being part research tool and part data entry system, I am going to need all or some or none of the filters in any combination or sequence. So it looks like I have some fun copy/paste/edit/repeat action planned for the afternoon!

Thanks again @ericonline! I appreciate it!

ericonline · ‎02-07-2019

My pleasure. Glad you were able to glean something from that brain dump!

Take care and good luck!
@tianaranjo might have a filter solution now that I think about it... those interesting filters in the app we were considering retiring today... might work for @wyotim?

KickingApps · ‎02-09-2019

@wyotim Let me explain what I have and tell me if this is along the same lines. The formulas are pretty extensive so I just want to make sure it is what you are looking for first. When app starts, user sees only their inputs. However, they can either search the whole database or filter. For my purposes, I use Or in my filter formula, but I have also tested with AND and it works fine.

wyotim · ‎02-12-2019

@KickingAppsApologies for the late reply; I had to leave the office unexpectedly on Thursday and am just now back at work. I would definitely be open to what you have if it isn't too much trouble to share it. Thanks a bunch!

KickingApps · ‎02-13-2019

@wyotim - no problem. Give me a few days. I have a high priority app project to get out by Friday so no time until then. Will try to get something to you this weekend.