cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

How to calculate loan balances in an App

Hi Team, 

 

I have the following lists in SharePoint 

 

  • Loan Table
  • Loan recoveries 

Loan Table recording the Loan given to individuals while Loan recoveries list recording the capital received and interest received. 

 

I want to have a screen as a report as follows, 

 

Name of the borrower---------Total Loan Given-------Total Capital paid (Recovered) ------Total Outstanding ----Total Interest received.

 

How can I do this ?

@

 

Osmand.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: How to calculate loan balances in an App

@OsmandFernando

Here's a mini-tutorial.  You can take parts of it and adapt to your situation.

 

#1  Create a new SharePoint List called "Loan Table" with the following columns.

 

imgA1.png

 

#2  Create another SharePoint List called "Loan Recoveries" with the following columns.  Loan ID must be a LookUp type column and reference "Loan Table"

 

IMGA2.png

 

#3 Create a new screen Screen1 in PowerApps.  Place a Gallery on the canvas with the datasource 'Loan Table' and include the fields Loan ID, Borrower and 'Total Outstanding'.  Put the following code in the OnSelect property of Gallery.  *Note* Form1 and Screen2 will show an error until we complete Step #4

 

IMGA3.png

 

#4 Create a new screen Screen2.  Place and Edit Form on the screen with the datasource 'Recoveries Table'.  Put this code in the DefaultSelectedItems property of the DataCardValue ComboBox for Loan ID.

 

 

{
	'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
	Id:Gallery1.Selected.ID,
	Value:Value(Gallery1.Selected.'Loan ID')
}

 

 

IMGA4.png

 

#5 Place a Button on the screen.  Put the following code in the OnSelect property.

 

imgA8.PNG

 

#6  To test, fill-in some values and click the button.

 

img6A.PNG

 

#7 Done

 

imgA7.PNG

 

 

 

View solution in original post

18 REPLIES 18
Highlighted
Super User
Super User

Re: How to calculate loan balances in an App

@OsmandFernando 

I will show you how to do it but I will need to make some assumptions about which columns are within which SP List.  See below for my assumptions.

 

Loan Table

  • Loan ID (a unique number identifying the loan)
  • Name of the borrower
  • Total Loan Given
  • Total Outstanding

 

Loan recoveries 

  • Loan ID (a unique number identifying the loan)
  • Total Capital paid (Recovered)
  • Total Interest received

 

One question before I propose a solution.  Is there only 1 entry in the loan recoveries table for each Loan or are there many for each loan?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Post Prodigy
Post Prodigy

Re: How to calculate loan balances in an App

@mdevaney Thank you so much for your reply. Your assumptions are really good. But let me tell you what I have. 

 

Loan Table

  • Loan ID
  • Capital Amount
  • Name of the borrower

I do not have a field for Total outstanding in the Loan table

 

Loan recoveries 

  • Loan ID
  • Capital recovered
  • Interest received
  • Name of the borrower

 

To answer your question, there can be multiple entries under loan recoveries for each loan. 

 

Thank you in advance

Osmand 

Highlighted
Super User
Super User

Re: How to calculate loan balances in an App

@OsmandFernando 

I would like to make a suggestion that you add 2 columns to your Loan Table: Total Capital Recovered and Total Outstanding.  It is definitely possible to calculate these when needed but it will improve performance if you store them in the database instead.

 

Loan Table

  • Loan ID
  • Name of the borrower
  • Capital Amount
  • Total Capital Recovered
  • Total Outstanding

 

When a new loan is created you will start with some capital amount, no capital recovered and the total loan amount still outstanding.  For example:

 

Capital Amount: 100000
Total Capital Recovered: 0
Total Outstanding: 100000

 

When a payment is recorded in the app you can PATCH the Total Capital Recovered and Total Outstanding values.  On your record a payment screen you would put this code in your OnSelect button.

 

