Retrieving More Complex Data

Some objects are more complex and hold higher volumes of data than previously discussed.

These objects are:

• Forms- these hold your form data and can hold hundreds of thousands to millions of data rows
• Jobs - prepopulated forms that are sent to the client and filled in and returned
• Notification History - the audit and sent history for notifications that are triggered when form data arrives

For each of these types of item, retrieving data is done by posting a filter object to a URL. Let's take retrieving form records as an example. The same principles apply to those objects where a search by filter is supplied.

Imagine for example that we have a form containing 200,000 records. It goes without saying that we wouldn't want to retrieve all of these records in one go. Instead, we can utilise the Form Complete Record Search.

/api/Forms/{FormId}/completedrecords/search

This method takes an object as a filter in the request body and we make an HTTP POST. The full packet looks as follows:

{
  "createdDateFrom": "2021-12-16T19:44:22.245Z",
  "createdDateTo": "2021-12-16T19:44:22.245Z",
  "uploadedDateFrom": "2021-12-16T19:44:22.245Z",
  "uploadedDateTo": "2021-12-16T19:44:22.245Z",
  "id": 0,
  "originalId": 0,
  "jobId": 0,
  "mobileUserId": 0,
  "userGroup": "string",
  "userDefinedFilter": [
    {}
  ],
  "orderBy": [
    {
      "propertyName": "string",
      "descending": true
    }
  ],
  "pagination": {
    "page": 1,
    "rowCount": 500
  }
}

The only attributes that are always required are orderBy and pagination. All other elements are optional.  A description of the attributes is listed below:

For example, to return records by a single mobile user, you would supply something like: 

{
  "mobileUserId": 18635,
  "orderBy": [
    {
      "propertyName": "id",
      "descending": true
    }
  ],
  "pagination": {
    "page": 1,
    "rowCount": 500
  }
}

The above query would retrieve the first 500 records that were collected by Mobile User Id 18635. To retrieve the second batch, you'd make a subsequent call, setting the page element to 2. 

User Defined Filters

As well as the predefined attributes that are applicable to any form. You can also search on of the custom fields on a form, as long as the field has been designated 'Searchable' using the checkbox in the form designer.

The userDefinedFilter is used by passing in an array of items containing the following elements:

{
	uniqueName: "<unique name of the field>">, 
	"value": "<value to be filtered by>"
}

For example, the following would filter on the field MeterType for all records containing the value Gas.

"userDefinedFilter": [
	{
    	uniqueName : "MeterType", 
    	"value" : "Gas"
	}
]  

You can AND multiple fields together as in the example below:

"userDefinedFilter": [
	{
    	uniqueName : "MeterType", 
        "value" : "Gas"
    }, 
    {
    	"uniqueName" : "Manufacturer", 
        "value" : "solenvis" 
    }
] 

By specifying the field name twice, you are implying a logical OR:

"userDefinedFilter": [
	{
		uniqueName : "MeterType", 
		"value" : "Gas"
		}, 
	{
		uniqueName : "MeterType", 
		"value" : "Water"
		}, 
	{
		"uniqueName" : "Manufacturer", 
		"value" : "solenvis" 
	}
] 

Internally this is interpreted as:

 ((MeterType = "Gas" OR MeterType = "Water") AND Manufacturer = "solenvis")