OData filter function

  • Updated
Follow

REST-Like API / OData Source

Unicontas RESTful API with limited data compatibility is suitable for: - Extraction of master data and transactions. - Integration with reporting tools (Excel, Power BI, etc.) - Data analysis and simple API solutions. - Integration to non Windows based systems.

For full access to all features, please refer to the Unicontas .NET API.

Note: Support does not support API requests, please refer to our resellers:

List of resellers that offer API support

 

Safety and reliability

All communication takes place over HTTPS and is protected with TLS (Transport Layer Security) encryption. This ensures:

By default, the API uses HTTP Basic Authentication, but when used from tools like Excel or Power BI, this happens automatically and securely in the background.

Admission requirements - Log in

To access the API requires: - The user has rights in the desired company - If you are Univisor, the company must have: - Integration package or - Automation package

You can then log in with HTTP Basic Auth (Normal HTTP username/password validation) either in your browser, in Excel or other.

Use in Excel

  1. Data > Retrieve data > From other sources > From OData feed 2. Enter URL 3. Log in with Uniconta user 4. Load data as table or pivot table

Read more here - a detailed setup guide

URL structure

Basic structure:

https://odata.uniconta.com/odata/<FIRMAID>/<ENDPOINT>?<URLPARAMETRE>

Field Explanation Mandatory field
FIRMAID Your Company ID

No (otherwise standard company)
ENDPOINT The table in Uniconta. If not specified, the list of available endpoints is displayed.

No, you can't.
URLPARAMETER Filtering, sorting, pagination

No, you can't.

Examples:

Popular Endpoints

Parameter Description of the parameter
DebtorClient Debtors
CreditorClient Creditors
DebtorTransClient Debtor transactions
GLTransClient Financial postings
GLBudgetLineClient Budget lines
InvItemClient Item list
DebtorOrderClient Sales orders
InvItemClientUser Custom items
 

URL parameters

Parameter Description of the parameter
$filter Filter data based on field values
$orderby Sort by field
$top Max number of rows returned
$skip Skip rows (paging)

Filtering with $filter

With $filter, you can limit the result of your query so that you only retrieve the data you need - for example, a specific customer, entries within a period, or items in a specific group. This saves time, network and system resources.

Comparison operators

Operator Description Example of an example

Master data * 1

Transaction *2
eq Equal to Account eq '1000' Yes, you can Yes, you can
no Not equal to Costmodel ne 'FIFO' Yes, you can Yes, you can
gt Larger than Balance gt 0 Yes, you can Yes, you can
ge Greater than or equal to OrderNumber ge 100 Yes, you can Yes, you can
It Less than Amount lt 1000 Yes, you can Yes, you can
the Less than or equal to Amount le 5000 Yes, you can Yes, you can
startswith() Starts with startswith(Name, 'ABC') Yes, you can Yes, you can
endswith() Ends with endswith(Name, 'ApS') Yes, you can Yes, you can
substringof() Contains substringof(Email, '.com') Yes, you can Yes, you can
tolower() Converts to small characters tolower(Name) eq 'john' Yes, it does
toupper() Converts to large characters toupper(Code) eq 'ABC123' Yes, it does
length() The length of a string length(Name) gt 5 Yes, the length of a string
substring() substring substring(Name, 0, 3) eq 'Joh' Yes, yes
indexof() Index of substring indexof(Name, 'x') gt 0 Yes, yes
trim() Trims spaces trim(Name) eq 'Alice' Yes, you can
  • 1) Master data tables - Indexes

Master data tables support all of the above operations.

You can find a list of these tables in Uniconta under Company - All notes - Here you will see a list of Indexes as below:

  • 2) Transaction tables

Transaction tables only support the most basic operations. Examples of transaction tables:

  • GLTransClient
  • GLDebtorTransClient
 

Logical operators

Operator Description of the operator Example: Example
Duck All conditions must be met Account eq '1000' and Balance gt 0
Gold At least one condition must be met Group eq 'A' or Group eq 'B'
Note Used with other operators to exclude CostModel NOT 'FIFO'

Example URL:

https://odata.uniconta.com/odata/FIRMAID/DebtorClient?$filter=Account eq '1000' and Balance gt 0
Description Table $filter URL
Find debtor with account 1101798 DebtorClient Account eq '1101798' Link to
Accounts receivable with balance > 0 DebtorClient Balance gt 0 Link
Transactions in 2024 DebtorTransClient Date ge datetime'2024-01-01T00:00:00' and Date le datetime'2024-12-31T23:59:59' Link to
Item list without blocked items InvItemClient Blocked eq false Link

Sorting with $orderby

With $orderby you can sort the result by one or more fields. You can specify both ascending (asc) and descending (desc) sorting.

Examples in table

To retrieve the 10 most recent debtors, sorted by name in ascending order, you can use a combined URL:

https://odata.uniconta.com/odata/123456/DebtorClient?$orderby=Name asc&$top=10

Top / Skip - Pagination - Save data

Use pagination to retrieve large datasets in chunks and avoid unnecessary load.

Note only on transaction tables, not master data tables, see info on master data tables vs transaction tables further up.

Examples:

Description URL parameters
First 10 debtors $top=10
Page 2 (rows 11-20) $top=10&$skip=10
Financial items page 3 $orderby=Date desc&$top=50&$skip=100
Last month's postings only $filter=Date ge datetime'2024-05-01T00:00:00' and Date le datetime'2024-05-31T23:59:59'

Feel free to combine $filter, $orderby, $top, $skip for faster and more targeted queries.

Endpoint documentation

Uniconta's REST-like API is built so that each callable endpoint returns its own fields directly in the response. This means you can:

  • Get a quick overview of endpoints on https://odata.uniconta.com/odata/ via your browser, excel, powerbi or other

  • View the data structure by simply calling the endpoint (e.g. with $top=1)

  • View field names, data types and content in JSON format

  • Decode the structure without a separate schema or documentation

Example:

https://odata.uniconta.com/odata/DebtorClientUser

Return data will contain all available fields for a debtor, including system fields and user fields.

Custom fields

If your company has added user fields via Uniconta's customization features (e.g. additional text fields or dropdowns), use the extended endpoints with the suffix User.

Examples:

Links

API support - Uniconta

Developers - Uniconta