Filter by Forms

Posted on 1997-06-12
Last Modified: 2010-04-06
I have two forms, one the Criteria Form and the other the Results Form.  The Criteria Form's fields (text, combo or checkbox, but not hooked to a table) equate to fields in the Criteria form's Activity.db. Based on entries in several fields in the Criteria Form, I want to filter the table values in the Results Form. However, I only want to filter the Results form's Activity.db on values in the Criteria Form if they're NOT blank, i.e., I only want to filter the Results Activity.db on fields that have data in the Criteria Form.  I am NOT using a datamodule.  

For example,  The Criteria Form has fields 5 fields - City, Entertainment Type, Event Type and High and Low Cost, each having corresponding fields the Activity.db on the Results form.  If a user only entered data in 3 of the fields, how do I filter the Activity table using their corresponding fields, on those values only?  I don't want Delphi to search for the Criteria Form's City.text entry in the Results Form's Activity.db High Cost field.
Question by:d4jaj1
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5

Expert Comment

ID: 1336991
This isn't too difficult.  You dont need a data module.  I assume that you have a datasource on the results form that hooks to a ttable that hooks to the activity.db??

You will need indexes for each field you wish to filter on.   You can use the indexname property of the dset (or even better, IndexFieldNames) in combination with the onFilterRecord.  OnFileterRecord is really neat cause yo get to veto the results base upon any criteria you want (including controls on a form).  The IndexFieldNames speaks for itself.  You set the index (or indexes) based upon fields of your choice.

As for the blank field, you can always subclass the ttable and create equivalent properties that ignore the blank fields.

If you need more, let me know.

Ian C.


Author Comment

ID: 1336992
I'm sorry I wasn't clear-er with my question.  I realize I will need to use the onFilterRecord event and the indexes of the Activity.db (yes there is a datamodule).  What I want to know from you guys is HOW.  What is the CODE I should write.  I don't need for you to write the program for me, but if you could tell me how to filter the table on three fields (one of which is blank) I can easiliy build the rest of my application from your example.  Thanks.

Expert Comment

ID: 1336993
OKeeeDokee..  The OnFilterRecord is an opportunity for you to accept or reject a record in its appearance to your data aware components.  I looks like  PROCEDURE xxFilterRecord( Dset: TDataset; VAR Accept: Boolean);

I use one like this:
PROCEDURE xxFilterRecord( Dset: TDataset; VAR Accept: Boolean);
   Accept := TRUE;    {Assume record will get through}

  IF NOT SomeForm.Checkbox.Checked THEN
   IF ActivityTableCustno.AsString  <> '12345' THEN
     Accept := FALSE;    {Only allow custno 12345 if checkbix is clear}

  IF Form1.Edit1.Text = ActivityTableCustName.AsString  THEN
   Accept := FALSE;   {Eliminate Whatever is in Edit1}

{ any other tests go here.   Remember to set filtered to TRUE}
{This is just a simple example}


Hope this helps....

Ian C.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 1336994
One more thought.... Don't get confused betwween index and filter.  The indexing can be viewed as a sorting of sorts.  The filter is just as its name implies.  

Filtering can also be done effectively using the SetRangeStart and SetRangeEnd methods.  This establishes a set of upper and lower bounds (the filter record occurs in addition to this) that remain till you CancelRange.

Ian C.


Author Comment

ID: 1336995
Sorry for the delay!!  I've een trying to upgrade to Delphi 3 without success (some of my old components don't work in 3).  I finally found the Delphi 2 CD and re-installed it.  I appreciate your code example above - I always give excellent scores when I grade.  I do have a question about your answer though;  since I want to accept or reject a combination of field entries, placing multiple accept statements don't work.  For example;

If frmFilter.fldCity.text <> ' ' then
    accept := tblActivities['City'] = frmFilter.fldCity.text;
If frmFilter.fldEntertainment.text <> ' ' then
    accept := tblActivities['EntertainmentType'] = frmFilter.                    fldEntertainment.text;

actually filters the table TWICE, i.e., only the second filter result is current.  I know I could use this code;

