Question

Order By with a SQL Data Provider

  • 11 February 2024
  • 3 replies
  • 27 views

Userlevel 5
Badge +4

How can I import data from SQL using an import scenario with an Order By clause?  Or what work arounds have you come up with? 

Most situations I’ve just made sure the data is in SQL in the order I want it, but sometimes that’s a challenge with complex views.

The issue is it creates conflicts where there are many detailed rows but the data is not in order.  Setting Save to Each row or Batch size to 1 doesn’t help either.  

The help documentation specifically says Order By cannot be used in the command fiels, so I’m guessing this is a common issue.


3 replies

Userlevel 7
Badge +5

One option might be to layer an additional view on top of the one that you’re trying to import and it’s only purpose is to call the view doing the hard work and ordering the output. Then adjust your import scenario to call the simplified view.

Userlevel 5
Badge +4

I tried that and it’s still out of order, I even tried building a function and then a view using the function.  

Userlevel 5
Badge +4

One of the source tables was missing a PK so it wasn’t indexed.  Once I added that it started cooperating.  I’ll leave this open for a bit in case someone has found another way to make this work.  The ability to use query would be very helpful. 

Reply


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