Light filtered

It’s cool November outside, so this article will not be about a refreshing drink in the summer heat, but about the filtering mechanism in the Data Keeper project, as a result of which we should receive filtered data. Of course, they will only be light if you use a light theme for the application.

Data structure

We only need two new fields:

• cproperty.is_filter – a flag that determines whether this property should be used to build a filter
• class.is_ShowFilterPanel – flag that enables/disables display of the filter panel.

efmCProperty

The new field will require a component on the class property edit form. Don’t forget to add it to the list of components for saving data in the btnSave button setting.

Scripts

First, I wanted to remake the efmObject_OnShow() procedure in such a way that I could use it both to generate a form for editing object parameters and to generate a filtering panel. Obviously, they have a lot in common: a request to fetch data, generation of editing components. However, significant differences may arise in the long term. For example, when filtering data by date, they usually use a period rather than a single date; for a checkbox you need to take into account three states and so on. If you put all the logic into one procedure, it will become very difficult to debug. So I created PrepareFilterPanel() which is only responsible for the filtering panel.

procedure PrepareFilterPanel(APanel:TdbPanel; AIDClass:integer );
// creating filtering elements
// APanel - panel for placing elements
// AIDClass - the class for which the filter is being built
var
  tmpForm: TForm;
  tmpSQL: string;
  tmpDataSet: TDataSet;
  tmpLineTop: integer;
  tmpCount: integer;
  tmpLabel: TdbLabel;
  tmpEdit: TdbEdit;
  tmpPanel: TdbPanel;
  tmpImageOff: TdbImage;
  tmpImageOn: TdbImage;
  tmpImageNull: TdbImage;
  tmpButton: TdbButton;
  tmpComboBox: TdbComboBox;
  tmpParentList:string;
  tmpChildList:string;
  tmpParent: TdbPanel;
  i: integer;
  tmpControlID: integer;
  tmpDataEdit : TdbDateTimePicker;
begin
  if APanel.Tag <> AIDClass then
  begin
    APanel.Tag := AIDClass;
  CForm(APanel,tmpForm);
  tmpParent := APanel;
  // hide all components
  for i := tmpParent.ControlCount - 1 downto 0 do
  begin
    APanel.Controls[i].Visible := False;
  end;
  tmpCount := 0;
  // create the components needed for filtering
  GetChildAndParent( AIDClass, tmpParentList, tmpChildList ); // список родительских узлов
  tmpSQL := 'SELECT cproperty.is_detail, cproperty.id_ptype, cproperty.id, cproperty.name, cproperty.is_name, class.name as cname, class.id_uicontrol, class.id as ClassID '+
    'FROM cproperty LEFT JOIN class ON class.id = cproperty.id_class1 WHERE id_class in ('+tmpParentList+') and cproperty.is_filter = 1 ORDER BY orderNum ';
  tmpLineTop := 0;
  SQLQuery(tmpSQL,tmpDataSet);
  while not tmpDataSet.EOF do
  begin
    tmpControlID := tmpDataSet.FieldByName('id_uicontrol').asInteger;
    // метка
    FindC(tmpForm,'labData_'+intToStr(tmpCount),tmpLabel,False);
    if tmpLabel = nil then
      tmpLabel := TdbLabel.Create( tmpForm );
    with tmpLabel do
    begin
      visible := True;
      parent := tmpParent;
      Font.Size := ScaleFontSize;
      top := tmpLineTop;
      left := Scale(8);
      name := 'labData_'+intToStr(tmpCount);
      Caption := tmpDataSet.FieldByName('name').asString;
    end;
    // поле ввода текста
    FindC(tmpForm,'edtData_'+intToStr(tmpCount),tmpEdit,False);
    if tmpEdit = nil then
      tmpEdit := TdbEdit.Create( tmpForm );
    with tmpEdit do // Tag -> cproperty.id ;
    begin
      visible := True;
      name := 'edtData_'+intToStr(tmpCount);
      parent := tmpParent;
      Font.Size := ScaleFontSize;
      top := tmpLineTop + tmpLabel.Height;
      left := Scale(8);
      width := APanel.width - Left*2; // по ширине панели с отступом
      // заполняем данными
      tag := tmpDataSet.FieldByName('id').asInteger; //
      dbTable := tmpDataSet.FieldByName('id_ptype').asString;
      dbField := '';
      tagString := '';
      text := '';
      //
      dbCurrency := False;
      NumbersOnly := False;
      if tmpControlID = 3 then // целое число
      begin
        dbCurrency := True;
        dbAccuracy := 0;
        Alignment := taLeftJustify;
      end;
      if tmpControlID = 4 then // вещественное число
      begin
        NumbersOnly := True;
      end;
      onChange := 'FilterEdit_OnChange';
      Font.Style := 0;
      tmpEdit.ReadOnly := False;
    end;
    // переключатель
    if tmpControlID = 7 then
    begin
      tmpEdit.Width := trunc(tmpEdit.Height * 1.1); // Scale(32);
      FindC(tmpForm,'panData_'+intToStr(tmpCount),tmpPanel,False);
      if tmpPanel = nil then
        tmpPanel := TdbPanel.Create(tmpForm);
      with tmpPanel do
      begin
        parent := tmpParent;
        name := 'panData_'+intToStr(tmpCount);
        caption := '';
        top := tmpEdit.Top;
        left := tmpEdit.Left;
        width := tmpEdit.width;
        height := tmpEdit.height;
        bevelWidth := 0;
        color := tmpParent.color;
        visible := True;
      end;
      //
      FindC(tmpForm,'imgDataOn_'+intToStr(tmpCount),tmpImageOn,False);
      if tmpImageOn = nil then
        tmpImageOn := TdbImage.Create(tmpForm);
      with tmpImageOn do
      begin
        parent := tmpPanel;
        name := 'imgDataOn_'+intToStr(tmpCount);
        align := alClient;
        center := True;
        stretch := True;
        picture.LoadFromFile( ExtractFilePath(Application.ExeName) + 'images\toggle\toggle_on.png' );
        onClick := 'Toggle_Off';
      end;
      //
      FindC(tmpForm,'imgDataOff_'+intToStr(tmpCount),tmpImageOff,False);
      if tmpImageOff = nil then
        tmpImageOff := TdbImage.Create(tmpForm);
      with tmpImageOff do
      begin
        parent := tmpPanel;
        name := 'imgDataOff_'+intToStr(tmpCount);
        align := alClient;
        center := True;
        stretch := True;
        picture.LoadFromFile( ExtractFilePath(Application.ExeName) + 'images\toggle\toggle_off.png' );
        onClick := 'Toggle_Null';
      end;
      //
      FindC(tmpForm,'imgDataNull_'+intToStr(tmpCount),tmpImageNull,False);
      if tmpImageNull = nil then
        tmpImageNull := TdbImage.Create(tmpForm);
      with tmpImageNull do
      begin
        parent := tmpPanel;
        name := 'imgDataNull_'+intToStr(tmpCount);
        align := alClient;
        center := True;
        stretch := True;
        picture.LoadFromFile( ExtractFilePath(Application.ExeName) + 'images\toggle\toggle_indeterminate.png' );
        onClick := 'Toggle_On';
      end;
      // переключатель в нейтральное положение
      tmpImageNull.Visible := True;
      tmpImageOff.Visible := False;
      tmpImageOn.Visible := False;
    end;
    if tmpControlID = 6 then // справочник значений - кнопка
    begin
      FindC(tmpForm,'btnData_'+intToStr(tmpCount),tmpButton,False);
      if tmpButton = nil then
      begin
        tmpButton := TdbButton.Create( tmpForm );
      end;
      with tmpButton do
      begin
        visible := True;
        name := 'btnData_'+intToStr(tmpCount);
        parent := tmpParent;
        Font.Size := Scale(13);
        Caption := chr(8981);
        imageAlignment := iaCenter;
        Top := tmpEdit.Top - 1;
        Height := tmpEdit.Height + 2;
        Width := Height + 2 ;
        Left := tmpEdit.Left + tmpEdit.Width - Width;
        onClick := 'efmObject_btnData_OnClick';
        tag := tmpDataSet.FieldByName('ClassID').asInteger;
        //
        tmpEdit.Width := tmpEdit.Width - Width;
        tmpEdit.ReadOnly := True;
      end;
    end;
    if tmpControlID = 2 then // выпадающий список
    begin
      //
      FindC(tmpForm,'cmbData_'+intToStr(tmpCount),tmpComboBox,False);
      if tmpComboBox = nil then
        tmpComboBox := TdbComboBox.Create( tmpForm );
      with tmpComboBox do
      begin
        visible := True;
        name := 'cmbData_'+intToStr(tmpCount);
        parent := tmpParent;
        Font.Size := tmpEdit.Font.Size;
        top := tmpEdit.Top;
        left := tmpEdit.Left;
        width := tmpEdit.width;
        dbSQL := 'SELECT object.id, oproperty.value_s FROM object LEFT JOIN oproperty ON oproperty.id_object = object.id WHERE oproperty.id_cproperty = ( SELECT id FROM cproperty WHERE cproperty.is_name = 1 ) AND object.id_class = '+tmpDataSet.FieldByName('ClassID').asString+' ORDER BY 2';
        dbUpdate;
        enabled := True;
        onChange := 'efmObject_cmbEdit_OnChange';
        Font.Style :=0;
      end;
    end;
    //
    if tmpControlID = 5 then // дата
    begin
      FindC(tmpForm,'dtpData_'+intToStr(tmpCount),tmpDataEdit,False);
      if tmpDataEdit = nil then
      begin
        tmpDataEdit := TdbDateTimePicker.Create( tmpForm );
        AssignEvents(tmpDataEdit);
      end;
      with tmpDataEdit do
      begin
        visible := True;
        name := 'dtpData_'+intToStr(tmpCount);
        parent := tmpParent;
        Font.Size := tmpEdit.Font.Size;
        top := tmpEdit.Top;
        left := tmpEdit.Left;
        width := tmpEdit.width;
        ShowCheckBox := True;
        DateTime := Now();
        Checked := False;
        //
        dbOnChange := 'efmObject_dtpEdit_OnChange';
        Font.Style :=0;
      end;
    end;
    inc(tmpCount);
    tmpLineTop := tmpLineTop + tmpEdit.Height*2;
    tmpDataSet.Next;
  end;
  tmpDataSet.Free;
  APanel.Visible := (tmpCount <> 0); // если ничего нет на панели, скрыть её
  end;
end;
Code language: Delphi (delphi)

I decided to use the same handlers for the combobox and date entry fields as for the edit form, slightly modifying them so that the data immediately went into the text edit fields associated with them:

procedure efmObject_cmbEdit_OnChange(Sender: TObject);
// selecting a new value in the drop-down list
var
   tmpEdit: TdbEdit;
   tmpForm: TForm;
begin
   CForm(Sender,tmpForm);
   FindC(tmpForm,'edt'+DeleteClassName(TdbComboBox(Sender).Name),tmpEdit);
   TdbComboBox(Sender).Font.Style := fsBold;
   // set the data update flag
   tmpEdit.Text := TdbComboBox(Sender).Text;
   tmpEdit.Font.Style := fsBold;
   tmpEdit.dbField := IntToStr( TdbComboBox(Sender).dbItemID );
end;

procedure efmObject_dtpEdit_OnChange(Sender: TObject);
// select a new date value
var
   tmpEdit: TdbEdit;
   tmpForm: TForm;
   tmpDate:TdbDateTimePicker;
begin
   tmpDate:= TdbDateTimePicker(Sender);
   CForm(Sender,tmpForm);
   FindC(tmpForm,'edt'+DeleteClassName(TdbDateTimePicker(Sender).Name),tmpEdit);
   TdbDateTimePicker(Sender).Font.Style := fsBold;
   // set the data update flag
   tmpEdit.Font.Style := fsBold;
   if tmpDate.Checked then
     tmpEdit.Text := DateToStr( tmpDate.DateTime )
   else
     tmpEdit.Text := '';
end;
Code language: Delphi (delphi)

The main difference between the operation of components located on the filter form and those located on the editing form is that when a value changes, the list of objects is immediately updated. The FilterEdit_OnChange() procedure is responsible for this.

procedure FilterEdit_OnChange( Sender: TObject);
// handler for changes in the filter text edit field
var
   tmpForm:TForm;
begin
   // if it works quickly, then use it, no - there should be a separate button
   CForm(Sender,tmpForm);
   Form_UpdateData(tmpForm);
end;
Code language: Delphi (delphi)

This procedure contains a philosophical statement about expected performance, so I decided to add a button to the main form toolbar that allows you to update the contents of the active table.

procedure DTF_btnUpdateOnClick(Sender: TObject; var Cancel: boolean);
// edit entry
var
   tmpForm: TForm;
begin
   if (ActiveGrid <> nil) then
   begin
     CForm(ActiveGrid,tmpForm);
     Form_UpdateData(tmpForm);
   end
   else
     ShowHint( TControl(Sender), 'Select table' );
end;
Code language: Delphi (delphi)

Magic

Let me remind you that the frmObject object list display form has a key procedure frmObject_btnUpdate_OnClick() – a handler for pressing the btnUpdate button. In it we add a call to the filter builder (line 16). And so that the filter is rebuilt only when the class is changed, we will save the current class in the Tag property (see PrepareFilterPanel() lines 26-28). Line 19 then calls btnPrepareOject(), which prepares the data to display.

