[Solved] Search Form Problem

[cwdavi1]

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

[Villeroy]

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.

[cwdavi1]

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

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,

Wayne