Filter Gallery with Checkbox

[Anonymous]

Hi All,

I am struggling with a filter issue within a gallery. I have a radio selection and search box, which allow the user to filter by OrderStatus, and further search by text (eg id, location etc). Every record is either a "VOR" or "Confirmation" request.

What I am after is a checkbox, which when checked, shows only VOR requests in the galley, but also applies the above filtering. Can anyone advise how to do this; I suspect its something simple which I've missed, but I can't get anything to work.

My code is below.

Thanks,

Chris

------

SortByColumns(If

(Radio1_1.Selected.Value="New",Filter(Orders,StartsWith(Title,TextInput2_26.Text)||StartsWith(Requester,TextInput2_26.Text)||StartsWith(FleetNu,TextInput2_26.Text)||StartsWith(OrderRefDup,TextInput2_26.Text)||StartsWith('Created By'.DisplayName,TextInput2_26.Text),OrderStatus="New"),

Radio1_1.Selected.Value="PO Created",Filter(Orders,StartsWith(Title,TextInput2_26.Text)||StartsWith(Requester,TextInput2_26.Text)||StartsWith(FleetNu,TextInput2_26.Text)||StartsWith(OrderRefDup,TextInput2_26.Text)||StartsWith('Created By'.DisplayName,TextInput2_26.Text),OrderStatus="Purchase Order Created"),

Radio1_1.Selected.Value="On Hold",Filter(Orders,StartsWith(Title,TextInput2_26.Text)||StartsWith(Requester,TextInput2_26.Text)||StartsWith(FleetNu,TextInput2_26.Text)||StartsWith(OrderRefDup,TextInput2_26.Text)||StartsWith('Created By'.DisplayName,TextInput2_26.Text),OrderStatus="On Hold"),

Radio1_1.Selected.Value="In Progress",Filter(Orders,StartsWith(Title,TextInput2_26.Text)||StartsWith(Requester,TextInput2_26.Text)||StartsWith(FleetNu,TextInput2_26.Text)||StartsWith(OrderRefDup,TextInput2_26.Text)||StartsWith('Created By'.DisplayName,TextInput2_26.Text),OrderStatus="In Progress"),

Radio1_1.Selected.Value="Cancelled",Filter(Orders,StartsWith(Title,TextInput2_26.Text)||StartsWith(Requester,TextInput2_26.Text)||StartsWith(FleetNu,TextInput2_26.Text)||StartsWith(OrderRefDup,TextInput2_26.Text)||StartsWith('Created By'.DisplayName,TextInput2_26.Text),OrderStatus="Cancelled"),

Radio1_1.Selected.Value="Completed",Filter(Orders,StartsWith(Title,TextInput2_26.Text)||StartsWith(Requester,TextInput2_26.Text)||StartsWith(FleetNu,TextInput2_26.Text)||StartsWith(OrderRefDup,TextInput2_26.Text)||StartsWith('Created By'.DisplayName,TextInput2_26.Text),OrderStatus="Completed"),

Filter(Orders,StartsWith(Title,TextInput2_26.Text)||StartsWith(Requester,TextInput2_26.Text)||StartsWith(FleetNu,TextInput2_26.Text)||StartsWith(OrderRefDup,TextInput2_26.Text)||StartsWith('Created By'.DisplayName,TextInput2_26.Text))),"Modified",Descending)

[v-xida-msft]

Hi @Anonymous ,

Based on the needs that you mentioned, I think there is something wrong with it. I have made a test on my side, please consider modify your formula as below:

SortByColumns(          
             Filter(
                    Orders,
                    StartsWith(Title,TextInput2_26.Text) || StartsWith(Requester,TextInput2_26.Text) || StartsWith(FleetNu,TextInput2_26.Text) || StartsWith(OrderRefDup,TextInput2_26.Text) || StartsWith('Created By'.DisplayName, TextInput2_26.Text),
                  If(
                     Radio1_1.Selected.Value="New",
                     OrderStatus="New",
                     Radio1_1.Selected.Value = "PO Created",
                     OrderStatus="Purchase Order Created",
                     Radio1_1.Selected.Value="On Hold",
                     OrderStatus="On Hold",
                     Radio1_1.Selected.Value="In Progress",
                     OrderStatus="In Progress",
                     Radio1_1.Selected.Value="Cancelled",
                     OrderStatus="Cancelled",
                     Radio1_1.Selected.Value="Completed",
                     OrderStatus="Completed"
                  )
             ),
             "Modified",
              Descending
)

