Filter a Table

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

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;

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;

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;

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.

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.

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

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?

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;

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.

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)

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

This question is reserved for ZifNab

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