version 1.5
OP Multi query selection (connectionID; tableID; tableIDs; fieldIDs; logicalOperators; queryOperators; queryValues; recordsFound) Number
Parameter | Type | Description | |
connectionID | Longint | Connection ID with target server | |
tableID | Longint | Number of the table in the database | |
tableIDs | Array | Array of related tables numbers in the database | |
fieldIDs | Array | Array of related field numbers in their tables | |
logicalOperators | Array | Query lines logical conjunctors | |
queryOperators | Array | Array of query operators | |
queryValues | Array | Array of query values | |
recordsFound | Longint | Numbers of records in resulting selection | |
Function result | Number | Error code result for the function |
Description
OP Multi query selection queries tableID using fields (from multiple tables) passed in fieldIDs. The new current selection is build from tableID 's current selection.
The maximum number of elements for any array is 20. If you pass an array with more than 20 elements, only the first 20 elements of the array will be used, but no error will be returned.
tableIDs and fieldIDs are arrays of type Integer or Longint, containing the table and field numbers of the tables and fields to query.
logicalOperator and queryOperator are arrays of type Integer, Longint, Real, String or Text, containing the logical and query operators.
The logical operators are as follows:
Description | Alpha Array | Numeric Array |
And | "&" | 1 |
Or | "|" | 2 |
Except | "#" | 0 |
The logical operator connects each query line to the result obtained by the previous line(s). Therefore, the logical operator for the first search line is simply ignored.
The query operators are as follows:
Description | Alpha | Numeric |
Array | Array | |
Equal | "=" | 1 |
Not equal | "#" | 2 |
Greater than | ">" | 3 |
Greater than or equal to | ">=" | 4 |
Less than | "<" | 5 |
Less than or equal to | "<=" | 6 |
queryValue is an array of type String or Text that contains one or more values that can begin with, contain or end with the "@" symbol for wildcard queries.
Values for each data type are passed as strings. For example, to query for a date, pass "12/03/97" for December 3, 1997. To query for a Boolean value, you must pass "false" for False and "true" for True.
Error Codes
If OP Multi query executes successfully, it returns 0. Otherwise, this function returns one of the following errors:
Error Code | Description |
-9969 | Invalid field type requested. |
-9971 | Field number is out of range. |
-9972 | Table number is out of range. |
10128 | The 4D Open for 4th Dimension package |
has not been initialized. | |
10135 | Invalid parameter type. |
10136 | The connection does not exist. |
10151 | Invalid query operator. |
10152 | Invalid logical operator. |
10153 | Unable to convert this type of data. |
10154 | This command cannot be executed right now. |
10156 | Empty search or sort definition. |
Note: If there is an error due to an element of one of the arrays that you have passed, OP Multi query sets the selected element number of each array to the number of the faulty element. For example, if there is an error in the fifth element of one of the arrays, the selected element number of all of the arrays is set to 5.
Example
This example searches for invoices concerning customers located in Ohio or Texas on a selection of invoices prviously made by the user
.
C_LONGINT($ErrCode;$TrgTable;vRecords) ARRAY LONGINT(arTableID;0) ARRAY LONGINT(arFieldID;0) ARRAY TEXT(arLogicalOp;0) ARRAY TEXT(arQueryOp;0) ARRAY TEXT(arValues;0) UserSelectInvoices `Display a dialog that let the user specify criteria ` Perform the query specified by the user OP Multi query (vConnectID;2;arFileID;arFieldID; arLogicalOp;arQueryOp;arValues;vRecords) ARRAY LONGINT(arTableID;3) ARRAY LONGINT(arFieldID;3) ARRAY TEXT(arLogicalOp;2) ARRAY TEXT(arQueryOp;2) ARRAY TEXT(arValues;2) $TrgTable:=2 ` We want to build a selection of records on [Invoices] arTableID{1}:=1 ` [Customers] arTableID{2}:=1 ` [Customers] arFieldID{1}:=6 ` [Customers]State arFieldID{2}:=6 ` [Customers]State arQueryOp{1}:="=" ` [Customers]State = arQueryOp{2}:="=" ` [Customers]State = arValues{1}:="Texas" ` [Customers]State = "Texas" arValues{2}:="Ohio" ` [Customers]State = "Ohio" arLogicalOp{1}:="" ` [Customers]State = "Texas" arLogicalOp{2}:="|" ` or [Customers]State = "Ohio" ` Then queries the user-defined selection with new criteria $ErrCode:=OP Multi query selection (vConnectID;$TrgFile;arFileID;arFieldID; arLogicalOp;arQueryOp;arValues;vRecords) If ($ErrCode=0) ALERT(String(vRecords)+" record(s) were found.") End if
See Also
OP Multi query, OP Single order by, OP Single query, QUERY SELECTION.