[Solved] Boolean Field in FilterTable

Creating and using forms
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] Boolean Field in FilterTable

Post by gkick »

Hi All,

with reference to viewtopic.php?f=39&t=56503&p=248985&hil ... le#p248985

going in circles. Using a listbox to filter a field by way of a filter table whether the records are true, false or null (tristate). So the listbox has a dummy table with id, state and value in order to populate the filtertable.
state = 0 for false,1 for true, 2 for null (integer)
value = No,Yes, All.
The filtertable works perfectly, but the query for the report does not, no error just shows all records.
Any ideas what I am missing ?

Thanks
Last edited by gkick on Sat Jun 27, 2020 3:58 am, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Boolean Field in FilterTable

Post by Sliderule »

gkick wrote: The filtertable works perfectly, but the query for the report does not, no error just shows all records.
Any ideas what I am missing ?
You have not shown any Query you have defined . . . therefore, I can only guess.

Perhaps you can should be using the HSQL 2.X ( NOT a part of HSQL Embedded Version 1.8.10 ) DECODE as in the following Query:

Code: Select all

Select 
   "MY_TABLE".*,
   DECODE("MY_TABLE"."MY_BOOLEAN_COLUMN", TRUE, 'Yes', FALSE, 'No', 'All') as "MY_TRISTATE_COLUMN"
From "MY_TABLE"
Explanation:
  1. According to HSQL 2.X documentation:
    HSQL 2.X Documentation - General Functions wrote:
    DECODE

    DECODE( <value expr main>, <value expr match 1>, <value expr result 1> [...,] [, <value expr default>] )

    DECODE takes at least 3 arguments. The <value expr main> is compared with <value expr match 1> and if it matches, <value expr result 1> is returned. If there are additional pairs of <value expr match n> and <value expr result n>, comparison is repeated until a match is found the result is returned. If no match is found, the <value expr default> is returned if it is specified, otherwise NULL is returned. The type of the return value is a combination of the types of the <value expr result ... > arguments. (HyperSQL)
  2. Therefore, by using the DECODE as in my sample Query, and, as described in your question at:

    https://ask.libreoffice.org/en/question ... ter-table/ ,

    the new column "MY_TABLE"."MY_TRISTATE_COLUMN" will return a value:
    1. When "MY_TABLE"."MY_BOOLEAN_COLUMN" is TRUE, 'Yes' is returned
    2. When "MY_TABLE"."MY_BOOLEAN_COLUMN" is FALSE, 'No' is returned
    3. Otherwise ( as in a NULL value ) 'All' is returned
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Boolean Field in FilterTable

Post by gkick »

@Sliderule

Gday Sliderule,

Thanks for that but I am lost, sorry for not being clear (brain thinking English, German and Spanish all at the same time).

My goal is to setup some sort of Report Generator which allows the user to configure reports in various ways by using listboxes and a filtertable

the data source for the Completion listbox is a dummy table providing the list with the three options

The listbox puts the desired option in the filtertable ok

the filtertable then is fed into the reports underlying query

Code: Select all

SELECT "qryOneOrOther"."lbList" "Client", "tblFilter"."pid", "tblProject"."projectType" "Project Type", "tblProject"."projectName" "Project Name", "tblProject"."projAddress" "Project Location", "tblProject"."pcity" "City", "tblProject"."pregion" "Region", "tblwbsc"."pcid", "tblwbsc"."cwbs" "Component", "tblpdetail"."item" "Item", "tblFilter"."Complete", "tblpdetail"."per" "Unit", "tblpdetail"."x_units" "Quantity", "tblpdetail"."labour" "Labour", "tblpdetail"."material" "Material", "tblpdetail"."rental" "Rental", "tblpdetail"."fees" "Fees", "tblpdetail"."fixed" "FC", "tblpdetail"."other" "Other", ( COALESCE ( "rental", 0 ) + COALESCE ( "labour", 0 ) + COALESCE ( "material", 0 ) + COALESCE ( "fees", 0 ) + COALESCE ( "other", 0 ) ) * "x_units" "SubTotal", "tblClient"."title" "Title", "cfname" || ' ' || "clname" "Client", "tblClient"."tel" "Telephone", "tblClient"."associate" "Associate", "tblpdetail"."dtid", "tblProject"."cid", "tblwbsc"."owner", "tblFilter"."Complete" FROM "tblwbsc" "tblwbsc", "tblProject" "tblProject", "tblpdetail" "tblpdetail", "tblClient" "tblClient", "qryOneOrOther" "qryOneOrOther", "tblFilter" "tblFilter" WHERE "tblwbsc"."pid" = "tblProject"."pid" AND "tblpdetail"."pcid" = "tblwbsc"."pcid" AND "tblProject"."cid" = "tblClient"."cid" AND "tblClient"."cid" = "qryOneOrOther"."cid" ORDER BY "tblpdetail"."dtid" ASC
The output will allow to run the report with records where the tasks are complete, incomplete or both.

