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:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

LessCodePaths 100 on at

Like
a
(0)

Report
Copy link

Link copied!
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)

Was this reply helpful? Yes No
WarrenBelz 156,117 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
IAmIntractable 280 on at

Like
a
(0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
DMSC 65 on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Verified answer

DMSC 65 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
WarrenBelz 156,117 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
LessCodePaths 100 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No