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
- - - - - - - - - - - - - - - - - - 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
-
- User
- Posts: 9
- Joined: 07 Dec 2015, 16:57
- Sage Version: v2016 22 UK/Europe/Africa
SOP_ITEM table issues
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?
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?
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: SOP_ITEM table issues
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.
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.
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: SOP_ITEM table issues
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.
-
- User
- Posts: 9
- Joined: 07 Dec 2015, 16:57
- Sage Version: v2016 22 UK/Europe/Africa
Re: SOP_ITEM table issues
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?
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?
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: SOP_ITEM table issues
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.
Re: SOP_ITEM table issues
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
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
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: SOP_ITEM table issues
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.
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.
Who is online
Users browsing this forum: Ahrefs [Bot] and 19 guests