Duplicate item_no in the Item Maintenance
Duplicate item_no in the Item Maintenance
(OP)
Since a couple of days, we see duplicate item_no in the Item Maintenance screen. I looked into the imitmidx_sql table and the item_no are duplicates. I did some test to find out what is causing the duplication and here is what I found. Each time we click on the Item Location button in the Item Maintenance screen, another record with the same data is added in the imitmidx_sql table. That means we can have multiple time the same item_no.
It never happened before. Can someone help on this odd issue?
Thanks in advance
It never happened before. Can someone help on this odd issue?
Thanks in advance
RE: Duplicate item_no in the Item Maintenance
Do you have any flexibility code running behind the item location screen?
Run the following SQL script to test for duplicates in the imitmidx_sql
CODE
from imitmidx_sql
group by item_no
order by ItemCount desc
The duplicates will be at the top of the list, anything with a count of > 1. If they all say 1 there are no duplicates.
The only way this can happen is if someone or someting altered the SQL table to allow duplicates. Let me know what you find on the SQL script.
Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:
www.gainfocus.biz/exceladdin.html
RE: Duplicate item_no in the Item Maintenance
The Item Maintenance screen is customized since 2005 and a recent changes has been done (2 weeks ago). The changes was to unhide the User Defined field (ID 75) from the Extra tab and show it in the Basics tab. The Item Location is the original one from Macola.
There is also a third party that should update only the exta_7 and extra_8 fields. But I noticed that there was an item_no created with blank (was not existing before). That means they have inserted items. Is that could be the cause?
Here is a part of the query's result:
item_no ItemCount
5
8556.0000R01 2
8555.2008R01 2
8531.2008R02 2
8558.0000R01 2
22055.4840R01 1
33000.2120R01 1
33200.0240R01 1
33200.0460R01 1
33501.1054R01 1
...
How someone can altered the SQL table to allow duplicates?
Here is the thing I don't understand. The table associated with Item Maintanance is imitmidx_sql and the table associated with the Item Location is iminvloc_sql. Right? How another entance in the imitmidx_sql table (duplicate) could be inserted when clicking on the Item Location button?
Thanks
RE: Duplicate item_no in the Item Maintenance
select item_no from imitmidx_sql where UPPER(item_no) <> item_no
Or issue with spaces
select item_no from imitmidx_Sql where rtrim(item_no) <> item_no
RE: Duplicate item_no in the Item Maintenance
Who did the recent work on the screen and what is the 3rd party app writing to those other fields? Is it not Flexibility?
Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:
www.gainfocus.biz/exceladdin.html
RE: Duplicate item_no in the Item Maintenance
I don't know how to check the table itself to make sure it has not been altered to allow duplicates. Could you explain me how to do it? I normally use Microsoft SQL Server Management Studio.
I did the recent work on the screen. It is not the first time I am doing it. I also did the same changes on another server and it does not duplicates the item_no. There are actually 3 servers for Macola (Prod, Test, a new one that will become the prod one). The problem occur on the Test server and I replicate the changes on the one that will become the prod one).
The 3rd party app is not Flexibility, it is a web app.
Thanks
RE: Duplicate item_no in the Item Maintenance
How can i check the imitmidx_sql table itself to make sure it has not been altered to allow duplicates?
Thank you
RE: Duplicate item_no in the Item Maintenance
For Macola ES the very first index is 'Iimitmidx_sql0' and it uses the item_no field only. It's clustered and it's unique. For you to have multiple records in the IMITMIDX_SQL table with the same item number, someone must have deleted or changed this index. There are additional indexes on things like upc code and the search description but those wont prevent duplicate item numbers.
No software will override the rules that are built into the SQL indexes i.e. if the index is marked as unique then values in that field, in that table have to be unique.
An easy way to see how the indexes for each table should be configured is to use ES's built-in ability to create a demo company. Create a new demo company (to rule out anyone messing with the indexes in an existing demo company) and then compare the indexes with those in your live company.
You should also realize that you wont be able to reinstate the missing 'Iimitmidx_sql0' index until you take care of the duplicates. If you try to manually recreate this index while the duplicates exist, it will fail. I'm suprised the duplicates aren't already giving you issues in other parts of Macola.
Once you do get the duplicates removed and the unique index recreated, don't be suprised if you get errors when trying to add new inventory items. For whatever reason, your Macola ES install is adding duplicates - when you reinstate the unique index, you'll interrupt the logic your application is trying to use.
Data integrity is everything and at this stage I'd be working with my reseller or Exact to understand this problem A.S.A.P.
Peter Shirley
Macola Consultant, PA and surrounding states.
http://www.erpessentials.com
RE: Duplicate item_no in the Item Maintenance
We have noticed the duplicates because we cannot access the Bill of resource when the item_no is duplicated.
To see the indexes of the table, I use MS SQL Server Management Studio. In our live company, here are the indexes:
Iimitmidx_sql0 (Clustered)
Iimitmidx_sql1 (Unique, non-clustered)
IMITMIDX_SQL2 (non-unique, non-clustered)
pk_imitmidx_sql (unique, non-clustered)
When I try to see the indexes for the table in our test company, the waiting cursor is displayed forever. I waited more than 3 minutes and still no indexed displayed. I tried another table (oeordhdr_sql) and the indexes was shown within a few seconds.
From what i understood, the best was would be to restore that table from either the live company or a backup. Is that correct?
Thanks
RE: Duplicate item_no in the Item Maintenance
Also it is very cheap if you wish to buy it.
After determining the difference between database structures and indexes, you can run a SQL script to sync them. You can sync the actal data too. As I said it is a great tool.
Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:
www.gainfocus.biz/exceladdin.html