Loading
Uniconta
Get Uniconta
  • Visit a Country Site
    • Danmark
    • Deutschland
    • Eesti
    • Ísland
    • Lithuania
    • Nederland
    • Norge
    • Österreich
    • United Kingdom
  • Search
  • Uniconta
    • Ledger
    • Customer
    • Sales Order
    • Vendor
    • Purchase
    • Inventory
    • Logistics
    • Project
    • Light Manufacturing
    • CRM
    • Dashboard
    • Company
    • Adaptability
  • Download
    • Uniconta for Windows
    • Uniconta for Mac
  • Resellers
    • Find a reseller
    • Become a reseller
    • Become an integration partner
    • Partner Portal Login
    • Partner Portal sign up
  • Developers
    • Become an integration partner
    • Documentation
    • Downloads
    • Samples
    • Support
  • Accountants
    • Become a Univisor
  • News
  • About us
    • About us
    • Management
    • Contact

Unipedia

  • Introduction
    • Get started with Uniconta
    • Which browser are you using?
    • Get started with an existing set of accounts
  • Converting to Uniconta
    • Conversion tool
    • What can be converted to Uniconta?
    • Converting from NAV to Uniconta
    • Converting from e-conomic to Uniconta
    • Converting from C5 to Uniconta
    • Converting from eCtrl to Uniconta
    • Checking your accounts after conversion
  • General Ledger
    • How to split voucher PDF
    • Chart of Accounts
      • Chart of Accounts
      • Account types
      • System Accounts
      • Import a Chart of Accounts
    • General journals
      • Journal Entries
      • Import from a file
      • Import Bank statement
        • Automatic account selection
      • Posting in foreign currency
      • Posting customer payments
      • How to manage voucher number series
      • Setting up Journals
      • Cancel voucher/delete journal/delete record
    • Bank Reconciliation
      • Bank Management / Bank reconciliation
      • Automatic account selection
    • Budget
      • Budget
    • Reports
      • Account statements
      • Transactions
      • Financial Statement
      • Physical Vouchers
      • Posted Journals
    • VAT reporting
      • VAT reporting – Denmark
      • VAT specification – Denmark
      • EU sales
      • Intrastat
      • EU sales list
    • Pivot table
    • Maintenance
      • Dimensions
      • – Join two dimensions
      • – Reverse charged VAT – Denmark
      • Voucher number series
      • – Automatic allocation of number series
      • Terms of payment
      • – Managing cash discounts
      • Accounting year
      • – Year end closing/opening balances
      • Join two accounts
    • Audit activities
      • Company accounts templates
      • Export transactions
  • Customer
    • Customer
      • Customer
      • – Reconciling Customer Accounts
    • Quick invoice
      • Quick invoice
    • Sales
      • Sales orders
      • – Create purchase/sales order
      • Quotations
      • Mass update
      • – Generate UBL
      • – Purchases and sales without VAT
      • Order journal
    • Reports
      • Statement
      • Transactions
      • Open transactions
      • Payment proposals (SEPA)
      • Ageing report
      • Interest and collection letter
        • – Collection letter messages
      • Invoices
      • Delivery note
      • Stock transactions
      • Order lines
      • Customer statistics
      • Pivot table
    • Maintenance
      • Customer groups
      • Order groups
      • Shipments
      • Terms of delivery
      • Layout groups
      • Number sequences
        • – Error messages
      • Invoice layout
        • – Creating watermarks
      • E-mail setup
        • – Setting up SMTP
        • – Mail server set up
        • – Forwarding e-mails to Uniconta
      • Document messages
      • SEPA set up
      • Mandates (SEPA)
      • Customer Direct Debit
      • Join
        • Join two accounts
        • Join two sales orders
        • Join many
  • CRM
    • – Introduction to CRM
    • Prospects
      • – Creating an quotation for a prospect
      • – Create follow-up
    • Customers & Prospects
    • Follow-up activities
    • Create e-mail list
    • Campaign
    • Maintenance
      • Interests
      • Products
      • CRM groups
      • Follow-up groups
      • Campaign groups
  • Vendor
    • Mass update
    • – Purchases and sales without VAT
    • Reports
      • Statement
      • Merge payments
      • Ageing report
      • Pivot table
    • Maintenance
      • Vendor groups
      • Purchase order groups
      • Vendor layout groups
      • Number sequences
      • File formats for methods of payment
        • – Setting up messages for vendor payments
        • – Setting up bank days
      • Join
      • – Join two accounts
      • – Join two purchase orders
      • – Join many
  • Inventory
    • Items
    • Stock Journal
    • – Posting in the stock journals
    • – BOMs in Uniconta
    • – Handling packaging fees
    • Reports
      • Inventory account statement
      • Inventory transactions
      • Item stock status
      • On-hand
      • Re-order list
      • Pivot table
    • Maintenance
      • – Purchasing number series items
      • Join two items
      • Variants
        • – Default variants
        • – Importing old inventory transactions
  • Project
    • Get started with Project and Time
    • Invoicing in the project module
    • Project Categories
    • Pivot table
    • Project groups
  • Company
    • Company information
    • View a demonstration company
    • User rights
      • Manage users
      • Requests for company access
      • Request company access
    • Maintenance
      • Configure application functionality
      • Preferences
      • Subscription (Administrators)
      • – Create subscription (Administrators)
      • – Create subscription (Client)
      • – From trial period to subscription
      • – Counting financial items
      • Modification URL
      • Create a new company
    • Contacts
  • Administrator
    • Distributor Administration
    • User Login History
    • Partner Plugin
  • Tools
    • Main Menu
    • Save layout as a menu item
    • User Plugin
      • How to change Uniconta’s default plugin path
    • Report Generator
      • Report Generator
        • Acccount statement
        • Collection letter documents
        • Company accounts cover page
        • Picking list
        • Purchase documents
        • Sales documents
      • Create a report to be viewed externally
      • Expression binding for Reports
    • Additional setup
      • Local backup
      • Import e-conomic
  • Data Access
    • Transfer data from Uniconta to Excel
    • OData filter function
  • General
    • User Interface
      • User Profile
      • Shortcut Keys in Uniconta
      • Layout
      • Templates
      • How to search in Uniconta
      • Filter/Clear Filter
      • General tips
    • General Functions
      • Attachments
      • Copy and paste in Uniconta
      • Export data and reports to file format
      • Send documents and data by e-mail
      • Print reports and list forms
      • National company number lookup
      • Excel, export and import
    • Security
      • Who has access to my accounts?
      • Forgot Password
      • Backup
      • Uniconta Security
    • Installation
      • Installation on RDS – Remote Desktop Server
      • Optimal internet access for Uniconta
      • Install Uniconta in Windows 7
      • Uniconta in the Microsoft App-Store
      • Uniconta for Mac

