Macola File Validations

[abaldwin]

During my semi-annual endeavor to run file validations, and hopefully address any items found by this "WONDERFUL" tool. I have run across the following issue.

Sample data

File Selected: IMINVLOC All Records Selected
------------------------------------------------------
------------------------------------------------------
Item No: LAB10008-01 Item Location: SR

INV-LOC-PROD-CATEGORY: Category Not On File.


The problem is this. The prod_cat field in IMINVLOC contains a valid product category. The IMITMIDX file is reported as NO ERRORS.

Is this simply a flaw in the report (like some others I have seen in the past)? Or am I misunderstanding the field referred to here? The real hummdinger is that I have over 1500 pages of this error and it is difficult to weed out any real errors.

Thanks

Andy



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[kscheeler]

The only time I've seen this error is when there is NO product category in the item master. The field in the item master allows null values where as the one in the item location file doesn't. So, technically, even both have no product catgory, they still have different values since one has a null value and one has 3 spaces in the field.

Yes, the report should exclude that scenario as an issue

Kevin Scheeler

 

[abaldwin]

That is the part that befundles me. The prod_cat fields in both the master and the location file contain the same 3 char entry, which by the way is a valid prod cat.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[kscheeler]

I just ran a test on 7.6.300c on a test company and didn't come up with that error. I had one where the one in the location table didn't match the one in the item master but when I tabbed through the item master it reset the category in the location table and came off the report.

One thing to check is if it's doing it for ALL items for the error message or not. It might have a problem reading the IMCATFIL_SQL table. Do you get the same message with the item master table? You could tab through one of the items to see if it comes off the report or not.

I agree, if it's in the product category table and also in the item master and item location tables, you shouldn't be getting that error.

Kevin Scheeler

 

[abaldwin]

They match, the item master runs clean. Exported, initalized and imported the cat file. Same results.

Tabbing throug it worked. There are 1296 pages left to tab through each item.

What do you think of running the following sql

UPDATE IMINVLOC set prod_cat = prod_cat



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[abaldwin]

Actually only worked on 2 of the first 6 items in the report. Tabbing does not have an effect on the next 6 items.

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[abaldwin]

Actually found that most of these do not have an entry in the prod_cat field. How does that happen?


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[MacolaHelp]

Probably through the notorious "copy from" button. It seems to populate the related fields on an inconsistent basis. I had a user who once used an update query to change PC on item master without realizing it was in iminvloc as well. Remember, you can leave PC blank in the master file, too. Anyway, net result of not keeping the 2 in sync was many inexplicable problems. Can you write an update query to set iminvloc_sql.prod_cat = imitmidx_sql.prod_cat? If you have to go the other way, you'll have to use a different field as the many to one thing has stymied me. You're a programmer & I am not, so if you know the syntax, send it my way for future use. Also, sometimes deleting the value or changing it to something else will update the record while simply tabbing will not.

Did you say what version you were on?

 

[abaldwin]

Yea, I already ran a couple of sql scripts to update the prod_cat to get rid of the problem. I can only think of the copy from button as being the culprit. When we have changed prod_cats in the past we have done both the master and location tables as well as any captured bill that may be out there.

Here is the sql to update the loc pc from the item master pc

UPDATE IMINVLOC_SQL
SET prod_cat = (SELECT prod_cat FROM IMINTIDX_SQL where item_no = iminvloc_sql.item_no)

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[MacolaHelp]

How about going the other way, though, from iminvloc to imitmidx? I have never figured that one out. Would the solution be to clear the values from prod_cat in iminvloc and then repopulate from imitmidx?

Peggy

 

[abaldwin]

I dont think I understand you last question.

Do you want to populate the master pc with the values in the location pc? I got confused when you added the clearing of values in the LOCATION adn repopulating from master. Sounds like you reversed part of your question.

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[dgillz]

I agree with Macolahelp, although there is no need to "clear" values in the IMINVLOC, just set the IMINVLOC_SQL.prod_cat equal to the IMITMIDX_SQL.prod_cat, after making sure none of the latter are null or invalid values.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[abaldwin]

Already have done this. He was asking for the syntax and then furher asked about going the "other" way.


If you were to go from location and write back to master that can be done but that would ASSume that the entries in all the locations were correct and that the item master was wrong. In effect you would set the location = to the last location the sql came across. VERY bad idea.

Later.


Andy Baldwin

"Testing is the most overlooked programming language on the books!