need to change item no. value

[gimmeabrk]

Hi,
I entered quite a few items in the inventory with wrong naming conception. Now, I planned to correct it by trying to change the item no. value. But I am not able to do it neither in the Item File Maintenance Change Screen nor through SQLScope (Win16) application of Pervasive SQL7 as it is the primary key.
The one way i found was to create manually and all over again the item with correct item no. value with rest of item property values as it is, and then through Product Structure file maintenance mass_replace screen.
Is there a simpler way of doing this ?

thanx...

shridhar

 

[vbajock]

The item number is a "key" value, and under the rules of all relational databases, cannot be changed. The way you are doing it is the best way for someone not familiar with working with Macola at the file level. If you are comfortable working at the file level, first, back up the Item master, then export the Item master file to a spreadsheet, initialize the Macola file, fix the keys in the spreadsheet, and reimport the fixed file into the initialized Macola file, otherwise, it really is something that should be handled by a pro.
Kirk

 

[dgillz]

There is a utility to do this at the item numnber level. It will change all sorts of files including the following:

Item master IMITMIDX
Item location IMINVLOC
Bill of Material BMPRDSTR
Item Trans History IMINVTRX
Item Audit Trail IMAUDFIL
OE Line Histoy OELINHST
OE Order Line OEORDLIN
....

The list goes on and on. There are literally hundreds of tables impacted by this typ of change. This utility program is available from your Macola reseller. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[gimmeabrk]

Hi vbajock,
thanx for ur help. I am game to ur idea. I take backup of all macola data files on a daily basis. I am using macola v6.7 and am not aware of how to export a file to a spreadsheet.
In the file utilities section, I only see the option of exporting ascii files. Your suggestion seems brilliant in helping me change the naming convention for quite a no. of items.
Can u help me out as regards exporting to a spreadsheet ?

thanx in advance.

shridhar

 

[dgillz]

Gimmeabrk,

I hope you are talking about Macola V7.6 not V6.7. There is no such version of Macola. There is a version of Elliott softare by Netcellent by that version number, but it is NOT Macola. It is somewhat of a spin off on Macola, but a totally separate company and package. There are updates from Macola v6x to Elliott, and it is a good package, but it is not Macola.

You cannot export from Macola directly to a spreadsheet. Even if you could, you are asking for big trouble, regardless if you are using Macola, Elliott, or any opther package, as the item number is literally in hundreds of tables and changing this one table will cause you more harm than help.

Please let me know what you are working with and I will do my best to help. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[vbajock]

You might want to check into the utility Don is talking about. I've never heard of a version 6.7 either. Are you sure this is correct? Have you processed any orders or created any location records for these keys or are they just entered in the Item Master?

 

[markajem]

shridhar - I will want to also put my two cents in here.

You state "I am using macola v6.7 and am not aware of how to export a file to a spreadsheet." please understand even seasoned programmers who know how to do this and have done it before MUST be very careful. So many problems can occured doing this. Without using a utility such as Don suggests where the software will handle making sure all the nec files are updated you can end up with major problems that may not even show up right away. It is also important to know how much data has already been entered into your database since you set up your items. That is also a key. Taking on a project such as this manually is dangerous and could cause serious problems.

If you have just set up your items, not entered any information anywhere else or anything, you may just be better off initializing the files needed and starting over again - but I would not even want to try and do that manually unless I had a full set of file definitions with full explanation of what each and every file does and when it does it.

Mark Mark
Email: markanas333@hotmail.com

 

[dgillz]

To followup on Kirk's post, the absolute bare minimum number of files you impact by adding an item number is 2, the Item Master IMITMIDX and Item Location IMINVLOC.

If the audit trail is turned on in IM setup, the number of files is 4, adding the audit files for the item master and item location. If you do any changes like you are contemplating, please keep this in mind.

Also, there is also a very good report within Macola that you need to run called a file validation, which is under system manager, maintain, system utilities, file validation. This report basically tells you what is wrong with all of your major files such as Item Master, Customer Master, etc. I would most definately run this if you do any sort of major file work. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[gimmeabrk]

