How to calculate loan balances in an App

[OsmandFernando]

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.

[mdevaney]

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

[OsmandFernando]

@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 

[mdevaney]

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

[OsmandFernando]

@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 ?

[mdevaney]

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

[OsmandFernando]

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 

[mdevaney]

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

[OsmandFernando]

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

[mdevaney · Accepted solution]

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

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

#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

#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')
}

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

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

#7 Done