cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Writing a summed value back to excel

I'm trying to develop a timesheet app for our staff, linking to an excel sheet in Onedrive.

My table structure has one row per working day of the year and a range of contracts (x4) against which time can be logged.

Contracts are selected using a dropdown list from a second table.

 

On DetailScreen1 I have a field Total_Hours which links back to my primary table. In here I have inserted the following formula to sum the hours inserted against the 4 selected contracts: 

 

Text(Value(Hours_1_Value.Text)+Value(Hours_2_value.Text)+Value(Hours_3_value.Text)+Value(Hours_4_value.Text))

 

This sums inside the app and displays the answer, but it does not write the value back to the excel sheet. Can anybody help me work out where I have gone wrong?

 

Many thanks

 

 

E.g.8.jpg 

 

Table1.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @stumac_31

 

The form that's in your screenshot is a 'display form'. Whilst you can use formula to display a calculated value in the way that you did, you can't update data with a display form.

The answer from @v-monli-msft refers to an 'edit form'. This is the form that you would find in EditScreen1. An 'edit form' is the type of form that we would use to create and update records.

If you only intend to update your spreadsheet from PowerApps and if you were to apply @v-monli-msftsuggestion, the solution would work from this point onwards, because the formula in the edit form would calculate the totals at the point of data entry.

If you want to update your total field from your display form, you can use the Patch function to do this. Hopefully, you should find plenty of examples on this forum on how to do this.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Hi @stumac_31,

 

I just tested with my app and it updated the Total column too in the worksheet. Please try again following my steps:

1. Create the app using the Excel table in OneDrive.

2. In Edit Form, unlock the Total column data card.

3. Select the textinput control (which name is DataCardValue13) in this data card, and set its Default property to:

DataCardValue10+DataCardValue11

DataCardValue10 and DataCardValue11 are the textinput controls of the number columns that you want to sum. 

4. Make sure that the Update property of this data card is 

DataCardValue13.Text

1.PNG

 

After this, when you put the numbers in Number1 and Number2, Total would show the summed value by default, and after you click the submit button, it will update in excel too.

 

Regards,

Mona

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

@v-monli-msft for some reason I don't have the same menu options in 'advanced' when selecting the data card!

In the 'data' section on the 'advanced' tab I do not have the same 'update' field as in your screenshot.

The option is not available when selecting the card or the labels.

Had been using the web editor but downloaded the desktop programme and still not an option!

 

 

E.g.10.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

E.g.11.jpg

 

 

 

E.g.9.jpg

 

Highlighted

Hi @stumac_31

 

The form that's in your screenshot is a 'display form'. Whilst you can use formula to display a calculated value in the way that you did, you can't update data with a display form.

The answer from @v-monli-msft refers to an 'edit form'. This is the form that you would find in EditScreen1. An 'edit form' is the type of form that we would use to create and update records.

If you only intend to update your spreadsheet from PowerApps and if you were to apply @v-monli-msftsuggestion, the solution would work from this point onwards, because the formula in the edit form would calculate the totals at the point of data entry.

If you want to update your total field from your display form, you can use the Patch function to do this. Hopefully, you should find plenty of examples on this forum on how to do this.

View solution in original post

Highlighted

Thank you @timl and @v-monli-msft

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,617)