Writing a summed value back to excel

[stumac_31]

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

 

[v-monli-msft]

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

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

[stumac_31]

@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!

[timl · Accepted solution]

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.

[stumac_31]

Thank you @timl and @v-monli-msft