Locate for two fields

Question

Locate for two fields

How to do a search with Locate with two or more fields simultaneously?

What are the disadvantages of doing this?

1

delphi delphi-7 delphi-10

Author: Edu Mendonça, 2017-11-25

Source

2 answers

The Locate 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 has not been loaded with FetchAll, since when running it it may be necessary for the DataSet to reload all the data in the table, increasing memory consumption.

A good alternative would be to use a ClientDataSet that has data compression property, in this way you would load this client with the dataset data and destroy it, leaving only the Client with the data ready for research.

In a client would use FindKey the operation is the same, but 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 work correctly, in the case of the example cited the index would be by the fields code, DATAVENCIAMENTO and product.

3

Author: Junior Moreira, 2017-11-27 10:11:55

score 2 · Accepted Answer

LOCATE-statement:

function Locate(const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions): Boolean; virtual;

This method allows the exact search of a record, for fields that are not part of the current index of the table represented by the component, and receives as parameters:

a string containing the names of the fields by which the search (separated by semicolons).
a string containing the values to be searched in the fields by which will be searched (separated by semicolons).
Um set of options, which can contain the following elements: LoCaseInsensitive-if this element is included, uppercase and lowercase letters are treated indifferently; LoPartialKey – indicates that the search will be approximated.

This is an example of how to make Method Locate with two fields 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') ;

No docwiki.embarcadero.com has all the description of the method and I also found another method that search with fields that is Extended Locating take a look the LocateEx method 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');