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.
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)
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.
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?
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.
Good you make separate lists for the lookups, this will help you in the futher.
What you should do:
On the ComboBox.Items
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:
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:
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.
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.".
Continue your learning in our online communities.
Tomorrow, September 18th at 8am PDT
Features releasing from October 2019 through March 2020
Coming to a city near you
Fill out a quick form to claim your user group badge now!
Connect, share, and learn with your peers year-round
Register by September 5 to save $200