Understanding and using odata with retool

[pete82]

Hi all.

I have an odata based api which i can query and receive data successfully using basic get commands using the api url ending with /odata.svc/workorders

and get results that look like
{
"odata.metadata": "https://myinternalserver/site/OData.svc/$metadata#WorkOrders",
"value": [
{
"WorkOrderID": 5,
"WorkOrderNumber": 11,
"WorkOrderSuffix": null,
},
I can spit the results out into a table using {{ myresults.data.value }} and looks great.

Now, If i wanted to fetch just a single particular "WorkOrderID", i tried using the URL params as i would for swagger based rest api's.

however the result of the query is exactly the same as if i were to run the query without url parameters.

Any idea?

Cheers

[AnsonHwang]

try this paramter?
$metadata#WorkOrders?workOrderID=20668

[pete82]

@AnsonHwang

$metadata#WorkOrders?workOrderID=

Hi Anson,
thanks for the suggestion. I had tried this suggestion yesterday as per below but always got an error which led me to believe i was going down the wrong path.

This is the request

{
"request": {
"url": "https://myinternalserver/OData.svc/?$metadata=#WorkOrders?workOrderID==20668",
"method": "GET",
"body": null,
"headers": {
"User-Agent": "Retool/2.0 (+https://docs.tryretool.com/docs/apis)",
"Content-Type": "application/json",
"Accept": "application/json",
"Authorization": "---sanitized---",
"X-Retool-Forwarded-For": "::ffff:serverip"
}
},
"response": {
"data": {
"odata.error": {
"code": "",
"message": {
"lang": "en-AU",
"value": "The query parameter '$metadata' begins with a system-reserved '$' character but is not recognized."
}
}
},
"headers": {
"cache-control": [
"private"
],
"content-type": [
"application/json;odata=minimalmetadata;streaming=true;charset=utf-8"
],
"x-content-type-options": [
"nosniff"
],
"dataserviceversion": [
"3.0;"
],
"x-aspnet-version": [
"4.0.30319"
],
"x-powered-by": [
"ASP.NET"
],
"date": [
"Tue, 05 Sep 2023 00:31:42 GMT"
],
"content-length": [
"163"
]
},
"status": 400,
"statusText": "Bad Request"
}
}

this is the response

{
"odata.error": {
"code": "",
"message": {
"lang": "en-AU",
"value": "The query parameter '$metadata' begins with a system-reserved '$' character but is not recognized."
}
}
}

**If i strip out the "$" from the initial query, the response i get seems to all the available endpoints but not the actual data from ID 20668.

Any chance you have any other suggestions?

[joeBumbaca]

Hey @pete82! Took a look at this, and there are some request params listed here that may be of interest.

I made a small example that should also fit your data structure.

Using the test endpoints provided by Odata I was able to query the data a few ways to retrieve different results.

Endpoint w/ no params retrieves all people:

Appending (id) to the endpoint retrieves only that person with that id:

You can also use a filter param, to retrieve a subset of the data that meets the filter criteria:

There are other options listed in that link, but thought those were the most relevant to your dataset. Let me know if that works for you!

[pete82]

Hey Joe,

Sorry for the delay in reading this and thanks for getting back to me.

I had been playing with that odata link earlier but can not seem to make it work.
Ive been looking at the docs here Using The MEX API

The endpoint as mentioned is WorkOrders however i only want the data from 1 entry under the WorkOrderID field etc id 20668

Ive tried several methods

Single Entity by ID
URL/WorkOrders('20668')
Response: "value": "Bad Request - Error in query syntax."

URL/WorkOrders/('20668')WorkOrderID
Response: "value": "Bad Request - Error in query syntax."

URL/WorkOrders('20668')/WorkOrderID
Response: "value": "Bad Request - Error in query syntax."

The Best i could get was an error indicating a collection but it was still an error

URL/WorkOrders/WorkOrderID('20668')
Response: "value": "The request URI is not valid. Since the segment 'WorkOrders' refers to a collection, this must be the last segment in the request URI. All intermediate segments must refer to a single resource."

URL/WorkOrderID('20668')/WorkOrders
Response "value": "Resource not found for the segment 'WorkOrderID'."

Cheers

[joeBumbaca]

Have you tried using the filter? Looks like you can filter by the id which only retrieves the one record

in your case that might look like:

URL/WorkOrders?$filter=WorkOrderID eq 5