I ran your query but ended up with a data could not be loaded error.

Hope this all makes sense.

Thank you
Attachments
filtertable.png
filtertable.png (2.57 KiB) Viewed 10246 times
table.png
table.png (3.36 KiB) Viewed 10246 times
parameterselections.png
parameterselections.png (3.84 KiB) Viewed 10246 times
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Boolean Field in FilterTable

Post by Villeroy »

Code: Select all

SELECT <lots of columns>
FROM <several joined tables>, "Filter"
WHERE "Filter"."ID"=0 // allways limit the row set to one row
  AND  "Filter"."IntFilter" = "Table"."IntField" // Condition A returns True if the 2 integers are equal
  AND ("Filter"."BoolFilter" = "Table"."BoolColumn" OR "Filter"."BoolFilter" IS NULL) 
// Condition B returns True if the 2 boolean values match or if no boolean filter value is given
A record is selected if both conditions are met. If the filter value for condition B is Null, the second condition is True anyway and the selection depends on condition A only.

Condition B requires braces (x=y OR x IS NULL) because without braces:

Code: Select all

  AND  "Filter"."IntFilter" = "Table"."IntField" // Condition A returns True if the 2 integers are equal
  AND "Filter"."BoolFilter" = "Table"."BoolColumn" 
  OR "Filter"."BoolFilter" IS NULL
This returns all records if BoolFilter is NULL regardless of the IntFilter.

Limiting the filter row to one particular row ID, allows you to use other rows for other filter jobs.
In addition, you should disable all form properties on the "Data" tab except "Modify record"=Yes. so the user can't navigate into another record or into the new record by hitting the tab key.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
chrisb
Posts: 294
Joined: Mon Jun 07, 2010 4:16 pm

Re: Boolean Field in FilterTable

Post by chrisb »

hello gkick,
i built a small db some time ago in order to demonstrate simple filtration, you may find it usefull.
the objective was to include a method which would enable the clearance of all filter items with a single click of the mouse. two small macros are included one to facilitate this feature & the other to reload the form when a list box is changed.

the data table i use has six fields, auto id, four FKs (all integers) & one other integer "Age", which makes it a fairly typical if small database table.

we have six list box queries & one other query 'qF_FilterTable_tData' which is the data source for the filtration form.
'qF_FilterTable_tData' processes & filters the data in accordance with the values contained in the filter table.

unfortunately a lot of your initial posts do not contain sufficient information from which one can deduce a possible solution, however the query code you finally posted makes no attempt to filter.

o.k. your 'table.png' which is the data source for a list box does not require the boolean field, it does nothing.
the field "complete" is the visible field & has the values (No,Yes, All) , "id" should be the bound field.

usually when filtering i use 'null' to represent 'All' because it's easy to implement & the majority of forum users do the same.
in my demo i have added 'All' to the table "Smoker" so now the list box 'qLB_Smoker_Non_Smoker_All' shows visible field (All, Non Smoker, Smoker), bound field (1,2,3).
in the query 'qF_FilterTable_tData' i replaced or (select "SmokerID" from "tFilter" where ID = 1) is null with or (select "SmokerID" from "tFilter" where ID = 1) = 1
the filter table has the field "SmokerID", this needed changing from 'NULL' to 'NOT NULL' because it must now always contains a value, this also removes that blank list box line you mentioned in a previous topic.

one last thing the query 'qF_FilterTable_tData' uses sub queries & explicit joins to performs the act of filtration, this enhances performance.
Attachments
Filter.odb
(26.09 KiB) Downloaded 329 times
Last edited by chrisb on Sun Jun 28, 2020 2:00 am, edited 1 time in total.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Boolean Field in FilterTable

