Search on two fields in different tables

I have an app in which users can browse a data grid of items. A richedit control (RE1) is used to show fields from the current item. The main field is the item description ('Description') which is stored in a memory table (kbmMemTable MT1). Users can add a comment ('Comment') which is stored in another memory table (MT2).  Users can also search on item description - the search term is then highlighted in RE1. I would like to enable a simultaneous search on both item description and user comment but can't work out how to do this. So far I am using the following code, which searches description only:
MT1.AddIndex('ItmIndex1','ItmNum1',[ixPrimary]);
MT1.AddFilteredIndex('FindIndex1','Description',[],'',[]);
MT1.OnFilterIndex := FindIndex;

procedure TFormMain.FindIndex(DataSet: TDataSet; Index: TkbmIndex;
  var Accept: Boolean);
var
  Input, FinePrint: String;
begin
  Input := Trim(FindDlg.ComboFind.Text);
  Fineprint := Dataset['Description'];
  Accept := (MT1.IndexName = 'ItmIndex1') or AnsiContainsStr(FinePrint,Input);
end;

procedure TFormMain.FindUserText (var FindString: String);
begin
  MT1.CancelRange;
  MT1.Filtered := False;
  MT1.IndexName := 'FindIndex1';
  MT1.Indexes.ReBuild('FindIndex1');
  MT1.SortOn('ItmNum1',[]);
  MT1.First;
end;

Open in new window

StewCam1103Asked:
Who is Participating?
 
StewCam1103Author Commented:
I have now solved my original question. I created a lookup field Comment1 in MTI based on the Comment field in MT2. I used this to modify my original code like this:
Input := Trim(FindDlg.ComboFind.Text);
FinePrint := DataSet['Description'] + ' ' + DataSet['Comment1']
Accept := AnsiContainsStr(FinePrint,Input);

Open in new window

Thanks, ewangoya for your contributions. They didn't enable me to answer the question but I am looking to modify some of my other code based on your suggestions.
0
 
Ephraim WangoyaCommented:

You are making it very complicated. You could simply use the filter.

How is the second table related to the first table, I assume you have a foreign key on the comment table which I'll refer to as ID


function FindText(const FindString: String);
begin
  MT1.DisableControls;
  MT1.CancelRange;
  MT1.Filtered := False;
  MT1.Filter := 'Description LIKE' + QuotedStr('%' + FindString + '%');
  MT1.Filtered := True;
  Result := not MT1.IsEmpty;
  MT1.EnableControls;
end;

//to always synchronize with the comments table, add a AfterScroll event to MT1/ or datasource DataChange
procedure TForm1.MT1AfterScroll(DataSet: TDataSet);
var
  KeyID: variant;
begin
  KeyID := Dataset.FieldByName('ID').AsInteger;
  MT2.DisableControls;
  MT2.Filtered := False;
  MT2.Filter := 'ID=' + QuotedStr(KeyID);
  MT2.Filtered := True; 
end;

Open in new window

0
 
Ephraim WangoyaCommented:

Or simply set a Master-Child relationship between the datasets, then you don't even have to write any code

MT2.MasterSource := dsMT1;  
MT2.IndexName := set the appropriate index
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
StewCam1103Author Commented:
Thanks for your replies but I am unable to get the suggestions to work. I have replaced my FindUserText function with yours as per attached code. However, when I call:

FindUserText2(FindString);

I just get an empty grid.

I have a foreign key called ItmNum2 on MT2, so have added the second part of your comment to the DS1 datachange procedure as per attached code.
function TFormMain.FindUserText(const FindString: String): Boolean;
begin
  MT1.DisableControls;
  MT1.CancelRange;
  MT1.Filtered := False;
  MT1.Filter := 'Description LIKE' + QuotedStr('%' + FindString + '%');
  MT1.Filtered := True;
  Result := not dm.MT1.IsEmpty;
  MT1.EnableControls;
end;

