Filtered Drop Down List based on Status of Item

[caruso1058]

Hello, 

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

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

Product1

Hardware Type

Processor Type

Subcomponent1  Subcomponent2 Subcomponent3

Our 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.

[RandyHayes]

@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.

[caruso1058]

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?

[caruso1058]

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?

[caruso1058]

@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!

[RandyHayes]

@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.

[caruso1058]

@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. 

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

[RandyHayes]

@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?

[caruso1058]

@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?

[RandyHayes]

@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?