Sage 100

End user has 300,000 ship to addresses that need to be removed. Here's a strategy to remove these records. I'll test and then report results. If anyone see a gotcha, please point it out. This is 100 Premium.

------------------------------
Myron Stevenson
Consultant
Clearis Consulting, Inc
Duluth, MN
218-525-6720
------------------------------

As long as you are careful (and test properly) it should be fine.  In Providex you'd be exporting, reinit the file, then re-importing the records you want to keep... which has it's own complications.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

First thought is be sure to run this in a test company / database :-) Also, I presume that you don't care if some of these bad Ship To's were already used and found today in SO Order History, AR Invoice History, etc

I was also wondering how the the 300k records are first identified as needing to be deleted. Maybe instead of importing to set UDF_DELETE = 'Y' and then subsequently deleting, you could while you import just Delete the record / row to begin with and skip the step of setting UDF_DELETE = 'Y'. This could be done in your T-SQL or could be done while V/I importing.

Assuming client has these 300k rows identified in a spreadsheet, then between SSMS and SSIS you could import that into a SQL table. That would let you construct the T-SQL that joins this 300k table to SO_ShipToAddress to ID the rows to delete and then subsequently the AR_Customer rows to blank out Primary Ship To Code.

Alternatively, knowing that you do Scripting, the 300k rows could be SQL imported into a UDT, then you could add a button script on some screen inside MAS that could GetObject to the UDT and AR_Customer_bus to set a blank value to PrimaryShipToCode AND then UDT to SO_ShipToAddress_bus to run the Delete() function. BUT scripting would run slower than T-SQL.

If you're not comfortable with any of that, your plan is just fine as is !! 

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA
------------------------------

Great suggestion to import data into a SQL table, join and delete the join. End user has confirmed that no ship to addresses are primary. Any were excluded in their data extraction. The method will be much faster that using VI or any part of Sage 100. This is what makes Sage 100 Premium the preferred platform when dealing with large data.

Ship to codes in history are fine to be left "unattached".

------------------------------
Myron Stevenson
Consultant
Clearis Consulting, Inc
Duluth, MN
218-525-6720
------------------------------

Original Message:
Sent: 05-14-2019 17:13
From: Alnoor Cassim
Subject: How to mass delete records in 100 Premium?

First thought is be sure to run this in a test company / database :-)

I was also wondering how the the 300k records are first identified as needing to be deleted. Maybe instead of importing to set UDF_DELETE = 'Y' and then subsequently deleting, you could while you import just Delete the record / row to begin with and skip the step of setting UDF_DELETE = 'Y'. This could be done in your T-SQL or could be done while V/I importing.

Assuming client has these 300k rows identified in a spreadsheet, then between SSMS and SSIS you could import that into a SQL table. That would let you construct the T-SQL that joins this 300k table to SO_ShipToAddress to ID the rows to delete and then subsequently the AR_Customer rows to blank out Primary Ship To Code. Alternatively, knowing that you do Scripting, the 300k rows could be brought into a UDT, then you could add a button script on some screen inside MAS that could GetObject to the UDT and SO_ShipToAddress_bus to run the Delete() method, and then to AR_Customer_bus to set a blank value to PrimaryShipToCode. BUT scripting would run slower than T-SQL.

If you're not comfortable with any of that, your plan is just fine as is !! I presume that you don't care if some of these bad Ship To's were already used and found today in SO Order History, AR Invoice History, etc

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA
------------------------------

My main reasons for liking Premium / SQL are related to data security and reporting speed / flexibility, but the ability to use "SQL Magic" when data changes are needed is certainly a significant benefit. 
When I first started with Sage 100 I was saddened / shocked at the poor tools available for ProvideX.

Before you run your big deletion, one thing I commonly do with big changes is to do a "select into..." copy of the table (stored in a separate database) to have a snapshot of the before data (just in case something goes wrong!).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 05-14-2019 17:24
From: Myron Stevenson
Subject: How to mass delete records in 100 Premium?

Great suggestion to import data into a SQL table, join and delete the join. End user has confirmed that no ship to addresses are primary. Any were excluded in their data extraction. The method will be much faster that using VI or any part of Sage 100. This is what makes Sage 100 Premium the preferred platform when dealing with large data.

Ship to codes in history are fine to be left "unattached".