If frmFilter.fldCity.text <> ' ' then
    accept := tblActivities['City'] = frmFilter.fldCity.text;
    accept := accept and tblActivities['EntertainmentType'] =    

to correct the above problem, but remember - I have 14 different fields.  It doesn't seem take into acount the second IF statement and doesn't make since to continue expanding the 'accept' statement.  

Any easy way to do this on multiple fields?  I used to do this in Paradox using Dynamic Arrays.

Author Comment

ID: 1336996
Any answer with this one yet?

Author Comment

ID: 1336997
I'll assume either you don't have the time to answer this or you don't know the answer.

Author Comment

ID: 1336998
I am trying to convert my Paradox application to Delphi 2.  In Paradox, I pass values from text boxes from a form to a Dynarray, then filter a table on the Dynarray values.  A code sample from Paradox is below;

if form1.City.isBlank() then
    Darray["City"] = cty

This ensures only non-blanks are passed to the array, then uses the table field "City" as the index for the variable 'cty' (which is what ever is in form1's City field.)

The question is, HOW do I pass variables from one form to filter another form's tables, using an array of sorts to 1) mimic the table's filed names 2) contain the variable to filter on 3) filter the table?  According to the Help file, you cannot pass strings in arrays. I know I should use the onFilterEvent in the resulting table, but I can't figure out how to filter on several values at the same time.  For example, the code below;

Accept := TRUE; {Assume record will get through}

IF form1.text1 <> '' then
   Accept := tblActivities['City'] = form1.text1.text;

IF form1.text2 <> '' then
   Accept := tblActivities['Event'] = form1.text2.text;

      And so on...

Actually filters the table TWICE.  How do I accomplish what I did in Paradox using Delphi?  I really need this solved like yesterday. Code examples would be best.


Expert Comment

ID: 1336999
I'm back.... sorry I was gone for a while.

OK,  The idea is to set Accept to FALSE;  It starts out being TRUE.
Don't use  Accept := (a-b);  kind of syntax, because you could set Accept back to true if it was false already.


  IF a=b THEN Accept := FALSE;
  IF c=d THEN Accept := FALSE;
  {Always try to set it FALSE.   IF your are worried about unnecessary }
  {tests, check Accept first}

  IF Accept THEN   {Accept is still true}
     IF a=b THEN Accept := FALSE:
  IF Accept THEN  { Accept is still TRUE}
    IF c=f THEN Accept := FALSE:

Now, if you don't mind being a little unstructured, You can start by setting Accept to FALSE and EXIT Whenever a filter fails.  Set It to TRUE if it makes it all the way through.

Accept := FALSE;
IF a=b THEN Exit;
IF c=d THEN Exit;
IF (Sender AS TTable).FieldByName('Orders').AsText = 'ABC' THEN Exit;
Accept := TRUE;

The above code exits as soon as it fails the test.  No unnecessary testing.  I just don't like the structure (but thats just me).

The filter record proc is only called when a record is referenced.  If you have a grid (with 5 records) on a datasource against a TTable,  then you will be call until five records finally qualify.

If you need more, I'll be more than glad to help till it works.  
Ian Campbell  (

Expert Comment

ID: 1337000
More..  you can make as many tests as you want.  Just because you made lets say 10 tests, doesn't mean you filtered 10 times.  You still only filtered once.  Look at my second example using the EXIT method.  You might prefer that if you find it clearer.

Author Comment

ID: 1337001
This question is reserved for ZifNab

Accepted Solution

ZifNab earned 100 total points
ID: 1337002
Hi d4jaj1,

Here are the results, 10x for sending it back and 10x for the code of your query (it was learnfully)!

with frmResults.tblActivity do
  {Prepare filter}
  Filter := '';
  if fldACity.text <> '' then AString :=
  if fldEType.text <> '' then AString := AString +
  if fldCuisine.text <> '' then AString := AString +
  if fldHCost.text <> '' then AString := AString +
  if fldLCost.text <> '' then AString := AString +
  frmResults.tblActivity.Filter := AString;
  frmResults.tblActivity.Filtered := true;


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question