OData filter function

1,423 views 9 May 2017 2 February 2018
Print Friendly, PDF & Email

Learn more about:

  • Layout
  • Filter/Clear Filter
  • Templates
  • How to search in Uniconta
  • Attachments
February 2nd, 2018

The OData filter function

 

It is possible to get OData to return selected data only by using a filter.

 

For example, for a debtor:

https://odata.uniconta.com/odata/DebtorClient?$filter=Account eq ‘1101798’

 

For example, for debtor transactions for a certain date range:

https://odata.uniconta.com/odata/DebtorTransClient?$filter=Date ge datetime’2016-01-01T00:00:00′ and Date le datetime’2016-12-31T00:00:00′

 

DefinitionExample and explanationEquivalent C/AL expression
Select a range of valuesfilter=Entry_No gt 610 and Entry_No lt 615

Query on GLEntry service. Returns entry numbers 611 through 614.

..
Andfilter=Country_Region_Code eq ‘ES’ and Payment_Terms_Code eq ’14 DAYS’

Query on Customer service. Returns customers in Spain where Payment_Terms_Code=14 DAYS.

&
Orfilter= Country_Region_Code eq ‘ES’ or Country_Region_Code eq ‘US’

Query on Customer service. Returns customers in Spain and the United States.

Important

OR operators can be used to apply different filters on the same field. However, OR operators cannot be used to apply filters on two different fields.

