Patching collection from gallery is overwriting data as blank / creating duplicates

[_ru]

Hello!

I am trying to get all of the data in a gallery to patch to a collection.

My app has a gallery with questions and controls so users can answer the questions, and then hit a button to save their answers that are patched to a collection. 

The gallery has 66 questions, and I have a tab list that filters the gallery questions based on the tab selected. The tabs are sections, each section has 3 or 4 questions, so when you select the tab, the appropriate 3 or 4 questions pop up in the gallery for the user to answer and then hit a save button to patch to the collection.

The problem however is when a users answers the first 3 questions and hits save, it saves the data to the collection just fine, but when they select the next tab and answer the next 3 questions and hit save, the first 3 answers are overwritten as blank and then only the second set of 3 questions are patched to the collection. And so on. 

For instance my collection will look like this when they answer the first 3 questions (it goes to 66 rows):

Data	Question	Title
Answer 1	Question 1	Section 1
Answer 2	Question 2	Section 1
Answer 3	Question 3	Section 1
	Question 4	Section 2
	Question 5	Section 2
	Question 6	Section 2

And then it will look like this when they answer the next 3 questions:

Data	Question	Title
	Question 1	Section 1
	Question 2	Section 1
	Question 3	Section 1
Answer 4	Question 4	Section 2
Answer 5	Question 5	Section 2
Answer 6	Question 6	Section 2

The OnSelect of my Save button is as follows:

ClearCollect(
	colFinal,
	colFormTemplate
);
ClearCollect(
	galData,
	galForm.AllItems
);
ForAll(
	galData,
	UpdateIf(
		colFinal,
		Title = galData[@Title] && Question = galData[@Question],
		{

			Data: Switch(
				ThisRecord.QuestionType,
				"LongText",
				txt_LongText.Text,
				"Choice",
				drp_Choice.Selected.Result
			)
		}
	)
);
Patch(
	'Results Data',
	varItem,
	{
		Title = "title here",
		AnswerData: JSON(
				colFinal,
				Compact
		)
	}
)

Now I did find that if I use "Collect" instead of "ClearCollect" it WILL keep the data previously entered for the first 3 questions and add on the data for the next 3 questions, HOWEVER it also adds all of the gallery data again, so instead of 66 rows, there's 132 which is not correct. 

I just need colFinal to be 66 rows and patch 3 or 4 rows at a time. 

How can I accomplish this? I feel like it should be simple but I just can't seem to get it right.

Please let me know if screenshots would help as well. Thank you so much in advance!

[opit]

@_ru what I would do: I would have an original collection with a questions loaded and when user answers 1 question I would make a collect answer to separate gallery (e.g. colAnswers). Before collecting answer I would search if there is already such answer and if it is then I update it and if not I add it.

for example:

// load questions to collection

ClearCollect(colQuestions, ...)

// then when user answer the question

If(

  ThisItem.ID in colQuestions.ID,
  update record in colAnswers,
  insert recorc in colAnswers
)

[_ru]

@opit I'm sorry, I must be confused because that's kind of what I'm doing already, no?

Collect(
	colFinal,
	colFormTemplate
);

This gives me a collection of all 66 rows of my collection that is Answer | Question | Title. Question and Title are filled out, Answer is blank. 

Collect(
	galData,
	galForm.AllItems
);

This gives me the current 3 questions AND answers that are being filtered in the gallery

ForAll(
	galData,
	UpdateIf(
		colFinal,
		Title = galData[@Title] && Question = galData[@Question],
		{

			Data: Switch(
				ThisRecord.QuestionType,
				"LongText",
				txt_LongText.Text,
				"Choice",
				drp_Choice.Selected.Result
			)
		}
	)
);

This is where I'm (trying at least) to do what you say. I want to Look at the colFinal collection, and ForAll of the current items in galData (which is the current 3 questions and answers), I want to add those 3 answers to colFinal where the Title and Question match. This works, except it also adds 66 more rows of everything and I don't want it to do that. I only want it to update the new stuff, not patch 66 duplicate items.

Do you know what syntax I would use to accomplish what you say? I thought UpdateIf would give me what I need, but if I need to wrap my ForAll(..) statement in an If statement, how would I do that?

[_ru]

