We need to be able to search our purchase orders by description field. For example a PO was written for 100 of a "#32 hex bolt grade 5" then a few years later we want to search for all POs with "hex bolt" in the description.
What should be a basic feature of QuickBooks is not. How can we achieve this function using QODBC?
Actually, with a bit of imagination, this can be done using string functions within the WHERE clause of a SELECT statement like this :-
SELECT RefNumber, TxnDate, PurchaseOrderLineItemRefFullName, PurchaseOrderLineDesc FROM PurchaseOrderLine WHERE {fn Locate('hex bolt', {fn Lcase(PurchaseOrderLineDesc)} )} > 0
How does this work?
The string function Locate is used to find the 'hex bolt' string within PurchaseOrderLineDesc. If 'hex bolt' is found within the Purchase Order Line Description, the function will return a value greater than 0 and the record will be displayed. If the string is not found, the value returned will be 0 and the record will not be displayed.
To make sure that we also find "Hex Bolt" or "hex Bolt" the string function Lcase is used to lower case the characters in Purchase Order Line Description before the Locate function is evaluated.
Note: This example will need to search every purchase order, so the greater the number of purchase orders, the greater the time it will take.
Reference Functions
LOCATE(string_exp1, string_exp2[, start]) - Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first position in string_exp2 unless the optional argument, start is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned.
Example:
SELECT {fn LOCATE('a', "Name", 2)} AS "LocationOfA", "Name" FROM Customer
Returns:
|
LocationOfA
|
Name
|
|
0
|
Abercrombie, Kristy
|
|
11
|
2nd story addition
|
|
11
|
2nd story addition
|
|
11
|
2nd story addition
|
|
0
|
155 Wilks Blvd.
|
|
0
|
75 Sunset Rd.
|
LCASE(string_exp) - Converts all upper case characters in string_exp to lower case.
Example:
SELECT {fn LCASE("Name")} AS "LCase", "Name" FROM Customer
Returns:
|
LCase
|
Name
|
|
abercrombie, kristy
|
Abercrombie, Kristy
|
|
2nd story addition
|
2nd story addition
|
|
2nd story addition
|
2nd story addition
|
|
2nd story addition
|
2nd story addition
|
|
155 wilks blvd.
|
155 Wilks Blvd.
|
|
75 sunset rd.
|
75 Sunset Rd.
|
| 
