LoanPro API Filtering

General

Several LoanPro API endpoints use OData so you can filter your results without having to use an Elasticsearch object. Any endpoint that includes /odata.svc/ works with OData functionality. This includes some Elasticsearch endpoints. When querying any API endpoint where multiple results are possible, the API will return 50 results by default. The reason for this is that usually, pagination is preferred rather than pulling every result. You can use OData filters to change the number of returned results.

Filter Example

For example, if you want to pull data from the loan status archive for a specific loan, you can use the endpoint:

GET https://loanpro.simnang.com/api/public/api/1/odata.svc/Loans({id})/StatusArchive

This will pull data from the loan status archive for the loan with the ID that appears in the parentheses  after Loans. Because the URL contains odata.svc, you know you can use filters. Our completed filter looks like this:

$filter=date gt datetime'2019-07-01T00:00:00' and date lt datetime'2019-07-10T00:00:00'

Now we'll go over the parts of the filter

  • $filter= specifies you are using a filter.
  • date is one of labels for data returned by our endpoint: /odata.svc/Loans({id})/StatusArchive
{
"d": {
"results": [
{
"__metadata": {
"uri": "https://loanpro.simnang.com/api/public/api/1/odata.svc/LoanStatusArchive(id=702)",
"type": "Entity.StatusArchive"
},
"id": 702,
"loanId": 702,
"date": "/Date(1562112000)/",
"followUpDate": "/Date(-62169984000)/",
"amountDue": "0.00",
"dueInterest": "0.00",
"duePrincipal": "0.00",
"dueDiscount": "0.00",
"dueEscrow": "0.00",
"dueEscrowBreakdown": "{\"2\":0}",
"dueFees": "0.00",
"duePni": "0.00",
"payoffFees": "0.00",
"delinquentBucket": 0,
"delinquentBucketBalance": null,
"nextPaymentDate": "/Date(1431043200)/",
"nextPaymentAmount": "398.69",
"lastPaymentDate": null,
"lastPaymentAmount": "0.00",
"principalBalance": "226.18",
"amountPastDue30": "0.00",
"daysPastDue": 0,
"dateLastCurrent": "/Date(1562112000)/",
"dateLastCurrent30": null,
"payoff": "0.00",
"perdiem": "0.00",
"interestAccruedToday": "0.00",
"availableCredit": "0.00",
"creditLimit": "15500.00",
"periodStart": null,
"periodEnd": null,
"periodsRemaining": 0,
"escrowBalance": "0.00",
"escrowBalanceBreakdown": "{\"1\":0,\"2\":0}",
"discountRemaining": "0.00",
"loanStatusId": 3,
"loanStatusText": "Deferment Process",
"loanSubStatusId": 16,
"loanSubStatusText": "Ready for admin review",
"sourceCompanyId": null,
"sourceCompanyText": null,
"creditStatus": "loan.creditstatus.11",
"loanAge": 1518,
"loanRecency": -1,
"lastHumanActivity": "/Date(1562112000)/",
"stoplight": null,
"finalPaymentDate": "/Date(1523145600)/",
"finalPaymentAmount": "228.45",
"netChargeOff": "142.20",
"firstDelinquencyDate": "/Date(1433635200)/",
"uniqueDelinquencies": 1,
"delinquencyPercent": "97.09",
"delinquentDays": 1036,
"calcedECOA": "loan.ecoacodes.1",
"calcedECOACoBuyer": "loan.ecoacodes.0",
"customFieldsBreakdown": null,
"portfolioBreakdown": "[]",
"subPortfolioBreakdown": "[]"
}
],
"summary": {
"start": 0,
"pageSize": 1,
"total": 1
}
}
}
  • gt - this means greater than. The other choices for a comparator are eq (equal), lt (less than), ge (greater than or equal), and le (less than or equal).
  • datetime - this specifies that the data we are comparing to is of the type date time. If you are comparing to text or a number, this is not necessary.
  • '2019-07-01T00:00:00'  - this is the value we are comparing to. In this case, midnight on July 1, 2019. The single quotes are necessary for date time or text values. If the comparison is to a number, the straight quotes aren't necessary. Any date time value must be formatted in this way.
  • and - this adds another comparison to the filter and acts as a logic AND. or can also be used, and will act as a logical OR.
  • The rest of the filter is another comparison comprised of the same things are the first comparison.

Our final request URL will look like this: https://loanpro.simnang.com/api/public/api/1/odata.svc/Loans(702)/StatusArchive?all&$filter=date gt datetime'2019-07-01T00:00:00' and date lt datetime'2019-07-10T00:00:00'

We are using the all parameter to indicate that we don't want only the most recent record.

Using a filter like this is helpful when looking at data for a single loan. If you are using a report endpoint (e.g. https://loanpro.simnang.com/api/public/api/1/Autopal.ArchiveLoanReport) you should use an Elasticsearch query object to filter your search.

Other Tokens

There are other tokens you can use to get the results you want form an endpoint.

For example, if you pull a payments report form LoanPro, you should  use the endpoint: POST https://loanpro.simnang.com/api/public/api/1/Autopal.PaymentReport

This will give you the first 50 results from the report. These results will be the first 50 payments in the report. This does nothing to filter by date range, or ID.

Let's start by looking at the following filters:

  • $start
  • $top
  • $skip

$start can be used to identify the record that you'd like the results to start with. By default, the results are ordered by ID. So, if your call is the following:

POST https://loanpro.simnang.com/api/public/api/1/Autopal.PaymentReport?$start=10563

You will get 50 results, starting with the record that has the ID of 10563.

$top is used to specify the number of results you want returned. The default is 50, but a filter of $top=1 will return a single result. $top=1000000 will return 1,000,000 results. For most companies, this means it will return all results. If you want a specific record, you can always combine these two filters like this:

POST https://loanpro.simnang.com/api/public/api/1/Autopal.PaymentReport?$top=1&$start=10563

This will return the single record with the ID of 10563.

$skip is usually used to return paginated results. Use $skip to skip some number of records in the returned results. For example, if you send the request:

POST https://loanpro.simnang.com/api/public/api/1/Autopal.PaymentReport?$skip=50

You will get 50 records, starting with the 51st record in the payments table.

Note: Dates and times in the database are in UTC time, and are not adjusted for timezone.


How did we do?


Powered by HelpDocs (opens in a new tab)