cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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
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
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."

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 

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."

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 ?

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."

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

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."

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. 
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
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 (Last 30 Days)
Users online (6,903)