Database Search String Error

hcl-bot · September 15, 2005, 1:25pm

I have a search form for a database that has the user fill in the different fields to get their search criteria. If the fields have data in them they are added into a search string that is then executed and the results are put into a folder “Query Report”. A portion of the code looks like this:

If (doc.site(0) <> “”) Then SearchFormula$ = SearchFormula$ + " & " + “Site = @Text('” + doc.site(0) +“')”

If (doc.manufacturer(0) <> “”) Then SearchFormula$ = SearchFormula$ + " & " + “@Contains(@ProperCase(Manufacturer);”+“@ProperCase('”+doc.Manufacturer(0)+“')”+“)”

If (doc.Product_Name(0) <> “”) Then SearchFormula$ = SearchFormula$ + " & " + “@Contains(@ProperCase(Product_Name);”+“@ProperCase('”+doc.Product_Name(0)+“')”+“)”

If (doc.Synonym(0) <> “”) Then SearchFormula$ = SearchFormula$ + " & " + “@Contains(@ProperCase(Synonym);”+“@ProperCase('”+doc.Synonym(0)+“')”+“)”

If (doc.MSDS_No(0) <> “”) Then SearchFormula$ = SearchFormula$ + " & " + “@Contains(@ProperCase(MSDS_No);”+“@ProperCase('”+doc.MSDS_No(0)+“')”+“)”

If (doc.Stock_Code(0) <> “”) Then SearchFormula$ = SearchFormula$ + " & " +“@Contains(@ProperCase(Stock_Code);”+“@ProperCase('”+doc.Stock_Code(0)+“')”+“)”

If (doc.Product_Group(0) <> “”) Then SearchFormula$ = SearchFormula$ + " & " + “@Contains(@ProperCase(Product_Group);”+“@ProperCase('”+doc.Product_Group(0)+“')”+“)”

Set collection = db.Search(SearchFormula$, dt, 0)

Call collection.PutAllInFolder( “Query Report” )

Call fview.Refresh

Call workspace.ViewRefresh

Call uidoc.Close

The code works great and I can obtain successful searches on all fields EXCEPT for the Stock_Code field. When a value is entered into that field I always come up with an empty search even though I know the documents exist. The field Stock_Code is a text list field in my forms “RP” and “RPLegacy”. It is also a text field in my search form called “Stock_Code”.

If I search for a paticular document I can find it using any of the other fields but if i put in the Stock_Code it will not find the document.

I have run the code through the debugger and it appears that it is grabbing the values correctly however it will not return any documents when i put in a Stock_Code

Why does this code work for all my other fields fine but not for this field?

**NOTE: I did not write this code originally but I maintain the database now. A user has asked me why the Stock_Code field is not being searched on correctly, and have claimed it had never worked.

hcl-bot · September 15, 2005, 1:56pm

Subject: Database Search String Error

Probably, the reason your query doesn’t work is that it actually doesn’t match any documents. If you use the debugger to look at the query, and you carefully consider that query in light of an existing document you think it’s supposed to match, stepping through the functions and logic of the select statement as if you were the computer, I think you will see why. If necessary, copy the query to the clipboard, create a view, use that as the selection criteria for the view. Then you can fiddle with it until your documents show up in the view.

Since the code for adding the Stock_Code field to the search string is identical to that for other fields, except for the substitution of the name Stock_Code, then logically the problem has to do with a difference in the way data are stored in that field or with the contents of the value the user enters, not with the syntax of the search query. However, you have not supplied information about these factors.

I have to wonder about the use of @ProperCase here. If the stock code contains RT56B and the user enters T56B, is that supposed to be a match? Because @ProperCase of RT56B is Rt56B, while @ProperCase of T56B is T56B. Rt56B does not contain T56B. Perhaps you should be using @Lowercase instead?

I’m assuming that there’s nothing strikingly unusual about the Stock_Code field that you’re failing to tell us, e.g. that it’s Computed for Display, or shows up as non-summary when you look at it in the Document properties from a view, or …? You might consider making a list of all the ways this field differs from other fields for which the query does work.

hcl-bot · September 16, 2005, 8:18am

Subject: RE: Database Search String Error

