Solved

Filter by Forms

Posted on 1997-06-12
12
273 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now