You'd have to omit Deleted records from your ODBC query

Hi Ken, thanks for the reply. How can the query be filtered to remove deleted items? There is a column that is ‘record deleted’ but the flag is only 0’s in my data. 

That's strange as that's where I would expect the deleted records to be showing with a '1' in that column.

I whink we need Darron Cockram or @AndyRickeard to respond to this one

Trouble is that when a line item is removed from a sales order it is simply orphaned, but remains in the data and does not have the deleted flag set.The orphaned records only get removed if you run a compress operation. Reasons behind this are lost in the mists of time but it has been like that ever since sales orders functionality was added by the looks of it.

Trouble is that when a line item is removed from a sales order it is simply orphaned, but remains in the data and does not have the deleted flag set.The orphaned records only get removed if you run a compress operation. Reasons behind this are lost in the mists of time but it has been like that ever since sales orders functionality was added by the looks of it.

Thanks for the clarification Darron. At least we know how to sort it now.

Thanks Darron, I will try this out and get back to you.

Hi Darron, we did run a compression and the orphaned rows have gone.  Our reseller advised this shouldn't be run too often, once a week at the most.

You can run it as often as you like. No need to limit how frequently it is run. There is no negative effect to running it frequently. In fact the only down side to running it at all is the fact that it is an exclusive action so needs other users to be logged out.