[Solved] Filtering Data in a Form

[Dagoxx]

Hello
I just need a hint how to do that

I have imported a spreadsheet into base. The goal is to filter the data

1. 3 columns
2. Colum 1 has characters like a, b, c, etc.
3. Colum 2+3 have to be filtered and shown in a table according to Colum 1
-- The filter can be single like show only colum 2+3 according to a or
-- according to a+b

Do you have a video or an example how this is done

Thank you very much
Dagoxx

[Villeroy]

I have imported a spreadsheet into base.

What is the resulting type of database as indicated in the database document's status bar?

[Nick N,]

@ Dagoxx

Please be so kind to show column titles and at least one record or two. It would make yourself more clear.

Regards

Nick N

[Dagoxx]

Hi Nick
Thank you of course
In this attached example I would like to filter "Importance" with XXX and XXXX
The result should be 2 rows in a table with columns Category and SubCategory and rows "Bluetooth"+"Mouse" and "Bluetooth"+"Keyboard"
Hope this gives and idea
Have a great time
Dagoxx

[Nick N,]

@ Dago

Good evening,

See example below with form filtering. To obtain 2 rows filtered, just modify data in 1st column of Table1.

Bye

Nick

[Dagoxx]

Hello
Looks great
Can I choose multiple criterias like "X" & "XX" and without having the "Submit" button.
Just when I choose it gives me the right rows.
Regards and have a great day
Dagoxx

[Nick N,]

Hi Dago,

If you intend a form with 2 or more filters you might find a solution (with a little help), but you need anyway to refresh your query by using a button.

The alternative with NO BUTTON TO PUSH, is a form with fixed related subform that you may browse by turning pages using the bar at the bottom of the form.

Hope you enjoy

[Dagoxx]

Hi Nick
Sorry for the delay
Hope you are having a great time.

Ok I understand. But is there a way to chose multiple X's.
Like: I want to see all that have XX and XXX together at once
Because with the 1st draft it is only possible to see XX or XXX

Regards and have a great weekend
Dagoxx

[Villeroy]

The most flexible filter tool is the form based filter available on the form navigation toolbar. It switches the form into a special mode where you enter filter criteria directly into your form controls and then confirm the filter settings. In form filter mode, there is a little filter navigator window where you collect multiple criteria like (A>5) AND (B LIKE *foo*) OR (X IS EMPTY). This allows for rather complex filters in a rather clumsy way. Notice that some form controls (e.g. text boxes) have a property "Filter proposal". When this option is checked, you get a listbox of unique text items while in form filter mode.

There is a dialog based standard filter like the one we know from spreadsheets. I don't know why it is hidden, but you can reach it with one extra click on the last button on the form navigation toolbar which opens an additional grid view on the current form with its own toolbar with the standard filter button.
With some macro code it is possible to get this standard filter dialog triggered by a push button on the form: https://ask.libreoffice.org/uploads/sho ... iFGReG.odt (demo form about form dispatches).

A proper set up with so called "power filtering" (storing crieria in a dedicated table) is tricky but with satifying results: download/file.php?id=14591

The following macro can be assigned to some toggle button's "status changed" event with complex filter criteria like "(A>5) AND (B LIKE *foo*) OR (X IS EMPTY)" stored in the button's "additional info" property.

Code: Select all

Sub Button_Filter_OnOff(e)
	b = cBool(e.Selected)
	m = e.Source.Model
	s = m.Tag
	frm = m.Parent
	REM print frm.Filter
	frm.ApplyFilter = b
	frm.Filter = s
	frm.reload()
End Sub

In order to get the right filter string for the "additional info", create a query and copy the WHERE clause without the "WHERE" keyword. You may also open your form for editing, turn off design mode, apply the filter manually (form filter or standard filter dialog), turn on design mode and copy the filter from the form properties. This predefined form filter can be saved with the form., so the form loads with the filter and you can toggle it on/off without any macro using the toggle filter button on the navi toolbar.

[Nick N,]

OK Dago,

See below for a job you could have done by yourself. I.E. Inserting the duplicated categories in the first raw.

Goodbye

Nick

[Villeroy]

Nick's filter form is faulty. You can easily tab from the filter row into the new row, which erases all form contents.

Recipe for a working filter form:
Source type: SQL
Source: SELECT * FROM "Filter" WHERE "ID"=0 (simply select everything from one row with a distinct row number)
Allow modification: Yes
Anything else: No
Navigation toolbar: No (there is only one record to edit)

This way you never lose the filter record while being able to user other row numbers for different kinds of forms.

[Nick N,]

Yes Villeroy, you are right! I have made a mistake in previous selection by showing the blank filter field.

Anyway Dago may nevertheless launch a query. E.G.:

Code: Select all

SELECT "Importance", "Category", "Subcategory" FROM "Table1" WHERE "Importance" = 'XX'

Kindly check my file Test002 after correction.

Regards

Nick

[UnklDonald418]

Nick G
Two more suggestions.

