Filtering
While retrieving data from Accounting System using Connect APIs, particular records in Accounting System can be filtered using HTTP question mark and 'where' clause at the end of the API End-point that are used to make GET calls.
To query any API, following format should be used:<br> GET api.connect.webgility.com/v1/{API name}?where={field1name}=’{value1}’AND {field2name}=’{value2}’<br>
e.g. In order to retrieve only that account from Accounting system that has AccountId equal to 55 and AccountCode equal to ACC01, the API call would be as follows: <br>GET https://api.connect.webgility.com/v1/accounts?where=AccountId=’55’ANDAccountCode=’ACC01’
The number of states and attributes behind the HTTP question mark should not go out of the maximum limit of HTTP URL limit=2048 characters & the values for query parameters have to be URL-encoded.
APIs support querying for only selected fields with respect to each API. The fields that can be used to query Accounting System data are as listed below:
| API name | Fields that can be used for Querying GET Data |
|---|---|
| Accounts | AccountId, AccountCode, Name, Type, Classification, IsActive |
| Customers | CustomerId, CustomerName, FirstName, Lastname, CompanyName, IsActive, EmailAddress |
| Invoices | InvoiceId, InvoiceNumber, InvoiceDate, DueDate, AmountDue, Status, Total |
| Items | ItemId |
| ManualJournals | ManualJournalId, JournalDate |
| SalesTransactions | CustomerId, CustomerName, EmailAddress |
| Vendors | VendorId, VendorName, FirstName, Lastname, CompanyName, IsActive, EmailAddress |
We suggest you to always use 'case sensitive' data for names and values of various fields while querying data as some Accounting System do not support 'case-insenstive' values for few fields.
Currently, only ‘AND’ operator is supported for querying Connect APIs. Querying using 'OR' operator is not yet supported due to limitation in some of the Accounting systems.
While using 'AND' operator, there is no restriction on the number of fields that can be used for querying. <br>
The following types of comparisons are supported by Webgility Connect:
| Comparison Operator | Meaning |
|---|---|
| = | x equals y |
| < | x is less than y |
| <= | x is less than or equal to y |
| > | x is greater than y |
| >= | x is greater than or equal to y |
| % | x LIKE y where y may contain wildcards |
For all the date and amount fields that can be used for querying, the comparison operators '>’, ‘=’ and ‘<’ can be used while for all other fields, only ‘=’ is supported. Dates should be used only in UTC format.
e.g. In order to retrieve only those invoices from Accounting system that have Amount due more than $1000, the API call would be as follows: <br>GET https://api.connect.webgility.com/v1/invoices?where=AmountDue>’1000’ <br>
You can also use ‘%’ in the values of states & attributes to query for results that have value like the state and attribute mentioned. If the attribute value itself has ‘%’ in it, then add 2 additional % in the query.
e.g. <br>In order to retrieve account(s) with account name starting with 'ch', the API call would be as follows: <br>GET https://api.connect.webgility.com/v1/accounts?where=AccountName=ch%
In order to retrieve account(s) with account name ending with 'ch', the API call would be as follows: <br>GET https://api.connect.webgility.com/v1/accounts?where=AccountName=%ch
In order to retrieve account(s) where account name has 'ch' somewhere and starting and ending with anything, the API call would be as follows: <br>GET https://api.connect.webgility.com/v1/accounts?where=AccountName=%ch%
In order to retrieve account(s) with account name starting with '10%', the API call would be as follows: <br>GET https://api.connect.webgility.com/v1/accounts?where=AccountName=10%%%
In order to retrieve account(s) with account name ending with '10%', the API call would be as follows: <br>GET https://api.connect.webgility.com/v1/accounts?where=AccountName=%10%%
In order to retrieve account(s) where account name has '10%' somewhere and starting and ending with anything, the API call would be as follows: <br>GET https://api.connect.webgility.com/v1/accounts?where=AccountName=%10%%%
If the field sent in the query is not supported, then error message displayed is: "Querying by ‘xyz’ field is not supported".
If the Query format is invalid, then error message displayed is: "Invalid Query format".
Copyright 2014 © Webgility, Inc. All rights reserved