I have tried doing a query on a document that I know exist with a certain Stock_Code. The search will come up empty. However, if I search on lets say just the manufacturer field of that same document it will be retrieved. If I try to search on both the maufactuer and Stock Code fields for a document I know exist it will come up NULL. Basically, if I put any information to search for with Stock_Code I cannot get any results. Searching on the other fields works as intended.

The Stock_Code field is a editable text list. The other fields are just text fields. I do not think that should matter since @Contains is meant to handle text list as well. I have broken down the code and have ran it through debugger and it appears that the code is working but it will not return any documents even though I know they exsist.

Here is what the Debugger shows SearchFormula$ after I step through the Stock_Code step using BV as my site and 9827337 as my stock code. (Note: This document does exist but does not display in results):

SearchFormula = “Form = ““RP”” | Form = ““RPLegacy”” & Site = @Text(‘BV’) & @Contains(@ProperCase(Stock_Code);@ProperCase(‘9827337’))”

If I were to not use Stock_Code as a criteria for search and used lets say maufactuer I would get: (This works and gives proper results)

SearchFormula = “Form = ““RP”” | Form = ““RPLegacy”” & Site = @Text(‘BV’) & @Contains(@ProperCase(Manufacturer);@ProperCase(‘Lucas’))”

If I try to combine the above searches which should retrieve the same document I get no results. The search string in debugger shows:

SearchFormula = “Form = ““RP”” | Form = ““RPLegacy”” & Site = @Text(‘BV’) & @Contains(@ProperCase(Manufacturer);@ProperCase(‘Lucas’)) & @Contains(@ProperCase(Stock_Code);@ProperCase(‘9827337’))”

That string is then passed into my db.search and then put into the folder.

I know @Propercase(‘9827337’) = “9827337” from doing some simple test and breaking down the code.

Does this help you anymore in describing my situation? I can’t figure out for the life of me why that field is the only one not working when the others do and use the exact same code…

hcl-bot · September 16, 2005, 10:19am

Subject: RE: Database Search String Error

Create a view, and copy the selection formula you think should work into that view. Does it find any documents?

From a view, look at the attributes of the Stock_Code item in a document that you think should match the query.

What all does it say in the right pane for this item (you can copy and paste this information)? Is the data type really text list? Do the field flags say “SUMMARY”? Is the value what you think it should be?

hcl-bot · September 16, 2005, 11:56am

Subject: RE: Database Search String Error

I am unable to paste a screen shot of the properties but to answer your questions:Data Type: Text List

Field Flags: “SUMMARY”

And yes, it is the value I think it should be.

I did some playing around with creating a new view and manipulating the selection statement with no success.

I did however figure out the problem. The problem is that Stock_Code does not work because it is a Text List. I do not know why considering it should, but it does not. I made the field a single text field and it works fine. My client informs me that they need that field to be multi-valued so I am currently changing the code to work for the Text List. Since @Contains does not appear to be working properly in this case, I will try to use some other functions and see if I can develop a solution.

If you have any suggestions let me know.

hcl-bot · September 16, 2005, 12:08pm

Subject: RE: Database Search String Error

I’m sorry – are you claiming that you changed the field to single-valued on the form and did nothing else, and that made it start working? Because I don’t believe that. Or did you also edit one or more documents? Did you try editing the documents without first changing the form?

@Contains works just fine with multivalued data. Your problem is not what you think it is.

hcl-bot · September 16, 2005, 12:54pm

Subject: RE: Database Search String Error

You know what, your right. It seems that if I refresh a document it will work. When I changed the form I also went in and edited the document. I put the form back to having multi-values and just refreshed the doc and it works fine. My guess is that at one time that field may have been a single value text field. Later on someone must have updated it to allow the client to enter in multiple values, however they did not refresh the previous documents. (Nor did they bother putting a comment in for what they did). I also saw that in my view that has the data categorized by Stock_Code that documents with the same stock_code would show up in different places with the same stock_code but were not grouped together like they should have been. By going into the doc and refreshing it it moved it with the other one and were then categorized correctly.

Thats interesting.

Thank you for all your help. I will just have an agent refresh all the docs in the database and that should work. Much less work then re-coding something that already works.