Duplicate item_no in the Item Maintenance

[mlbechard]

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

 

[dgillz]

If this has only been happenning for a couple of days did you just perform an update?

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

select item_no, COUNT(Item_no) as ItemCount
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:

http://www.gainfocus.biz/exceladdin.html

 

[mlbechard]

I don't think there is any update installed in the last couple of weeks.

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

 

[NEmacGuy]

Hm, ES so its not the filler in Progression Key is Item_No and Item_Filler. Perhaps lowercase and uppercase issue.

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

 

[dgillz]

Or check the table itself to make sure it has not been altered to allow duplicates.

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:

http://www.gainfocus.biz/exceladdin.html

 

[mlbechard]

I ran the 2 sql script suggested by NEmacGuy and the result is empty for both.

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

 

[mlbechard]

Need help on this please!!!!

How can i check the imitmidx_sql table itself to make sure it has not been altered to allow duplicates?

Thank you

 

[crystalreporting]

Look at the properties of the IMITMIDX_SQL table in SQL (EM or SMS). The indexes should be listed there.

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

 

[mlbechard]

The duplicates item_no happen in our test company only. The live company is working fine. The test and live company are not in the same server either.

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

 

[dgillz]

I would run SQL Delta against your newly created demo and test databases to see if any more indexes are missing. It has a free 14 day trial period and it is a great tool:

http://www.sqldelta.com/downloads.html

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:

http://www.gainfocus.biz/exceladdin.html