English
Conventions
Variables
We use a custom naming convention based on the type of variables.
Each variable name consists of a maximum of 6 components:
- Array (Optional) a_ when it is an array.
- Key (Optional) pk for a primary key, fk for a foreign key, dfk for a key on a discriminator, or efk for a foreign key to an external system.
- Type Always lowercase and represents the variable type.
- Table The first letter is uppercase and the rest are lowercase. Represents the name of the table from which the variable originates.
- Field (Optional) The first letter is uppercase and the rest are lowercase. Represents the name of the field. Exception: if the field name is “ID”, it is kept uppercase to indicate that it is a unique identifier.
- Discriminator (Optional) Present only when two identical fields are stored in the same table to differentiate them from each other.
Here is a summary table explaining the convention.
| Array | Key | Type | Table | Field | Discriminator | |
|---|---|---|---|---|---|---|
| Optional | Yes | Yes | No | No | Yes | Yes |
| Naming | Fixed | Fixed | Lowercase | First letter uppercase and the rest lowercase | First letter uppercase and the rest lowercase, except for the ID field, which is always uppercase | First letter uppercase and the rest lowercase |
| Values | a_ | pk fk dfk efk | s (string) t (text) c (char) sha (sha-1 string) md5 (md5 string) bin (binary string) i (integer) f (float) d (decimal) e (enum) dt (date or datetime) b (boolean) obj (object) m (mixed) | Any value | Any value | Any value |
Here are examples of typical variable names.
| Variable name | Array | Key | Type | Table | Field | Discriminator | Explanation | Example |
|---|---|---|---|---|---|---|---|---|
| pkiContactID | pk | i | Contact | ID | Integer primary key for the ID field in the Contact table | 133 | ||
| fkiContactID | fk | i | Contact | ID | Integer foreign key pointing to the pkiContactID field in the Contact table | 133 | ||
| efkiContactID | efk | i | Contact | ID | External integer foreign key pointing to the pkiContactID field in the Contact table | 133 | ||
| fkiContactIDOwner | fk | i | Contact | ID | Owner | Integer foreign key pointing to the pkiContactID field in the Contact table with a Owner discriminator | 266 | |
| sContactFirstname | s | Contact | Firstname | Character string for the Firstname field in the Contact table | John | |||
| bPurchaseIspaid | b | Purchase | Ispaid | Boolean value for the Ispaid field in the Purchase Table | True | |||
| dPurchaseTotal | d | Purchase | Total | Decimal number for the Total field in the Purchase table | 2199.78 | |||
| objEzsignfolder | obj | Esignfolder | Object of type Ezsignfolder | {"pkiEzsignfolderID": 122, "sEzsignfolderName": "Test"} | ||||
| a_objEzsignfolder | a_ | obj | Ezsignfolder | Array of objects of type Ezsignfolder | [{"pkiEzsignfolderID": 122, "sEzsignfolderName": "Test"}, {"pkiEzsignfolderID": 234, "sEzsignfolderName": "Test 2"}] | |||
| a_sContactFirstname | a_ | s | Contact | Firstname | Array of character strings for the Firstname field in the Contact table | ['John', 'Mary', 'Jane'] | ||
| a_fkiContactIDOwner | a_ | fk | i | Contact | ID | Owner | Array of integer foreign keys pointing to the pkiContactID field in the Contact table with an Owner discriminator | [266, 277, 288] |
List Filter
Each GetList endpoint has an sFilter query parameter that can be used to filter the returned items.
The syntax of the sFilter parameter is not documented for each endpoint, as this would be redundant. This section describes the syntax.
- Each property returned by the endpoint can be used to construct the sFilter string, with a few rare exceptions.
- Each filter can be combined using the and operator.
- Not all properties support all operators. The list of valid operators depends on the variable type. For example, only character string types support the like operator. You can refer to the Variables article in the Conventions section of the documentation to learn about variable types and their representation in variable names.
- Variables of type Enum have a predefined list of filters that will be documented at the endpoint level.
- The value of sFilter must be URL-encoded.
- Character string values must be enclosed in single quotation marks.
Valid operators for Boolean values:
| Operator | Description | Examples |
|---|---|---|
| eq | Equal to | bEzsigndocumentEzsignclause eq true bEzsigndocumentEzsignclause eq false |
Valid operators for integer values:
| Operator | Description | Examples |
|---|---|---|
| eq | Equal to | iEzsigndocumentPagetotal eq 10 |
| gt | Greater than | iEzsigndocumentPagetotal gt 10 |
| gte | Greater than or equal to | iEzsigndocumentPagetotal gte 10 |
| lt | Less than | iEzsigndocumentPagetotal lt 100 |
| lte | Less than or equal to | iEzsigndocumentPagetotal lte 100 |
| in | In the list | fkiEzsignfoldertypeID in '1,2,3' |
Valid operators for date and datetime values:
| Operator | Description | Examples |
|---|---|---|
| eq | Equal to | dtEzsigndocumentDuedate eq '2005-07-01 18:15:59' dtEzsigndocumentDuedate eq '2005-07-01' |
| gt | Greater than | dtEzsigndocumentDuedate gt '2001-01-01 00:00:00' dtEzsigndocumentDuedate gt '2001-01-01' |
| gte | Greater than or equal to | dtEzsigndocumentDuedate gte '2001-01-01 00:00:00' dtEzsigndocumentDuedate gte '2001-01-01' |
| lt | Less than | dtEzsigndocumentDuedate lt '2025-12-31 23:59:59' dtEzsigndocumentDuedate lt '2025-12-31' |
| lte | Less than or equal to | dtEzsigndocumentDuedate lte '2025-12-31 23:59:59' dtEzsigndocumentDuedate lte '2025-12-31' |
| rg | In the list see the documentation for the range operator | dtEzsigndocumentDuedate rg '=m,=m+7d' |
Valid operators for character string values:
| Operator | Description | Examples |
|---|---|---|
| eq | Equal to | sEzsigndocumentName eq 'Test contract' |
| like | Search for a partial character string using the % wildcard character | sEzsigndocumentName like 'Test contra%' sEzsigndocumentName like '%contract' sEzsigndocumentName like '%con%' |
Valid operators for enum values (valid values are documented at the endpoint level):
| Operator | Description | Examples |
|---|---|---|
| eq | Equal to | eEzsigndocumentStep eq 'PartiallySigned' |
| in | In the list | eEzsigndocumentStep in 'PartiallySigned,Archived' |
Example of combining multiple filters:
text
Filter=bEzsigndocumentEzsignclause eq true and iEzsigndocumentPagetotal gt 10 and iEzsigndocumentPagetotal lte 100 and dtEzsigndocumentDuedate gt '2001-01-01 00:00:00' and dtEzsigndocumentDuedate lte '2025-12-31 23:59:59' and sEzsigndocumentName like '%con%' and eEzsigndocumentStep eq 'PartiallySigned' and fkiEzsignfoldertypeID in '1,2,3' and dtEzsigndocumentDuedate rg '=m,=m+7d'`1
Same example, but properly URL-encoded:
text
Filter=bEzsigndocumentEzsignclause%20eq%20true%20and%20iEzsigndocumentPagetotal%20gt%2010%20and%20iEzsigndocumentPagetotal%20lte%20100%20and%20dtEzsigndocumentDuedate%20gt%20%272001-01-01%2000%3A00%3A00%27%20and%20dtEzsigndocumentDuedate%20lte%20%272025-12-31%2023%3A59%3A59%27%20and%20sEzsigndocumentName%20%20like%20%27%25con%25%27%20and%20eEzsigndocumentStep%20eq%20%27PartiallySigned%27%20and%20fkiEzsignfoldertypeID%20in%20%271%2C2%2C3%27%20and%20dtEzsigndocumentDuedate%20rg%20%27%3Dm%2C%3Dm%2B7d%27`1
Range Operator
Dates used in list filters can use the rg operator to define ranges. This allows data to be filtered based on relative dates. The range operator is simply another way of calculating the dates used in filters.
For the remainder of this section, assume that today's date is February 25, 2019, and the time is 10 h 15 min 37 s. Assume that we want to filter the dtInvoiceDate field.
If we wanted to retrieve all invoices that were generated during the previous month, we could use (not URL-encoded for readability): sFilter=dtInvoiceDate gte '2019-01-01 00:00:00' and dtInvoiceDate lte '2019-01-31 23:59:59'
The range operator allows the complexity of date calculations to be handled by the API rather than by the calling application.
The general format of the range operator is as follows: dtInvoiceDate rg '[STARTDATE],[ENDDATE]'
[STARTDATE] and [ENDDATE] use the same format, which is a sequence of one or more [SUBSECTION]. For example, we could have: dtInvoiceDate rg '[SUBSECTION],[SUBSECTION][SUBSECTION][SUBSECTION][SUBSECTION]'
Both [STARTDATE] and [ENDDATE] have a starting time corresponding to the current time (so, in this example, 2019-02-25 10:15:37).
A [SUBSECTION] starts with an operator that can be = to reset the pointer, + to move forward in time, or - to move backward in time.
The = operator can be followed directly by a letter representing the [PERIOD] (e.g., =m) to set the date to the beginning or end of the period, or by a number and a letter representing the [PERIOD] (e.g., =7m) to set the [PERIOD] to a specific value.
The + and - operators are followed by a number, then a letter representing the [PERIOD] (e.g., +7d or -1m).
Here is a list of valid [PERIOD] values:
| [PERIOD] | Description |
|---|---|
| y | Year |
| m | Month |
| w | Week |
| d | Day |
| h | Hour |
| i | Minute |
| s | Second |
The = operator without a number resets the pointer to the beginning or end of the [PERIOD], depending on whether it is used in [STARTDATE] or [ENDDATE]. The following table indicates when the pointer is reset.
| Syntax | Description | [STARTDATE] | [ENDDATE] |
|---|---|---|---|
| =y | Year | 2019-01-01 00:00:00 | 2019-12-31 23:59:59 |
| =m | Month | 2019-02-01 00:00:00 | 2019-02-28 23:59:59 |
| =w | Week | 2019-02-24 00:00:00 | 2019-03-02 23:59:59 |
| =d | Day | 2019-02-25 00:00:00 | 2019-02-25 23:59:59 |
| =h | Hour | 2019-02-25 10:00:00 | 2019-02-25 10:59:59 |
| =i | Minute | 2019-02-25 10:15:00 | 2019-02-25 10:15:59 |
| =s | Second | 2019-02-25 10:15:37 | 2019-02-25 10:15:37 |
INFO
The start day of the week is configurable for each user.
The = operator followed by a number resets the [PERIOD] to a specific value and works the same way for [STARTDATE] and [ENDDATE]. Here are some examples. Please note that it is not possible to reset the week this way (e.g., =7w).
| Syntax | Description | New date |
|---|---|---|
| =2025y | Year | 2025-02-25 10:15:37 |
| =11m | Month | 2019-11-25 10:15:37 |
| =7d | Day | 2019-02-07 10:15:37 |
| =17h | Hour | 2019-02-25 17:15:37 |
| =1i | Minute | 2019-02-25 10:01:37 |
| =18s | Second | 2019-02-25 10:15:18 |
Combining [SUBSECTION]
You can combine multiple [SUBSECTION] under the same operator. For example:
- Instead of using =m=7d=8h=6m=32s, you can simplify the expression to =m7d8h6m32s.
- Instead of using +7d+7h+7m+7s, you can simplify the expression to +7d7h7m7s.
- Instead of using =3m=m, you can simplify the expression to =3mm.
Order of precedence
[STARTDATE] and [ENDDATE] are evaluated from left to right. The order matters. For example, the following values would produce different results in [ENDDATE]:
- =m-1m would give 2019-01-28 23:59:59
- -1m=m would give 2019-01-31 23:59:59
Usage Examples
| Syntax | Explanation | Start date | End date |
|---|---|---|---|
| sFilter=dtInvoice rg '-7d,=s' | Invoices from the last 7 days up to now | 2019-02-18 10:15:37 | 2019-02-25 10:15:37 |
| sFilter=dtInvoice rg '=d-7d,=s' | Invoices from the last 7 days, starting at 00:00:00 and up to now | 2019-02-18 00:00:00 | 2019-02-25 10:15:37 |
| sFilter=dtInvoice rg '=m-1m,=m-1m' | Invoices from the last month | 2019-01-01 00:00:00 | 2019-01-31 23:59:59 |
| sFilter=dtInvoice rg '=m,=m' | Invoices from this month | 2019-02-01 00:00:00 | 2019-02-28 23:59:59 |
| sFilter=dtInvoice rg '=m-1m+10d,=s' | Invoices from the 10th of last month up to now | 2019-01-10 00:00:00 | 2019-02-25 10:15:37 |
| sFilter=dtInvoice rg '-10h,=d8h+1d1s' | Invoices from the last 10 hours until 9:00 AM tomorrow | 2019-02-25 00:15:37 | 2019-02-26 09:00:00 |
| sFilter=dtInvoice rg '-1y=4mm,=3mm' | Invoices from the second half of last year through the first quarter of this year | 2018-04-01 00:00:00 | 2019-03-31 23:59:59 |
| sFilter=dtInvoice rg '=w-3w,=w' | Invoices for the last 3 weeks (with Sunday as the first day of the calendar week) through the end of the week | 2019-02-03 00:00:00 | 2019-03-02 23:59:59 |
| sFilter=dtInvoice rg '=y,=3mm' | Invoices for the first quarter | 2019-01-01 00:00:00 | 2019-03-31 23:59:59 |
| sFilter=dtInvoice rg '=4mm,=6mm' | Invoices for the second quarter | 2019-04-01 00:00:00 | 2019-06-30 23:59:59 |
| sFilter=dtInvoice rg '=7mm,=9mm' | Invoices for the third quarter | 2019-07-01 00:00:00 | 2019-09-30 23:59:59 |
| sFilter=dtInvoice rg '=10mm,=y' | Invoices for the fourth quarter | 2019-10-01 00:00:00 | 2019-12-31 23:59:59 |