cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DaweiLi2333
Microsoft
Microsoft

use a button in powerapps to Sum every row in my excel table

In my Excel table, I have three columns like this

This idea is I just need to push the button in power apps and in my excel, the column3 will display the number of column1+column2.

Just like the formula in Excel

How to write the code in OnSelect in the Button

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @DaweiLi2333 ,

Based on the needs that you mentioned, I have made a test on my side, and don't have the issue that you mentioned.

 

Please consider re-generate a new app based on your Excel table data source, then within this generated app, add a button, set the OnSelect property to following:

 

UpdateIf(
         product,
         true,
         {
           total: Value(price) * Value(number)
         }
)

 

Note: In default, when you create a connection to your Excel table from your canvas app, it would generate a new column called "__PowerAppsId__" within your Excel table as below:

4.JPG

Please check if the "__PowerAppsId__" field is generated in your Excel table already.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
WarrenBelz
Super User
Super User

Hi @DaweiLi2333 ,

Doing this on a PowerApps form - using the names Column1Txt and Column2Txt for the controls containing these and TotalTxt for the control you want the total in the code on the OnSelect on the button would be

UpdateContext({vTotal: Value(Column1Txt.Text) + Value(Column2Txt.Text)});
Reset(TotalTxt)

The Default of TotalTxt

If(
  vTotal >0,
  vTotal,
  Parent.Default
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

I still cannot figure it out.

Column1 and Column2 have number, and Column3 is empty.

and I need write the sum of Column 1 and 2 into 3.

I don't need to show any information my my Powerapps.

Just need update my excel

Ok @DaweiLi2333 ,

You need to display it somewhere (in a Form as described in my last post or a Gallery) to get the item you want to action.

Are you wanting to update a selected item or all items that you can see?

For a single item using a Gallery as an example with the button in the gallery (you will see a button for every item), you could write back to the data source with something like this (you will also need a unique identifier in your table)

UpdateIf(
   YourExcelTableName,
   Lookup(
      YourExcelTableName,
      YourUniqueIDName = ThisItem.YourUniqueIDName
   ),
   {Column3: ThisItem.Column1 + ThisItem.Column2}
);
Refresh(YourExcelTableName)

You would then see the updated total in the gallery if it were displayed there.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

v-xida-msft
Community Support
Community Support

Hi @DaweiLi2333 ,

Do you want to calculate the Column3 value based on the Column1 and Column2 in your Excel table when you press the button in canvas app?

Where do you store your Excel table? In OneDrive or OneDrive for Business folder?

 

Based on the needs that you mentioned, I think the UpdateIf function could achieve your needs. Please consider set the OnSelect property of the "Button" to following:

UpdateIf(
         YourExcelTable,
         true,
         {
           column3: column1 + column2
         }
)

or

UpdateIf(
         YourExcelTable,
         true,
         {
           column3: Value(column1) + Value(column2)
         }
)

Note: When executing above formula, please make sure your Excel is not opened in your browser. Otherwise, you may face a File Lock Issue while executing above formula

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @DaweiLi2333 ,

@v-xida-msft 's response is the next bit it I asked if you want to update all the items.

The Value option would be applied if you were storing the fields as text (I assumed they were numeric).

It still cannot work

I store my excel file in OneDrive

And using connection in PowerApps to connect OneDrive for Business

Please look at the attachments.

 

I just need this 

see attachment

put the button and the excel will show price *number in total

I store this file on my OneDrive, and use PowerApps to connect this table.

Hi @DaweiLi2333 ,

Try

UpdateIf(
   YourExcelTableName,
   true,
   {total: price * number}
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up

Hi @DaweiLi2333 ,

Based on the needs that you mentioned, I have made a test on my side, and don't have the issue that you mentioned.

 

Please consider re-generate a new app based on your Excel table data source, then within this generated app, add a button, set the OnSelect property to following:

 

UpdateIf(
         product,
         true,
         {
           total: Value(price) * Value(number)
         }
)

 

Note: In default, when you create a connection to your Excel table from your canvas app, it would generate a new column called "__PowerAppsId__" within your Excel table as below:

4.JPG

Please check if the "__PowerAppsId__" field is generated in your Excel table already.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,497)