How to do a search with Locate
with two or more fields simultaneously?
- What are the disadvantages of doing this?
How to do a search with Locate
with two or more fields simultaneously?
LOCATE - Statement:
function Locate(const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions): Boolean; virtual;
This method allows the exact search of a record, by fields that are not part of the current index of the table represented by the component, and receives as parameters:
LoPartialKey
- indicates that the search will be approximate. This is an example of how to do the Locate
method with two fields that I found in docwiki.embarcadero.com / Using_Locate .
CustTable.IndexFieldNames := 'ID';
if CustTable.Locate('ID1';'ID1', VarArrayOf([´String1´,´String2´]), [])then
ShowMessage ('O cliente com ID = String1 e ID = String2 encontrado')
else
ShowMessage ('O cliente não encontrado') ;
In the docwiki.embarcadero.com has the full description of the method and I also found another method that searches with the fields that is the Extended Locating a> take a look at the LocateEx
method that accepts field values or expressions.
Field Value :
if not CustTable.LocateEx('COMPANY', 'AMCO', [lxoCaseInsensitive]) then
ShowMessage('The customer from AMCO company is not found')
else
ShowMessage('Order is not found');
Expression :
if FDQuery1.LocateEx('Price >= 1000 and Price <= 2000', []) then
ShowMessage('Order is found')
else
ShowMessage('Order is not found');
The Locate
that I'm used to doing is in this format:
DataSetX.Locate('CODIGO;DATAVENCTO;COD_PRODUTO',VarArrayOf([QOperacoesCODIGO.AsInteger
,QOperacoesDATAVENCIMENTO.AsDateTime
,QOperacoesPRODUTO.AsInteger]), [])
The disadvantage of Locate
exists only when you have a DataSet
that was not loaded with FetchAll
, because running it may require that the DataSet reload all the data in the Table, increasing the consumption of memory.
A good alternative would be to use a ClientDataSet
that has data compression property, so you would load this client with data from the dataset and destroy it, leaving only Client with data ready for search.
In a Client it would use FindKey
the operation is the same, however, much faster.
ClientDataSetX.FindKey([QOperacoesCODIGO.AsInteger
,QOperacoesDATAVENCIMENTO.AsDateTime
,QOperacoesPRODUTO.AsInteger])
It is worth remembering that for this you need to define an Index for the Client to function correctly, in the case of the example mentioned the Index would be by the fields CODE, DATAVENCIMENTO E PRODUCT .