------------------------------
Myron Stevenson
Consultant
Clearis Consulting, Inc
Duluth, MN
218-525-6720
------------------------------


Original Message:
Sent: 05-14-2019 17:13
From: Alnoor Cassim
Subject: How to mass delete records in 100 Premium?

First thought is be sure to run this in a test company / database :-)

I was also wondering how the the 300k records are first identified as needing to be deleted. Maybe instead of importing to set UDF_DELETE = 'Y' and then subsequently deleting, you could while you import just Delete the record / row to begin with and skip the step of setting UDF_DELETE = 'Y'. This could be done in your T-SQL or could be done while V/I importing.

Assuming client has these 300k rows identified in a spreadsheet, then between SSMS and SSIS you could import that into a SQL table. That would let you construct the T-SQL that joins this 300k table to SO_ShipToAddress to ID the rows to delete and then subsequently the AR_Customer rows to blank out Primary Ship To Code. Alternatively, knowing that you do Scripting, the 300k rows could be brought into a UDT, then you could add a button script on some screen inside MAS that could GetObject to the UDT and SO_ShipToAddress_bus to run the Delete() method, and then to AR_Customer_bus to set a blank value to PrimaryShipToCode. BUT scripting would run slower than T-SQL.

If you're not comfortable with any of that, your plan is just fine as is !! I presume that you don't care if some of these bad Ship To's were already used and found today in SO Order History, AR Invoice History, etc

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA

You could use VI to delete the records using the approach detailed here.
https://www.sagecity.com/support_communities/sage100_erp/f/sage-100-business-object-interface/125437/im_pricecode_bus-doesnt-have-a-delete-function/334945#334945

A big benefit of this approach imho, is that it uses the business object to handle the deleting of the record, so if you needed to delete a bunch of header records that also have detail records, calling it on the header will delete the lines and handle any associated logic that goes with a line being deleted, such as updating IM_ItemWarehouse quantities.

Granted, if you are deleting records from a table that doesn't have any impact else where than there isn't any harm in using SQL.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 05-14-2019 17:33
From: Kevin Moyes
Subject: How to mass delete records in 100 Premium?

My main reasons for liking Premium / SQL are related to data security and reporting speed / flexibility, but the ability to use "SQL Magic" when data changes are needed is certainly a significant benefit.
When I first started with Sage 100 I was saddened / shocked at the poor tools available for ProvideX.

Before you run your big deletion, one thing I commonly do with big changes is to do a "select into..." copy of the table (stored in a separate database) to have a snapshot of the before data (just in case something goes wrong!).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 05-14-2019 17:24
From: Myron Stevenson
Subject: How to mass delete records in 100 Premium?

Great suggestion to import data into a SQL table, join and delete the join. End user has confirmed that no ship to addresses are primary. Any were excluded in their data extraction. The method will be much faster that using VI or any part of Sage 100. This is what makes Sage 100 Premium the preferred platform when dealing with large data.

Ship to codes in history are fine to be left "unattached".

------------------------------
Myron Stevenson
Consultant
Clearis Consulting, Inc
Duluth, MN
218-525-6720


Original Message:
Sent: 05-14-2019 17:13
From: Alnoor Cassim
Subject: How to mass delete records in 100 Premium?

First thought is be sure to run this in a test company / database :-)

I was also wondering how the the 300k records are first identified as needing to be deleted. Maybe instead of importing to set UDF_DELETE = 'Y' and then subsequently deleting, you could while you import just Delete the record / row to begin with and skip the step of setting UDF_DELETE = 'Y'. This could be done in your T-SQL or could be done while V/I importing.

Assuming client has these 300k rows identified in a spreadsheet, then between SSMS and SSIS you could import that into a SQL table. That would let you construct the T-SQL that joins this 300k table to SO_ShipToAddress to ID the rows to delete and then subsequently the AR_Customer rows to blank out Primary Ship To Code. Alternatively, knowing that you do Scripting, the 300k rows could be brought into a UDT, then you could add a button script on some screen inside MAS that could GetObject to the UDT and SO_ShipToAddress_bus to run the Delete() method, and then to AR_Customer_bus to set a blank value to PrimaryShipToCode. BUT scripting would run slower than T-SQL.

If you're not comfortable with any of that, your plan is just fine as is !! I presume that you don't care if some of these bad Ship To's were already used and found today in SO Order History, AR Invoice History, etc

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA