Sage50 OBDC [RECORD_DELETED] field isn't working

Question

I am trying to import invoices from Sage 50 UK using MS access. The problem I am facing is that sometimes, the invoices on sage might have had a product on it before but, has since been removed. Unfortunately, using a select * query on [INVOICE_ITEM] will include all items on the invoice, including deleted records.

Judging by the database schema on here, sage seems to use a RECORD_DELETED field in order to keep track of items (products) that are no longer supposed to be on the invoice. Obviously, the next step is to use a WHERE in the query to exclude the deleted records. Well, unfortunately, my RECORD_DELETED field is always showing the default "0" value - even for actual deleted items on the invoice!

Example, highlighted record is actually supposed to be deleted.

Would appreciate any help here!

Answer 1

Late answer but, as it turns out, the RECORD_DELETED field doesn't actually work - or at least is not available via the ODBC connection.

The solution for this issue is to change the SQL query. Instead of getting data directly from the INVOICE_ITEM table, we have to use a join from the INVOICE table to the INVOICE_ITEM table. This solves the issue and only produces items that are actually on the invoice.

I do not know why this works, would love if anyone can shed some light.

Query:

SELECT INVOICE.INVOICE_NUMBER, INVOICE_ITEM.STOCK_CODE, INVOICE_ITEM.DESCRIPTION, INVOICE_ITEM.QUANTITY
       FROM INVOICE
       JOIN INVOICE_ITEM ON INVOICE.INVOICE_NUMBER = INVOICE_ITEM.INVOICE_NUMBER

You can add the optional where clause:

WHERE INVOICE.INVOICE_OR_CREDIT <> 'Credit Note'

To exclude credit notes. You can also do where invoice_number = 'number' (don't forget the the apostrophe) to get a particular invoice.