Hi there,
First of all thanks for making this awesome Airtable / NocoDB / Baserow alternative. My first impressions are very positive and I especially like the Big Data option, in case you outgrow the default row limits.
So one issue I am running into is that I want to request rows via the API that match some specific filter. I noticed that the List Filtered Rows endpoint is Deprecated since version 2.3, so I was looking into the alternative: SQL Query.
So I do this simple Query:
{
"sql": "SELECT * from RecipeVariations",
"convert_keys": true
}
But sadly, when I query the desired table, it returns Rollup formula columns as
"Prot": "#VALUE!",
And this is exactly the value I want to filter on. So when I do this filtered query:
{
"sql": "SELECT * from RecipeVariations WHERE Prot=50",
"convert_keys": true
}
It doesnât return any relevant rows.
So my two questions are:
- Is the query endpoint returning rollup values as #VALUE! expected behaviour?
- What other way, that is not deprecated, could I use to filter the rows endpoint?
Thanks in advance for any response
ps: I am on the cloud version.
EDIT: I managed to create a view with the required filter in the UI. Now I can use that with the List Rows endpoint. Like so:
view_name=P 25-35
However, the List Rows endpoint returns
"Photo": "[object Object]",
For a lookup column with attachment. Where the List Rows (with SQL) returns a nice and working object with a url to the file, for the exact same table and column. Any ways to overcome this without creating another request? I am trying to minimize requests in order to stay away from API limits in future use. Another alternative would be to use a proxy API, but I would rather delay that.
EDIT2: I notice now that for some columns using the query api a âLink formulaâ does show up a value, and in some cases it doesnât. It is hard to really see a pattern. It almost seems as if when table A has a one to many relationship to table B and Table B has a column with Link formula to a single record in table A, it does show up, but when Table B has a many to many relationship to Table C and you have link formula column in Table B referring to multiple rows in table C, it shows up blank with the query API. This does not happen with the view filtered list api.
So for instance this does work:
{
"sql": "SELECT Photo from RecipeVariations",
"convert_keys": true
}
And gives response:
{
"success": true,
"error_message": "",
"results": [
{
"Photo": [
{
"name": "bacon worst met ei.jpg",
"size": 211829,
"type": "file",
"url": "https://cloud.seatable.io/workspace/34414/asset/1264b3bb-7def-4b9c-a8b8-c808e701cdaf/files/2023-04/bacon%20worst%20met%20ei.jpg"
}
]
},
{
"Photo": [
{
"name": "bacon worst met ei.jpg",
"size": 211829,
"type": "file",
"url": "https://cloud.seatable.io/workspace/34414/asset/1264b3bb-7def-4b9c-a8b8-c808e701cdaf/files/2023-04/bacon%20worst%20met%20ei.jpg"
}
]
},
{
"Photo": [
{
"name": "bacon worst met ei.jpg",
"size": 211829,
"type": "file",
"url": "https://cloud.seatable.io/workspace/34414/asset/1264b3bb-7def-4b9c-a8b8-c808e701cdaf/files/2023-04/bacon%20worst%20met%20ei.jpg"
}
]
},
{
"Photo": [
{
"name": "bacon worst met ei.jpg",
"size": 211829,
"type": "file",
"url": "https://cloud.seatable.io/workspace/34414/asset/1264b3bb-7def-4b9c-a8b8-c808e701cdaf/files/2023-04/bacon%20worst%20met%20ei.jpg"
}
]
}
],
"metadata": [
{
"key": "pfj2",
"name": "Photo",
"type": "link-formula",
"data": {
"array_data": null,
"array_type": "file",
"formula": "lookup",
"level1_linked_table_column_key": "EBsu",
"link_column_key": "787V",
"operated_columns": [
"787V"
],
"result_type": "array"
}
}
]
}
And this doesnât
{
"sql": "SELECT Proteins from RecipeVariations",
"convert_keys": true
}
Gives:
{
"success": true,
"error_message": "",
"results": [
{
"Proteins": []
},
{
"Proteins": []
},
{
"Proteins": []
},
{
"Proteins": []
}
],
"metadata": [
{
"key": "yBG3",
"name": "Proteins",
"type": "link-formula",
"data": {
"array_data": {},
"array_type": "number",
"formula": "lookup",
"level1_linked_table_column_key": "7H5J",
"link_column_key": "c0np",
"operated_columns": [
"c0np"
],
"result_type": "array"
}
}
]
}