Populating an Excel file using Buttons

[Anonymous]

Hi, 

I am having an issue with Buttons populating an Excel Sharepoint file correctly. I want to be able to press a Button and OnSelect it populates a cell in a given column of the file. I have multiple Buttons pointing to different columns, but the issue is a new row is created every time the button is pressed. 

For example: When I press BUTTON1, it should print "first" in column A on a new row, when I press BUTTON2, it should print "second" in column B on the same row, and then if I press BUTTON1 again, it should print "first" on a new row of column A again, and so on. 

I am using this code for BUTTON1 at the moment: Patch(Table1, Defaults(Table1), { A: "first" } ) 

and this code for BUTTON2: Patch(Table1, Defaults(Table1), { B: "second" } )

But this is the result

                                                A            B

                                               first       

                                                         second

whereas it should be

                                              A             B

                                            first          second

Any help would be greatly appreciated. I hope the question is clear. 

Thanks in advance

[eka24]

Hi

Using  Defaults in Patch will create a new rows always. So on the second click button, change the formula to somthing like:

Patch( Table1, First( Filter( Table1, A= "Abc" ) ), { B: "Second" } )

Change Abc in column A to the row that matches column B. Preferably a unique field Like and ID Column like:

Patch( Table1, First( Filter( Table1, ID= 1 ) ), { B: "Second" } ) ......That if row 1.

If you are using a TextboxA for "A", you can try:

Patch( Table1, First( Filter( Table1, A= TextboxA.Text) ), { B: "Second" } )

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[Rahber]

Since the Excel works at a row level and there is no function to do this at column level

What you need to do is if you are entering new data it should CREATE the row. If you are pressing the second button it should UPDATE the row.

Please click Accept as Solution if it resolved your problem or give it a Thumbs Up if it helped you in anyway this will allow other people to search correct solutions effectively.

Thanks,

Rahber

[v-bofeng-msft]

Hi @Anonymous :

As @eka24  said, this is a formula syntax problem.

When you want to add a new record, you should use the following syntax:

Patch( DataSource, Defaults(DataSource), ChangeRecord1)

When you want to modify a specified record, you should use the following syntax:

Patch( DataSource, TheSpecifiedRecord, ChangeRecord1)

So I suggest you set your buttom2 's OnSelect property to:

Patch(Table1, Last(Table1), { B: "second" } )

Best Regards,

Bof