bump...anyone have any ideas as to what i might be doing wrong? 

[opit · Accepted solution]

@_ru  I enclose power apps app to show principle what i mean. Just import to your PowerApps.

Here i initialize collections wiht questions

Clear(colAnswers);
ClearCollect(
	colFinal,
	{Question: "Question1", Section: 1},
    {Question: "Question2", Section: 1},
    {Question: "Question3", Section: 1},
    {Question: "Question4", Section: 2},
    {Question: "Question5", Section: 2},
    {Question: "Question6", Section: 2},
    {Question: "Question7", Section: 3}
);

Here I add collect answer to separate collection colAnswers. 

If(
    ThisItem.Question in colAnswers.Question,
    UpdateIf(
        colAnswers,
        Question = ThisItem.Question,
        {Data: Self.Text}
    ),
    Collect(
        colAnswers,
        {
            Question: ThisItem.Question,
            Data: Self.Text
        }
    )
)

Please note that for InputField default property is

LookUp(colAnswers, Question = ThisItem.Question).Data

And then you transform results for patching to datasource.

ClearCollect(
    colResults,
    AddColumns(
        colFinal,
        "Data", LookUp(colAnswers, Question = colFinal[@Question]).Data
    )
)

[_ru]

@opit thank you for the clarification!

Unfortunately when I import your app it's all blank:

Do you have any idea why that might be the case? Was it not saved/published before exporting?

In any case, I will try and replicate this in the meantime with the code you've given me and will report back if that works.

[opit]

@_ru try this on

[_ru]

@opit Thank you so much! This actually works exactly how I need it to except for one small problem:

When I'm replicating it for my app, I need it to match a row in the gallery based on 2 keys (the section and the question) because some sections have questions that are the same. 

So I tried to alter your OnChange code here:

If(
    ThisItem.Question in colAnswers.Question,
    UpdateIf(
        colAnswers,
        Question = ThisItem.Question,
        {Data: Self.Text}
    ),
    Collect(
        colAnswers,
        {
            Question: ThisItem.Question,
            Data: Self.Text
        }
    )
)

To this instead (just the If/UpdateIf lines are changed):

If(
    ThisItem.Question && ThisItem.Title in colAnswers.Question,
    UpdateIf(
        colAnswers,
        Question = ThisItem.Question && Title = ThisItem.Title,
        {Data: Self.Text}
    ),
    Collect(
        colAnswers,
        {
            Question: ThisItem.Question,
            Data: Self.Text
        }
    )
)

It writes the answer correctly, but when I change an answer, it patches a new row instead of updating. So I must be doing something wrong but I can't seem to get the syntax right for this either. 

Any ideas what it might be?

I tried:

If(
    ThisItem.Question in colAnswers.Question && ThisItem.Title in colAnswers.Title,
    UpdateIf(
        colAnswers,
        Question = ThisItem.Question && Title = ThisItem.Title,
        {Data: Self.Text}
    ),

But then that doesn't patch anything at all. 

And thank you a million times over for your help so far, I genuinely appreciate you and can't thank you enough.

[_ru]

@opit I just realized, I think it's looking at the following statement 

If(
	ThisItem.Question in colAnswers.Question && ThisItem.Title in colAnswers.Title,

as in if the ThisItem.Question is ANYWHERE in the collection Question column, and ThisItem.Title is ANYWHERE in the collection Title column. 

I need it to be "If ThisItem.Question AND ThisItem.Title in the SAME ROW Together (they're both used to create a unique key) THEN update."

If that makes sense. Hopefully it does. I threw a text label in the app to test why it wasn't working and I set it to

galForm.Selected.Question in colAnswer.Question && galForm.Selected.Title in colAnswers.Title 

and saw that it was displaying "true" for a something that it should've been displaying false for. So again, it seems like it's not looking for the Question and Title in the same row like I need them to be. 

The Titles and Questions are used together to create a unique key. "Section 1 Question 1" for instance. Since there is another question titled "Question 1" in the other sections, i.e. "Section 2 Question 1"

[_ru]

I was able to fix my own issue above by using an ID column instead. It fixed this problem, and then created another one (of course), but I'll create a separate thread for that.

Thank you so much @opit for all your help! I really appreciate it!