Cascading drop down list - Error message and design limitations of cascading drop down menus

[Anonymous]

I have 2 seperate lists in O365 and can connect to them with no issue.....The aim is that 1 lists contains cascading values from a left to right table etc....

However, when I connect the 2nd table and select the new source, I get a warning message to replace all my fields. If I select yes, I see the data only the 2nd table, if I select no and add, I get a red cross as if it doesnt recognise the 2nd table. Essentially, I am trying to "merge" 2 datasets and am familiar with the principles etc. However, I dont seem to be able to get both datasets into 1 pick list for the gallery....

Any advice? I have watched a ton of how to videos that all make sense and I "get it", but in practice, there must be  something I am missing? Any pointers?

I am also very confused about the "write back" of data to SharePoint and whether or not the extra code is needed in the update details to pass the values back to the live list from the drop down values list etc. 

[RandyHayes]

@Anonymous 

You will not be able to use two datasources for a DropDown.  Instead you can base your dropdown off of a collection that will be built using the two data sources.

So, for example, if you are trying to have the "Title" columns from two lists all presented in a drop down, you can add a formula such as this to an Action in your App (a button OnSelect, a OnStart, OnVisible, etc)

ClearCollect(colMergedList, 
          ShowColumns(dataSource1, "Title"),
          ShowColumns(dataSource2, "Title")
)

Then set the Items property for your dropdown to colMergedList  

I am not sure where you are going to with your closing question about "write back".  I assume you want to get the value of your dropdown back to the datasource in the Update property.  If that is the case, then (and assuming based on the above that the column you are "writing" to is a text column) your Update property should be your DropDown control .Value  (ex. DropDown1.Value)

I hope this is helpful for you.

[v-yuxima-msft]

Hi @Anonymous 

Could you please reference @RandyHayes 's suggestion?

And one dropdown can only has one datasource, you can firstly union the two tables and then use the unioned one as the datasource.

Could you please reference my reference of  this case?

https://powerusers.microsoft.com/t5/General-Discussion/Retrieve-and-Display-Data-From-Multiple-Sharepoint-lists-via-ID/m-p/324231#M93266

More information:

function-clear-collect-clearcollect

Best Regards.

Yumia

[Anonymous]

Hi - I appreciate the response, but just need to be ultra accurate here with what I am saying;

1.  Is the "receiving list" where the data is written to. This list will contain 2 drop down lists that are cascasding in nature
2. A list with 2 columns used in 1 above
3. A list with 3 columns used in 1 above

Also, when the "values" are taken from lists 2 & 3 columns, my expected behavior is that the values selected are held in list 1. These lists value selected from list 2 & 3 can then flow to PowerBI.

Not far off these actually....(not the update/write back bit of code needed to update SP)

http://aarohblah.blogspot.com/2018/01/how-to-use-cascading-drop-down-lists-in.html

https://collab365.community/o365-powerapps-cascading-dropdown-list-form-lookup-fields/

The confusion being, I create a Power App using SharePoint integration and pull in list 1, all ok. I see all my fields.

However, once I add the other data sources, it asks me to update the cards. If I do, I then only see the datacards per list and I dont seem to be eaily able to "merge" list 1 & 2 & 3 into one editiable form as it were. Am I being over ambitious or should it do this?