Filter by Forms

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.
Who is Participating?

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

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.

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.

d4jaj1Author Commented:
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.
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.

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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.

d4jaj1Author Commented:
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.
d4jaj1Author Commented:
Any answer with this one yet?
d4jaj1Author Commented:
I'll assume either you don't have the time to answer this or you don't know the answer.
d4jaj1Author Commented:
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.

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  (
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.
d4jaj1Author Commented:
This question is reserved for ZifNab
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;

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

From novice to tech pro — start learning today.