Searching, Paging and ExtendedInfo

Tags:
Categories:

Building a Search Query

Most EPASS records can be retrieved using a SEARCH endpoint and sending a query in the payload.

For example, to search for and retrieve a customer record by it’s ID (or code), you would send:

POST /customer/search
{
    "query": {
        "fieldName": "ID",
        "eval": "EQUAL",
        "value": "12345"
    }
}

You can expand the search query by including a number of sub-query arrays.

To retrieve a customer record where the customer’s last name equals “Smith”, and they are located in the city of Vancouver, you would send:

POST /customer/search
{
    "query": {
        "fieldName": "lastname",
        "eval": "EQUAL",
        "value": "SMITH"
    }, 
    "subquery":[
        {
            "logic": "AND",
            "query": {
                "fieldName": "CITY",
                "eval": "EQUAL",
                "value": "Vancouver"
            }
        }
        
    ]
}

Search queries can be as expansive as needed by adding additional sub-queries and changing the logic operator.

In this example, all customer records with the last name “Smith” or “Jones” that live in Vancouver are retrieved:

POST /customer/search
{
    "query": {
        "fieldName": "lastname",
        "eval": "EQUAL",
        "value": "SMITH"
    }, 
    "subquery":[
        {
            "logic": "OR",
            "query": {
                "fieldName": "lastname",
                "eval": "EQUAL",
                "value": "JONES"
            }
        },
        {
            "logic": "AND",
            "query": {
                "fieldName": "city",
                "eval":"EQUAL",
                "value": "Vancouver"
            }
        }
    ]
}

Paging

Paging is a core component of endpoint searching provided by the xAPI. Paging prevents overconsumption of resources for a single query, and allows the calling application to control their response speed to payload size ratio.

Each time a search is executed it returns an indicator which informs the calling application whether or not there are more records after this page.

For example, if the search returned 1,000 rows and the page size was set to 100, the calling application would need to call the endpoint 10 times to return all 1,000 rows of data.

Here’s an example of a search endpoint with paging:

POST /customer/search
{
   "paging": {
        "pageSize": 100,
        "pageStartId": 0
    },
    "query": {
        "fieldName": "lastname",
        "eval": "EQUAL",
        "value": "SMITH"
    }  
}

The results of this query will return the highest unique record key on this given page, as well as provide a Boolean value indicating if there are more pages.

{
  "Results": {
    "Success": true,
    "Message": "Returned 100 records",
    "LastException": null
  },
  "Paging": {
    "PageSize": 100,
    "LargestID": 271620,
    "PagesRemaining": true
  }
    ...

In the above example, the next query would simply issue the request again except change the starting page ID:

POST /customer/search
{
   "paging": {
        "pageSize": 100,
        "pageStartId": 271620
    },
    "query": {
        "fieldName": "lastname",
        "eval": "EQUAL",
        "value": "SMITH"
    }  
}

ExtendedInfo

To include fields from related tables in your search query, you have to include them in an ExtendedInfo object. The ExtendedInfo object is added to the end of your search query.

For example, to return the details of an invoice, including the details of the primary Salesperson that’s attached to the invoice (which is contained in the Salesperson table), you would send the following endpoint and query:

POST /invoicing/invoice/search/code
{
    "query": {
        "invoicecode": "123456" 
    },
    "extendedInfo": {
        "Salesperson1": []
    }
}

The response would include data from all the invoice fields, as well as the data from the Salesperson table. This data is included at the end of the response, within an ExtendedInfo object:

...
"ExtendedInfo": {
        "Salesperson1": {
          "Fields": {
            "ID": 345,
            "Code": "00001",
            "Description": "ASH WILLIAMS",
            "SalesGroup": "INSIDE SALES",
            "Obsolete": false,
            "BranchCode": "Default",
            "Email": "awilliams@acme.com",
            "CurrentSales": false
          }
        }
      }

You can also specify which data fields are returned by entering the field names in the table array. Using the above example, if you wanted the query to return just the Salesperson’s ID and Description, you would send the following endpoint:

POST /invoicing/invoice/search/code
{
    "query": {
        "invoicecode": "123456" 
    },
    "extendedInfo": {
        "Salesperson1": [
            "ID",
            "DESCRIPTION"
        ]
    }
}

The response will include the data from the ID and Description fields only:

...
"ExtendedInfo": {
        "Salesperson1": {
          "Fields": {
            "ID": 345,
            "Description": "ASH WILLIAMS"
          }
        }
      }