[Solved] Boolean Field in FilterTable

[gkick]

Hi All,

with reference to viewtopic.php?f=39&t=56503&p=248985&hilit=base+boolean+field+in+filter+table#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

[Sliderule]

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   Expand viewCollapse view

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:
   
   

   
   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/252277/boolean-tri-state-for-filter-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]

@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   Expand viewCollapse view

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

[Villeroy]

Code: Select all   Expand viewCollapse view

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   Expand viewCollapse view

  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.

[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.

[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

[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

[Villeroy]

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.

[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.

[Villeroy]

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.

[UnklDonald418]

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

Code: Select all   Expand viewCollapse view

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.

[gkick]

Thank you all for this - a quantum leap in my never ending learning curve !

[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.

[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.

[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 ?

[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.

[gkick]

Yes the dummy was just a quick and dirty at 4 at night and I called the form uncascaded.
All working now, thanks