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

SOP_ITEM table issues

Sage 50 general help forum - Free help and support for all general issues
Post Reply
owen_legend
User
User
Posts: 9
Joined: 07 Dec 2015, 16:57
Sage Version: v2016 22 UK/Europe/Africa

SOP_ITEM table issues

Post by owen_legend » 22 Aug 2018, 16:34

I'm finding the SOP_ITEM table accessed through the ODBC driver contains lines of Sales orders lines that have been deleted.

For example - when I create a line on a SO and save, a line entry is created in SOP_ITEM. If I then delete this line by pressing F8, the entry is not deleted in the SOP_ITEM table - so it's difficult to report on this table in the way I want to.

When I generate a report in Sage - "Back Orders" specifically (Returns outstanding SOP lines) - Sage doesn't return these deleted rows and so it must have a way of knowing which SOP lines have or haven't been deleted that I just can't figure and replicate.

For reference, the [RECORD_DELETED] field is always 0 so I can't filter on that.

Am I missing something?

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

Re: SOP_ITEM table issues

Post by brucedenney » 30 Aug 2018, 12:00

I think this is an "issue" in Sage that you can not overcome.

I have some other ways of looking at the tables and there is no "record deleted" field.

I think sage is relying on the indexes to know what is deleted, indeed this may be related to a data corruption issue (in POP SOP and INV) where items on one order move onto another...which we regularly see.
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.

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

Re: SOP_ITEM table issues

Post by brucedenney » 30 Aug 2018, 12:30

P.S. If you reindex and compress... the records should disappear.
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.

owen_legend
User
User
Posts: 9
Joined: 07 Dec 2015, 16:57
Sage Version: v2016 22 UK/Europe/Africa

Re: SOP_ITEM table issues

Post by owen_legend » 04 Sep 2018, 16:32

I see - that's interesting

The record_deleted field exists in the odbc driver but as it is always 0 - I assumed it was not used.

I will try a re index & compress - would you recommend performing this maintenance regularily?

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

Re: SOP_ITEM table issues

Post by brucedenney » 04 Sep 2018, 16:53

It doesn't do any harm. Backing up first is still a good idea.
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.

JaniceF
User
User
Posts: 1
Joined: 08 Jan 2019, 17:01
Sage Version: v2018 24 UK/Europe/Africa

Re: SOP_ITEM table issues

Post by JaniceF » 08 Jan 2019, 17:14

Hi Bruce
I'm interested in the comment you make on this thread on data corruption in the sales orders, sales invoices and purchase orders files where items on one order move to another. You say it is common - we too have had that problem (twice) on the sales order file anyway. Each time it has happened after compressing the sales order file. The compression appears to work fine and the check data done after the compression shows no errors, but during the following work period a few errors show up as described and we have to go back to the backup taken before the compression - and redo the work which is not popular of course.

How can we get around this situation - the file needs compressing as we've deleted a years worth of orders? Should we be doing a reindex before the compression? - I am never sure about when to do a reindex, in fact I've never done one. If the problem is common presumably you have a solution to offer? I would be grateful for any pointers. Thanks

Janice

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

Re: SOP_ITEM table issues

Post by brucedenney » 08 Jan 2019, 17:50

Hi Janet

The way I fix it... Is to export all the Invoices (or sales orders or purchase orders), Delete all of them from sage using the rebuild option and then import them all back in with our 3rd party tools.

With large data sets it is tedious. There are a number of issues that arise in older data.

As part of the process I check that all the items on each order belong to that order and make a note of any that are mixed up as these need to be manually checked afterwards.
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.

Post Reply

Who is online

Users browsing this forum: Ahrefs [Bot] and 19 guests