Filtering using a toggle button

[jeremywoakes]

Hello

I have a simple app linked to an excel document. Excel docuement attached.

One of the columns (column G) in the excel spreadsheet is the status of an item which is either "Open" or "Closed". I would like to filter records shown in in the app when a field in one of the excel columns is "Open".  I'd like to use a toggle button as the control. I think I use an IF statement but not sure how to do it.

Any help would  be appreciated.

[WarrenBelz · Accepted solution]

Hi @jeremywoakes ,

Something like (assuming the table and toggle name here)

Filter(
   Table1,
   'Open/Closed' = 
   If(
      Toggle1.Value,
      "Open",
      "Closed"
   )
)

 where the "on" is Open and the "off" is closed.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[poweractivate · Accepted solution]

@jeremywoakes 

First I recommend strongly change the name of the column to OpenClosed  in Excel File, delete and recreate the data source. There may be a problem if having the Slash / in column name.

Then, try something like this and it should work:

1) Place a data table on the canvas to test it. Suppose it is called DataTable2  for this example.

1b) Select a couple fields so you can see the data table columns - to do this click on "Edit Fields" on the right side, and select some fields you want - such as OpenClosed field and Client field for example. If having trouble, go to the tree view, select DataTable2 first from the tree view, then try again from the right side.

2) In Items Property of the data table (suppose it is called DataTable2  for this example) put this: 

Filter(Table1,OpenClosed=VarOpenClosed)

3) Place a Toggle property on the canvas. Suppose it is called Toggle1 for this example.

4) (Optional) Regarding Toggle1 - > for the Properties:

 TrueText

put "Open"

and

FalseText

put "Closed"

5) For Screen1 (the first screen) OnVisible property (the OnVisible property of Screen1) put this:

Set(VarOpenClosed,"Open")

6) For  the Default property of Toggle1 put

true

7) For OnChange property of Toggle1 

put

If(Self.Value=true,Set(VarOpenClosed,"Open"),Set(VarOpenClosed,"Closed"))

😎 😎 Try the app.

I have also attached a working sample msapp file pl2_8-11-2021.msapp if you would like to import a full, working example yourself for your convenience.

To use the sample msapp attached, follow these steps:

1) Download the msapp file attached to this post, to Desktop or a folder of your choice, by clicking on it from this post.

2) Create a new, blank Power App Canvas App

3) Go to File -> Open -> Browse

4) Navigate to location of .msapp file from Step 1, select it, and press the "Open" button.

5) The working example msapp file should load

6) Note, the msapp file will come in without the connector (intentionally). You'll need to set up the Excel Online (business) connector and hook it to your sample excel file to see the sample app I made fully working

7) After step 6, you can try previewing the app and using the Toggle, and it should show Open cases only, or Closed cases only.

Check if it helps.

[Irfan · Accepted solution]

Hi,

OnCheck of your toggle button:

ClearCollect(collAllItems,Filter(Table1,'Open/Closed'="Open"))

OnUnCheck of your toggle button:
ClearCollect(collAllItems,Filter(Table1,'Open/Closed'="Closed"))

In your gallery/datatable, use the the collection collAllItems as your source

Open

Closed

Please mark it as solved if this solves your issue.

Thanks.

-Irfan

[jeremywoakes · Accepted solution]

Thank you very much, greatly appreciated.

[jeremywoakes · Accepted solution]

Wow, incredible. Thank you very much.