Searching for data [BH40-Base Handbook p.200]

[bbater]

Operating system: Windows 10 Pro Version of LibreOffice: Version: 6.2.5.2 (x64) File format: ODB Database engine: Firebird.

Using a ‘FilterExample’ table as described on page 198 works well with a ListBox control which has a ‘Type of list contents’ property which can be set to ‘Sql’ with the query entered in property ‘List content’.

The advice on searching, rather than filtering, on page 200 however, fails to say how to mimic this construct using text entered into a control which is not of ListBox type. A TextBox control does not have a ‘Type of list contents’ property which can be set to ‘Sql’, so my question is:

In order to search on text entered into a TextBox, where does one enter the SQL query when a TextBox control has no ‘Type of list contents’ property?

FYI, the valid SQL query in question is:

SELECT FamilyName, Client_ID FROM “Client”
WHERE LOWER(“FamilyName”) LIKE NULLIF( ( SELECT ‘%’ || LOWER(“SearchTerm”) ||’%’
FROM “SearchTable” ), LOWER(“FamilyName”) )

All advice appreciated!

[Ratslinger]

Hello,

The text box is not associated with any SQL itself. It’s main purpose, unlike the list box, is to enter partial information. This is again stored in the filter table and then used by the query. The query is the basis of the sub form. A push button with the ‘Action’ set to ‘Refresh form’ run the query and displays the information.

A sample of this can be found in this post → Need to Filter a table with two fields for key words. Please keep in mind this is a crude Q&D sample.

For a test enter hor in the text box and press the button to see the result.

Edit 2020-01-16:

Same sample as in link except for Firebird ----- BookSearch.odb

[bbater]

Thanks Ratslinger. I think I asked the wrong question. I didn’t have the query as the data source for the subform, but now that’s rectified, it’s still not working. The problem seems to be that the partial string value entered into the TextBox (the search term) is not being saved to the filter table. So the question is, what event or change of state of the TextBox can be used to trigger saving of the value to the filter table before the query is run?

BTW, I can’t run the example you mentioned because a) it’s based on HSQLDB (I’m using Firebird) and it says it can’t find a 64-bit JRE, which is odd, because I reinstalled it last night just to be sure. And my own database seems happy with the installed JRE.

[Ratslinger]

Firebird does not need JRE so you probably have the wrong JRE installed or the settings in 'Tools-Options-LibreOffice-Advanced` set incorrectly.

You should have a main form and a second form (or sub form). The main form has the text box and the sub form the query results. The push button with the action set to Reload form should be on the sub form.

Edit: Added same Base file sample using Firebird to answer.

[bbater]

I’ve re-installed JRE with an explicitly-stated 64-bit version in Control Panel > Programs. Your example using HSQLDB now works. I have a Main and subform as you describe and my Filter Table now updates when I enter a search term. However, the [Search] button on the subform refreshes only the corresponding control in the subform with the partial string entered as the search term. None of the other fields are refreshed. I will continue working on it. But one question: my subform itself contains several further subforms. Could this be part of the problem? Thanks for your help so far, I’ll get back if I need to.

[bbater]

Problem solved. Further to the above, in Ratslinger’s example, I noted that there was no linkage entered between Mainform and Subform. My subform was not refreshing because I had explicitly set the Mainform > Subform linkage (Master field / Slave field) to my FilterForm ‘SearchTerm’ > Subform (corresponding column), which I understood to be necessary between any Mainform/Subform pair. Deleting this linkage and leaving it blank solved the problem. The first-level Subform refreshes, as do its multiple second-level Subforms. So, thanks again, Ratslinger.

BTW, my query on which the first Subform is based is not as stated above, but actually:

SELECT * FROM “Client” WHERE LOWER(“FamilyName”) LIKE NULLIF( ( SELECT ‘%’ || LOWER(“SearchTerm”) ||’%’ FROM “SearchTable” ), LOWER(“FamilyName”) )

[Ratslinger]

Just to clarify, in the sample the two could have been main forms each instead of a main and a sub.