Solved

Filter a Table

Posted on 1997-07-10
15
410 Views
Last Modified: 2010-05-18
I want to filter a table on a form.  I have 5 text boxes that represent five fields in the table.

City, Event, Cost, Cuisine and Hours.

I want to filter the table on ALL of these values at the same time.  I already know I should use the onFilterRecord event to look at each record, my question is how?  If I use the code below, Delphi aplies each filter separtely, thus only the last filter is seen.

accept := tblActivities['City'] = frmFilter.fldCity.text;
accept := tblActivities[Event'] = frmFilter.fldCity.text;
accept := tblActivities['Cost'] = frmFilter.fldCity.text;
etc..

What should I do to filter the table on ALL of these values?  Would I have to do something different if one of the fields were blank?
0
Comment
Question by:d4jaj1
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 1

Expert Comment

by:Zonnald
ID: 1338592
d4jaj1
I have been using the setrange and assigning values to the fields then using the applyrange as such
      with tblActivites do
      begin
         DisableControls;
         try
            SetRangeStart;
            FieldbyName('City').AsString  := frmFilter.fldCity.text;
            FieldbyName('Event').AsString  := frmFilter.fldEvent.text;
            FieldbyName('Cost').AsString  := frmFilter.fldCost.text;
            setRangeEnd;
            FieldbyName('City').AsString  := frmFilter.fldCity.text;
            FieldbyName('Event').AsString  := frmFilter.fldEvent.text;
            FieldbyName('Cost').AsString  := frmFilter.fldCost.text;
            KeyExclusive := True;
            ApplyRange;
         Finally
             EnableControls;
         end;
Sometime you may have to set another field with an actual range  on it so that it can give you all the records in the range i.e.
fieldbyname('Stree').asstring := 'Aaaaaaa';
and
fieldbyname('Stree').asstring := 'Zzzzzzzz';
Try this and see how you go

See you

Zonnald

0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1338593
I keep getting an error stating "  " when I know and I have checked through the Database Desktop that the indexes exist.  Why is this happening?  I have included my code below.

procedure TfrmCriteria.btnResultsClick(Sender: TObject);
begin
Application.CreateForm(TfrmResults, frmResults);
with frmresults.tblActivities do
begin
frmresults.tblActivities.DisableControls;
try
   SetRangeStart;
      if fldAFood.text <> '' then frmResults.tblActivities.FieldbyName('CuisineType').AsString := fldAFood.text;
      if fldEntertainment.text <> '' then frmResults.tblActivities.FieldbyName('EntertainmentType').AsString := fldEntertainment.text;
   setRangeEnd;
      if fldAFood.text <> '' then frmResults.tblActivities.FieldbyName('CuisineType').AsString := fldAFood.text;
      if fldEntertainment.text <> '' then frmResults.tblActivities.FieldbyName('EntertainmentType').AsString := fldEntertainment.text;
      KeyExclusive := True;
   ApplyRange;
Finally
   EnableControls;
   frmresults.show;
end;
end;
end;
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1338594
I keep getting an error stating "...rasied an exception with class EDatabaseError with message 'Field 'EntertainmentType' is not indexed and cannot be modified " when I know it is and I have checked through the Database Desktop that the indexes exist.  This is happening on ALL fields.  Why is this happening?  I have included my code below.

procedure TfrmCriteria.btnResultsClick(Sender: TObject);
begin
Application.CreateForm(TfrmResults, frmResults);
with frmresults.tblActivities do
begin
frmresults.tblActivities.DisableControls;
try
   SetRangeStart;
      if fldAFood.text <> '' then frmResults.tblActivities.FieldbyName('CuisineType').AsString := fldAFood.text;
      if fldEntertainment.text <> '' then frmResults.tblActivities.FieldbyName('EntertainmentType').AsString := fldEntertainment.text;
   setRangeEnd;
      if fldAFood.text <> '' then frmResults.tblActivities.FieldbyName('CuisineType').AsString := fldAFood.text;
      if fldEntertainment.text <> '' then frmResults.tblActivities.FieldbyName('EntertainmentType').AsString := fldEntertainment.text;
      KeyExclusive := True;
   ApplyRange;
Finally
   EnableControls;
   frmresults.show;
end;
end;
end;
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1338595
Not sure that I understood you, but try this one:
If you want to perform an action on all records that match at list one of the criterias indicated above do these steps
:
procedure TForm1.Button1Click(Sender: TObject);
begin
with tblActivitie do
begin
Filter:='(City='+fldCity.text+') OR (event='+fldCity.text +') OR (Cost='+fldCity.text+')';
//that is (City='Paris') OR (Event='Paris') OR (Cost='Paris')
Filtered:=True;
//when setting filtered to true, all actions you declare are performed only on these records
//do some action
.
.
Filtered:=False; //return to a full table display
end;
end;
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1338596
Finally got D2 re-installed.  Actually filtering a table using OR logic is NOT what I want to do.  What I really need is the ability to filter or query a table based on data entered on another form.  This is really an 'AND' operation, not 'OR' because the record would have to meet all of the criteria (non-blank data entered on the calling form) to pass the filter/query.

I notice you added comments to my 'How to Use TQuery' question and your statement these questions are related is correct. I re-asked my question because the answers I have received thus far are either incorrect, vague or don't answer the question I asked.  I simply want some one to explain to me how I can limit the records shown on a table, based on NON BLANK values in another forms controls.  I don't really care if its a query or a filter, I just want to limited the view to data entered on the calling form.

I could modify your answer to reflect AND logic instead or OR, but that code would not take into account blank fields in the calling form.  If I write IF statements to check if the value is blank first, Delphi applies each filter separately.

If my question still isn't clear, I'd be happy to send you an e-mail with more details, although my question seems simple enough to me.  You can answer my question here or the other question, but I'm going to give them both the same grade.
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1338597
Sorry, right now I don't have the time to answer your question.
I suggest you reject my previous answer in order to let others to post an answer.
0
 

Expert Comment

by:edye
ID: 1338598
d4jaj1,

- Open a new application / project
- Place a TTable, TDBGrid, TDataSource,  2 X TEdit boxes and a TButton on the form.
- Connect the TTable to DBDEMOS and set the Table name to Animals.DBF
- Connect the datasorce to the table and the grid to the datasource
- Set the active property on the table to TRUE. At this point you should see all the records from the table in the dbgrid
- Place the following code in the OnClick event for the button:

     //Name, Size
     Table1.Filter:= 'Name = ''' + Edit1.Text + '*'' AND Size = ' + Edit2.Text;
     Table1.Filtered := TRUE;

What this does is take the values entered into the edit boxes and use them as a filter. Edit1 applies a filter on the name in the form : Match the first letter and don't worry about the rest
Edit2 Applies a filter on the size field.

- To see this work type in some values in the Edit boxes and you will see that the table shows only the matching records when the button is clicked. (You might want to change the names of a few things to start with the same letter. Eg I changed House Cat to PHouse Cat as I wanted to find all the matches with name starting with P and a size of 5. This gave me PHouse Cat and Parrot.

I hope this helps.....

--Donovan

PS. As for the blank entries. Simply use intelligent logic to build the filter....
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Author Comment

by:d4jaj1
ID: 1338599
Donovan,

Sorry for the delay. As you can see from my original question and you suggestion, there are many ways to filter a table.  The filter part is what I have a pretty good understanding of. The problem is the 'Intellegent Logic' you speak of.  I can look in Help and figure out how to filter on one field.  My problem is filtering the table on multiple field values (AND operations) - only if they are not blank.  With your example, I would have to explicited assign all of the values from my code, but I couldn't account for blank entries.  Any suggestions?
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1338600
Hi d4jaj1!!!!!!

I think I found a solution!!!!

Here it comes, let me know as soon as possible!!!

In Edit1.text user has to put a number
In Edit2.text user has to put a string.

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
with Table1 do begin
 if Edit1.text <> '' then Filter := '([CustNo] = '+Edit1.text+')';
 if Edit2.text <> '' then
  Filter := Filter + ' AND ([Company] = '+'''Edit2.text'''+')';
 Filtered := True;
end;
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1338601
ZifNab,

Close & I though of that, but I really have about 20 fields I need to search on - I only listed 5 for the easy of this question.  Using this option, I'd have to write the Accept for the previous Accept 20 times - and I'd still run into problems if the blanks occurred in the middle of the group of accepts string - like say at the 9th accept clause.

My background is in Paradox development, and one of the great thing I could do is create a DynArray (a Dynamic Array) that allowed me to use Strings as the pointer and another String as the value.  I would filter a table using the first string as the Table's field name and the second as the filter value.  This easily allowed me to only filter the table on non-blank values by only adding values to the Array that weren't blank.  Apparently, Delphi doesn't allow Strings as pointers.  If someone could tell me a way around this string limitation and let me know how to link the array to the onFilterevent, my app would finally be complete.

You didn't offend me in the other question, I'm just frustrated I can't complete this piece of my app.  Once I figure this out, I'm be finished.  Would you be willing to look at the screens I'm talking about.  I'm certainly not asking you to code my program for me, but I'm thinking if you take a look at what I'm trying to do, it would be easier for you to understand what I'm looking for.  I can send it on email.

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1338602
Well sorry, I really don't get it anymore.

Maybe it's too late, I don't know.

One thing I don't get :

 you are saying that you want to fill an array with the fields that aren't blank. -> Then you've also got to test 20 times if the value is blank. Just as in my code, you have to test 20 times and that's it.

see :

 if City = '' then Filter := '([City] = '+Edit1.text+')';
 if Event = '' then Filter := Filter+'AND([Event] =                               '+Edit2.text+')';
 if Cost = '' then Filter := Filter+'AND([Cost] =                               '+Edit2.text+')';  

 ....

 Suppose user enters Paris, 100

 then filter will look like ([City] = Paris) AND ([Cost] = 100)

 Suppose user enters Paris

 -> ([City] = Paris)



0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1338603
Well, it's late...

Know what. If you send me the code of your form with all edits and that button. I'll look at it and if my suggestion works I'll send it you back, written with my code. Then you paste it in your form and look at it.

But you have to send it right away otherwise I'm going home, because I'm almost falling a sleep here, but I really want to help you with this. And my vacation begins tomorrow, so I'd like to help you now.

Tom.Deprez@uz.kuleuven.ac.be


0
 
LVL 3

Author Comment

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

Expert Comment

by:ZifNab
ID: 1338605
Hi d4jaj1,

I'm back and yes I had a great vacation. Glad the example I sended you worked. Well, I don't have the code anymore i've sended you, so if you still have it please send it to me, than i can give it as an answer. Otherwise I've to make it all over again.

Have fun,
c.u. ZifNab;

I'll see if I can help you with the other questions, but first I have to do something on the overloaded in-workbox

0
 
LVL 8

Accepted Solution

by:
ZifNab earned 50 total points
ID: 1338606
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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

12 Experts available now in Live!

Get 1:1 Help Now