Hi,
first of all, thanx a lot for all the valuable information.
I am using the Netcellent Elliott Software V6.72b.
My elliott data files contains very large information say 15k+ parts and quite a lot of p/o's and bom's, so initialising is just out of scope. I need to make a change in item no. value for around 50 items.
vbajock's idea of exporting to a spreadsheet, changing the primary key values and importing them back to data file won't work as it would just change IMITMIDX file. I would have to repeat this step for many other such data/table files, i assume atleast 6-8 tables, so this option is also out of scope.
Product Structure file maintenance mass_replace screen takes care of replacing an obsolete item with another item in all other places but as mentioned before, it involves my manually creating a copy of my existing item with wrong naming convention. i wanted to avoid the manual entry part for these 50 items.
Hi dgillz, the utility program which u mentioned can take care of this item no. change in all the tables; Is it a free utitility provided by my elliott reseller or do i have to purchase it ?
is the file validation screen available in elliott as i couldn't find one on my own.
Doesn't Macolla/Elliott provide for creating copies of items and modifying their properties. This would help in faster creation of new items. I have so far seen only Copy_structure screen which helps in creating copies of BOM structures. What if my item doesn't have a structure ?
Lastly, is this the right forum to deal with elliott issues or am I in the wrong group.

shridhar

 

[dgillz]

I do not believe there is a separate Netcellent forum, but this is probably as good of a place as any to discuss this.

In terms of tables impacted, if you are actually using the software and not just loading in the data, 6 to 8 is not even near the number of tables you are looking at, it is more like 75 to 100 tables, depending on weather or not you are using Serial control, Lot control, multibin, EDI, etc.

I would call Netcellent and see if they offer this type of service Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[JonathanR]

Peak Data Systems has a utility that allows you to change the Item Number, and this utility changes all affected files. They are a developer of enhancements for Macola. I do not know if the program works with Netcellent - I doubt it - but you could certainly give them a call at (973) 659 1990 or

http://www.peakdata.com.

The enhancement is called Global Item Number Change.

 

[dgillz]

JohnathanR,

Please read this post more closely.

You are correct in that Peak has this ulility -- it is the one I was talking about -- but this user does not own Macola 7x.

This user owns Netcellent's Elliott system, a Macola spin-off. Do you know if Peak has this utility? I do not believe they do. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[vbajock]

I would doubt that any utility written for Macola would work for a step-child system. There are bound to be differences in the database structures and version differences that would cause the utility to bomb. If this is something that is going to cost you big bucks to fix manually I would find a pro who knew how to hook your old Btrieve database engine to a Microsoft Access database, that is, if it could even be done. If it could, someone with the right skill set could use Access to do mass updates on multiple files. To me, it looks like you have a lot of typing to do.

 

[AKPolarBear]

Just an FYI, I have used the Peak Data product in Macola to rename over a thousand item numbers with auditing turned on. It works with out any problem.

 

[MacolaHelp]

This fellow is on Netcellent, an outgrowth of Macola v6. There was an enhancement for Macola v6 to mass change item numbers that affected all the tables involved that probably would work for NSI/Elliott. The program exports the files to text, applies the changes to the tables involved & imports them back in again. I would feel pretty confident that it would work. In addition, then, the additional files that are Elliott-specific that were added above & beyond generic Macola could be exported & changed manually using ultraedit. We would need to verify with NSI which files were added in his version, but it would probably save a lot of typing. If gimmeabrk wants more information, I can assist as I have used the v6 utility many times & have a couple of customers on Elliott.

 

[netcellent]

Hi -

I'm also an Netcellent customer. I am familiar with the lack of a MASS Item setup change in Elliott. I was able localize table changes to IMITMFIL and IMITMADT for a new item setup. (I did this through timestamp analysis )

At this point, if you find the item setup to be far too redundant, if you're a bold developer, you can actually create a front end interace using VB or some sort of app to create your own custom item setup or a batch action to import all the new items. The field mappings and everything else along those lines are up to you. I have encountered this problem before and wrote a lot of 3rd party utilities.

I'm just curious, but wouldn't it make more sense to normalize the tables?

- Mike