Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

Sage50 OBDC [RECORD_DELETED] field doesn't work

Sage 50 general help forum - Free help and support for all general issues
Post Reply
sageNoob
User
User
Posts: 102
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Sage50 OBDC [RECORD_DELETED] field doesn't work

Post by sageNoob » 03 Aug 2020, 12:10

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?

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Sage50 OBDC [RECORD_DELETED] field doesn't work

Post by brucedenney » 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
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

sageNoob
User
User
Posts: 102
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Sage50 OBDC [RECORD_DELETED] field doesn't work

Post by sageNoob » 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!

sageNoob
User
User
Posts: 102
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Sage50 OBDC [RECORD_DELETED] field doesn't work

Post by sageNoob » 03 Aug 2020, 16:41

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.

Post Reply

Who is online

Users browsing this forum: No registered users and 13 guests