Submit button overwrites previously populated rows in excel table in PowerApps?

2
votes

I am creating a form where end user can submit their form entries by clicking Submit button. The Datasource for this task is Excel table. I created 2 screens.[SEE BELOW FOR MORE UPDATE]

Screen1 contains Summary of many systems (Sys1,Sys2,Sys3...) in the Gallery form. When End user clicks on one of the tab (Say "Sys1"), it takes them to new screen(which contains Sys1 detail information) where end user can Signup, by looking at the summary screen. I added "Signup" button at the bottom of the screen, which navigates to Screen 2.

Screen 2 contains edit form where end user can signup for System. I also wanted to use some values from Screen 1 (Say System 1 ID,Model# etc). So for those items, I set their "Item" property to Gallery.Selected, and disabled those fields so end user cannot edit those fields. Fields that needs end user's input are in "Text box" form such as "Signup by", "Signup Time" etc. I have submit button at the bottom of the screen. So end user can submit the form. The "OnSelect" property of Submit button is set to SubmitForm(Form1).

I have two issue with above process

  1. When user clicks Submit button, Previous entry in Excel table is getting overwritten. I would like to save previous entries as well
  2. When Screen2 is re-open all the previous entries are not getting clear

Update after Arun's comment

The code suggested by Arun for Create and Update is not something I am looking for. The create code Appends two new row in Excel table. The Update code removes the last assigned value. I am looking for a way in Powerapps , Where PowerApps code will first find the row in below case "Sys1" and Update that row as "User1" and "Mod1" for the column. Let's say "User1" wants to add another model for "Sys1" row, it will append at the bottom of the Excel table, as shown in Figure 2. Figure 2 is my desired output. Figure 1 is my initial Excel table.

Figure 1 Initial Excel Table (Data Source)

enter image description here

Figure 2: Expected Output

enter image description here

1
formspowerappspowerapps-formulapowerapps-canvaspowerapps-selected-items
so what you need is "if, else" block, if there is an entry already - Patch update, else Patch create as in my answer :) – Arun Vinoth
@ArunVinoth..I was thinking of same, but wasn't sure how to write it ?If possible could you help on that ?Thanks in advance! :) – biggboss2019
updated my answer, I have not tested it. pls verify – Arun Vinoth
@ArunVinoth Thanks for updating the answer. Everytime I run Edited code, Only Else condition gets executed ("Create" Code). Not sure what's the issue ? – biggboss2019
Unfortunately, now the edited code is running FALSE condition (Create) condition only. :( Will try to check your reference. – biggboss2019

1 Answers

1
votes

You should use Patch to write/append to excel file instead of SubmitForm(Form1).

For create:

Patch(ExcelSource, 
Defaults(ExcelSource), 
{NameColumn: TextInput.Text, SurveyResultColumn: Value})

For update:

Patch(ExcelSource, 
LookUp(ExcelSource, NameColumn=TextInput.Text), 
{NameColumn: TextInput.Text, SurveyResultColumn: Value})

Read more

Edit:

Please replace with your control names & values, I tried my best assumption :)

If(CountRows(Filter(Table1, System=SystemControl.Text && Model=""))>0, 
    Patch(Table1,LookUp(Table1, System=SystemControl.Text && Model=""), {Model: DatacardValue10.Text, UserName: UserControl.Text}), 
    Patch(Table1,Defaults(Table1),{System:SystemControl.Text, Model: DatacardValue10.Text, UserName: UserControl.Text}))

Reference