SubmitForm(Form_LoanRecoveries);
Patch(
    'Loan Table',
    LookUp('Loan Table','Loan ID' = Form_LoanRecoveries.LastSubmit.'Loan ID'),
    {
        'Total Capital Recovered': Total_x0020x_Capital_x0020x_Recovered 
        + Value(TextInput_CapitalRecovered.Text),
        'Total Outstanding': Total_x0020x_Outstanding
        - Value(TextInput_CapitalRecovered.Text)
    }
);

 

However, if you loan payments are not input into the list via a PowerApps screen we would need to either summarize by using the GROUPBY function.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Post Prodigy
Post Prodigy

Re: How to calculate loan balances in an App

@mdevaney  Thank you for your wonderful reply. That means I have to use the correct Loan ID to record the recoveries and Can I have a lookup for Loan ID from Loan table to the loan recoveries ?

Highlighted
Super User
Super User

Re: How to calculate loan balances in an App

@OsmandFernando 

Yes, you should make the Loan ID column in your 'Loan recoveries' table a LookUp column to 'Loan Table'.  When applying a loan payment the data entry person will be able to choose the specific loan from a searchable list.  The Items property for the ComboBox should automatically generate when creating an Edit Form but here's the code just in case it does not.

 

Choices([@'Loan recoveries'].Loan_x0020_ID)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Post Prodigy
Post Prodigy

Re: How to calculate loan balances in an App

Thank you. That is clear and I have done that. I am now applying your patch formula and having issues, Kindly refer the following image. And also I am not going to input the Total outstanding in the recovery list. That is something I am expecting to calculate automatically. Please help @mdevaney 

 

Capture.PNG

Highlighted
Super User
Super User

Re: How to calculate loan balances in an App

@OsmandFernando 

Upon testing I found LOOKUP needed to be used here instead of directly referencing the column.

 

SubmitForm(RecoveryEditForm);
Patch(
    'Loan Table',
    LookUp(
        'Loan Table',
        'Loan ID' = Value(RecoveryEditForm.LastSubmit.'Loan ID'.Value)
    ),
    {'Total Capital Recovered': LookUp('Loan Table','Loan ID' = Value(RecoveryEditForm.LastSubmit.'Loan ID'.Value),'Total Capital Recovered') + Value(DataCardValue217.Text)}
);

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Post Prodigy
Post Prodigy

Re: How to calculate loan balances in an App

@mdevaney  Formula is working. But noticed the following errors, 

 

  1. It is patching to wrong Loan ID
  2. It is duplicating as well. If I edit a recovery entry and submit again. It is patching it so figures are inaccurate. 
Highlighted
Super User
Super User

Re: How to calculate loan balances in an App

@OsmandFernando

Here's a mini-tutorial.  You can take parts of it and adapt to your situation.

 

#1  Create a new SharePoint List called "Loan Table" with the following columns.

 

imgA1.png

 

#2  Create another SharePoint List called "Loan Recoveries" with the following columns.  Loan ID must be a LookUp type column and reference "Loan Table"

 

IMGA2.png

 

#3 Create a new screen Screen1 in PowerApps.  Place a Gallery on the canvas with the datasource 'Loan Table' and include the fields Loan ID, Borrower and 'Total Outstanding'.  Put the following code in the OnSelect property of Gallery.  *Note* Form1 and Screen2 will show an error until we complete Step #4

 

IMGA3.png

 

#4 Create a new screen Screen2.  Place and Edit Form on the screen with the datasource 'Recoveries Table'.  Put this code in the DefaultSelectedItems property of the DataCardValue ComboBox for Loan ID.

 

 

{
	'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
	Id:Gallery1.Selected.ID,
	Value:Value(Gallery1.Selected.'Loan ID')
}

 

 

IMGA4.png

 

#5 Place a Button on the screen.  Put the following code in the OnSelect property.

 

imgA8.PNG

 

#6  To test, fill-in some values and click the button.

 

img6A.PNG

 

#7 Done

 

imgA7.PNG

 

 

 

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,715)