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.