Form Based Filter not working

Creating and using forms
Post Reply
JWDog
Posts: 4
Joined: Sun Jul 18, 2021 6:35 pm

Form Based Filter not working

Post by JWDog »

I have a database for a game collection. I am trying to use the form based filter on a dropdown control with a one to many relationship. The form is based on a games table (fk) connected to a systems table (pk). The form based filter works for one of the system table records, but not for others, even though records in the games table have those other system records. I am not sure why it is not consistant with the filter. Please advise. Systems Table 1 : M Games Table
JWDog
OpenOffice Version 4.1.10
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form Based Filter not working

Post by Villeroy »

Download the attached database document having an embedded HSQLDB with a one-to-many relation.
Open the document and the embedded form.
Click the form filter button on the navigation bar.
The form is in criteria input mode now. Notice the additional toolbar with 3 icons ("Form Filter").
Select a person from the PID column.
Click the funnel icon on the form filter toolbar (tooltip: "Apply form based filter").
Result: The form is back in data mode showing only the records having the person that has been selected in criteria mode.
Attachments
t105725.odb
(52.73 KiB) Downloaded 287 times
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
JWDog
Posts: 4
Joined: Sun Jul 18, 2021 6:35 pm

Re: Form Based Filter not working

Post by JWDog »

Villeroy, my database is probably too big to post here for you to see what I am asking about.i am going to try and shrink it. Do you know what size of .odb I can post?

I did get the form based filter to work on the database and form you posted.

Thanks.
JWDog
OpenOffice Version 4.1.10
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form Based Filter not working

Post by Villeroy »

[Forum] How to attach a document here

Remove all forms and reports except one form demonstrating the issue.
Call Tools>SQL.... and run command

Code: Select all

SHUTDOWN COMPACT
Close the document.
If the document is still larger than 128 kB, remove records and do another SHUTDOWN COMPACT.
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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form Based Filter not working

Post by Villeroy »

General recipe for a 2-column list box with a first visible field (index 0, text) and a second bound field (index 1, ID number). This is how it works in my example database.

All your primary keys and foreign keys are names (text). The items are stored in tables having a single column. Your visible fields are the same as the bound fields, so the bound field index should be 0.

Integer auto-IDs have the advantage that you can easily rename your items and the name will instantly change in all referencing list boxes. Integer keys are more efficient because computers prefer to look up integer numbers instead of long byte sequences representing strings.
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
JWDog
Posts: 4
Joined: Sun Jul 18, 2021 6:35 pm

Re: Form Based Filter not working

Post by JWDog »

Thank you for this explanation, I will try to integrate this into my database. And let you know if it worked.
JWDog
OpenOffice Version 4.1.10
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form Based Filter not working

Post by Villeroy »

Set all bound field properties of your list boxes to 0 and test the form based filter. Then you may decide to modify the relations so they are linked through integer auto-IDs.
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
JWDog
Posts: 4
Joined: Sun Jul 18, 2021 6:35 pm

Re: Form Based Filter not working

Post by JWDog »

My database is now in recovery mode, and when recovery is complete and i try to open the form or change to tables, openoffice base crashes, please helpl!?

Thanks!
JWDog
OpenOffice Version 4.1.10
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form Based Filter not working

Post by Villeroy »

No backup, no mercy.
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: Form Based Filter not working

Post by UnklDonald418 »

[Tutorial] Recovering a damaged odb file
If that fails, open a new Writer document or Calc spreadsheet.
From the Menu select Tools>Options
In the Options dialog select OpenOffice>Paths
In Windows explorer navigate the the directory shown for Backups. With a little luck you might find a usable version there.
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
Post Reply