Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

Problem with Locate

https://forums.devart.com/viewtopic.php?f=8&t=31510

Page 1 of 1

Problem with Locate

Posted: Fri 27 Mar 2015 21:27
by 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

Re: Problem with Locate

Posted: Tue 31 Mar 2015 12:50
by AlexP
Hello,

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

Re: Problem with Locate

Posted: Tue 31 Mar 2015 17:50
by 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.

Re: Problem with Locate

Posted: Wed 01 Apr 2015 07:58
by 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.

Re: Problem with Locate

Posted: Wed 01 Apr 2015 20:06
by 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

Re: Problem with Locate

Posted: Thu 02 Apr 2015 08:14
by 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.

Re: Problem with Locate

Posted: Fri 05 Feb 2016 11:47
by 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.

Re: Problem with Locate

Posted: Fri 05 Feb 2016 13:00
by 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

Re: Problem with Locate

Posted: Mon 08 Feb 2016 10:14
by 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.

Re: Problem with Locate

Posted: Mon 08 Feb 2016 11:11
by 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

Re: Problem with Locate

Posted: Mon 08 Feb 2016 13:49
by mangakissa_events
the result is

1
4

P.S. The latest MyDAC version is 8.6.21
Your right. My apologies.

Re: Problem with Locate

Posted: Mon 08 Feb 2016 14:10
by 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.

Re: Problem with Locate

Posted: Tue 09 Feb 2016 09:22
by AlexP
Please send a complete sample to support*devart*com - and we will try to reproduce the issue once again.

Re: Problem with Locate

Posted: Tue 09 Feb 2016 16:52
by 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.

Re: Problem with Locate

Posted: Wed 10 Feb 2016 06:21
by AlexP
You are welcome. Feel free to contact us if you have any further questions.
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/