Using the REST API I need to know which is the first record (chronologically) of JournalTransaction. That is, the one with the eldest CreatedDateTime.
sort + top=1?Best answer by CherryStreet
This is what I am getting:
This is a known nuance with Acumatica’s Contract-Based REST API (CBAPI).
You are using valid OData-style parameters ($orderby, $top), but those do not work reliably in CBAPI, especially for ordering by system-generated fields like CreatedDateTime or LastModifiedDateTime. Here’s why:
📌 CBAPI does not fully support OData query parameters like $orderby or $top, even if it appears to accept them silently. These parameters are not guaranteed to be parsed or respected unless the endpoint is explicitly built to do so.
This is documented behavior, and Acumatica support confirms that only OData endpoints support $orderby, $top, $filter reliably.
Since you’re restricted to CBAPI, here’s the correct approach:
Because CBAPI returns unordered data, you’ll need to:
Retrieve a small page of records
Manually find the oldest by CreatedDateTime
Example request:
POST /entity/Default/23.200.001/JournalTransaction
{
"pageSize": 100,
"fields": {
"ReferenceNbr": {},
"CreatedDateTime": {}
}
}Then in your client code:
oldest = min(results, key=lambda x: x['CreatedDateTime'])⚠️ If there are many pages, you will need to loop through them.
If you have admin access, this is the most maintainable method:
Go to: Web Services Endpoints (SM.20.20.60)
Clone the default Default/23.200.001
Add a new custom endpoint method:
Based on JournalTransaction
With a view or GI that sorts by CreatedDateTime ASC
Publish the endpoint
Use this new endpoint in your REST call — no $orderby needed
If you’re allowed to use OData GIs, you can:
Build a GI on JournalTransaction
Sort by CreatedDateTime ASC
Publish to OData
Use:
/OData/YourGI?$top=1
⚠️ You mentioned OData is not allowed, so this may be blocked in your environment.
| Task | Status in CBAPI | Workaround |
|---|---|---|
| $orderby (CreatedDateTime) | ❌ Not supported reliably | Use paging + client-side sort |
| $top=1 | ❌ Ignored unless combined with sort | Use paging manually |
| Custom endpoint with sort | ✅ Works | Use custom endpoint + sorting |
| OData GI | ✅ Works | Only if OData is permitted |
If you cannot modify endpoints, your only safe solution is:
Pull a page of data
Manually sort by CreatedDateTime
If necessary, page through all results and keep the min
Just to expand, I don't have access to the OData interface, only the basic REST API.
Ran this through our AI Assistant, let me know if this helps:
Since you're using Acumatica’s Contract-Based REST API (not OData), here's exactly how you can get the first JournalTransaction record chronologically by CreatedDateTime using filtering and ordering.
✅ Goal:
Get the oldest (earliest) JournalTransaction record by CreatedDateTime using the Contract-Based REST API.
✅ Answer Summary:
Yes — you can use:
🔧 Example Request:
Assuming your endpoint is similar to:
/entity/Default/20.200.001/JournalTransaction
Use this GET request:
GET /entity/Default/20.200.001/JournalTransaction?$top=1&$orderby=CreatedDateTime asc
🔍 Explanation:
❓ What's the default ordering?
The Contract-Based REST API has no guaranteed default order.
So without explicitly using $orderby, the results are unordered or based on internal index, and not reliable for chronological operations.
✅ Bonus: Using Filters (Optional)
If you want the oldest transaction of a specific type, you can combine $filter:
GET /entity/Default/20.200.001/JournalTransaction?$top=1&$filter=Ledger eq 'ACTUAL'&$orderby=CreatedDateTime asc
🧪 Testing Tip
Enable API Logging or use Postman to:
🚫 Caveat
✅ Final Answer:
Use this Contract-Based REST API call:
GET /entity/Default/20.200.001/JournalTransaction?$top=1&$orderby=CreatedDateTime asc
It will return the oldest journal transaction by creation time.
Awesome!
I couldn't find any reference to the $orderby parameter in the documentation, that's why I didn't know it existed! It makes all the difference in my case.
Thank you very much.
My pleasure!
Well, I tried it and it doesn't work. The API call doesn't fail, but it doesn't matter what I put in the parameter, I always get the same result 😢
entity/Default/23.200.001/JournalTransaction?$orderby=LastModifiedDateTime desc&$top=1
entity/Default/23.200.001/JournalTransaction?$orderby=LastModifiedDateTime Desc&$top=1
entity/Default/23.200.001/JournalTransaction?$orderby=LastModifiedDateTime asc&$top=1
entity/Default/23.200.001/JournalTransaction?$orderby=LastModifiedDateTime Asc&$top=1
Ok, let me do some troubleshooting for you.
This is what I am getting:
This is a known nuance with Acumatica’s Contract-Based REST API (CBAPI).
You are using valid OData-style parameters ($orderby, $top), but those do not work reliably in CBAPI, especially for ordering by system-generated fields like CreatedDateTime or LastModifiedDateTime. Here’s why:
📌 CBAPI does not fully support OData query parameters like $orderby or $top, even if it appears to accept them silently. These parameters are not guaranteed to be parsed or respected unless the endpoint is explicitly built to do so.
This is documented behavior, and Acumatica support confirms that only OData endpoints support $orderby, $top, $filter reliably.
Since you’re restricted to CBAPI, here’s the correct approach:
Because CBAPI returns unordered data, you’ll need to:
Retrieve a small page of records
Manually find the oldest by CreatedDateTime
Example request:
POST /entity/Default/23.200.001/JournalTransaction
{
"pageSize": 100,
"fields": {
"ReferenceNbr": {},
"CreatedDateTime": {}
}
}Then in your client code:
oldest = min(results, key=lambda x: x['CreatedDateTime'])⚠️ If there are many pages, you will need to loop through them.
If you have admin access, this is the most maintainable method:
Go to: Web Services Endpoints (SM.20.20.60)
Clone the default Default/23.200.001
Add a new custom endpoint method:
Based on JournalTransaction
With a view or GI that sorts by CreatedDateTime ASC
Publish the endpoint
Use this new endpoint in your REST call — no $orderby needed
If you’re allowed to use OData GIs, you can:
Build a GI on JournalTransaction
Sort by CreatedDateTime ASC
Publish to OData
Use:
/OData/YourGI?$top=1
⚠️ You mentioned OData is not allowed, so this may be blocked in your environment.
| Task | Status in CBAPI | Workaround |
|---|---|---|
| $orderby (CreatedDateTime) | ❌ Not supported reliably | Use paging + client-side sort |
| $top=1 | ❌ Ignored unless combined with sort | Use paging manually |
| Custom endpoint with sort | ✅ Works | Use custom endpoint + sorting |
| OData GI | ✅ Works | Only if OData is permitted |
If you cannot modify endpoints, your only safe solution is:
Pull a page of data
Manually sort by CreatedDateTime
If necessary, page through all results and keep the min
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.