Post by gkick »

@Sliderule
@Villeroy
@chrisb

Hello Gentlemen,

Thank you very much for your wonderful help, more so for all the excellent resources and of course your time providing me with new insights and ideas all the time, Must confess though after DBase3, Clipper compiler, Access, Lotus Notes, Base can be a bit of a Love Hate relationship at times, but yet there always seem to be work arounds.
Hope you guys are safe and sound with all that current crap around the world happening.

@chrisb appreciate that Filter database I am going to ripp apart over the weekend.

Cheers all
Gerhard
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] Boolean Field in FilterTable

Post by gkick »

@chrisb
I just love the database, the example of the subquery and the clear all filter button in particular!
Studying the db a couple of questions:
Firstly -How do you keep the indentation in queries as I always end up with unreadable monster queries hard to debug. Start off with indentation but its not saved ?
Secondly do the explicit joins in the subquery need to be all of the same name ?
Now unrelated to the original question, some of the filterboxes need to be cascading.
The only examples showing how to do this all use some dummy table. However I recall some discussion of how to store the selection of box1 to a variable and use this variable as part of the datasource for box2
Would you possibly have come across an example of this ?
Cheers
Gerhard
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Boolean Field in FilterTable

Post by Villeroy »

gkick wrote:Firstly -How do you keep the indentation in queries as I always end up with unreadable monster queries hard to debug. Start off with indentation but its not saved ?
Base stores all parsed queries without line feeds. Direct queries are stored literally but direct queries have their own limitations.
I manually copy&paste SQL into a decent code editor with syntax highlighting and add line breaks in front of FROM WHERE GROUP ORDER BY. After editing I paste back the query into the Base query window.
Secondly do the explicit joins in the subquery need to be all of the same name ?
No
The only examples showing how to do this all use some dummy table. However I recall some discussion of how to store the selection of box1 to a variable and use this variable as part of the datasource for box2
Keep it simple. Use a dummy table.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
chrisb
Posts: 294
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Boolean Field in FilterTable

Post by chrisb »

i see that Villeroy has responded & agree with every word but i have penned a response & rather than edit will post anyway.
How do you keep the indentation in queries?
from the BASE window:
hit 'Queries', hit 'Create Query in SQL View', (activate the icon 'Run SQL command directly' OR 'menu:Edit>Run SQL command directly'.
we are now in DIRECT MODE (DO NOT confuse with menu:Tools>SQL).

DIRECT MODE advantages:
indentation is retained.
code may be commented.
the BASE parser is bypassed & SQL is sent directly to the database (HSQLDB in our case) which means quicker execution.
it should also be noted that the BASE parser is restrictive & sometimes rejects perfectly valid SQL.
some functions e.g. UNION require DIRECT MODE.

DIRECT MODE disadvantages:
because the BASE parser is ignored parameters can NOT be passed.
BASE parameters include the PARAM function & FORM master field, slave field links.
to circumvent these disadvantages a VIEW created from the QUERY can be used as the data source.
do the explicit joins in the subquery need to be all of the same name?
i do not understand the question!
we have five subqueries.
each subquery selects data from a different table & is assigned a unique alias.
the data selected will vary in accordance with a referenced field contained in the table "tFilter".

our master table is "tData", its subquery alias is 'd' & four of its fields are foreign keys.
the other tables ("tPeople","tGender","tSmoker","tSoaps") represented by the subquery aliases (p,g,sm,so) are all joined to our master table.
how to store the selection of box1 to a variable and use this variable as part of the datasource for box2
i do not have an example.
it's not impossible but your talking macros & macros tend to be specific rather than generic.
i suggest taking this route only when other solutions are impossible.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Boolean Field in FilterTable

Post by Villeroy »

chrisb wrote: one last thing the query 'qF_FilterTable_tData' uses sub queries & explicit joins to performs the act of filtration, this enhances performance.
The drawback is that the resulting row set is read-only which you normally do not want in an input form. It is a major improvement for a report, though.

A mistake on your filter form is that the refresh button is attached to the filtering parent form. It should be attached to the filtered subform. Now it prompts if you want to save the modified filter record before refreshing both the filtering form and the subform. When the button belongs to the subform, the filter record will be saved when the subform button takes the focus and then the subform only will be refreshed.
If editing the filtred subform is not required, you can also move it anywhere in the form hierarchy and save it as direct SQL. It does not depend on the filtering form anyway. It depends on the data stored in the filter table but not on the filter form object.
Adding an undo button to a filtering form is also a good idea.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Boolean Field in FilterTable

Post by UnklDonald418 »

You can store persistent values as global variables, outside the Sub or Function

Code: Select all

Global myListBoxSelection as Integer

Sub Main

'store a value in the global variable.
myListBoxSelection = box1Value

End Sub
Defined that way, another macro even in a different library can read the value stored in myListBoxSelection It remains available until the database session closed.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] Boolean Field in FilterTable

