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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StewCam1103Author Commented:
Please close question
0
StewCam1103Author Commented:
Provided answer to own question but aquired some useful pointers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.