SageForum for UK versions of Sage 50- Help and support.

Sage UK - version 25.1.128.0

I'm posting a question I also asked on stack over flow a while back:

https://stackoverflow.com/questions/617 ... nt-working

- 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.

Any ideas?

The RECORD_DELETED value is always 0 it doesn't get set!

It is a bug in Sage!

If you join the INVOICE TABLE to the INVOICE_ITEM table on INVOICE_NUMBER you will find that the deleted line will not be returned.

Weirdness as normal form Sage

There are also some differences between the fields available in ODBC and via SDK as we use for our integrations.

https://www.makingithappen.co.uk/CI/sge_int_files.htm

brucedenney wrote: 03 Aug 2020, 13:43 The RECORD_DELETED value is always 0 it doesn't get set!

It is a bug in Sage!

If you join the INVOICE TABLE to the INVOICE_ITEM table on INVOICE_NUMBER you will find that the deleted line will not be returned.

Weirdness as normal form Sage

There are also some differences between the fields available in ODBC and via SDK as we use for our integrations.

https://www.makingithappen.co.uk/CI/sge_int_files.htm

Damn

What do you suppose is a possible solution? I want to access only the products that are to be shipped (i.e. not deleted). Please help!

sageNoob wrote: 03 Aug 2020, 13:48

brucedenney wrote: 03 Aug 2020, 13:43 The RECORD_DELETED value is always 0 it doesn't get set!

It is a bug in Sage!

If you join the INVOICE TABLE to the INVOICE_ITEM table on INVOICE_NUMBER you will find that the deleted line will not be returned.

Weirdness as normal form Sage

There are also some differences between the fields available in ODBC and via SDK as we use for our integrations.

https://www.makingithappen.co.uk/CI/sge_int_files.htm

Damn

What do you suppose is a possible solution? I want to access only the products that are to be shipped (i.e. not deleted). Please help!

Sorry, I just figured out how to do it, based on your suggestion. The solution is to JOIN from INVOICE to INVOICE_ITEM. My program was directly using INVOICE_ITEM and a simple WHERE clause based on the INVOICE_DATE.

The RECORD_DELETED field must be some carry-over from a previous version of sage or something. Still sucks that we have no way of knowing how Sage stores what records (products) have been deleted.