cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DangerousBrian
Level: Powered On

Filtering choices that are populated from a sharepoint lookup.

Hi, 

 

I'm pretty new to powerapps & pretty much anything related to programming to be honest.

 

I've got 2 sharepoint lists, one is a list of people that have recieved an induction. & a 2nd list that is used for my powerapp which is a signing in system.

 

The sign-in list uses a lookup of the person_name in the induction list, this all works fine without a problem. But now i need to filter the choices in the person combobox based on another lookup field that is in the induction list.

 

sign-in.JPG

The two fields circled above are the 2 lookups from the induction list in sharepoint, the "red_card" field on the right is always = to "OK" unless it is manually removed.

 

Below is a screenshot of my filter that i have attempted, i only want to display names where the red_card_status is OK.

the error is "Wrong column type, expects text type"

Filter.JPG

 

I have literally been going around in cirlcles with this and it's driving me mad. 

 

Thanks,

 

Bri 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filtering choices that are populated from a sharepoint lookup.

Hi @DangerousBrian,

Could you please share more details about the data structure of your two SP lists (Induction list and Sign In list)?

Could you please share a bit more about the "RED_CARD_STATUS" column? Is it a Single line text column in your Induction list?

Based on the formula that you provided, I think there is something wrong with it. When you filter the Choices in the Combo Box which connects to the Person_Name column (LookUp column), you could only reference values from the Name column in your Induction list (where Person_Name reference values from), you could not reference values from the RED_CARD_STATUS column in your Induction list.

I have made a test on my side, please take a try with the following workaround:

The data structure of Induction list and SignIn list as below:4.JPG

 

5.JPG

App's configuration as below:3.JPG

Unlock the Person_Name Data card within the Edit form, set the Items property of the Combo Box control to following:

Filter(
Choices('20181225_case4_SignIn'.Person_Name),
Value in Filter('20181225_case4_Induction',RED_CARD_STATUS = "OK").UserName
)

On your side, you should type:

Filter(
Choices('Contractor Sign-In'.Person_Name),
Value in Filter('YourInductionList', RED_CARD_STATUS = "OK").ReferencedName
)

Note: The RED_CARD_STATUS column and ReferencedName column are both columns in your Induction list. The Person_Name column in your SignIn list ('Contractor Sign-In') references values from the ReferencedName column in your Induction list.

Please check the following GIF screenshot for more details:

 

More details about the Filter function in PowerApps, please check the following article:

Filter function

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Highlighted
Super User
Super User

Re: Filtering choices that are populated from a sharepoint lookup.

Choice and Lookup type columns are embedded tables usually with an ID and value. You must add .Value to the column name to see the record.
DangerousBrian
Level: Powered On

Re: Filtering choices that are populated from a sharepoint lookup.

Hi 

 

I'm not sure i understand, I've already got the .value in there when adding the filter. like below:

 

"Filter(Choices('Contractor Sign-In'.Person_Name,'Person_Name:RED_CARD_STATUS'.Value="OK"))"

 

Thanks,

 

Bri

Community Support Team
Community Support Team

Re: Filtering choices that are populated from a sharepoint lookup.

Hi @DangerousBrian,

Could you please share more details about the data structure of your two SP lists (Induction list and Sign In list)?

Could you please share a bit more about the "RED_CARD_STATUS" column? Is it a Single line text column in your Induction list?

Based on the formula that you provided, I think there is something wrong with it. When you filter the Choices in the Combo Box which connects to the Person_Name column (LookUp column), you could only reference values from the Name column in your Induction list (where Person_Name reference values from), you could not reference values from the RED_CARD_STATUS column in your Induction list.

I have made a test on my side, please take a try with the following workaround:

The data structure of Induction list and SignIn list as below:4.JPG

 

5.JPG

App's configuration as below:3.JPG

Unlock the Person_Name Data card within the Edit form, set the Items property of the Combo Box control to following:

Filter(
Choices('20181225_case4_SignIn'.Person_Name),
Value in Filter('20181225_case4_Induction',RED_CARD_STATUS = "OK").UserName
)

On your side, you should type:

Filter(
Choices('Contractor Sign-In'.Person_Name),
Value in Filter('YourInductionList', RED_CARD_STATUS = "OK").ReferencedName
)

Note: The RED_CARD_STATUS column and ReferencedName column are both columns in your Induction list. The Person_Name column in your SignIn list ('Contractor Sign-In') references values from the ReferencedName column in your Induction list.

Please check the following GIF screenshot for more details:

 

More details about the Filter function in PowerApps, please check the following article:

Filter function

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DangerousBrian
Level: Powered On

Re: Filtering choices that are populated from a sharepoint lookup.

Hi Kris, 

 

Thanks very much, that did the trick! I still don't massively understand the reason for two filters etc but as long as it works, i can try and understand it later! Smiley Happy 

 

Thanks,

 

Bri 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 118 members 4,653 guests
Please welcome our newest community members: