Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Filter by Forms

Posted on 1997-06-12
12
Medium Priority
?
288 Views
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.
0
Comment
Question by:d4jaj1
  • 6
  • 5
12 Comments
 
LVL 2

Expert Comment

by:icampbe1
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.

0
 
LVL 3

Author Comment

by:d4jaj1
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.
0
 
LVL 2

Expert Comment

by:icampbe1
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);
BEGIN
   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}

END;

Hope this helps....

Ian C.

0
Technology Partners: 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!

 
LVL 2

Expert Comment

by:icampbe1
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.

0
 
LVL 3

Author Comment

by:d4jaj1
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'] =    
frmFilter.fldEntertainment.text;

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.
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1336996
Any answer with this one yet?
0
 
LVL 3

Author Comment

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

Author Comment

by:d4jaj1
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
else
    Darray["City"] = cty
endif

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.

0
 
LVL 2

Expert Comment

by:icampbe1
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.

Use:  

  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  (iancamp@interlog.com)
0
 
LVL 2

Expert Comment

by:icampbe1
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.
Ian.
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1337001
This question is reserved for ZifNab
0
 
LVL 8

Accepted Solution

by:
ZifNab earned 200 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
 begin
  {Prepare filter}
  Filter := '';
  if fldACity.text <> '' then AString :=
       'AND([City]='''+fldACity.text+''')';
  if fldEType.text <> '' then AString := AString +
       'AND([EventType]='''+fldEType.text+''')';
  if fldCuisine.text <> '' then AString := AString +
       'AND([CuisineType]='''+fldCuisine.text+''')';
  if fldHCost.text <> '' then AString := AString +
       'AND([HighCost]<='''+fldHCost.text+''')';
  if fldLCost.text <> '' then AString := AString +
       'AND([LowCost]>='''+fldLCost.text+''')';
  Delete(AString,1,3);
  showmessage(AString);
  frmResults.tblActivity.Filter := AString;
  frmResults.tblActivity.Filtered := true;
 end;


0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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 Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

916 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