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|
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.
Solved! Go to Solution.
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!
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.
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))
Hello @RandyHayes ,
I was able to figure it out. Here is what I came up with:
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?
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.
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?
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?
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?
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?