Problem with Locate
Problem with Locate
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
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
Hello,
No, such behavior is not supported. You should search for data in each field manually.
No, such behavior is not supported. You should search for data in each field manually.
Re: Problem with Locate
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.":
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:
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.
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.",
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);
Re: Problem with Locate
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
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
Thanks,
SDE
Re: Problem with Locate
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.
-
- Posts: 7
- Joined: Fri 05 Feb 2016 11:38
Re: Problem with Locate
I have the same problems. I used this code to find a record:
these are my data from 4 records:
With TMyQuery it works fine.
So, its a bug as far as I can see.
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;
The 4th record should be found, but will be append.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
With TMyQuery it works fine.
So, its a bug as far as I can see.
Re: Problem with Locate
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
-
- Posts: 7
- Joined: Fri 05 Feb 2016 11:38
Re: Problem with Locate
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.fpartijID = integer
Uitslag.fStartdatum = date
Uitslag.fEinddatum] = date
The TVirualtable I use comes from MyDAC 8.6.23 pro version.
Re: Problem with Locate
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.
P.S. The latest MyDAC version is 8.6.21
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
-
- Posts: 7
- Joined: Fri 05 Feb 2016 11:38
Re: Problem with Locate
the result is
1
4
1
4
Your right. My apologies.P.S. The latest MyDAC version is 8.6.21
-
- Posts: 7
- Joined: Fri 05 Feb 2016 11:38
Re: Problem with Locate
I tested the console application with some modifications
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:
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.
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);
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;
[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
Please send a complete sample to support*devart*com - and we will try to reproduce the issue once again.
-
- Posts: 7
- Joined: Fri 05 Feb 2016 11:38
Re: Problem with Locate
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.
But I already have a workaround.
Thank you for your help.
Re: Problem with Locate
You are welcome. Feel free to contact us if you have any further questions.