cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

How to filter on a Column in SharePoint with Choices Attributes

Hello -

Asking for help as I have not found a solution to the following problem:  Trying to filter a gallery based on a column in a SP List:
The Column contains 6 values (Choices):
Value1
Value2

Value3
Value4

Value5
Value6

The intent is the following:  The Initial screen in PowerApps allows the user to filter records based on the menu across the top (Choices 1 - 7).  Choice "All" is the default value where all the records load.  The user click on a Choice (1-6) and the records then should filter based on that choice.  TabMenu field  is a TextBox that gets the value through the following:
OnSelect: Set(TabMenu, "Choice1....6")

The error received is "Invalid argument type. Cannot use Table values in this context"

The syntax looks like this:
Filter('SharePoint ListName', Choices('SharePoint List Name'.ColumnName).value in TabMenu)

The Column in SP has 6 choices, and only one can be selected when the record is created in SP.

See screenshot for how the initial screen looks like and the error when trying to filter on that column.
Thanks for ANY help you can provide!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: How to filter on a Column in SharePoint with Choices Attributes

Hi @fcaldero ,

Is the Column single choice type?

Could you show me the setting of this field? Is its choice :"Choice1","Choice2",...."Choice6"?

Please set textbox1's OnSelect: 

Set(TabMenu, "Choice1")

 textbox2's OnSelect: 

 

Set(TabMenu, "Choice2")

....

textbox6's OnSelect: 

 

Set(TabMenu, "Choice6")

 

 

 

 

Best regards,

 

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: How to filter on a Column in SharePoint with Choices Attributes

I believe you are getting an error because you are asking PowerApps to find a Table within a Number type variable.

Instead of using this code::
Choices(‘SharePoint List’, ‘Column Name’).Value in TabMenu

I would advise using this code instead:
‘Column Name’.Value = TabMenu

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.
Highlighted
Community Support
Community Support

Re: How to filter on a Column in SharePoint with Choices Attributes

Hi @fcaldero ,

Firstly, let me explain the reason why you can not get right result:

Choices('SharePoint List Name'.ColumnName) will get a table of the choice columns' choices like this:

Value      ID

choice1   1

choice2   2

choice3   3

choice4   4

choice5   5

choice6   6

It will not get the choice column's value. So please do not filter based on this.

Choice columns' value 's structure is like this:

{Value:....,ID:....}

So if you want to compare this with a text, you need to use formula like this: fieldname.Value

Please try this formula:

set the TextBoxes' OnSelect:

Set(TabMenu, "Choice1....6")

set the all textboxes' OnSelect:

Set(TabMenu,"all")

set the gallery's Items:

If(TabMenu="all",'SharePoint ListName',Filter('SharePoint ListName', ColumnName.Value = TabMenu))

 

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: How to filter on a Column in SharePoint with Choices Attributes

Thanks for the explanation and suggestions @v-yutliu-msft.  Following your suggestions yields this error:  "Invalid Argument Type" after using the code in gallery's item.
If I switch the operator to 'exactin' or 'in', then the ColumnName.Value  gets the red squiggly line indicating "Invalid Argument Type. Cannot use table values in this context".  Below is how the code was entered:

 

If(TabMenu="All", ‘SP List',Filter('SP List',ColumnName.Value = TabMenu)) <--- Invalid argument type

 

If(TabMenu="All", ‘SP List',Filter('SP List',ColumnName.Value in TabMenu)) <-- Cannot use tables values in this context


Thanks for your continued help!


Highlighted
Community Support
Community Support

Re: How to filter on a Column in SharePoint with Choices Attributes

Hi @fcaldero ,

Is the Column single choice type?

Could you show me the setting of this field? Is its choice :"Choice1","Choice2",...."Choice6"?

Please set textbox1's OnSelect: 

Set(TabMenu, "Choice1")

 textbox2's OnSelect: 

 

Set(TabMenu, "Choice2")

....

textbox6's OnSelect: 

 

Set(TabMenu, "Choice6")

 

 

 

 

Best regards,

 

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: How to filter on a Column in SharePoint with Choices Attributes

See attachment @v-yutliu-msft .  The Column is a choice-type of field, with the choices displayed as radio button.  I changed the Display Choices Option to "Radio Buttons" thereby allowing only 1 choice, NOT Multiple Selections.   Once this was changed, the code you provided works!   Thanks for your input!

btw - what is the solution (Code to use) when this type of field allows the user to select multiple options?

 

Thanks again.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,158)