Sage 100

I have a client on MAS90 4.4.0.10 and we keep having a re-occuring issue that I cannot resolve. Periodically an items average cost will go haywire. They are using standard cost so it does not cause any real issue anywhere but when we pull the data into Shadow Access (Connex Software MS Access Sync Utility) we get a decimal precision error. The error occurs because the value in CI_Item:AverageCost and CI_Item:TotalWarehouseValue are crazy numbers with a huge number of decimal places. In examining the item transactions there are no extraneous entries with wild cost values, in fact the item I checked today showed every transaction in and out as 0.45 going back to 2006. Has anyone seen this before or know what is causing it? Can some one write a utility to correct this once it is discovered? I manually edit CI_Item to make the average cost equal to the standard cost and calculate what the total warehouse value should be. If I remember correctly the Item Warehouse file also gets hosed and I have to manually correct it as well.

It wouldn't supprise me if the data is actually stored to the Nth decimal but the display is controlled by the decimal precision masking. Probably best bet is to contact Connex and have him put some type of rounding formula on that field..... I've seen this in Excel when doing queries. The data will appear as .0000000000022 and is corrected with formatting or an @round formula.

Thanks Jeff, BUT, been there done that, Dan is stumped. We tried everyhting we could think of and have actually been working on this for months.

I wouldn't say that I'm out of ideas. Usually I've found that the Enfore Double setting on the ODBC parameters is all that is needed. From your post it is clear that that didn't work. Perhaps you could send me a sample of the problematic data ala DFD&M via screen shot and I should be able to give you a work-around.

I agree - there is a large value out there somewhere.

I know there is a large value out there - I keep fixing them - the problem is it keeps re-occuring. and Dan you are correct, the enforece double is not working, Ralph and I both confirmed the enforcedouble=1 setting is in the ODBC connection screen. Since I just repaired the file I can't give you a screen shot but I can tell you the value in the average cost field was -89147849.3536 and the total warehouse value field was that number times 1466. I just manually set them to teh average cost or in this case .45.

I have had a client who is not using Average Cost as their valuation method who has some whacko avg costs out there and it was causing issues on either a screen or a standard report - can't remember which. Also had to manually fix the fields. I can't remember which of their companies had it happen, but they do use Bill production and I know sometimes that if you go negative QOH and THEN have your production bring it back positive, Average cost can't handle it. But can't say that's the cause of my client's issues.

Beth, that may be the same issue. This client is using BM Production Entry. I should followup on the negative quantitiy on hand issue.

Dan, didn't mean to sound like you were out of ideas, stumped was probably a poor choice of words. I just know how much time you and Ralph spent and thought we had it fixed but it is still happening. sheesh!

Instead of using a shadowed copy of CI_Item you might try checking the Pass Thru query check box in table setup. You may need to re-verify databases in the report, but usually it's not for the CI_Item table that you are shadowing so taking that one directly from MAS may not slow you down much.

If you prefer to continue Shadowing then you can go into design mode on the table and up the preceision of the field as high as 28 places and make the scale large as well. The Shadow program does not alter any settings you make to tables you can even delete fields and it won't bother to refresh those.

Dan, i remember trying both of those solution and not getting anywhere. Could I have been doing something wrong?

clarification: I have tried deleteing columns and increasing the precision - have not tried as a pass through.

@GregStiles I recreated a similar decimal precision error with your values and confirmed two ways that circumvent the issue. 1.) Delete the table from the Access database, de-select the table for Shadowing and immediately re-select it. This will recopy the MAS table structure. 2.) If you don't use the the oversized field - deleting it from the Access table also worked for me. If you use the first step then I suggest that you make the ItemCode and MASCompany fields key fields for the database so not to degrade performance of existing reports. You will likely need to do a Verify Database command also.

Average Cost Calculation Utility doesn't re-calc avg cost on Std-cost items. We modified the Utility for a customer to also recalculate the avg cost on Standard Cost items - would that help?

Dan, does deleting and recopying the table structure in Access change the precision when it finds a large value in the table? Just curious on that? Since the fields are not actually being used, we will try again to remove them from the table definitions. If we can get that to work that may prove to be the easist solution. Lee, that may actually be helpful, If we are not successful in going around this issue I will contact you on the recalc utility.

@GregStiles I couldn't see anything that had changed so I tested this twice and both times it worked after having failed. It may be that having the large value in the field was responsible for some unapparent change. I didn't test copying a repaired table structure.

UPDATE: Removing the offending columns from the access tables appears to have worked. I think when I had tried it before I was unaware of a second field that was causing the same error. Removing averagecost and totalwarehousevalue from both ci_item and im_itemwarehouse was necessary. Thanks.