Unanswered

URGENT:How to calculate remainder from excel table and show it on PowerApps

(0) Share

Report

Posted on by tsangd

The top image shows an example of 3 users entering in bought used and remaining items , I want to implement into my power apps where it automatically calculates the remaining amount and displays it onto the screen. So far ive tried subtracting bought from used but if bought has no value it returns a minus number.

I tried subtracting the datacards of Bought and used but after the purchase and putting a new record in where buy amount is not requiried it returns a minus number, how can i overcome this problem, need URGENT HELP.

Tried: Sum(ThisItem.boughtAmount - ThisItem.usedAmount) in data default for dataCard remaining

Categories:

Building Power Apps

All responses (5)

Answers (0)

Sort by

tsangd 39 on at

Like
a
(0)

Report
Copy link

Link copied!

If you need additional information please feel free to ask any questions.
Mister_Shaik 1,008 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @tsangd ,

I assume you want to print the remainder as a positive number, irrespective of either Used or Bought data being a greater value,

Then you can use a if condition to compare the values first and then show the positive difference, like below,
If(ThisItem.boughtAmount > ThisItem.usedAmount, ThisItem.usedAmount-ThisItem.boughtAmount, ThisItem.boughtAmount-ThisItem.usedAmount)

Hope this helps 🙂

Kind Regards,
Shaik Sha
________________________________________________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.
tsangd 39 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your reply , where would this code go?
Rather than print the remainder as a positive, i want to take the remainder from the previous record and minus that from the current boughtAmount that the user inputs

E.g A User Bought 10 - Used 0 Remaining 10
next record: A User bought 0 used 3 remaining 7
And this would continue until the remaining is 0.
Mister_Shaik 1,008 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @tsangd ,

The above code goes to the Default property of Remaining Amount field and will replace your Sum function,

You want to get the remainder after subtracting with previous remainder? We can still do this, but it depends on how and what controls you are using, can you explain a few things?
What is your data source? SharePoint List or Excel?
Do you have a gallery to display all the data? are you showing the form on click of a gallery/data table item?
In your form, how are identifying if a record that you're editing belongs to a particular user? how are you getting the latest record of that user?

Regards,
Shaik Sha
tsangd 39 on at

Like
a
(0)

Report
Copy link

Link copied!
Hello @Mister_Shaik ,
I did as instructed and it doesnt update straight away, only If i refresh the page.

Q1:
What is your data source? SharePoint List or Excel?
A: I am using Excel as my database

Q2
Do you have a gallery to display all the data? are you showing the form on click of a gallery/data table item?
Yes I have a gallery which is very similar to the imported one when you first create a power app application
Q3:
In your form, how are identifying if a record that you're editing belongs to a particular user? how are you getting the latest record of that user?
Unfornately I have not implemented a way to identify a record belongs to a user other than the name they enter.

Im using a browseGallery as shown in the image to display the records that the user inputs(Apologies its in Japanese.)
As well as the excel sheet used to retrieve data inputted from the user
(担当者) Name id
(購入枚数)Bought amount
(使用枚数)Used amount
(残数) Remaining amount