Passing calculated fields to Excel

Like (0) Share

Report

Posted on 20 Aug 2018 19:01:25 by jake-hodgson

Hi,

I'm a bit of a PowerApps newbie so just getting to grips with things.

I'm looking a making a really simple profit & loss app for a small events company, one revenue field and a few costs fields to be sumed and then subtracted from the 'Revenue' field.

Firstly I created a basic table in Excel for all the fields with the calculations for the total costs and profit field. I then created the app from the Excel sheet so it created the required screens and forms for editing and viewing the data. It didn't like the formulas I had put in so I've removed them from the Excel table. How do I make PowerApps add the calculations to the fields in the Excel workbook?

Thanks in Advance!

Jake

Categories:

General Questions

Connecting to Data

All responses (3)

Answers (0)

jake-hodgson 19 on 26 Aug 2018 at 20:30:15

Like (0)

Report

Re: Passing calculated fields to Excel

Hi Guys,

Brilliant thanks for the advice and information, it was really helpful!

Thanks Jake
v-xida-msft on 22 Aug 2018 at 06:57:24

Like (0)

Report
Re: Passing calculated fields to Excel

Hi @jake-hodgson,

Do you want to do some calculations on a few fields within your Excel table and then fill in the Total costs field and the Profit field using PowerApps?

I agree with @rc's thought almost. I have made a test on my side, please take a try with the following workaround:

The Excel table data structure:

Generate an app from the Excel table.

Within the Edit form control of the Edit screen, Unlock the "TotalCost" Data card, Set the Default property of the TotalCost Data card contorl to following formula:

Value(EmployeeCost_DataCard2.Update)+Value(EquipmentCost_DataCard2.Update)

Note: The EmployeeCost_DataCard2 represents the EmployeeCost Data card, the EquipmentCost_DataCard2 represents the EquipmentCost Data card control within my Edit form.

Unlock the "Profit" Data card control, set the Default property of the "Profit" Data card control to following formula:

Value(Revenue_DataCard2.Update)-Value(TotalCost_DataCard2.Update)

Note: The Revenue_DataCard2 represents the Revenue Data card, the TotalCost_DataCard2 represents the TotalCost Data card within my Edit form.

The GIF image as below:

Best regards,

Kris
rc on 20 Aug 2018 at 20:06:01

Like (0)

Report
Re: Passing calculated fields to Excel

Welcome to PowerApps, Jake!

You can use the functions in PowerApps to default values in your app form and then pass those values to columns in Excel. Her's a quick runthrough:

I've recreated an Excel table like you describe and stored on my OneDrive -- I've left the TotalCost and Profit columns blank without formulas:

Then I generated an app from data, and pointed it at the same table.

On the form screen of this app, select the card for the Cost column. Then on the properties tab, switch to Advanced, and unlock the card.

Now you can change the Default property of the card to be a calculated expression, in this case summing the cost (and then later calculating profit), based on the updates a user would make when using the app:

The expression in this case:

Sum( EquipmentCost_DataCard2.Update, StaffCost_DataCard2.Update, TransportCost_DataCard2.Update )

When you submit the form, the items will write back to Excel.

The Budget Tracker sample app has some of these patterns in a bit more sophisticated detail that might be useful to check out.

Another option would be to use the Common Data Service instead of Excel, and take advantage of Calculated Fields there, which have the benefit of performing calculations like this server-side for any app using the data (instead of having to repeat the logic if multiple apps touch the same data source).

Good luck!