[Solved] Search Form Problem

Creating and using forms

[Solved] Search Form Problem

Postby cwdavi1 » Fri Sep 13, 2019 10:09 pm

I need help debugging a new form. I copied the search form from viewtopic.php?f=100&t=42845#p197503. It works perfectly as is but when I change it for my table and query the search returns 0 records. I have made the minimum modifications to the form. My query has a subquery, both run correctly separate outside the form. I have read the oo debugging page but I need something simpler. All the logging instructions I've found involve compiling with debug flags on. I've stared at form properties and controls for my version and the original until I can't see anymore. Have I missed a logging option that would help? Can anyone give me any pointers on how to solve this?

Thank you, Wayne
Last edited by Hagar Delest on Wed Sep 18, 2019 1:41 am, edited 1 time in total.
Reason: tagged solved
Libreoffice 6.3 on Windows 10 Pro
Posts: 29
Joined: Fri Sep 13, 2019 9:47 pm

Re: Search Form Problem

Postby Villeroy » Sat Sep 14, 2019 1:56 pm

Without seeing your database, it is impossible to give any concrete advice on this complicated matter.
Short recipe: viewtopic.php?f=13&t=98623&p=474172#p474172

You need one filter table with enough columns to take the criteria and one distinct row with a distinct row number as primary key (no auto-value).
The filtering form fetches this distinct row only for editing. Everything except modification is disabled for this form. This way you can not tab away from that row nor delete the row by mistake.
The filtered subform is filtered by the stored values in the distinct row of the parent form. This form should have a refresh button (push button with action "Refresh") which also takes away the focus from the parent form. This way you store the modified parent before refreshing the subform. Make sure that the button is not attached to the parent form.
A modified form is stored
-- either by moving to another record
-- or by hitting a save button
-- or by moving the focus to another (sub-)form.
The refresh button takes the focus away from the modified parent form to the subform to be refreshed, thus saving the parent form, before it executes the refresh command which requeries the table data according to the parent form's stored data.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Posts: 28647
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Search Form Problem

Postby cwdavi1 » Sun Sep 15, 2019 6:25 am

Problem solved. I didn't understand how filter tables work, in spite of reading tons of documentation. My filter field was defined as int so when text was entered as the search parameter of course it didn't work. Probably not the last mistake I'll make.

I have a new question. I'm using a subquery and it's too slow:

Code: Select all   Expand viewCollapse view
SELECT "prod_DB"."aid" "Aid", "prod_DB"."type" "Type", "prod_DB"."language" "Language", "prod_DB"."title" "Title" FROM "prod_DB" WHERE :P1 IS NOT NULL AND "prod_DB"."aid" = ANY ( SELECT DISTINCT "prod_DB"."aid" FROM "prod_DB" WHERE ( UPPER ( "prod_DB"."title" ) LIKE UPPER ( '%' || :P1 || '%' ) OR :P1 IS NULL ) ) ORDER BY "prod_DB"."aid", "prod_DB"."language"

I think I need to do a join but I'm too rusty to figure it out, especially late at night. There are only 61,000 records in my test DB. I have indexed "aid" and "title" but the query is still too slow. I will greatly appreciate any pointers.

As an aside, I'm extremely happy that I decided to go with LibreOffice. I'm amazed at the level of support I've seen.

Thank you,

Last edited by robleyd on Sun Sep 15, 2019 6:37 am, edited 1 time in total.
Reason: Added Code tags
Libreoffice 6.3 on Windows 10 Pro
Posts: 29
Joined: Fri Sep 13, 2019 9:47 pm

Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests