Am I doing this the wrong way?

[JRS1]

Hi,

The file shows a super simplified version of what is going on and hopefully shows what I'm trying to achieve. 

I'm trying to build a pretty simple app but I'm struggling combining data sources. I might be doing this all wrong. I'd take ideas on how to do it or if you can solve where I currently am that would be great too.

What the app should do is allow management of user requests from various MS Forms. All the requests need to sit in a master view 'unassigned'. The list should have some key fields visible and when you click on the record you are taken to the full detailed view where it can be assigned and all the response fields from the MS Form can be seen. There's a load of other functionality but I'm sure I can manage that once I've got the data sorted. 

Here is where I am:

• There are various MS Forms for users to complete for different services/requests - I can't change this.
• I have created sharepoint lists for each MS Form
• I have also created an 'overview' list with key information from the sharepoint lists for the MS Forms 
  
  • I haven't combined all the responses from all the forms into one master list due to all the different questions in the forms - would result in a massive list full of columns  with some empty and some populated depending on which form was used (maybe my first mistake?)
  • Automations add the results from users MS Forms to it's corresponding sharepoint list and the overview list
  • Automation creates unique job number and puts in both lists (forms sharepoint list and overview list)
• The app is basically a gallery with the data source being the sharepoint 'summary list'
• I thought I could use the job number from the summary list to lookup the job detail in the corresponding forms sharepoint list and pull the detail from there with the job id
  • I can't figure that part out. If I can get the data working, I can do all the other bits.

Thanks for any advice offered

[BrianS]

Since you are stuck with the forms as they exist, I think I would change what you are doing slightly. Instead of what you have as a "Master" list, I would create a table to hold the ID of each and some header data to identify it, and a field to keep track of which list it came from. I would use that info in your main form. Then for your detail screens I would set up the galleries to match the various forms so that they are easier for the user - you won't end up with fields that don't apply, names that don't fit, etc. Then capture the form number and ID from your main form and navigate to the correct detail screen and use the ID to do a lookup.

[JRS1]

Hi Brian,

Thanks for the reply. Unfortunately, I don't really understand your solution. 

Do you mean to create a data table like this? Data table control in Power Apps - Power Apps | Microsoft Learn 

If you meant a table in the dataverse, I don't have the rights. 

If you have any links or videos of what you mean I'd appreciate it. 

[BrianS]

What I meant was rather than trying to copy all the data from the various Form SP lists, put in just enough info to your Master list to find the form data you are looking for. Since each Form is reporting slightly different data, the details screens will not be very useful to the app user if they have space for all the fields on all the forms, many which will be blank. If you make separate screens for each forms worth of data then it will be easier for the user to digest. By gathering the ID and Form name from each you can then use the LookUp() function to query the SP lists and recover the data you need.

[JRS1]

I see, thanks again for replying.

What you describe is what I have currently done, or at least trying to do - the automation copies a few key fields from the Form SP lists into the master list. 

In the excel file I show the main screen having the gallery with the master list as it's source. I can't seem to make that lookup to the sp lists. I'll try what you suggested with the form ID.

Where should the lookup go? The 'next arrow' in the main gallery or 'on select' of the main gallery? 

Sorry, all this is new but I'm trying to learn

[JRS1]

I'm trying to load a form in a different screen in power apps - using the 'next arrow' in the gallery on main screen - on select. Depending on the value of the request type field I want to load a different screen with a different form. I can only get one to load, if I try to nest some IF statements to get other screens to load, it breaks... Couple of images attached. 

[JRS1]

I have added a text input field to the power app form in the app and although I can pull the data through based on the jobID, I can't submit that data back to where it is being pulled from. 

[BrianS · Accepted solution]

To do the navigation you would probably be better off with a Switch function (documentation here)

I would set your variables before the Switch function, and your resets on the page OnVisible.

For the Lookup, that would be in the Items of Gallery on the detail page. Something like

LookUp(FormSPList, varRecord.ID=ID)

If you use a Form, your DataSource will be the Form SP list and the Item will be the Lookup. Once you are in the Form it will handle writing the info back to the list and since your "Master List" is only the ID and Form, you don't need to write anything back to that. If you are using Patch it will take a little more work, but just Set a variable to the Row found with the Lookup and use that as your field sources, then you can just Patch using the variable as your Record to Patch

Hope that helps - let me know what other issues show up

[JRS1]

Thanks, I found switch shortly after posting my problem. Much better than nested IF statements. 

Thanks for letting me know where the lookup goes - linked to the form no issues. 

All working now so cheers!