|
Less thanfilter=Entry_No lt 610

Query on GLEntry service. Returns entry numbers that are less than 610.

<
Greater thanfilter= Entry_No gt 610

Query on GLEntry service. Returns entry numbers 611 and higher.

>
Greater than or equal tofilter=Entry_No ge 610

Query on GLEntry service. Returns entry numbers 610 and higher.

>=
Less than or equal tofilter=Entry_No le 610

Query on GLEntry service. Returns entry numbers up to and including 610.

<=
Different from (not equal)filter=VAT_Bus_Posting_Group ne ‘EXPORT’

Query on Customer service. Returns all customers with VAT_Bus_Posting_Group not equal to EXPORT.

<>
endswithfilter=endswith(VAT_Bus_Posting_Group,’RT’)

Query on Customer service. Returns all customers with VAT_Bus_Posting_Group values that end in RT.

*
startswithfilter=startswith(Name, ‘S’)

Query on Customer service. Returns all customers names beginning with “S”.

substringoffilter=substringof(Name, ‘urn’)

Query on Customer service. Returns customer records for customers with names containing the string “urn”.

lengthfilter=length(Name) gt 20

Query on Customer service. Returns customer records for customers with names longer than 20 characters.

indexoffilter=indexof(Location_Code, ‘BLUE’) eq 0

Query on Customer service. Returns customer records for customers having a location code beginning with the string BLUE.

replacefilter=replace(City, ‘Miami’, ‘Tampa’) eq ‘CODERED’
substringfilter=substring(Location_Code, 5) eq ‘RED’

Query on Customer service. Returns true for customers with the string RED in their location code starting as position 5.

tolowerfilter=tolower(Location_Code) eq ‘code red’
toupperfilter=toupper(FText) eq ‘2ND ROW’
trimfilter=trim(FCode) eq ‘CODE RED’
concatfilter=concat(concat(FText, ‘, ‘), FCode) eq ‘2nd row, CODE RED’
dayfilter=day(FDateTime) eq 12
monthfilter=month(FDateTime) eq 12
yearfilter=year(FDateTime) eq 2010
hourfilter=hour(FDateTime) eq 1
minutefilter=minute(FDateTime) eq 32
secondfilter=second(FDateTime) eq 0
roundfilter=round(FDecimal) eq 1
floorfilter=floor(FDecimal) eq 0
ceilingfilter=ceiling(FDecimal) eq 1

 

Referencing Different Data Types in Filter Expressions

 

The appropriate notation for different data types must be used with filter expressions.
String values must be delimited by single quotation marks.
Numeric values require no delimiters.
DateTime values must be delimited by single quotation marks and preceded by the word datetime, such as datetime’2010-01-25T02:13:40.1374695Z’.

Go to:

Layout
Templates
Search

  • Was this Helpful ?
  • YesNo
Categories: Data Access, Unipedia

Get in touch with us

Uniconta

  • Uniconta
  • Subscription Terms
  • System Status

PARTNER

  • Resellers
  • Get Uniconta
  • Documentation

INFO

  • News
  • Video
  • Security

Support

  • Unipedia
  • API Support
  • Visit a Country Site
    • Danmark
    • Deutschland
    • Eesti
    • Ísland
    • Lithuania
    • Nederland
    • Norge
    • Österreich
    • United Kingdom
Uniconta
© Copyright 2019 | All rights reserved | info@uniconta.com | Terms & Conditions
Facebook Twitter LinkedIn Youtube