Saving a Form and Gallery data to two SharePoint lists but linking the data to enable it to be edited, completed

[ClarkyPA365]

Hi,

I have a screen that contains a Form and a Gallery.

The Gallery is connected to a SP list which contains various questions, relating to Competencies.  75 in total and this could go either way over time as in grow or reduce. 

The Gallery, displays the questions and Radio buttons for the three potential answers. Within the Gallery there is a Comments section.  
The Form portion holds user information and some additional information required by the team.

Due to the number of columns I have two SharePoint lists, where data will be stored

Competencies Results - This will hold user information from the form and the answers to the questions listed in the Gallery.  These will consist of No, Yes, N/A

Competencies Comments.  This will hold the Comments made relating to the 75 questions from the Gallery.

The CC1, CC2, CC3, etc relate to the Competency

The Comments list will have the same format...

1st Question - What is the best way to save the data to both SharePoint sites.

2nd Question - How retrieve the data for a record when searched for to bring the data back for all 75 questions.

3rd Question.  How to save this again as potentially the answer could be different.

This exercise could be done multiple times over a 12 month period and will be used to feed a BI Dashboard... 

[developingteal]

TLDR I skipped to your questions

1- I would just make matching patch functions and patch to both using the same data.

2-Not really sure of your question, but if the record=what you searched for you can set the items property to whatever based on that

3- Patch function should be able to handle it

[ClarkyPA365]

Hi @developingteal 

Thank you for your reply.

The one thing that I'm uncertain of is, do I have to refer all the fields in the app to all the columns in the two SP lists??
Or will the For All formula perform the task??


Number

[developingteal]

Yep, you will have to refer all the fields in the app to all the columns in the two sp lists. There might be a way to not with forall and using forms (I am less familiar with forms) but I know the simplest way to me and definitely easier way to debug is to refer all the fields to all the columns.

[ClarkyPA365]

Hi,

Patching nearly 150 columns 75 for the answers and 75 for comments, plus some additional fields,  will be a bit of a huge formula
I have thought about using Forms to create the initial list of answers as I can branch the Form to show certain questions depending on a selection.  Then using a PowerApp to enable the updates that may be required for answers that have a no value so this would reduce the number of data being retrieved...

[developingteal]

You have 150 columns???? If you made the form with all the entry you're probably going to end up with a big formula. Yeah if you have 150 columns I would definitely limit the amount the user has to input because that seems almost ridiculous

[WarrenBelz]

Hi @ClarkyPA365 ,

I might make a couple of suggestions on a better direction to take here which is also scalable. Have a separate list of questions for an editable Gallery (you could number them and only display the next one when the previous one is answered) and then write all the answers with things like the question ID, the answer, the student etc to another list. If they are multiple choice, you could even auto-mark them if you had the answer in the first list.

Second option is what I do - take the whole thing out of Power Apps on to a Form - MS Forms would probably do the job, but I use a third-party solution (Cognito Forms). You can then use Power Automate to write it all back to SharePoint and if you wanted to, view the important fields in Power Apps.

[ClarkyPA365]

Hi @WarrenBelz 

Thank you for your suggestions.
After a few coffee's and some re-thinking I have started to look at the MS Form route which I can use the branching option to see more or less questions, depending on a selection.

With this I will also be able to keep the Answers and Comments in a separate SP list as I'll be using unique ID's
And as you also mentioned use PowerApps to retrieve the stored answers and amend them as required and save them back to the SP lists.. 

[ClarkyPA365]

Hi @WarrenBelz 

I've created a form and the Flow to save the answers and comments to there respective SharePoint lists.

I've now come across an issue on how to retrieve the answers and the comments associated with them into one Form or probably two forms.  The answers and comment are linked by the SharePoint ID column.

I'm hoping the organisation will only require retrieval of specific answers, i.e. those that have a 'No'.
What is the best way to these to display so the answer can be changed were required and the comments updated and then saved back to the respective SP lists 

As previously mentioned there are 75 questions split out in section headers.  Each one as a specific code that relates to the section question. The same code is used for the Comments.

Hope this makes sense 🤔