cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KevinHardy
Level: Powered On

Where best to do calculations

H,

I am building my first app and coming up against some issues. The app I am building will be used to record petty cash transactions - The image below shows the columns set up. The calculations relate to where users can enter details of money spent, or added to the balance.

My question is where is best to do the calculations? i.e. subtracting the spent amount or adding the top-up to/from the balance.

I made a Calculated Column to do this on the SharePoint List (BalanceSum) but this has given me issues with displaying the amount in the app as the current balance in that it displays too many decimal points and "[$-en-US],  £0.00" only works with number columns and not calculated ones for some reason. So would it be better to do the calculations within the app before passing the data back to the list?

Also any advice on doing the sums would be helpful, especially on how to get the BalanceBF in line 2 to equal the BalanceSum of line 1 (in the List or in the app)

1 ACCEPTED SOLUTION

Accepted Solutions
Edwin-Abdalian
Level 10

Re: Where best to do calculations

now try to add a label on your screen and use the Last function to get the data in the last record of your list.

Text(Value(Last(YourSharePointList).BalanceSum),"[$-en-US]£#.00")

View solution in original post

9 REPLIES 9
Edwin-Abdalian
Level 10

Re: Where best to do calculations

Calculated fields in SP are converted to Text type in PowerApps, tha's why the formatting didn't work for you. wrap your column in the Value() function to convert it to number and then use the formatting.

Text(Value(YourSPColumn), "[$-en-US], £0.00")

KevinHardy
Level: Powered On

Re: Where best to do calculations

@Edwin-Abdalian 

I've attached a screen shot of the result when I try to display the BalanceSum in the app. I'm sure I'm missing something pretty basic.

Edwin-Abdalian
Level 10

Re: Where best to do calculations

an where is that label? is it inside a form or a gallery, or you just added it to your screen?

KevinHardy
Level: Powered On

Re: Where best to do calculations

@Edwin-Abdalian 

 

The box in the screenshot is just placed on a screen, but I have another (on a different screen) which is a datacard on a form. Both give the same result.

Edwin-Abdalian
Level 10

Re: Where best to do calculations

When you use something like BH_PettyCash.BalanceSum, you refer to the entire column, not a single value. you need to specify the record in the list to be able to get the value out of the column.

for now, and just to see if your formatting formula works, create a temp Gallery that has a simple layout with only one Title label. Then bind it to BH_PettyCash list. then change the formula of the label to ThisItem.BalanceSum and apply the formatting formula to it. and see if the gallery shows the correct format.

KevinHardy
Level: Powered On

Re: Where best to do calculations

@Edwin-Abdalian  Thank you for your help, although not 100% successful it did guide me toward the answer 🙂

 

I made a new app as you instructed with a gallery with just one label with the list as the data source.

 

ThisItem.BalanceSum = 200.000000000000

 

Text(Value(ThisItem.BalanceSum)) = 200

 

However, after some experimenting I found that this worked:

Text(Value(ThisItem.BalanceSum),"[$-en-US]#.00") = 200.00

KevinHardy
Level: Powered On

Re: Where best to do calculations

@Edwin-Abdalian Not sure if you can help with an additional query on this subject.

 

Text(Value(ThisItem.BalanceSum),"[$-en-US]£#.00") returns the result of £200.00 which is the correct FORMAT, but not the right result. I want to get the LAST record in the list. However, after much experimentation I have not found how I can add LAST to the existing formula. Any ideas?

Edwin-Abdalian
Level 10

Re: Where best to do calculations

now try to add a label on your screen and use the Last function to get the data in the last record of your list.

Text(Value(Last(YourSharePointList).BalanceSum),"[$-en-US]£#.00")

View solution in original post

KevinHardy
Level: Powered On

Re: Where best to do calculations

@Edwin-Abdalian Thank you. That works.

My issue is I have a little knowledge but not enough to know the correct syntax 🙂

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 395 members 6,722 guests
Please welcome our newest community members: