Filtering on a boolean field. lazarus / 16-Databases ---------------------------------------------------------------------- Hopestation · 2016-01-16 12:47 Hi. I am using a .dbf database with Tdbf. I originally had a Character field which contained either "Y" or nothing. I used a filter: Field = "Y", or Field <> "Y". These located all the records with or without "Y" in the field. I decided to change this field to a logic field and changed the "Y" to "TRUE" in each record. I cannot find the correct formula for the logical field. I tried Field = "TRUE" but this gives an error message:     "Index base on unknown field  "="." I have tried Field "True" without success. What is the correct way to filter a Boolean field? Thanks ---------------------------------------------------------------------- teos · 2016-01-16 21:08 Have you tried field = true ? ---------------------------------------------------------------------- Hopestation · 2016-01-17 11:27 Thanks for your reply. Field = true results in the message:     Index based on unknown field "true" I changed the field from character to logical in the .dbf file, using OpenOffice SCalc then opened the Database in my program. The Letter "Y" in the DBGrid field column was automatically changed to a tick box, so I assumed that I did not have to make any changes in the DBGrid. Was this a mistake? ---------------------------------------------------------------------- teos · 2016-01-17 12:45 TDBF seems to follow a default approach for filtering: Filtering TDBF: filter = fieldname_here shows the records where the boolean is true, filter = not fieldname_here shows the recors where the boolean is false. fieldname_here offcourse is your fieldname. It seems to be a very default approach (Delphi TClientdataset does it this way too) but unmaintainable. Works like a charm tough. ---------------------------------------------------------------------- Hopestation · 2016-01-17 13:16 Thanks teos. Brilliant! ---------------------------------------------------------------------- Hopestation · 2016-01-17 15:26 Sorry. I spoke too soon. Using the Object Inspector in design mode I can activate the database and then set the filter to "MyField" and the DBGrid shows all the records with the Myfield box ticked. If I set the filter to "not MyField" the DBGrid shows all the records without the Myfield box ticked. So I thought the problem was fixed, but it's not. When I compile the program with the filter set to MyField the database loads correctly, but if I set the filter to Not MyField with the filter on or off when the database loads I get the message : Project raise exception class 'EDbfError' with message: "Index based on unknown field "NOT". I deleted the filter and put these lines in FormCreate:   Form.Dbf.Filtered := True;   Form.Dbf.Filter := 'MyField'; The database loaded correctly, with only the checked records displayed. Then I changed the code to:   Form.Dbf.Filter := 'not MyField'; and tried again. When the database loaded I got the same error message. If I choose "Continue" the database is loaded and the filter setting is ignored. Is this a bug in TDBF? ---------------------------------------------------------------------- teos · 2016-01-17 16:42 I can't reproduce your errors in the way you describe them. My test is a TDBF component with 2 fields, no indexes. I would however recommend to set the filter before the filtered property. But I wonder what happens if you remove the code from formcreate to formshow or in the onclick of a button or in the afteropen of the TDBF component. I don't know how in which stadium of the form the event is triggered but I would not be suprised if that is in the creation or opening of the database. May be worth the trouble to test. ---------------------------------------------------------------------- Jurassic Pork · 2016-01-17 16:49 hello, you can try to use onFilterRecord event instead of Filter property. Something like this  : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TMainForm.Dbf1FilterRecord(DataSet: TDataSet; var Accept: Boolean);begin    Accept := False;   IF Dataset.FieldByName('myBooleanField').AsBoolean  then Accept := true; end; ---------------------------------------------------------------------- teos · 2016-01-17 17:01 The combination of filter and onfilterrecord also works. But all of them require the "filtered" property to be set to true. But I think most helpfull would be a suggestion why the filter string gives this error. Even when your solution is better, I would like to learn why the error occurs. Besides that: The filter string shows in designtime that the filter works as expected. ---------------------------------------------------------------------- Hopestation · 2016-01-17 17:08 Thanks for both your answers. I'll start a new project with a simple database and DBGrid and see if the problem persists. ---------------------------------------------------------------------- Hopestation · 2016-01-17 17:41 I've started a new project with a new database containing two fields: Name, 20 characters and Checked, Logical. It contains two records, Name1, Checked and Name2, unchecked. The Form has a DBGrid and three buttons. The first, "Checked", sets the filter to 'Checked'. The second, "Clear", sets the filter to 'not checked'. The third, "All", sets the filter to ''. Each button works correctly, showing the checked record, the unchecked record and both records. The problem must be in my program. Thanks for your help. ---------------------------------------------------------------------- teos · 2016-01-17 19:46 Does the problem persist if you move your code from oncreate to onshow? Onshow is much later, so the dataset is opened in time. (if that helps). another thing: if you use the same buttons in your program, does that help? I'm very curious to learn how you solved this. ---------------------------------------------------------------------- Hopestation · 2016-01-18 09:28 Hi. I went back to my original program and removed the Filter statements from the FormShow procedure and used the original program. I have a button on my main form which shows a new form modally. This contains a drop-down box of Fields, a drop-down box of conditions and a text box for the comparisons. In the Conditions box there are "is TRUE" and "is False". If one of these is selected a string is set to "myField" or "not myField" When I click a Find button on the form the filter is set to the string and the form is closed. I have cleared the filter in the Object Inspector and the program is now working correctly. I am still unsure why putting Not in the fillter at design time causes a problem, but having cleared it the problem has gone, so I am happy to leave it there. Thanks for your help in solving the problem.