Filtering a gallery by a choice field in a collection

(2) Share

Report

Posted on by DMSC

Hi all,

I have a gallery that is pulling a collection of data (from a SharePoint list).

I am trying to filter the data based on status via a check box.

Check box default & OnCheck property is as follows:

Set(
    showRequestStatusUser,
    Filter(
        Collection,
        Status.Value <> "Complete"
    )
)

The UnCheck property is:

Set(
    showRequestStatusUser,
    Filter(
        Collection,
        Status.Value = "Complete"
    )
)

On the items property of my gallery I have the following filters (keeping in mind I have a text input search bar, and a year drop down, and a further user logged in checkbox) to refine the data, all of which is currently firing as expected:

Filter(
    Collection,
    (TextInput.Text in 'Document Code' Or 
    (TextInput.Text in 'Document Name' Or 
    TextInput.Text in ID) || IsBlank(TextInput.Text)),
    Requestor.DisplayName = showUserValidation || IsBlank(showUserValidation),
    (Len(DropdownYear.Selected.Value) = 0 || ReportingYear = DropdownYear.Selected.Value)
)

I have tried to add the following line into the above filter

Status.Value = showRequestStatusUser || IsBlank(showRequestStatusUser),

However, I am running into the old "data incompatible table, text" issue. I have tried a few things, but I am stumped. Can anyone help me fix the code?

Thanks,

Categories:

General Questions

All responses (7)

Answers (1)

LessCodePaths 100 on at

Like (0)

Report

Filtering a gallery by a choice field in a collection

DMSC
I'm glad it helped you to resolve the issue.

Could you mark my reply as solution?

I would make my day better :)

P.S. be aware of delegation...

L.
WarrenBelz 146,524 Most Valuable Professional on at

Like (0)

Report

Filtering a gallery by a choice field in a collection

I cannot see your data and can only be guided by the extent of the information you post Value suggests a choice or lookup column - it is obviously text
Verified answer

DMSC 59 on at

Like (0)

Report

Filtering a gallery by a choice field in a collection

LessCodePaths

This worked perfectly, thank you!!

And to answer your question, the collection is the datasource (trying to keep the app running as fast as possible).

Thanks again.

DW
DMSC 59 on at

Like (0)

Report
Filtering a gallery by a choice field in a collection

Thanks WarrenBelz

I tried your solution, however, the .Value in the first part of the code is still throwing the same error ("can't convert data type, can't convert this text to record")

Status.Value in showRequestStatusUser.Status || IsBlank(showRequestStatusUser)

DW
IAmIntractable 245 on at

Like (0)

Report
Filtering a gallery by a choice field in a collection

This might help. I made explicit all the ands and ors: Filter( Collection, ( TextInput.Text in 'Document Code' Or TextInput.Text in 'Document Name' Or Value(TextInput.Text) = ID Or IsBlank(TextInput.Text) ) And ( Requestor.DisplayName = showUserValidation Or IsBlank(showUserValidation) ) And ( Len(DropdownYear.Selected.Value) = 0 Or ReportingYear = DropdownYear.Selected.Value ) ) ID is a number.
WarrenBelz 146,524 Most Valuable Professional on at

Like (0)

Report
Filtering a gallery by a choice field in a collection

Hi DMSC

Your issue is that showRequestStatusUser is a Table and you are trying to compare a Text string with it. You also need the field name in the Table you are wanting to compare so
Status.Value in showRequestStatusUser.YourFieldName || IsBlank(showRequestStatusUser)

Please click Does this answer your question 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 a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee
LessCodePaths 100 on at

Like (0)

Report
Filtering a gallery by a choice field in a collection

If I understand correctly, you would like to add

Status.Value = showRequestStatusUser || IsBlank(showRequestStatusUser)

as the next if statement to the filter.

Based on formulas defined on the top showRequestStatusUser is a table, so u can't compare it with column Status.Value which is text.

It might become
Status.Value in showRequestStatusUser.Status || IsBlank(showRequestStatusUser),

where showRequestStatusUser.Status is column corresponding to the text column containing Status value.
I'm not exactly sure, because I don't know structure of the Collection.
I don't understand why Collection is in all the statements. Is it the came datasource?

If yes, I thing it could be rewritten as

(Status.Value<>"Complete" && CheckBox.Value) || (Status.Value="Complete" && !CheckBox.Value)