Item search fails to return data for single item 2

[AKPolarBear]

I have one item (so far anyway) that includes the word 'Wrench" at the end of its description. When you search on that word using "contains", nothing is returned. The description is less than 30 characters long and only uses the first line for text.
I tried using the wildcards and like but same result. All other items appear to work just fine.

Suggestions?

 

[dgillz]

Using Crystal, or enteprise manager or some other tool, look at the raw data and see if the desc_1 field matches the Search_desc field. I would bet you an adult beverage that "wrench" does't appear in the search_desc field.


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[AKPolarBear]

Do you include gift wrapping with that offer?

Although, I thought the same thing at first. Here is the raw data output:

11/3/04
item_no search_desc item_desc_1
3420-20401 OBS 9/04 SNAP TIE WRENCH OBS 9/04 SNAP TIE WRENCH

Looks all jumbled here, but the descriptions matched.

I tried deleting the text and re-entering it, no change.
Then I tried just using the word "wrench". It failed to return from either "begins with" or "contains" searches. But I could read it through Crystal without any problem each time I changed it through Macola.

Now I wonder how many others I might have lurking out there? What would cause Macola not to return a file like that fom SQL. SQL appears to be working as I can access it through CRW.

 

[dgillz]

Wow.

Just grasping at straws here, but delete the slash "/" from both fields and they try to search it.

Also, if you run a file validation report on the IMITMIDX_SQL table for this item, do you find anything?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[crystalreporting]

For many reasons - I always strongly advise against using ANY special characters in Macola (or any database for that matter) - it just causes problems with searches, exports etc. The '/' may not be the cause of the problem, but let's hope so - Don could do with that 'adult beverage'

Do other searches work OK? I've also seen where the searches act funny when the Macola/SQL user name contains special characters..

Peter Shirley

http://www.linkcorp.com

 

[dgillz]

Bob,

Are you using a SQL or Pervasive database?

What kind of "adult beverages" do they drink up there? Yukon Jack?




Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[AKPolarBear]

Sorry for the delay, our phone system was hacked yesterday so had to spend my day cleaning up that mess. Maybe this subject might become a new forum!

We are SQL database. I removed the slash, same results.

I also tested in CRW searching on the search description table using "is like *wrench". Found it no problem.

The file validation report reported:
------------------------------------------------------------Item No: 3420-20401
Item Description: OBS 9/04 SNAP TIE WRENCH
ITEM-DEFAULT-LOCATION: Default Item Location Not On File.

The location in master was correct so I reentered it into the item location file.

The search now works!

Guess I better run that report against the entire inventory some time.

Thanks

 

[kscheeler]

Don,

A star for you for pointing in the right direction. I tried to set up an item in the same manner with a default location that didn't exist but it still found it in the search.

The important thing is that it worked!



Kevin Scheeler

 

[dgillz]

Bob, I am glad you found the problem.

Kevin, thanks for the star.

I love the file validation reports, especially when I import data form other databases or applications. I just wish they would come up with something similar for Macola ES.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[AKPolarBear]

I ran the report and found 109 errors with the ITEM-DEFAULT-LOCATION problem. I randomly selected a handful for searching and none came up in the search.

I looked up the location in the Master and added it to the Item location file.
Then the search worked for that item.

So it would appear that when you set up an item in the Master you must also do the same in the Item location for the search to find it.

Does that make sense?

I suppose it does in Macola...

 

[dgillz]

The search not working in this situation makes no sense to me, but I'm glad you found it. I will definately file that away for future reference.

However I would be concerned about your database. When you initially setup a new item , the default item location record (IMINVLOC) gets created automatically. Also, the item's primary IMINVLOC record cannto be deleted in Macola.

So in order for you to get this error on the file validation report, one of 3 things had to happen:

1) Someone deleted the location record from the IMLOCFIL.
2) Someone changed the Item master record to have a difference primary location, then deleted the IMINVLOC record which could not have been deleted as the primary record.
3) The IMITMIDX table was written to by an external program such as MS Access which does not have any built in referential integrity checks.

It might be a good idea for you to look into this and see how and why this happened. The answer(s) could be legitimate, but then again.....



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[AKPolarBear]

In response to:

#1 Item location not deleted and still very active.

#2 This might be true. We obsolete items from time to time (add the OBS in the description) and the primary location is changed to another that is used for liquidation and to keep it out of the active groups. But that location we use, is a valid location number.

#3 nah, we don't do that stuff. Macola is fragile enough!

So, #2 appears to be the problem. I suspect when they change the item, the item location file is no longer valid and needs to be changed as well. Unlike when you first created the item, it was handled automatically.

Sounds like a possible bug to me.

 

[kscheeler]

To clarify something. When you add a new item, Macola creates an item location record for the primary location you enter on the item master record. If you then go back and change the primary location to someother location after the fact, Macola DOES NOT create any additional records if necessary in the item location table.

You have to either manually create the records or run the add/delete item location function in IM > Processing to do that on a mass scale.

Kevin Scheeler