Problem with Locate

[sde]

Hello,

I am having a problem with the TVirtualTable.Locate method. According to the documentation, the first input can be a string with multiple field names separated by semicolons so that it will search through each of those fields. However, when I try that, it does not locate a value that does exist in one of those fields. However, when I only pass in the specific field with the value to locate in it, it does work. If you could offer any help with this issue, I would appreciate it. I really need to locate fields over the entire data set as I do not know which field it will be in specifically, and calling the locate function for each field is not ideal.

Thanks!
SDE

[AlexP]

Hello,

No, such behavior is not supported. You should search for data in each field manually.

[sde]

That's unfortunate.

But why does the documentation indicate that you can? (Copied from http://docs.embarcadero.com/products/ra ... e_xml.html below)

Locate moves the cursor to the first row matching a specified set of search criteria. In its simplest form, you pass Locate the name of a column to search, a field value to match, and an options flag specifying whether the search is case-insensitive or if it can use partial-key matching. (Partial-key matching is when the criterion string need only be a prefix of the field value.) For example, the following code moves the cursor to the first row in the CustTable where the value in the Company column is "Professional Divers, Ltd.":

Code: Select all

var
  LocateSuccess: Boolean;
  SearchOptions: TLocateOptions;
begin
  SearchOptions := [loPartialKey];
  LocateSuccess := CustTable.Locate('Company', 'Professional Divers, Ltd.', SearchOptions);
end;

Code: Select all

TLocateOptions SearchOptions;
SearchOptions.Clear();
SearchOptions << loPartialKey;
bool LocateSuccess = CustTable->Locate("Company", "Professional Divers, Ltd.",

If Locate finds a match, the first record containing the match becomes the current record. Locate returns True if it finds a matching record, False if it does not. If a search fails, the current record does not change.

The real power of Locate comes into play when you want to search on multiple columns and specify multiple values to search for. Search values are Variants, which means you can specify different data types in your search criteria. To specify multiple columns in a search string, separate individual items in the string with semicolons.

Because search values are Variants, if you pass multiple values, you must either pass a Variant array as an argument (for example, the return values from the Lookup method), or you must construct the Variant array in code using the VarArrayOf function. The following code illustrates a search on multiple columns using multiple search values and partial-key matching:

Code: Select all

with CustTable do
  Locate('Company;Contact;Phone', VarArrayOf(['Sight Diver','P']), loPartialKey);

Code: Select all

TLocateOptions Opts;
Opts.Clear();
Opts << loPartialKey;
Variant locvalues[2];
locvalues[0] = Variant("Sight Diver");
locvalues[1] = Variant("P");
CustTable->Locate("Company;Contact", VarArrayOf(locvalues, 1), Opts);

Locate uses the fastest possible method to locate matching records. If the columns to search are indexed and the index is compatible with the search options you specify, Locate uses the index.

[AlexP]

When working with several fields, the Locate method will return True (and go to a record) only in case when the values specified for each field are found in the record. If at least one value is not found, the method will return False. Please describe the required functionality in more details.

[sde]

Ah, thank you for the clarification, Alex. I didn't realize the functionality is built to only return true if the value is found in ALL of the fields. I need it to return true if it is found in ANY of the fields, which I guess is not supported. Is there an enhancement request list where I could suggest the addition of such functionality?

Thanks,
SDE

[AlexP]

You can leave your suggestion on our uservoice page: https://devart.uservoice.com/forums/104 ... 909-common . If it gets enough user votes, we will implement it.

[mangakissa_events]

I have the same problems. I used this code to find a record:

Code: Select all

procedure foo;
begin
    fVtDagvoorraad.IndexFieldNames := 'klant;partijID;startdatum;einddatum';
    if VtDagvoorraad.Locate('klant;partijID;startdatum;einddatum',vararrayof([
         Uitslag.fklant,Uitslag.fpartijID,Uitslag.fStartdatum,Uitslag.fEinddatum]),[]) then
      begin
        // edit record
      end else
      begin
         // new record
      end;
end;

these are my data from 4 records:

0000018, 11004, 01-11-2015, 30-11-2015
0000020, 11004, 01-11-2015, 30-11-2015
0000019, 11004, 01-11-2015, 30-11-2015
0000018, 11004, 01-11-2015, 30-11-2015

The 4th record should be found, but will be append.

With TMyQuery it works fine.

So, its a bug as far as I can see.

[AlexP]

On the latest VT version, your code doesn't reproduce the issue. Please specify the field types, as well as data types Uitslag.fklant,Uitslag.fpartijID,Uitslag.fStartdatum,Uitslag.fEinddatum

[mangakissa_events]

Code: Select all

 with fVtDagvoorraad do
  begin
    AddField('klant',ftString, 10);
    AddField('artikel',ftString, 12);
    AddField('startdatum',ftDate, 0);
    AddField('einddatum',ftDate, 0);
    AddField('aantal_bollen',ftInteger, 0);
    AddField('aantal_fust',ftInteger, 0);
    AddField('aantal_dagen',ftInteger, 0);
    AddField('inkooporder',ftInteger, 0);
    AddField('leverancier',ftString, 10);
    AddField('aantal_per_fust',ftInteger, 0);
    AddField('kostenplaats',ftString, 50);
    AddField('tarief',ftBCD, 0);
    AddField('partijID',ftInteger, 0);
    AddField('verkooporder',ftInteger, 0);
    AddField('uitgeleverd',ftBoolean, 0);
    AddField('factuurdatum',ftDate, 0);
    AddField('saldo',ftBCD, 0);
  end;
   fVtDagvoorraad.IndexFieldNames := 'klant;partijID;startdatum;einddatum';

Uitslag.fklant = string
Uitslag.fpartijID = integer
Uitslag.fStartdatum = date
Uitslag.fEinddatum] = date

The TVirualtable I use comes from MyDAC 8.6.23 pro version.

[AlexP]

Below is a console sample demonstrating correct functioning of the Locate method. Please modify it, so that it reproduces the issue and send it back to us.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, VirtualTable, Data.DB, System.Variants;

var
  VT: TVirtualTable;
  s: string;
  i: integer;
  d1, d2: TDate;
begin
  VT := TVirtualTable.Create(nil);
  try
    VT.AddField('klant',ftString, 10);
    VT.AddField('artikel',ftString, 12);
    VT.AddField('startdatum',ftDate, 0);
    VT.AddField('einddatum',ftDate, 0);
    VT.AddField('aantal_bollen',ftInteger, 0);
    VT.AddField('aantal_fust',ftInteger, 0);
    VT.AddField('aantal_dagen',ftInteger, 0);
    VT.AddField('inkooporder',ftInteger, 0);
    VT.AddField('leverancier',ftString, 10);
    VT.AddField('aantal_per_fust',ftInteger, 0);
    VT.AddField('kostenplaats',ftString, 50);
    VT.AddField('tarief',ftBCD, 0);
    VT.AddField('partijID',ftInteger, 0);
    VT.AddField('verkooporder',ftInteger, 0);
    VT.AddField('uitgeleverd',ftBoolean, 0);
    VT.AddField('factuurdatum',ftDate, 0);
    VT.AddField('saldo',ftBCD, 0);
    VT.IndexFieldNames := 'klant;partijID;startdatum;einddatum';

    VT.Open;

    VT.Append;
    VT.FieldByName('klant').AsString := '0000020';
    VT.FieldByName('partijID').AsInteger := 11004;
    VT.FieldByName('startdatum').AsDateTime := StrToDate('01.11.2015');
    VT.FieldByName('einddatum').AsDateTime := StrToDate('30.11.2015');
    VT.Post;

    VT.Append;
    VT.FieldByName('klant').AsString := '0000018';
    VT.FieldByName('partijID').AsInteger := 11004;
    VT.FieldByName('startdatum').AsDateTime := StrToDate('01.11.2015');
    VT.FieldByName('einddatum').AsDateTime := StrToDate('30.11.2015');
    VT.Post;

    VT.Append;
    VT.FieldByName('klant').AsString := '0000019';
    VT.FieldByName('partijID').AsInteger := 11004;
    VT.FieldByName('startdatum').AsDateTime := StrToDate('01.11.2015');
    VT.FieldByName('einddatum').AsDateTime := StrToDate('30.11.2015');
    VT.Post;

    VT.Append;
    VT.FieldByName('klant').AsString := '0000018';
    VT.FieldByName('partijID').AsInteger := 11004;
    VT.FieldByName('startdatum').AsDateTime := StrToDate('01.11.2015');
    VT.FieldByName('einddatum').AsDateTime := StrToDate('30.11.2015');
    VT.Post;

    VT.First;
    Writeln(VT.RecNo.ToString);

    s := '0000020';
    i := 11004;
    d1 := StrToDate('01.11.2015');
    d2 := StrToDate('30.11.2015');
    VT.Locate('klant;partijID;startdatum;einddatum',vararrayof([s, i, d1, d2]), []);

    Writeln(VT.RecNo.ToString);
  finally
    VT.Free;
    Readln;
  end;
end.

P.S. The latest MyDAC version is 8.6.21

[mangakissa_events]

the result is

1
4

P.S. The latest MyDAC version is 8.6.21

Your right. My apologies.

[mangakissa_events]

I tested the console application with some modifications

Code: Select all

    VT.Open;

    Writeln('new record');
    VT.Append;
    VT.FieldByName('klant').AsString := '0000020';
    VT.FieldByName('partijID').AsInteger := 11004;
    VT.FieldByName('startdatum').AsDateTime := StrToDate('01-11-2015');
    VT.FieldByName('einddatum').AsDateTime := StrToDate('30-11-2015');
    VT.Post;

    s := '0000080';
    i := 11004;
    d1 := StrToDate('01-11-2015');
    d2 := StrToDate('30-11-2015');
    if VT.Locate('klant;partijID;startdatum;einddatum',vararrayof([s, i, d1, d2]), []) then
      Writeln('record found')
    else
    begin
      Writeln('new record');
      VT.Append;
      VT.FieldByName('klant').AsString := '0000018';
      VT.FieldByName('partijID').AsInteger := 11004;
      VT.FieldByName('startdatum').AsDateTime := StrToDate('01-11-2015');
      VT.FieldByName('einddatum').AsDateTime := StrToDate('30-11-2015');
      VT.Post;
    end;

    s := '0000019';
    i := 11004;
    d1 := StrToDate('01-11-2015');
    d2 := StrToDate('30-11-2015');
    if VT.Locate('klant;partijID;startdatum;einddatum',vararrayof([s, i, d1, d2]), []) then
      Writeln('record found')
    else
    begin
      Writeln('new record');
      VT.Append;
      VT.FieldByName('klant').AsString := '0000019';
      VT.FieldByName('partijID').AsInteger := 11004;
      VT.FieldByName('startdatum').AsDateTime := StrToDate('01-11-2015');
      VT.FieldByName('einddatum').AsDateTime := StrToDate('30-11-2015');
      VT.Post;
    end;

    s := '0000018';
    i := 11004;
    d1 := StrToDate('01-11-2015');
    d2 := StrToDate('30-11-2015');
    if VT.Locate('klant;partijID;startdatum;einddatum',vararrayof([s, i, d1, d2]), []) then
      Writeln('record found')
    else
    begin
      Writeln('new record');
      VT.Append;
      VT.FieldByName('klant').AsString := '0000018';
      VT.FieldByName('partijID').AsInteger := 11004;
      VT.FieldByName('startdatum').AsDateTime := StrToDate('01-11-2015');
      VT.FieldByName('einddatum').AsDateTime := StrToDate('30-11-2015');
      VT.Post;
    end;

    VT.First;
    Writeln(VT.RecNo.ToString);

    s := '0000020';
    i := 11004;
    d1 := StrToDate('01-11-2015');
    d2 := StrToDate('30-11-2015');
    VT.Locate('klant;partijID;startdatum;einddatum',vararrayof([s, i, d1, d2]), []);

    Writeln(VT.RecNo.ToString);

The result gave me 3 new records, because the 4th is found.
So I tested again in my GUI application with debugger and log files:

Code: Select all

procedure TKoelkosten.Save;
var myList    : TSearchableObjectList<TKoelInslag>;
    Inslag    : TKoelInslag;
    searchNr  : integer;
    Uitslag   : TKoelUitslag;
    oudnr     : integer;
begin
  Log('procedure save');
  myList := (TSearchableObjectList<TKoelInslag>(fKoelInslag));
  oudnr  := 0;
  try
    fVtDagvoorraad.IndexFieldNames := 'klant;partijID;startdatum;einddatum';
    for Uitslag in fKoeluitslag do
    begin
      searchNr  := Uitslag.fpartijID;
      if searchNr <> oudnr then
      begin
        // Let´s make it more interesting and perform a case insensitive search,
        // by comparing with SameText() instead the equality operator
        Inslag := myList.Search(function(aItem : TKoelInslag): boolean
                              begin
                                Result := aItem.fpartijID = searchNr
                              end);
      end;
      oudnr := searchNr;
      Log(format('klant %s, partijID %d, Startdatum %s, Einddatum %s',[Uitslag.fklant,Uitslag.fpartijID,DateToStr(Uitslag.fStartdatum),DateToStr(Uitslag.fEinddatum)]));
      if VtDagvoorraad.Locate('klant;partijID;startdatum;einddatum',vararrayof([
         Uitslag.fklant,Uitslag.fpartijID,Uitslag.fStartdatum,Uitslag.fEinddatum]),[]) then
      begin
        Log('toevoegen aan bestaand record');
        fVtDagvoorraad.Edit;
        fVtDagvoorraad.FieldByName('aantal_bollen').AsInteger := fVtDagvoorraad.FieldByName('aantal_bollen').AsInteger + Uitslag.faantal_bollen;
        fVtDagvoorraad.FieldByName('aantal_fust').AsInteger   := fVtDagvoorraad.FieldByName('aantal_fust').AsInteger   + Uitslag.faantal_fust;
        fVtDagvoorraad.FieldByName('saldo').AsFloat           := fVtDagvoorraad.FieldByName('saldo').AsFloat           + Uitslag.fSaldo;
        fVtDagvoorraad.Post;
      end else
      begin
        if Uitslag.faantal_dagen > 0 then
        begin
          Log('nieuw record');
          fVtDagvoorraad.Append;
          fVtDagvoorraad.FieldByName('klant').AsString            := Uitslag.fklant;
          fVtDagvoorraad.FieldByName('artikel').AsString          := Inslag.fartikel;
          fVtDagvoorraad.FieldByName('startdatum').AsDatetime     := Uitslag.fStartdatum;
          fVtDagvoorraad.FieldByName('einddatum').AsDatetime      := Uitslag.fEinddatum;
          fVtDagvoorraad.FieldByName('aantal_bollen').AsInteger   := Uitslag.faantal_bollen;
          fVtDagvoorraad.FieldByName('aantal_fust').AsInteger     := Uitslag.faantal_fust;
          fVtDagvoorraad.FieldByName('aantal_dagen').AsInteger    := Uitslag.faantal_dagen;
          fVtDagvoorraad.FieldByName('inkooporder').AsInteger     := Inslag.finkooporder;
          fVtDagvoorraad.FieldByName('leverancier').AsString      := Inslag.fleverancier;
          fVtDagvoorraad.FieldByName('aantal_per_fust').AsInteger := Inslag.faantal_per_fust;
          fVtDagvoorraad.FieldByName('kostenplaats').AsString     := Inslag.fkostenplaats;
          fVtDagvoorraad.FieldByName('tarief').AsFloat            := Inslag.ftarief;
          fVtDagvoorraad.FieldByName('partijID').AsInteger        := Inslag.fpartijID;
          fVtDagvoorraad.FieldByName('verkooporder').AsInteger    := Uitslag.fverkooporder;
          fVtDagvoorraad.FieldByName('factuurdatum').AsDatetime   := Uitslag.fFactuurdatum;
          fVtDagvoorraad.FieldByName('uitgeleverd').AsBoolean     := Uitslag.fUitgeleverd;
          fVtDagvoorraad.FieldByName('saldo').AsFloat             := Uitslag.fSaldo;
          fVtDagvoorraad.Post;
        end else
          Log('Record valt buiten aantal dagen > 0');
      end;
    end;
  finally
    Inslag := nil;
  end;
end;

This is the result:
[08-02-2016 15:05:20] [0] procedure save
[08-02-2016 15:05:25] [0] klant 0000018, partijID 11004, Startdatum 01-11-2015, Einddatum 30-11-2015
[08-02-2016 15:05:25] [0] nieuw record
[08-02-2016 15:05:25] [0] klant 0000020, partijID 11004, Startdatum 01-11-2015, Einddatum 30-11-2015
[08-02-2016 15:05:25] [0] nieuw record
[08-02-2016 15:05:25] [0] klant 0000019, partijID 11004, Startdatum 01-11-2015, Einddatum 30-11-2015
[08-02-2016 15:05:25] [0] nieuw record
[08-02-2016 15:05:25] [0] klant 0000018, partijID 11004, Startdatum 01-11-2015, Einddatum 30-11-2015
[08-02-2016 15:05:25] [0] nieuw record

I see during debugging the code there are really appended 4 records.

That's strange.

[AlexP]

Please send a complete sample to support*devart*com - and we will try to reproduce the issue once again.

[mangakissa_events]

I've created two samples. Both of them can't reproduce my problem. So I stripped the locate and first took 2 params. That's working. With 3 also. But the 4th parameter causes problems. But not in my created samples. There's something strange with 'Uitslag.einddatum'. But my log file still give me the correct date as well as the float value. I can see nothing strange.

But I already have a workaround.

Thank you for your help.

[AlexP]

You are welcome. Feel free to contact us if you have any further questions.