Problem with Locate

Problem with Locate

Postby sde » Fri 27 Mar 2015 21:27

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
sde
 
Posts: 10
Joined: Thu 26 Mar 2015 02:02

Re: Problem with Locate

Postby AlexP » Tue 31 Mar 2015 12:50

Hello,

No, such behavior is not supported. You should search for data in each field manually.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Problem with Locate

Postby sde » Tue 31 Mar 2015 17:50

That's unfortunate.

But why does the documentation indicate that you can? (Copied from http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/devwin32/5datasetusinglocate_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.
sde
 
Posts: 10
Joined: Thu 26 Mar 2015 02:02

Re: Problem with Locate

Postby AlexP » Wed 01 Apr 2015 07:58

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.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Problem with Locate

Postby sde » Wed 01 Apr 2015 20:06

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
sde
 
Posts: 10
Joined: Thu 26 Mar 2015 02:02

Re: Problem with Locate

Postby AlexP » Thu 02 Apr 2015 08:14

You can leave your suggestion on our uservoice page: https://devart.uservoice.com/forums/104635-delphi-data-access-components/category/18909-common . If it gets enough user votes, we will implement it.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Problem with Locate

Postby mangakissa_events » Fri 05 Feb 2016 11:47

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.
mangakissa_events
 
Posts: 7
Joined: Fri 05 Feb 2016 11:38

Re: Problem with Locate

Postby AlexP » Fri 05 Feb 2016 13:00

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
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Problem with Locate

Postby mangakissa_events » Mon 08 Feb 2016 10:14

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.
mangakissa_events
 
Posts: 7
Joined: Fri 05 Feb 2016 11:38

Re: Problem with Locate

Postby AlexP » Mon 08 Feb 2016 11:11

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
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Problem with Locate

Postby mangakissa_events » Mon 08 Feb 2016 13:49

the result is

1
4


P.S. The latest MyDAC version is 8.6.21

Your right. My apologies.
mangakissa_events
 
Posts: 7
Joined: Fri 05 Feb 2016 11:38

Re: Problem with Locate

Postby mangakissa_events » Mon 08 Feb 2016 14:10

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.
mangakissa_events
 
Posts: 7
Joined: Fri 05 Feb 2016 11:38

Re: Problem with Locate

Postby AlexP » Tue 09 Feb 2016 09:22

Please send a complete sample to support*devart*com - and we will try to reproduce the issue once again.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Problem with Locate

Postby mangakissa_events » Tue 09 Feb 2016 16:52

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.
mangakissa_events
 
Posts: 7
Joined: Fri 05 Feb 2016 11:38

Re: Problem with Locate

Postby AlexP » Wed 10 Feb 2016 06:21

You are welcome. Feel free to contact us if you have any further questions.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35


Return to VirtualDAC