Kit - Delete Product & ADD a new product

(0) Share

Report

Posted on by Community Member

Client needs to remove a product from a kit, and replace it with a new product. They have literally thousands of kits, this effects.

Is there anyway we can do this in bulk, rather than manually editing each Kit?

Thanks in advance!

*This post is locked for comments

All responses (2)

Answers (0)

Suggested answer

L Vail 65,271 on at

Like (1)

Report
RE: Kit - Delete Product & ADD a new product

Hi Stan,
Heather has given you some sound ideas. I agree that the macro method would be difficult unless each of the items you want to change are in the same line on the scrolling window. I would use the SQL method. The SQL script is pretty easy. You need a list of the Item numbers of the Kit, Details about the component you want to change and details about the component you want to add. Get all of that in an Excel Spreadsheet. You are looking to update the IV00400 table, it is the Item Kit Master. It contains the following fields:

ITEMNMBR - The Item Number of the Kit

SEQNUMBR - The sequence that each item in the kit appears in

CMPTITNM - The item number of the kit component

CMPITUOM - The unit of measure of the kit component

CMPITQTY - The quantity of the kit component

CMPSERNM - A flag indicating if the component is Serial Numbered: 0=no, 1=yes

DEX_ROW_ID - The is an identity field that SQL automatically provides

Fabrikam's Kit Master file (IV00400) looks like this:

I have three Kits highlighted. Let's say we need modify kit 'HDWR-DCD-0001' and replace the component item ITCT-CIR-CD85 that uses one of those in the kit with two pieces of item 100XLG that are serial numbered.

The SQL statement I would use would look something like this:

BEGIN TRANSACTION

UPDATE IV00104

SET CMPTITNM = 'XLG100' , CMPITQTY = 2 , CMPSERNM = 1

WHERE ITEMNMBR = 'HDWR-DCD-0001' AND CMPTITNM = 'ITCT-CIR-CD85'

If you like the results, execute COMMIT TRANSACTION

if you don't like the results, execut ROLLBACK TRANSACTION and no change will be made.

If you are only replacing one item, I normally go the lazy way and just change what I need to in the table without recalculating the sequence number. If you've got more than one just come up with another sequence number. Each component of an item must have a different sequence number.

Since you have thousands I would create a spreadsheet showing the Kit Item I'm changing, the component opjects leaving and the component opjects coming. I'd then write a script similar to the one above and use mail-merge in Word so that it would create the monster SQL script I would need to make this change. Like all things, try it on a test company first.

I know it works because I've done it. It know it used to work anyway.

Test - test - test.

Kind regards,

Leslie
Heather Roggeveen 9,146 on at

Like (0)

Report

RE: Kit - Delete Product & ADD a new product

Hi

I am not aware of any automated ways. Rough thoughts - non of them tested - just to get some thinking going:

- Is the product that is being removed still a product that you sell? If not, could you use the Inventory Modifier tools in PSTL to modify the core item to the new item - thereby updating all the kits at the same time?

- SQL script to identify all the kits the item is on and use the output from that to be able to script the change - definite SQL skills and backup required there.

- SQL script to identify all the kits and use that to create a macro file (but this would probably be bitsy as each kit may have a different number of lines on it).

Those are my ideas.

Cheers

Heather