The ListBox query returns multiple identical choices which can be confusing for the user and make long lists even longer! Instead of a simple SELECT use SELECT DISTINCT to list each choice only once.

Filter tables should not hold more that one row of data. But in the Filter table the ID field has AutoValue set to YES , making it easy to add additional rows.
At minimum, set AutoValue to No., making it more difficult to add more rows.
To make it even more difficult, change ID from an Integer to a Boolean type. Then it is clear to anyone that looks at it that this is a special table.

I use the following SQL script as a template when creating a Filter table.

Code: Select all

DROP TABLE "FILTER" IF EXISTS;
CREATE TABLE "FILTER" (
"ID" BOOLEAN NOT NULL PRIMARY KEY,
"FString" VARCHAR(50),
"F1" INTEGER NOT NULL,
"FDate" Date
);

INSERT INTO "FILTER"("ID", "FString","F1","FDate")
VALUES (FALSE, 'AAA', 0, NOW());

When executing scripts at Tools>SQL you are working directly with the underlying database engine, so when done there be sure to select View>Refresh Tables to inform the Base front end about the changes.

[Nick N,]

UncleDonald,

Thanks for yr suggestions. I appreciated. But, generally speaking, in LO, the form i proposed gives actual response Dago was waiting for, because there's a zone list that can be scrolled and the user can anyway make univoke choice.

[Dagoxx]

Hello
Thank you all
I am complete newbie in Databases
Sorry for my delay in responding
But the example do not work, when I want to see X + XX in the same time as mentioned
Usecase would be
a) show me all with X
b) show me all with X or XX
c) show ma all with X or XX or XXX
Is that possible?
Have a great day
Regards
Dagoxx

[Villeroy]

Code: Select all

SELECT  "T".* FROM "Topics" AS "T","Filter" AS "F"
WHERE "F"."ID" BETWEEN 3 AND 5
AND UPPER("T"."Topic") LIKE UPPER('%'|| "F"."TXT" ||'%')

The above parameter query filters my table "Topics" by 3 different expressions in 3 different rows in my filter table. The upper function makes the comparison case-insensitive.

[Villeroy]

It's easier to do with a separate filter table having an auto-ID where you add or remove criteria rows as needed.
See form and query "Filter2" in https://www.mediafire.com/file/jq7dl1pw ... 7.odb/file

[Nick N,]

Else you could edit query in a notepad and modify the variabile of the criteria as per previous message, E.G. using the statement OR.

Code: Select all

SELECT "Importance", "Category", "Subcategory" FROM "Table2" WHERE "Importance" = 'XX' OR "Importance" = 'XXX'

With which you could extend selection to criterion X too.

Have a nice time

[UnklDonald418]

Another approach based on the example uploaded by Nick N,
Form document Selection2 allows up to 3 selections.

Test003.odb

(31.03 KiB) Downloaded 653 times

[Dagoxx]

Hey
Great examples thank you
One solution is fix that means I need to script it
The other solution ask for saving after selecting.
Is it not possible to use CTRL+Mouse to higlight the selection
and after press enter or submit and get all possible combination according to the higlighting
Regards and have a great day
Dagoxx

[Nick N,]

Hola Hombre! Que passa?

Anything is feasable in IT, unless you use the good approach!

A zone list made in pure SQL doesn't grant you to do what you fancy.

If AOO Base doesn't suit your moves, why not trying to code in Java? But of course this would be the wrong place.

[UnklDonald418]

A relatively simple coded Macro can eliminate the push button, if that's the issue.. See form document Selection3 in the attached demonstration.
The problem with coded macros is that they can be difficult to write and once working, easy to break. A seemingly minor change to the form document can sometimes render it useless.
.

Test004.odb

(41.9 KiB) Downloaded 377 times

[Nick N,]

Would like to thank UncleDonald for his knowledge, as I haven't got sufficient skills.

Regards

Nick

[Villeroy]

A relatively simple coded Macro can eliminate the push button, if that's the issue.. See form document Selection3 in the attached demonstration.
The problem with coded macros is that they can be difficult to write and once working, easy to break. A seemingly minor change to the form document can sometimes render it useless.
.Test004.odb

This is why I wrote viewtopic.php?f=21&t=88831, which can be set up to refresh any listbox, combo box or (sub-)form of the same form document.
Just install the macro, add a hidden control to the triggering form * where you specify all the elements you want to be refreshed when the triggering form gets a record modified, inserted or deleted. The macro never needs to be touched. When things have been broken (renamed, removed), all you need to adjust is the text of the hidden control.

* RIght-click the triggering form in the form navigator, click New --> Hidden, right click the new element, change its name to AutoRefresh and enter one element or more than one separated by semicolon.
../Subform1 refreshes the triggering form's parent form's element "Subform1".
Subform/TableGrid/Listbox1 refreshes the triggering form's subform's element Listbox1 which is embedded in a table control "TableGrid".

[Dagoxx]

Hello
Thank you very much for all your support and demos.
It has been really worth.
Have a great and fantastic day
Dagoxx