If you also want to add the Checkbox filter to above formula, please consider modify your formula as below:

SortByColumns(          
             Filter(
                    Orders,
                    StartsWith(Title,TextInput2_26.Text) || StartsWith(Requester,TextInput2_26.Text) || StartsWith(FleetNu,TextInput2_26.Text) || StartsWith(OrderRefDup,TextInput2_26.Text) || StartsWith('Created By'.DisplayName, TextInput2_26.Text),
                  If(
                     Radio1_1.Selected.Value="New",
                     OrderStatus="New",
                     Radio1_1.Selected.Value = "PO Created",
                     OrderStatus="Purchase Order Created",
                     Radio1_1.Selected.Value="On Hold",
                     OrderStatus="On Hold",
                     Radio1_1.Selected.Value="In Progress",
                     OrderStatus="In Progress",
                     Radio1_1.Selected.Value="Cancelled",
                     OrderStatus="Cancelled",
                     Radio1_1.Selected.Value="Completed",
                     OrderStatus="Completed"
                  ),
                  If(                                // Add formula here
                     Checkbox1.Value = true,
                     RequestType = "VOR",   // I assume you use RequestType column to store the "VOR" or "Confirmation" value
                     true
                  )
             ),
             "Modified",
              Descending
)

If the RequestType column is Choice type column, please consider modify above formula as below:

SortByColumns(          
             Filter(
                    Orders,
                    StartsWith(Title,TextInput2_26.Text) || StartsWith(Requester,TextInput2_26.Text) || StartsWith(FleetNu,TextInput2_26.Text) || StartsWith(OrderRefDup,TextInput2_26.Text) || StartsWith('Created By'.DisplayName, TextInput2_26.Text),
                  If(
                     Radio1_1.Selected.Value="New",
                     OrderStatus="New",
                     Radio1_1.Selected.Value = "PO Created",
                     OrderStatus="Purchase Order Created",
                     Radio1_1.Selected.Value="On Hold",
                     OrderStatus="On Hold",
                     Radio1_1.Selected.Value="In Progress",
                     OrderStatus="In Progress",
                     Radio1_1.Selected.Value="Cancelled",
                     OrderStatus="Cancelled",
                     Radio1_1.Selected.Value="Completed",
                     OrderStatus="Completed"
                  ),
                  If(                                // Add formula here
                     Checkbox1.Value = true,
                     RequestType.Value = "VOR",   // Modify formula here. Type RequestType.Value
                     true
                  )
             ),
             "Modified",
              Descending
)

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[Anonymous]

Thanks @v-xida-msft ,

The second code you mentioned has worked, but the If statement is giving a delegation warning. Are you able to advise a way around this?

Thanks,

Chris

[v-xida-msft · Accepted solution]

Hi @Anonymous ,

How many records stored in your data source? More than 2000?

Firstly, the Delegation warning issue is not an error, it just means that you could not delegate the data process to your data source, instead, you could only process data locally. In default, you could only process 500 records locally at most. You could change this limit to maximum value -- 2000, then you could process 2000 records locally at most.

More details about Delegation in PowerApps, please refer to the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

If the amount of your data source records is not more than 2000, you could ignore this warning issue. If the amount of your data source records is more than 2000, you could consider bulk-load data source records into a collection in your app, then use the collection as data source in your app.

Please check and see if the alternative solution mentioned in the following threads would help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Pulling-in-large-ish-SQL-tables/m-p/243777#M71518

Best regards,

[Anonymous · Accepted solution]

@v-xida-msft  Thank you I will take a look at that, worse-case I can keep the delegation and work around it.

Thanks,

Chris

[v-xida-msft]

Hi @Anonymous ,

Is the solution I provided above helpful in your scenario?

If the solution I provided above is helpful in your scenario, please consider go ahead to click "Accept as Solution" identify my solution as helpful solution.

Best regards,

[Anonymous]

Hi @v-xida-msft ,

Yes it has worked, thank you very much

Chris