procedure frmObject_btnUpdate_OnClick (Sender: TObject; var Cancel: boolean);
// update display
var
   tmpIDClass: integer;
   tmpButton: TdbButton;
   tmpDataSet: TDataSet;
   tmpSQL: string;
   i:integer;
   tmpTabSheet: TdbTabSheet;
   Splitter:TSplitter;
begin
   frmObject.labIDClass.Caption := 'Change'; // block frmObject_tgrMain_OnColumnResize from firing
   // a query is built to select data - all objects of the specified class
   // get the class ID from the class tree
   tmpIDClass := Form_GetDataViewer( GetFormByName('dtfClass_Tree') ).dbItemId;
   PrepareFilterPanel( frmObject.panFilter, tmpIDClass ); // configure the filter panel
   frmObject.labIDClass.Tag := tmpIDClass; // remember the class
   tmpButton := TdbButton(Sender);
   btnPrepareOject(tmpButton, tmpIDClass);
   // remove detail
   for i:= frmObject.pgcDetails.PageCount - 1 downto 0 do
   begin
     frmObject.pgcDetails.Pages[i].Free;
   end;
   FindC(frmObject,frmObject.panDetails.TagString,Splitter);
   Splitter.Visible := False;
   frmObject.panDetails.Visible := False;
   // determine if there is detailing and create button tabs
   tmpSQL := 'SELECT class.id, class.name FROM cproperty LEFT JOIN class ON class.id = cproperty.id_class WHERE cproperty.is_detail = 1 AND cproperty.id_class1 = '+IntToStr( tmpIDClass );
   SQLQuery(tmpSQL,tmpDataSet);
   while not tmpDataSet.EOF do
   begin
     tmpTabSheet := TdbTabSheet.Create( frmObject );
     with tmpTabSheet do
     begin
       PageControl := frmObject.pgcDetails;
// Name := '';
       Caption := tmpDataSet.FieldByName('name').asString;
       Tag := tmpDataSet.FieldByName('id').asInteger;
// TagString := '';
     end;
     tmpDataSet.Next;
   end;
   if frmObject.pgcDetails.PageCount > 0 then
   begin
     frmObject.panDetails.Visible := True;
     Splitter.Visible := True;
     frmObject_Detail.btnUpdate.Click;
   end;
end;

Code language: Delphi (delphi)

There are few improvements to btnPrepareOject(); logic has been added to generate a filter condition based on data, in lines 81-86.

procedure btnPrepareOject( AButton: TdbButton; AIDClass: integer; ACheckBox:Boolean = False; ADetail: boolean = False);
// generating a table view of the class
// AButton - button for generating a request
// AIDClass - the class for which the request is made
// ACheckBox - whether a column with checkers is needed
// ADetail - whether filtering by master is needed
var
   tmpParentList: string;
   tmpChildList: string;
   tmpDataSet: TDataSet;
   tmpSQL: string;
   tmpFields: string;
   tmpJoins: string;
   tmpCount: integer;
   tmpCaptions: string;
   tmpTableAlias: string;
   tmpOrderBy: string;
   tmpIDMasterClass: integer;
   tmpIDMasterObject: integer;
   tmpFilter: string;
   tmpVisible: boolean;
   tmpForm:TForm;
   tmpPanel:TdbPanel;
   tmpFilterValue: string;
   tmpFiltered : boolean;
begin
   CForm(AButton,tmpForm);
   FindC(tmpForm,'panFilter',tmpPanel,False);
   tmpFiltered := (tmpPanel <> nil) and (tmpPanel.Visible);
   tmpFilter := '';
   if ADetail then
   begin
     tmpIDMasterClass := frmObject.labIDClass.Tag;
     tmpIDMasterObject := frmObject.tgrMain.dbItemID;
   end;
   GetChildAndParent( AIDClass, tmpParentList, tmpChildList); // get a list of parent and child IDs
   tmpSQL := 'SELECT * FROM cproperty WHERE id_class in ('+tmpParentList+') ORDER BY orderNum ';
   SQLQuery(tmpSQL,tmpDataSet);
   tmpFields := 'object.id';
   if ACheckBox then
     tmpFields := tmpFields + ',"$checkbox"';
   tmpJoins := '';
   tmpCaptions := 'delete_col'; // do not display
   tmpOrderBy := '2';
   if ACheckBox then
   begin
     tmpCaptions := tmpCaptions + ',#';
     tmpOrderBy := '3';
   end;
   tmpCount := 0;
   //
   while not tmpDataSet.EOF do
   begin
     tmpVisible := tmpDataSet.FieldByName('visible').asInteger = 1;
     tmpTableAlias := 'OP_'+IntToStr(tmpCount);
     //
     if tmpVisible then
     begin
       if tmpFields <> '' then
         tmpFields := tmpFields + ', ';
       tmpFields := tmpFields + tmpTableAlias+'.value_s ';
     end;
     //
     tmpJoins := tmpJoins + 'LEFT JOIN oproperty '+tmpTableAlias+' ON '+tmpTableAlias+'.id_object = object.id AND '+tmpTableAlias+'.id_cproperty = '+tmpDataSet.FieldByName('id').asString+' AND '+tmpTableAlias+' .orderNum is NULL '+CR ;
     //
     if tmpVisible then
     begin
       if tmpCaptions <> '' then
         tmpCaptions := tmpCaptions + ', ';
       tmpCaptions := tmpCaptions + tmpDataSet.FieldByName('name').asString;
     end;
     //
     if tmpDataSet.FieldByName('is_name').asInteger = 1 then
       AButton.Tag := tmpCount;
     //
     if ADetail and (tmpDataSet.FieldByName('id_class1').asInteger = tmpIDMasterClass) then
     begin
       tmpFilter := tmpFilter + tmpTableAlias+'.id_object1 = '+IntToStr(tmpIDMasterObject)+' AND ';
     end;
     //
     if tmpFiltered and (tmpDataSet.FieldByName('is_filter').asInteger = 1) then
     begin
       tmpFilterValue := GetTextFilterValue( tmpPanel, tmpDataSet.FieldByName('id').asInteger );
       if tmpFilterValue <> '' then
         tmpFilter := tmpFilter + tmpTableAlias+'.value_s LIKE "%'+tmpFilterValue+'%" AND ';
     end;
     inc(tmpCount);
     tmpDataSet.Next;
   end;
   tmpDataSet.Free;
   if tmpCount = 0 then
     tmpOrderBy := '1';
   tmpSQL := 'SELECT '+tmpFields+CR+' FROM object '+tmpJoins+CR+' WHERE '+tmpFilter+' object.id_class in ('+tmpChildList+') ORDER BY '+tmpOrderBy;
   AButton.dbSQL := tmpSQL;
   AButton.dbListFieldsNames := tmpCaptions;
  end;

Code language: Delphi (delphi)

To retrieve specific values for the filter, the GetTextFilterValue() function has been added. The search principle is based on the fact that the value CProperty.ID is stored in the Tag property of the edit field – the identifier of the class property. Once a match is found, the text from the edit field is returned to the calling procedure. An additional visibility check is associated with the mechanism for “recycling” components on the panel to avoid errors associated with changing the settings of the filtering panel while the application is running.

function GetTextFilterValue( APanel:TdbPanel; ACPropertyID:integer ):string;
// retrieving filter string value
var
  i:integer;
  tmpEdit:TdbEdit;
begin
  Result := '';
  for i := APanel.ControlCount - 1 downto 0 do
  begin
    if APanel.Controls[i] is TdbEdit then
    begin
      tmpEdit:=TdbEdit(APanel.Controls[i]);
      if tmpEdit.Visible and (tmpEdit.Tag = ACPropertyID) then
      begin
        Result := tmpEdit.Text;
        Break;
      end;
    end;
  end;
end;
Code language: PHP (php)

Result

The ToDo list has decreased by 1 item, but still looks impressive:

• Removing detail when deleting a master record
• Right alignment for numeric data types
• Interval class tree
• Searching for objects by a given property value (several properties)
• Setting the visibility of columns in a table view without using inheritance
• Macros for automatically filling fields on the client
• Calculated fields for table view
• Creation of arbitrary views (using an analogue of SQL for OOP).
• Flag for mandatory completion of an object property (analogous to a required field)
• Flag of the uniqueness of a property of a class object (analogous to the uniqueness of a field value)
• Global uniqueness flag of an object property (or property with GUID generation)
• Extension of basic data types (time, yes/no, images/files, etc.)
• Adding control for data types (analogous to domains) – range of values for numbers and dates, text length for strings, etc.
• Manually adjusting the position and size of components on the class editing form.
• Adding new reference values directly from the object editing form.
• Form scaling: the user sets the display scale of forms and their contents.

It’s not really necessary to implement all this in a prototype (on My Visual Database), at some point you will need to make a decision to implement all this in Delphi in a multi-platform Windows/Android project. But first, it’s probably worth testing performance to clearly understand the limitations of the adopted data architecture.

Links

• Data Keeper 1.6 – project source