Skip to main content
Question

Can't fetch JournalTransaction even with filter, top and skip.

  • March 20, 2026
  • 3 replies
  • 18 views
Litox
Freshman I

Until two weeks ago my integration was working. But for the last two weeks (since March 7th, 2026) I can't make it succeed no more.

For context:

  • I'm also downloading many other tables.
  • Sometimes we get 5xx but with retries is enough.
  • For many other integrations (other clients) things are working fine.

The problem:

As I need to download all the Journals, I'm applying this strategy:

  1. I define a date range to $filter: 30 days.
  2. Within that range, I paginate with $top=1000 + $skip to fetch all the journals in the period.
  3. Then I choose the previous period and repeat (moving backwards until there are no previous journals).

I could retrieve a couple of pages, then I started getting timeouts from my side after waiting 10 minutes for a request to finish. Even retrying 5 times. No success. Example request:

HTTP Request: GET my.acumatica.com/entity/Default/24.200.001/JournalTransaction?%24filter=LastModifiedDateTime+ge+datetimeoffset%272026-02-15T15%3A46%3A19.303Z%27+and+LastModifiedDateTime+lt+datetimeoffset%272026-03-17T15%3A46%3A19.303Z%27&%24expand=Details&%24custom=BatchModule.CuryViewState%2CBatchModule.AutoReverse%2CBatchModule.AutoReverseCopy%2CBatchModule.CreateTaxTrans%2CBatchModule.SkipTaxValidation%2CBatchModule.BatchType%2CBatchModule.OrigBatchNbr%2CBatchModule.ReverseCount%2CBatchModule.CuryDebitTotal%2CBatchModule.CuryCreditTotal%2CBatchModule.CuryControlTotal%2CBatchModule.UsrBZStoreCode%2CBatchModule.UsrBZJournalEntryID%2CBatchModule.UsrBZNumber%2CBatchModule.NoteText%2CBatchModule.NoteID&%24top=1000&%24skip=2000

 

Then we grew that timeout period, to wait for the server a little longer, until it responded with 500:

HTTP Request: GET my.acumatica.com/entity/Default/24.200.001/JournalTransaction?%24filter=LastModifiedDateTime+ge+datetimeoffset%272026-02-18T06%3A48%3A41.342Z%27+and+LastModifiedDateTime+lt+datetimeoffset%272026-03-20T06%3A48%3A41.342Z%27&%24expand=Details&%24custom=BatchModule.CuryViewState%2CBatchModule.AutoReverse%2CBatchModule.AutoReverseCopy%2CBatchModule.CreateTaxTrans%2CBatchModule.SkipTaxValidation%2CBatchModule.BatchType%2CBatchModule.OrigBatchNbr%2CBatchModule.ReverseCount%2CBatchModule.CuryDebitTotal%2CBatchModule.CuryCreditTotal%2CBatchModule.CuryControlTotal%2CBatchModule.UsrBZStoreCode%2CBatchModule.UsrBZJournalEntryID%2CBatchModule.UsrBZNumber%2CBatchModule.NoteText%2CBatchModule.NoteID&%24top=1000&%24skip=3000 "HTTP/1.1 500 Internal Server Error"

This error:

500 - An error has occurred. Operation Cancelled System.OperationCanceledException    at PX.Data.PXCancellationToken.CheckCancellation()
   at PX.Data.PXAccess.GetConnectionString()
   at PX.Data.PXDatabase.get_Provider()
   at PX.Data.BQL.PXDatabaseRecordStatusHelper.HasRecordStatusSupport(PXCache cache, Type table, String& recordStatusFieldName)
   at PX.Data.PXGraph.EnsureIfArchived()
   at PX.Data.PXGraph.Unload()
   at PX.Api.ContractBased.EntityExportContextBuilder.Dispose()
   at PX.Api.ContractBased.EntityService.<GetListAsyncFromEnumerable>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at PX.Api.ContractBased.WebApi.Controllers.EntityController.<GetList>d__14.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at lambda_method(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.AwaitableObjectResultExecutor.<Execute>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextActionFilterAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__13.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.<InvokeNextExceptionFilterAsync>d__24.MoveNext()

 

I don't really know what to do...

Litox

    3 replies

    Dmitrii Naumov
    Acumatica Moderator
    Forum|alt.badge.img+7
    • Dmitrii Naumov
    • Acumatica Moderator
    • March 20, 2026

    ​@Litox the strategy looks good. However, if your goal is to download all records, can you change the filter from LastModifiedDateTime to CreatedDateTime? It’s an indexed column, so theoretically that should speed up the request. 

     

    But also, if you download 1000 transactions and it times out after 10 minutes, I’d say it’s worth creating a support case. Maybe the specific SQL query needs to be looked at.

      Litox
      Freshman I
      • Litox
      • Author
      • Freshman I
      • March 20, 2026

      ​@Litox the strategy looks good. However, if your goal is to download all records, can you change the filter from LastModifiedDateTime to CreatedDateTime? It’s an indexed column, so theoretically that should speed up the request. 

       

      But also, if you download 1000 transactions and it times out after 10 minutes, I’d say it’s worth creating a support case. Maybe the specific SQL query needs to be looked at.

      Using CreatedDateTime didn't help 😭

      Litox
      kbibelhausen
      Freshman I
      • kbibelhausen
      • Freshman I
      • March 21, 2026

      Based on the knowledge context, this appears to be a known performance issue with the JournalTransaction endpoint when using pagination parameters ($skip and $top). Here are the key points and solutions:

       

      ## The Problem

      This is a documented issue where JournalTransaction queries experience degrading performance as the $skip parameter increases, eventually timing out. The issue is particularly pronounced when dealing with large batches linked to projects.

       

      ## Confirmed Workaround

      Acumatica support has provided a specific database index to improve performance:

       

      ```sql

      CREATE NONCLUSTERED INDEX usrPMBudget1 ON [dbo].[PMBudget]

      ([CompanyID],[ProjectTaskID])

      ```

       

      This index addresses the underlying query performance issue when Journal Transactions are linked to project data.

       

      ## Additional Context

      The knowledge base also reveals that CBAPI has limitations with OData-style parameters:

      - `$orderby`, `$top`, and `$skip` may not work reliably in CBAPI endpoints

      - These parameters are accepted silently but not guaranteed to be respected

      - OData endpoints support these parameters more reliably than CBAPI

       

      ## Alternative Approaches

      If the index doesn't resolve your issue completely:

       

      1. **Reduce page size**: Try smaller batch sizes (e.g., 100-200 records instead of 500)

      2. **Add date filters**: Use specific date ranges to limit the dataset

      3. **Custom endpoint**: Create a custom endpoint with proper sorting/paging support

      4. **Switch to OData**: If available, use OData endpoints which have better parameter support

       

      The ETA for a permanent fix is 2025R2 according to Acumatica support. Try the database index first - it's been the most effective workaround for this specific performance issue.

       

      ---

      Drafted by AcuDev (AI) · Reviewed by Kevin Bibelhausen, Studio B

      AcuOps — AI-powered environment management for Acumatica · b.studio

      Terms & ConditionsCookie settingsAccessibility statement
      About Acumatica ERP system
      Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
      © 2008 — 2024  Acumatica, Inc. All rights reserved