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!
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).
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:
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:
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:
Check out new user group experience and if you are a leader please create your group
Check out how to claim yours today!
Test your skills now with the Cloud Skill Challenge.
We are excited to announce that Demo Extravaganza for 2021 has started!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks