API – Custom Query

Custom queries are a powerful way to pull data from LoanPro. It offers pulling specific information that can include current, archived, or reverse archived values from loans that match a certain query and exports it to a CSV file. This allows you to pull what you need in an easy-to use format.

This article will discuss how to use this using the API.

WARNING: This is a highly advanced topic and pulling custom queries through the API should only be done by those experienced with the LoanPro API! Otherwise, please pull it from the UI as discussed in Run a Custom Query Report!

Getting Variables

Before we can start creating a custom query, we need to know which variables we can use for the custom query. This is done by sending a GET request to api/public/api/1/odata.svc/ContextVariables, usually with a nopaging attribute added on, as follows:

GET https://loanpro.simnang.com/api/public/api/1/odata.svc/ContextVariables?nopaging

This endpoint will return tons of variables that are available for you to use. Below is a sample response:

{
 "d": {
 "results": [
  {
    "__metadata": {
     "uri": "http://loanpro.simnang.com/api/public/api/1/odata.svc/ContextVariables(id=69)",
     "type": "Entity.ContextVariable"
    },
    "id": 69,
    "parent": null,
    "name": "source-company-country",
    "friendlyName": "Country",
    "format": "context.format.selection",
    "section": "context.section.source",
    "computation": 0,
    "flags": 0,
    "stoplights": 0,
    "mailMerge": 1,
    "created": "/Date(1436818217)/"
   },
   {
    "__metadata": {
     "uri": "http://loanpro.simnang.com/api/public/api/1/odata.svc/ContextVariables(id=70)",
     "type": "Entity.ContextVariable"
    },
    "id": 70,
    "parent": null,
    "name": "source-company-bank-name",
    "friendlyName": "Bank Name",
    "format": "context.format.text",
    "section": "context.section.source",
    "computation": 0,
    "flags": 0,
    "stoplights": 0,
    "mailMerge": 1,
    "created": "/Date(1436818217)/"
   }
  ],
  "summary": {
   "start": "25",
   "pageSize": "2",
   "total": 2
  }
 }
}

The fields that are returned and what they mean are listed below:

  • id – This is the system ID for the variable. We won’t be using this so you don’t have to worry about it.
  • parent – This specifies the type of entity that this variable belongs to. We’ll discuss this more later.
  • name – This is the system name for the variable. This is quite useful as we’ll see later.
  • friendlyName – This is the human-readable name for the variable. If you’re going to display a name for a variable to a user, we strongly recommend to use this name.
  • format – This specifies the format that the variable is in
  • section – This specifies the variable section that the variable belongs to
  • computation – This specifies whether or not the variable is computed (if it’s a “1” then the variable is computed). We recommend using as few of these as possible to speed up the query time
  • flags – This is system reserved, so we won’t worry about this
  • stoplights – Same as flags
  • mailMerge – Ditto
  • created – This is the timestamp that the variable was created. We won’t be using this in this article.

Additionally, each request will return the most recent list of available variables. This means that as settings in your tenant changes or improvements are published to LoanPro, you can poll the endpoint again to get the latest list version.

This also means that you can implement a cache (if desired) on your side to lower the amount of times that you have to poll this endpoint, as the list can only change after a scheduled release or someone inside your company changes a setting. By implementing a cache, you’ll be able to reduce the amount of API calls and speed up your software integration.

Please note that how to implement the cache will vary depending on your system and the protocols that are in place for your company on when settings get changed and how that is reported (sometimes you won’t be able to cache for very long or at all since your company may change it’s settings very frequently).

Also, make sure to pay attention to your pagination settings, otherwise you’ll get a small subset of the variables available and be missing some!

Generating The Payload

First, we’ll need a JSON object with a key “search”; that’s where we’ll be doing the rest of our payload. See below for an example:

{
   "search":{
    }
}

Next, we have two main section inside of that: the query and the reportColumns. The query is going to be a Query Object, while the reportColumns is going to be an array. Go ahead and add those in, as shown below.

{
   "search":{
      "query":{},
      "reportColumns":{}
    }
}

Next, fill out you’re query object. In this article we’re going to do a simple one, but feel free to use a more advanced version for your query object.

{
   "search":{
      "query":{
         "bool":{
            "must":[
               {
                  "query_string":{
                     "query":"*Demo*",
                     "fields":[
                        "title",
                        "displayId"
                     ]
                  }
               }
            ]
         }
      },
      "reportColumns":[]
   }
}

Report Columns

Next, we have the reportColumns section. This is where we’ll start using the list of variables that we got earlier. It’s also where things start getting complicated.

First, we’ll start off with the simple variables. The simplest variables to use are the ones that are not computation variables (they returned with the “computation” field set to 0). The reasons for this will become apparent later on. For now, though, we’ll just stick with non-computation variables.

Adding a Non-Computation Variable

To add a non-computation variable, we’ll need to tell the system the variable’s friendlyName, name, ruleId, helpVarId, format, and more. Basically, we’re going to add a JSON object the reportColumns array that looks like the following:

{
   "name":"status-scheduled-payments",
   "format":"context.format.numeric",
   "columnName":"Scheduled Payments"
 },

At first, that seems like some cryptic information. Don’t worry, we go through all the fields, what they mean, and what to set them to below.

  • name – This is the system name of the variable; this must match what is in the variable list.
  • format – This is the format of the variable; this must match what is in the variable list.
  • columnName – This is the column name you want to have appear in the CSV file. You can set it to whatever you want, but it’s recommended to set it to something meaningful.

That wasn’t so bad and it works well for non-calculated values. However, once we start getting into calculated values we’ll see that things get slightly more complicated.

Adding a Computed Variable

We’ll now take the above object, and add some new fields. However, these fields change shape depending on what you want to do.

With computed variables, you’ll be able to pull them from an archive, or calculate the current date. This is since most calculated fields are archived. Pulling from an archive can be faster than calculating the value, but they can also be slower depending on which archive it references and how much of that archive is kept in the system. Knowing when it’s faster and when it’s slower is beyond the scope of this article, but the important thing to keep in mind is to expect a computed field (whether pulled from an archive or not) will generally be slower than a non-computed field.

So, what fields do we need to add? Well, first we need to add a field called “arcConf“. This field is going to be a JSON object which says how to calculate the field.

There are three different flavors of arcConf. The flavors are:

  • current – This calculates the current value
  • archive – This pulls from an archive table
  • reverse – This pulls from the reverse archive

Additionally, for archive and reverse, you have two different ways to specify the date of the value. The first is to specify the number of days in the past (relative to the current day); the second is to specify the full date.

To specify the flavor of arcConf, you’ll need to add a “set” field to the object with the flavor as the value. Below is a sample of the configuration for each flavor.

Current
Archive
Reverse Archive
{ “arcConf”:{ “set”:”current” } }
{ “arcConf”:{ “set”:”archive” } }
{ “arcConf”:{ “set”:”reverse” } }

 

Specifying the number of days in the past will change based off of how you want to do it. Both require a “type” field that will be set to either “days” for specifying the number of days away or “date” for specifying the hard-coded date and both require a “val” field.

For the “day” option “val” is the number of days in the past relative to the current day. For the “date” option “val” will be set to the date you want. Below is an example.

Days
Date
{ “arcConf”:{ “set”:”archive”,”type”:”days”,”val”:3 } }
{ “arcConf”:{ “set”:”archive”,”type”:”date”,”val”:”2016-11-09″ } }

Naming a Custom Query

In addition to creating a custom query, it can also be named. This allows the custom query to be readily identified when browsing a list of custom queries that have been generated. To do this, simply add the “savedSearchTitle” to the root of the payload, as follows:

{
   "search":{
    ...
    },
    "reportColumns":{
    ...
    },
    "savedSearchTitle":"Custom Query Via The API"
}

The value specified for the “savedSearchTitle” field will be the name of the custom query in the UI. By default, this defaults to “Custom Query”. Please note that this name does not have to be unique.

Sending a Request

Once your payload is generated, send a POST request to https://loanpro.simnang.com/api/public/api/1/CustomQueryReport/Autopal.SearchDataDump()/csv with the payload as the request body.

Response

Once the payload is successfully made and sent to the server, the server will reply with a response. This response is important since it gives important information about how to find the custom query once it is generated. Below is a sample response:

{
   "d":{
      "__metadata":{
         "uri":"http://loanpro.simnang.com/api/public/api/1/odata.svc/DataDumps(id=1)",
         "type":"Entity.DataDump"
      },
      "id":1,
      "entityType":"Reports.CustomQuery.Admin",
      "fileName":null,
      "url":null,
      "status":"dataDumpProcess.status.inProgress",
      "created":"/Date(1480092388)/",
      "createUser":"API User",
      "info":"custom search"
   }
}

There are several important fields. The first is the “id” field; this is the id of the DataDump entity which will hold the custom query information. The second is the status, which specifies the status of the custom query. Finally, there is the URL. Initially, the URL will be null since the custom query is in the generation process. However, if the custom query finishes successfully then it will be the URL from which the query can be generated. Please note that custom queries are only kept on the LoanPro server for about 24 hours after generation has completed. Once the file has existed for 24 hours it will be deleted.

To check a custom query’s status, simply send a GET request to the following URL:

GET https://loanpro.simnang.com/api/public/api/1/odata.svc/DataDumps(<id>)

Replace “<id>” with the ID received from the earlier server response. When the status field is set to “dataDumpProcess.status.complete”, it means that the query was successfully generated. When this happens, check the URL field to get the file URL.

Downloading a Completed Custom Query

Once your custom query is complete, send a GET request to the URL in the “url” field of the DataDump entity. This will give you a CSV file that you can then save and process. Below is an example:

Loan ID,Nxt Pmt Amt
"44449995",""
"44450003",""
"44450053","150.00"
"44450055",""
"44450082","2083.83"
"44450083","1917.41"
"44450084","2085.83"

Complete Payload Example

For those who wish to see a full payload example, one is provided below:

POST https://loanpro.simnang.com/api/public/api/1/CustomQueryReport/Autopal.SearchDataDump()/csv

{
   "search":{
      "query":{
         "bool":{
            "must":[
               {
                  "query_string":{
                     "query":"*Demo*",
                     "fields":[
                        "title",
                        "displayId"
                     ]
                  }
               }
            ]
         }
      },
      "reportColumns":[
         {
            "friendlyName":"Sub-Portfolio List",
            "format":"context.format.text",
            "includeInReport":"1",
            "name":"sub-portfolio-list",
            "items":[
            ],
            "visible":false,
            "label":"Sub-Portfolio List",
            "columnName":"Sub-Portfolio List",
            "custom_column":"Sub-Portfolio List",
            "uid":"0.37338010413410605",
            "children":[
            ],
            "expanded":false,
            "classes":[
               "leaf"
            ],
            "selected":false
         },
         {
            "friendlyName":"Portfolio List",
            "format":"context.format.text",
            "includeInReport":"1",
            "name":"portfolio-list",
            "items":[
            ],
            "visible":false,
            "label":"Portfolio List",
            "columnName":"Portfolio List",
            "custom_column":"Portfolio List",
            "uid":"0.2521413281282239",
            "children":[
            ],
            "expanded":true,
            "classes":[
               "leaf"
            ],
            "selected":true
         }
      ]
   }
}

 

 

Related Articles

Leave A Comment?