Locate for two fields

1

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

  • What are the disadvantages of doing this?
asked by anonymous 25.11.2017 / 12:36

2 answers

2

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:

  • A string containing the field names for which the (separated by semicolons).
  • A string containing the values to be searched in fields by the which will be done (separated by semicolons).
  • A set of options, which may contain the following elements: LoCaseInsensitive - if this element is included, uppercase and lowercase letters will be treated regardless; 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');
    
25.11.2017 / 13:26
3

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 .

    
27.11.2017 / 11:11