I was trying to get data using API and when I apply filter it gives the error message "The given OData filter expression references fields not supported by this server: Date at 0". Without filter it is working fine.
Hi @sabari193,
Welcome to the forum! If you have a moment, introduce yourself in the welcome thread.
Could you give us more details about the filter expression?
Are you using the OData submissions endpoint documented at https://docs.getodk.org/central-api-odata-endpoints/#data-document and are you aware of the limitations explained there? (e.g. you can only filter on some fields and only use a limited set of OData filter expressions and functions)
What tool are you using, pyodk or ruODK or others?
Wider question, does the size of your dataset allow to download it completely and filter after the download to your purposes?
The first API response I get through Postman is {
"value": [
{
"state": "6",
"clusterid": "52",
"hh_no": "010",
"hh_status": "1",
"outcome_status": {
"family_head": "Google ",
"hh_total_hh_size": 5,
"hh_adult": "1"
}
}
],
"@odata.context": "https://domain.com/v1/projects/2/forms/sample.svc/$metadata#Submissions"
}
When I apply filter using clusterid like $filter=clusterid eq 51 it throws the error response
{
"message": "The given OData filter expression references fields not supported by this server: clusterid at 0",
"code": 501.5,
"details": {
"at": 0,
"text": "clusterid"
}
}
ODK central version added for your reference
versions:
2d95a612218e7c73ee8b3e715749401f08fc00ac (v2024.2.1-1-g2d95a61)
99b11a8cc7d30dbc03376f97ab345dc0d18c2a98 client (v2024.2.1)
63ca7881f6e6eb0b5c9051bf64448d802720f100 server (v2024.2.0)
Thanks for the detailed answer!
As of ODK Central v1.1, the $filter querystring parameter is partially supported. In OData, you can use $filter to filter by certain data fields in the schema. The operators lt, le, eq, ne, ge, gt, not, and, and or are supported. The built-in functions now, year, month, day, hour, minute, second are supported. These supported elements may be combined in any way, but all other $filter features will cause an error.
The fields you can query against are as follows:
Submission Metadata REST API Name OData Field Name
Submitter Actor ID submitterId __system/submitterId
Submission Timestamp createdAt __system/submissionDate
Submission Update Timestamp updatedAt __system/updatedAt
Review State reviewState __system/reviewState
So according to the docs you can't filter against the actual fields of your form (which you define in the XLSX), only the metadata fields I've pasted above (directly from the docs).
If that is indeed the case, you might want to download all submissions and filter after the download.
1 Like