cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
caruso1058
Microsoft
Microsoft

Filtered Drop Down List based on Status of Item

Hello, 

I am building a Power App based on a SharePoint List. 

 

The list is meant to create a Product Family, i.e:

Product1Hardware TypeProcessor Type

Subcomponent1 

Subcomponent2

Subcomponent3

 

powerapp.pngOur current SharePoint List to populate this data works as we have a SharePoint List that contains all of the Subcomponents and we have a lookup field in the Product Family List for the indicated 4th column, where we can open the dropdown list and choose all of the necessary subcomponents. 

 

However, the list of subcomponents is 400+ items, some of which are are no longer used. 

We have a field on the Subcomponents List called 'Status' that indicates this. 

I am looking for a solution that allows us to filter the dropdown lookup field to only show Subcomponents where the Status Field is not Canceled or EoL. 

The issue I am running into is how to filter the Lookup field on the List the item is originating from and how to populate this drop down with multiple selections.

Any help is greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions

@RandyHayes,
I have a working Power App Form now.

After a few days of struggling with this Form I have abandoned most of what I have discussed previously in this thread. 

My goal was to create a Form that allowed you to create a product family: i.e. Name an item (parent) and choose all of the products (children) that will be included in the single Product Family. 

We track a long list of child products, and I have a SP lists that track each of the parent and child items. 

For this specific Form's use case, I needed to filter out a number of child items. I struggled with filtering the lookup column, then I struggled with populating the SP list. 

My Solution: I created a new Calculated Column in the child SP list to only display relevant child items:
=IF(Status="Production",[Model Number],IF(Status="Development",[Model Number],""))

Then, I created a new lookup field in the Parent SharePoint List to bring in the items being populated by the above column.

Then, I brought that field into the Power App Form, Filtered out the Blank Values with: 
Filter(Choices([@'L11 - PFAM List Duplicate'].Building_x0020_Blocks), Len(Value)>0) Filter(Choices([@'L11 - PFAM List Duplicate'].Building_x0020_Blocks), Len(Value)>0) 
AND then VOILA...my Form works as intended!

View solution in original post

11 REPLIES 11
RandyHayes
Super User
Super User

@caruso1058 

You will need to filter based on the Looked up list.

Set your DropDown items to a formula similar to this:

Filter(lookedUpList, !(Status in "Canceled|Eol"))

 This will give you the filtered list you want.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thank you for your help on this one @RandyHayes

 

One question on the ordering of the Filter Function. I can't seem to get the ordering to work:

Here is what I have but I keep getting an error:


Sort(Distinct(Filter('Building Blocks & Model Numbers','Model Number', !(Status in "Canceled|EoL")), Result))

 

Any pointers?

Hello @RandyHayes , 
I was able to figure it out. Here is what I came up with:

Sort(Distinct(
Filter('Building Blocks & Model Numbers', Status.Value = "Production" Or Status.Value = "Development"),
'Model Number'), Result)

 

However, now I have a seemingly more complex issue. Being that these dropdown items are children of the parent item (title in this SharePoint List). Do you know how I can choose multiple items from this dropdown in order to list them in the same SharePoint row?

@RandyHayes ,  I wrote too soon. I am using the list box to choose and display multiple items. 

 

However, now my items are not populating in my SharePoint List. I have the Update field set to: ThisItem.'Building Blocks2'
which is the name of the field in SharePoint however, when I fill this field out, no values populate in SharePoint.

Would you happen to have any tips for this?

 

Many thanks!

@caruso1058 

Sorry for delays...the forum notifications were not working properly yesterday - seems like they are now.

 

So, if your Update property of your datacard is set to be the value of the field (ThisItem.'Building Blocks2'), then you will never update anything.  Your Default property should be ThisItem.'Building Blocks2' and your Update property should be whatever control has the value you want to put into the datasource.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes , 
Not a problem at all and I greatly appreciate your help!

My Update Field is set to: ListBox3.SelectedItemsText

My Default Field is set to: ThisItem.'Building Blocks2'  (Receiving an Error on this item)

 

As you can see from the photo below, the Building Block2 field is custom field I created within the Power App.
I have since added a Multiline Text field in the SharePoint List in order to write data into that field from the Power App. But I am not having luck with any data being saved to that field. 

 

caruso1058_0-1603300441048.png

 

caruso1058_3-1603302298682.png

 

 

caruso1058_2-1603302269470.png

Is there something else I need to write in the Update field in order for the SP Column to accept the data?

 

@caruso1058 

Update is what it's all about for an EditForm!  Whatever is there is what goes to the datasource.

 

If your underlying column in your SharePoint list is text, then you will not be able to use ListBox3.SelectedItemsText (and actually should not be used as it is a deprecated property).  That property is a Table of selected Items.  You would need text.  You should be getting an expected text error in the Update property.

 

BUT...I'm going to stop you here for you to make a choice.  You originally were using a Combobox or DropDown control.  Now you are using a ListBox.  I just want you to be aware that if you use the listbox, you will never be able to display what someone had selected in a record, should you end up editing a record.  In other words, you cannot set the selections of a listbox.  It can only be done manually.  So if a user selected item1 and item 2 in a listbox - fine, you can write that to the datasource, but if you then go to view that record again, you cannot have the listbox display item1 and item2 as selected.

So...do you want to continue with a listbox?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes ,
Ahh! I did not realize these limitations regarding the list box. Thanks for the heads up on that!

I have changed the list box to a Combo Box and functionality seems to be working great (within the Power App).
But the hurdle for me is understanding the Update Function.

How would you advise getting this data to populate to my List?

caruso1058_0-1603308608711.png

 

@caruso1058 

The update property is the property that defines the value that SubmitForm will write to the datasource.

What is your current Update property on the datacard?  And what is the column name and column type in your SharePoint list?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (3,369)