procedure TFormMain.DS1DataChange(Sender: TObject; Field: TField);
var
  ItmNum3: variant;
begin
  ItmNum3 := Dataset.FieldByName('ItmNum1').AsInteger;
  MT3.DisableControls;
  MT3.Filtered := False;
  MT3.Filter := 'ItmNum1=' + QuotedStr(ItmNum3);
  MT3.Filtered := True;
end;

Open in new window

0
 
StewCam1103Author Commented:
Sorry, previous entry should have read:

FindUserText(FindString)
0
 
Ephraim WangoyaCommented:

Sorry, an oversite from my side

The wild card character for TkbmMemTable is '*'

Then you have to set the FilterOptions, set both options to true
  MT1.FilterOptions.foCaseInsensitive := True
  MT1.FilterOptions.foNoPartialCompare := True

You can set this from the properties at design time

The code becomes
function TFormMain.FindUserText(const FindString: String): Boolean;
begin
  MT1.DisableControls;
  MT1.CancelRange;
  MT1.Filtered := False;
  MT1.Filter := 'Description LIKE ' + QuotedStr('*' + FindString + '*');
  MT1.Filtered := True;
  Result := not dm.MT1.IsEmpty;
  MT1.EnableControls;
end;

procedure TFormMain.DS1DataChange(Sender: TObject; Field: TField);
var
  ItmNum3: variant;
begin
  ItmNum3 := Dataset.FieldByName('ItmNum1').AsInteger;
  MT3.DisableControls;
  MT3.Filtered := False;
  MT3.Filter := 'ItmNum1=' + QuotedStr(ItmNum3);
  MT3.Filtered := True;
end;

Open in new window

0
 
Ephraim WangoyaCommented:
I prefer setting the MasterSource property for MT3 instead of using the DataChange
DataChange is called too many times
0
 
StewCam1103Author Commented:
Thanks again. I have got your search code working as shown below. However, it seems somewhat slower than the search code I started with in my initial question - perhaps because I was using a filtered index. Also it doesn't seem to help me with the thing that I am really trying to achieve, which is to search across both item description in MT1 and item comment in MT3 at the same time.

function TFormMain.FindUserText(const FindString: String): Boolean; 
begin 
  with MT1 do 
  begin 
    DisableControls; 
    CancelRange; 
    Filtered := False; 
    FilterOptions := [foCaseInsensitive, foNoPartialCompare]; 
    Filter := 'Description LIKE' + QuotedStr('*' + FindString + '*'); 
    Filtered := True; 
    Result := not IsEmpty; 
    EnableControls; 
  end; 
end;

Open in new window


As regards your second suggestion, to use the MasterSource property for MT3 instead of using DS1DataChange, I have been unable to implement this. I have a primary index ItmIndex1 on MT1, as defined by:

dm.MT1.AddIndex('ItmIndex1','ItmNum1',[ixPrimary]);

However, the number of records in MT3 (item comment) is much less than the number of records in MT1 (item description). Up till now I have been using the following code to synchronize the tables, which looks awkward but works well.

procedure TFormMain.DS1DataChange(Sender: TObject; Field: TField);
begin
  with MT3 do
  begin
    if Locate('ItmNum3',Fields[0].AsInteger,[]) = True then
      Locate('ItmNum3',Fields[0].AsInteger,[])
    else
    begin
      Insert;
      Fields[0].AsInteger := dm.MT1.Fields[0].AsInteger;
    end;
  end;
end;

Open in new window

0
 
Ephraim WangoyaCommented:

The search works slower because its using wildcards, it does not use indexes

Here is a an example of how to link the tables
This way, whichever method you use to search, the comments table will always be synchronized with the current record
Setup-Master-source.png
Unit1.dfm
Unit1.pas
0
 
StewCam1103Author Commented:
Please close question
0
 
StewCam1103Author Commented:
Provided answer to own question but aquired some useful pointers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.