Edit form multiple data source

[AB4N]

Hi,

I currently have an app with 2 screens and 2 sharepoint lists as my datasources. The lists are identical (but for our different departments).

On my first screen a drop down allows the user to change department - which changes the gallery to display the items from the chosen datasource. The user can then select the item, and move to the edit screen.

The edit screen - edit form will also change depending on which item the user selected on the home screen.

This all works well - however, the datasource for the edit forms will not change. I have tried a few different formulas but it errors or does not work.

This means when a user selects a department, selects an item, then moves to the edit screen, it retrieves all the correct information from the correct datasource (items are gallery1.selected), but the datasource for the edit form will not change to reflect the choice made on the initial dropdown, so an error occurs when trying to submit the form.

I have tried to change the datasource on the edit form to this:

If(Dropdown1.Selected.Value="X",Department1List,
Dropdown1.Selected.Value="Y",Department2List)

But that still errors.

Any help would be greatly appreciated!

[mdevaney · Accepted solution]

@AB4N 

I believe you are having difficulties changing the List because PowerApps Forms can only be bound to a single datasource.  The form cannot be conditionally bound to multiple datasources.  I will suggest 2 alternate design options to work around this in your app.

Option #1 

Combine the 2 Sharepoint lists into 1 list.  Add an extra column called Department and fill-in with the values Department1 or Department2. This can be determined using the Office 365 connector and looking at the employee's profile OR using the dropdown control.  To ensure employee's can only view/edit their own department's data enable Item level permissions in Sharepoint and restrict access.  Filtering of data should also take place within the app.

Option #2 

Bind the Edit form to a Collection instead of DepartmentList1 or DepartmentList2.  The collection should have all of the same fields as DepartmentList1&2.  Once the form is completed, instead of using the code Submit(EditFormName) in a button to Submit you'll need to PATCH the data instead using some code like Patch(myCollection,Defaults(myCollection),{field1:999, field2:"sometext"});

My personal preference would be for Option #1.  I think it would be easier to implement/maintain in the long run.  Of course, you should do whichever you feel most comfortable with and what works best your own situation.  Let me know if you have any general questions.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[AB4N]

Hi, that's great thank you! 

I chose your option of adding a department column and merging into one list. However, I am now finding it difficult to use a text input and drop down to filter these results, my previous method does not seem to work with this. 

I have managed to filter the results with this formula

If(Dropdown2.Selected.Value = "X", Filter(List, Department.Value = "X"),
Dropdown2.Selected.Value = "Y", Filter(List, Department.Value = "Y"), List)

But I am struggling to add a text input to this, to allow users to search from the items that have now been refined by the department dropdown.

Any suggestions?

Thank you for all your help!

[GrantWilliams]

I have to vent.

Unfortunately, the more I investigate, the more I am finding limitation after limitation with this product.

There are some basic things that you would think are possible like connecting to an Excel spreadsheet in your OFFICE 365 sharepoint site.  AND YOU CANT.

And binding a control to a datasource other than the forms data source.

AND YOU CANT.

Then try connecting to SQL.  Oh no, you need a special "premium licence" for that.  

Then try and find the correct licence you need to get at this "special" service.  

It is a ridiculous maze of licencing rubbish that requires a masters degree.

Starting to think this is really a very dinky approach to providing "customers" with tools to develop sophisticated apps.

I'm going back to building apps from scratch in HTML with Node.js and VS Code.

Goodbye PowerApps

[WebWmn53]

Not sure if you're using a gallery to filter, but check out Reza Dorani's video. It works. https://youtu.be/bujH2gzTduQ

If not using a gallery, check out his other videos, he's brilliant.