Multiple data sources for single form

[Special_K]

Hi there, 

I'm working on a PowerApp to submit and track our order escalations. I'm using SharePoint as my main data source though I do have a couple of static Excel tables and a collection from a second SharePoint list pulling into the app as well. 

I'm stuck on the 'New Escalation' form. I have a couple of similar issues that seem to be stemming from my need to use multiple data sources to write back to one SharePoint list:

1) I need to build cascading drop downs for Root Cause 1 and Root Cause 2 to eliminate incorrect combinations being used (one of the major pain points of our current process). I have been able to build the cascading drop downs without any issues (I've done it in a variety of ways). However, the only way I have been able the get the selected Root Cause to write back to SharePoint:

    -SharePoint column MUST be single line of text (I wanted to use a choice column but this returns blank in SharePoint even though the form submits successfully and I have gotten choice columns to work in other instances - radio buttons replacing a drop down for example)

    -Insert a visible combobox or dropdown within the DataCard & tie it to the static Excel table with the options

    -Have the original single line of text DataCardValue default to the selection from the combobox/dropdown and make the single line of text box invisible to the end user

I have tried many things to get this to populate and this is the only solution I could get to work. I wish I could just tie the original SharePoint field directly to the Excel list or at least have it write back to the SharePoint column by having the DataCard Update the column using the combobox/dropdown selection. Is there any way to simplify this? I can leave it as is, if I have to. It works. It's just clunky as heck. 

2) The trickier issue is around populating the team responsible for the escalation. There are 6 fields for this. Ideally, I want to auto populate them based on the person logged in and their role but this has slowed down the app immensely so I've resorted to filtering the drop downs to the people in those 6 roles for the time being. 

I can't use the Office365 directory for this because ours is wildly inaccurate and I'm not just looking for direct relationships. We have an alignment SharePoint list (the secondary list I'm using) that has all this information. However, I'm running into the same issue as above with the cascading dropdowns. If I use this list to provide the Items for the dropdown, it will not write back to SharePoint even though the form submits successfully. I've pulled that list in as a collection but I'm not sure if that's playing a factor or not at this point. I really do not want to use the Single Line of Text solution from above but will if that's my only option. Any suggestions here? 

I know this is a lot of information so please let me know what else would be helpful to troubleshoot. This is the first time I'm posting to the community for help. Usually I can find what I need here or via YouTube videos but this one has me thoroughly stumped.  

*Just as another note - delegation will not be an issue. We will not have more than 100-200 records at a time in the source list. Likely less. 

[v-yuxima-msft]

Hi @Special_K ,

1)

Do you want to save Excel Data to SP list?

You can use form or Patch function to achieve that.

If you use Form, please try the following workaround, I test as next.

Data Source:

Then App Test:

Add two dropdown into the datacard.

Dropdown_Category:Table2.Category

DataCardValue3.Default:Dropdown_Category.Selected.Category

Dropdown_Product.Items:Filter(Table1,Category = Dropdown_Category.Selected.Category).Product

DataCardValue2.Default:Dropdown_Product.Selected.Product

If you want to use Patch,more information:

function-patch

2)Do you want to limit the users by Role and you can't use Person type Column?

 You can create two columns as next:

Role:(Choices type)

Email (Single line)

 Then  App Test:

Set the Gallery.Visible:If(LookUp('20190423ExceTest',Email = User().Email).Role.Value = "Manager",true,false)

App test:

Hope this could be helpful.

Best Regards.

Yumia

[Special_K]

@v-yuxima-msft - thanks for your help on this! 🙂

1) I do want to save Excel Data to my SP list, thanks for understanding what I'm trying to achieve! I am currently using a form linked to my SP list. I've been meaning to look into Patching but haven't attempted it yet. 

It looks like we did very similar things though I did realize after reviewing your steps that I could use the Update property of the DataCard to write the combobox selection directly back to Sharepoint vs. having to transfer it into the Single Line of Text field first. It skips one step. 

However, when I originally set up the column in SharePoint, it was a Choice column. No matter what I did, I couldn't get the Excel data to write back to SharePoint until I created a new Single Line of Text column for the same field. I was hoping to have a cleaner solution then what you and I are doing as a work around since this is several extra steps. 

2) This one needs a better explanation from me. This is the section of my form I'm trying to sort out: 

The form can be filed out by either of the Coordinators. I have a secondary SharePoint list (not the Form source list) that collects all the info I need to auto-populate these fields and have pulled in only the needed columns as a collection. I tried directly referencing the Sales Ops Team SharePoint list but it was SLOOOOOOOW. 

Here's what I'm trying to accomplish:

A) Ideally, I'd like for the correct coordinator to auto populate based on the user logged in. This can be done by doing a lookup to the Role column based on the Office365 user logged in. Even with the collection, this seems to take over a minute to populate. Too long when you can populate the column manually much faster. 

B) Once the Coordinator fields are populated, the Supervisor and Manager columns can also auto populate using this same collection. This does not seem to be as slow but until I can sort out A, it's hard to test this exclusively. 

C) The SharePoint columns are setup as People columns. I currently have to do the same workaround as I use for the cascading dropdowns - add another dropdown or combobox to link to the collection within the DataCard, pass that information to the DataCardValue, and then write it back to SharePoint. That being said, it's currently NOT writing back to SharePoint. The form submits successfully but the SharePoint columns are blank. My best guess is that I will have to create new Single Line of Text columns to replace the People columns to get this to actually pass over.

Again, not ideal. Too clunky of a solution. All these extra controls to get fields to format correctly (radio buttons) or write back (info coming from a data source other than the source SP list tied to the form) is slowing down the app performance. Is there a better option that I'm missing?