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

Cascading SharePoint Dropdown Filter Statement

Hi Folks,

 

I have a SharePoint list with 2 DropDowns, one is a prepoulated list of Company Names and the other is a prepopulated list of Employees.  I'm wanting to filter the list of employees based on the company name selected.

 

On DataCardValue for Employees this is the statement I'm working with:

Filter(Choices('IST Student Check In and Out'.Employee),Company.Value)

 

The errors are "Invalid argument type (Text). Expecting a Boolean value instead." and "The function "Filter' has some invalid arguments."

 

I watched Shane Young's video on cascading dropdowns, I'm getting the syntax or something is different since I'm using a SP list and not an Excel workbook.

6 REPLIES 6
KroonOfficeSol
Level 10

Re: Cascading SharePoint Dropdown Filter Statement

@trishmalloy

 

Are those fields both choice fields? Or are this lookups to other lists?

Best  this should be separate SP list with a LookUp to the master SP List. To the Master SP List also add a CompanyID (number) field and store the flat ID here also from PowerApps, this to overcome delegation issues.

 

When I look at your Filter() formula this can't work because you haven't got a valid condition in the second part.  That's why you get the error on returning text instead off a boolean, the condition should return a boolean you see. Should be more like this:

Filter(Choices('IST Student Check In and Out'.Employee),
	Company.Value = FirstDropDown.SelectedItems.Company)

or

Filter(Choices('IST Student Check In and Out'.Employee).Company,
	Value = FirstDropDown.SelectedItems.Company)

Also I would change the dropdowns to Combobox controls. They work better in my opinion.

 

Hope this helps.

 

Paul

trishmalloy
Level: Powered On

Re: Cascading SharePoint Dropdown Filter Statement

Hi KroonOfficeSol,

 

Yes, they are (were) both choice fields.  I'm not for sure what you were saying in the italics text below.  What should be a separate list?  After reading this I created a separate list with 1 lookup to Employee list and using the Filter statement below with no luck and then creating an entirely separate list with 2 lookup fields, one for Company and one for Employee.  That didn't seem to solve the problem either, I just got a different error message, "The columns produced by this rule are all nested tables and/or records ..."  Sorry for being dense, I'm new to PA.  Can you give me a little more direction, please?

trishmalloy
Level: Powered On

Re: Cascading SharePoint Dropdown Filter Statement

I'm hoping someone can help me out with this.  I have since done some more research and based on that created 3 lists; one called Table_Company with 2 fields Company_ID (Number field) and Field_Company (Single Line of Text (SLOT) field), the second list is called Table_Employee and has one field called Field_Employee (SLOT).  There is a 3 list called Master_Table_Company_and_Employee with 2 SLOT fields named MasterField_Company and_MasterField_Employee.  I'm using the Master, Table and Field in the names to help me keep things straight. 

 

From MasterTable_Company and_Employee I created a new PowerApps app from the list in SharePoint.  From Edit form I removed the SLOT fields from MasterField_Company and MasterField_Employee and created 2 Dropdowns to replace them.  I set the Items property of Table_Company.Field_Company and did the same except with the Employee dd except it's Items property is set to Table_Employee.Field_Employee.

 

I need help with a few things:  a)  Do I have the 3 lists and fields set up correctly? b) I can't seem to get the Filter statement on the MaterField_Employee right.  I want to be able to filter employees based on company selected by the user.

KroonOfficeSol
Level 10

Re: Cascading SharePoint Dropdown Filter Statement

@trishmalloy

 

Good you make separate lists for the lookups, this will help you in the futher.

What you should do:

 

On the ComboBox.Items

ShowColumns(Table_Company, "Field_Company","Company_ID")

Set the multiple selection off, so users can use only one company. This will pull in the records from your lookup in the ComboBox.

 

 

Now on the DataCard.Update you connect the combobox to the datacard. You can try this:

 

ComboBox.SelectedItems

Or if that doesn't work then this:

LookUp(MasterField_Company, ID = ComboBox.Selected.ID)

Both basically gives back an object the complex lookup field needs to write back to your SharePoint list.

 

 

For the Field_Employee you have to add an ID and CompanyID field. Then you do the same as on the Table_Company only put in a filter in the .Items attribute. Like

ShowColumns(Filter(Table_Employee, CompanyID = Combobox1.Selected.ID), "Field_Employee ","ID","CompanyID")

Do here also the changes to the .Update attribute off the datacard.

What I mean by adding an number field with just the ID, this is to filter your master list on later without the delegation issues you have on filtering off a complex field. So just add a number field called CompanyID and EmployeeID. You then add them to your form, connect them to your combobox. So for  CompanyID the formula is:

Combobox1.Selected.ID

And then hide them. The form will be able to write this field to the Sharepoint list then.

 

For when you expect your companies to exceed 2000 records in the future, you also should add a active or status field. This should also be a number field. The active is 0 or 1 as possible values (And yes 0 is false and 1 is true).  This you can use then to filter the items in your combobox. 

 

Hope this explanation is clear and possible to digest.

 

Paul

trishmalloy
Level: Powered On

Re: Cascading SharePoint Dropdown Filter Statement

Hello KroonOfficeSol,

 

I'm stuck on the DataCard.Update step.  If I set the Update property of the DataCard to ComboBox4.SelectedItems, I get this error message, "The property expects Text values, but this rule produces incomopatile Record values.".  If I use the LookUp statement, I get this error message, "Invalid argument type.".

 

Trish

 

 

KroonOfficeSol
Level 10

Re: Cascading SharePoint Dropdown Filter Statement

Trish

Take Combobox4.selected and place a dot behind it. Then chose the attribute the datacard expects. This will return text.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Tomorrow, September 18th at 8am PDT

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: 110 members 3,773 guests
Please welcome our newest community members: