Search and filter for records with any match of the input value

[jderica]

In firebird, have a table that stores info of public institutions and I’ve concatenated the attributes for “name”, “abbreviated name”, “street address name” and “street address number” in a query and also a table view.

With libre base, I made a form that display these institutions with relevant info and project dependent on them. Currently I use a combo box to search for an institutions, using this macro:

Sub SearchINS
        dim oFilter as object
        dim oFormCtl as object
        dim doMove as boolean
       
        on local error goto SearchByNameError
       
        oFormCtl = ThisComponent.Drawpage.Forms.getByName("L0INS")
       
       if oFormCtl.isModified then
          doMove = doUpdateRecord( oFormCtl, oFormCtl.isNew )
       else
          doMove = TRUE
        end if
       
        if doMove then     
            oFilter = oFormCtl.getByName("fmtID_INS_COMBO")
             if oFilter.CurrentValue <> "" then
                oFormCtl.Filter = "NUME_INS LIKE " + "'"+oFilter.CurrentValue+"'"
                oFormCtl.ApplyFilter = True
             else
                oFormCtl.ApplyFilter = False
             end if
             oFormCtl.Reload
          end if
          
          exit sub
       
        SearchByNameError:
            resume next

End Sub

function doUpdateRecord( aDataForm as variant, aNew as boolean ) as boolean
   
   doUpdateRecord = FALSE
      
      if aNew then
         aDataForm.InsertRow
      else
         aDataForm.UpdateRow
      end if
   
   doUpdateRecord = True
   
end function

This works great if I know exactly what I want to search for and type the name in correctly, left to right. But with the concatenated version, an institution name is something like “ABCD - Agency Bureau of Conical Deployments, Palm Street nr 24”.

I wonder if it’s possible to make an “ad-hoc” search feature, such that, if you type “deployments”, the above example shows up. I suspect that this will require a dedicated table control to display results, but that’s another issue.

I found this example of a search filter that does just that, but it uses a filter table and a query to generate the results. Is it possible to replace the “Nume_INS LIKE” in the above macro with the following SQL command:

SELECT "Customers"."CompanyName", "Customers"."ID" FROM "Customers", "FILTER" WHERE UPPER ( "Customers"."CompanyName" ) LIKE '%' || UPPER ( COALESCE ( "FILTER"."txtString", "Customers"."CompanyName" ) ) || '%'

Where “txtstring” is the concatenated name. So I tried something like:

oFormCtl.Filter = "NUME_INS LIKE " '%' || UPPER ( COALESCE ( oFilter.CurrentValue, NUME_INS ) ) || '%'

But that just stays greyed out in the edit macro view. I don’t understand what the ‘%’ even does.

[Ratslinger]

Hello,

The % is a wildcard character. Please see this → The SQL LIKE Operator

For your statement try:

oFormCtl.Filter = "NUME_INS LIKE  '%' || UPPER ( COALESCE ( " & oFilter.CurrentValue & ", NUME_INS  ) ) || '%'"

[jderica]

I’m getting an error when using that.

`SELECT * FROM "L0INS" WHERE NUME_INS LIKE  '%' || UPPER ( COALESCE ( Apa Nova București S.A., NUME_INS  ) ) || '%`'
SQL Status: HY000
Error code: 1000

syntax error, unexpected NAME, expecting ')' or ',' or ';'

Now I tried somethign different and this works for a query/sql:

SELECT "ID_INS", "ACR_INS", "NUME_INS" FROM "L0INS" WHERE LOWER ( "NUME_INS" ) LIKE '%nova%'

That returns the “Apa Nova Bucuresti” entry so I attempted to adapt it as:

Edit: This is the final version hopefully. It works great so far. Note that disabling autofill for the combobox probably solved some issues.

oFormCtl.Filter = "LOWER(CompanyName) LIKE " + "LOWER('%"+oFilter.CurrentValue+"%')"

[jderica]

This is the final form and it works great. Added an odb model (the form using the macro is “_WILD CARD SEARCH MACRO”.

Demo36a_CustomerSelect.odb

Sub SearchRec_v1
        dim oFilter as object
        dim oFormCtl as object
        dim doMove as boolean
       	dim sSQL as string
       	
        on local error goto SearchByNameError
       
        oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
       
       if oFormCtl.isModified then
          doMove = doUpdateRecord( oFormCtl, oFormCtl.isNew )
       else
          doMove = TRUE
        end if
       
        if doMove then     
            oFilter = oFormCtl.getByName("fmtID_REC_COMBO")
             if oFilter.CurrentValue <> "" then
				oFormCtl.Filter = "LOWER(CompanyName) LIKE " + "LOWER('%"+oFilter.CurrentValue+"%')"
                oFormCtl.ApplyFilter = True
             else
                oFormCtl.ApplyFilter = False
             end if
             oFormCtl.Reload
          end if
          
          exit sub
       
        SearchByNameError:
            resume next

End Sub

function doUpdateRecord( aDataForm as variant, aNew as boolean ) as boolean
   
   doUpdateRecord = FALSE
      
      if aNew then
         aDataForm.InsertRow
      else
         aDataForm.UpdateRow
      end if
   
   doUpdateRecord = True
   
end function