cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fcaldero
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

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
mdevaney
Super User III
Super User III

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.
v-yutliu-msft
Community Support
Community Support

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.

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!


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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (59,310)