Power apps Excel file collect response

[em1]

Hello,

I am using this existing template in Power Apps (employee engagement) where we collect responses. I tested it and it works, however it seems like the code looks up an existing user from the Excel file where the responses are stored.

If there is an existing user on that file, it will override the previous response. If not on the file, it will create new line item.

How do I modify the code to create a new line per response and add a new column to capture date too? Thank you.

If(!IsBlank(LookUp(Responses,Username=User().FullName,Responses)),UpdateIf(Responses,Username=User().FullName,{ResponseId:ResponseId, Answer1:Radio1.SelectedText.OptionText,Answer2:Replace(Concat(Listcollect,OptionText & ", "),Len(Concat(Listcollect,OptionText & ", "))-1,1," "),Answer3:Text(Slider1.Value), Answer4:If(Radio1_1.SelectedText.OptionText="No","No," & TextInput1.Text,Radio1_1.SelectedText.OptionText),Answer5:TextInput1_1.Text,Username:User().FullName}),
UpdateContext({NewSurvey:Patch(Responses,Defaults(Responses), {ResponseId:Text(CountRows(Responses)+1), Answer1:Radio1.SelectedText.OptionText,Answer2:Replace(Concat(Listcollect,OptionText & ", "),Len(Concat(Listcollect,OptionText & ", "))-1,1," "),Answer3:Text(Slider1.Value), Answer4:If(Radio1_1.SelectedText.OptionText="No","No," & TextInput1.Text,Radio1_1.SelectedText.OptionText),Answer5:TextInput1_1.Text,Username:User().FullName})}));Navigate(Finish_Screen,ScreenTransition.Fade)

[WarrenBelz]

Hi @em1 ,

Can you please use Format Text on that and re-post - it will then format as per example below

Also, are you wanting to create a new line regardless of whether the user's line exists (new record for all instances) and what is the new column you want to create and what will it contain?

[em1]

Hello @WarrenBelz ,

Yes, correct. We need to create new line/new record for all instances. The new column is date, tried using formula in Excel to get the current date but PowerApps does not allow formulas. We need to capture the date when the record was created.

If(
!IsBlank(LookUp(Responses, (Username = User().FullName), Responses)),
UpdateIf(Responses, (Username = User().FullName), {
ResponseId: ResponseId,
Answer1: Radio1.SelectedText.OptionText,
Answer2: Replace(
Concat(Listcollect, OptionText & ", "),
Len(Concat(Listcollect, OptionText & ", ")) - 1,
1,
" "
),
Answer3: Text(Slider1.Value),
Answer4: If(
(Radio1_1.SelectedText.OptionText = "No"),
"No," & TextInput1.Text,
Radio1_1.SelectedText.OptionText
),
Answer5: TextInput1_1.Text,
Username: User().FullName,
}),
UpdateContext({
NewSurvey: Patch(Responses, Defaults(Responses), {
ResponseId: Text(CountRows(Responses) + 1),
Answer1: Radio1.SelectedText.OptionText,
Answer2: Replace(
Concat(Listcollect, OptionText & ", "),
Len(Concat(Listcollect, OptionText & ", ")) - 1,
1,
" "
),
Answer3: Text(Slider1.Value),
Answer4: If(
(Radio1_1.SelectedText.OptionText = "No"),
"No," & TextInput1.Text,
Radio1_1.SelectedText.OptionText
),
Answer5: TextInput1_1.Text,
Username: User().FullName,
}),
})
);
Navigate(Finish_Screen, ScreenTransition.Fade);

[WarrenBelz]

@em1 ,

You did not use Format Text as I asked - I do not have the time to format all of that.

Also dates in Excel are problematic to say the least and need to be stored in either numeric or Text format - have you considered using SharePoint ?