Post by gkick »

Thank you all for this - a quantum leap in my never ending learning curve !
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
chrisb
Posts: 294
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Boolean Field in FilterTable

Post by chrisb »

Villeroy wrote
A mistake on your filter form is that the refresh button is attached to the filtering parent form
there is no mistake in my form, the positioning of the refresh button is intentional.
the refresh button is clearly labelled 'Refresh List Boxes'.
the sole purpose of this button is to enable the list boxes to be manually updated should any of their underlying tables "tGender", the field "Age" in the table "tData", "tSmoker" or "tSoaps" have been edited/updated.

the four list boxes & single text box are all located in the parent form 'fFilter'.
whenever the value of a control is changed a macro is fired which updates the underlying table 'tFilter' & reloads the form 'fFilter' which in turn reloads the subform 'fData'.
i probably should have coded the last line of the macro 'ControlChanged' as oForm.fData.reload and not oForm.reload but the difference in performance is undetectable.

note:
the uploaded filter db is old.
the only changes made in relation to this topic are to update the visible field of a list box to display 'All' in preference to null.
the inner subform 'fData' should be a main-form & the prefix of 'f' tells me that it once was.
i probably cast it as a subform to demonstrate that a subform will be updated when its parent is refreshed even when master field & slave field links are not defined.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Boolean Field in FilterTable

Post by Villeroy »

I see. Have a look at my form utilizing my "universal AutoRefresh macro". I changed the button's action to "Save form", added a hidden control with a list of items to be refreshed (4 list boxes and one subform) and pointed the "After record action" event to my macro. The save button is also the default button triggered by the Enter key.
 Edit: Sorry, the first upload was pointing to the wrong macro. 
Attachments
Filter_AutoRefresh.odb
(37.26 KiB) Downloaded 320 times
Last edited by Villeroy on Mon Jun 29, 2020 11:39 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] Boolean Field in FilterTable

Post by gkick »

@villeroy
With reference to your recommandation to use a dummy table for cascading listboxes, does that not really mean that the tables(s) need to be manually maintained via main, sub,sub... forms as in the attached example ?
So you would need multiples of maintainance forms to keep the boxes synchronised ?
Attachments
CascadeLsts.odb
(38.53 KiB) Downloaded 340 times
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Boolean Field in FilterTable

Post by Villeroy »

So you would need multiples of maintainance forms to keep the boxes synchronised ?
Your cascading subforms do not require anything but most users hate simple stuff. You can select a country, then a city of that country and a suburb of that city and finally add another subform where you use the suburb entry.
Your list boxes don't work. No, you don't need any maintainance forms to make listboxes work. You need consistent data entry forms.
Listboxes are made to enter one value depending on the list selection. Subforms are made to filter records. Most users try desparately to make a listbox filter records. This is why I "invented" power filtering some 13 years ago. With "power filtering" each form control stores a value as filter criterion and a subform shows filtered records. It just puts things together in a different way, so they can work as designed.
---------------
P.S. your attachment is a perfect example deonstrating why the whole concept of Base is badly implemented, misleading, plain wrong. You can not click together some database quickly. Database design is development work.
Your database has inconsistent records (foreign key without parent) because all fields are nullable and the relations window is blank. The suburb table's foreign key is a VARCHAR because a mindless "table wizard" created this, With an inconsistent structure you enter inconsistent test data which make it extremely difficult to find out why the forms don't work.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] Boolean Field in FilterTable

Post by gkick »

Yes the dummy was just a quick and dirty at 4 at night and I called the form uncascaded.
All working now, thanks
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Post Reply