How to print the recordset produced by a filter table

English
,
#1

LO 7.1.2.2(64) WIN10 HSQL2.51

Hi,

I have a form which contains a textbox and a table control producing a set of filtered records where the textbox contains the criteria for the underlying filter table - see screenshot

image description
image description1383×785 54.8 KB

The datasource for the table control is a sql statement as below

SELECT “tbldiario”.*, “tbldiario”.“id” FROM “tbldiario” WHERE ( UPPER ( “journal” ) LIKE ‘%’ || UPPER ( :P1 ) || ‘%’ OR :P1 IS NULL )

Is there a way to redirect the output to some temporary table or query for the source for a report as the statement itself outside the form will prompt for
the :stuck_out_tongue: parameter?

Thanks for your thoughts

image description
image description1383×785 85.8 KB
Mockup.odb

Added original sample database from DACM

LIKE_user_input_filter.odb

#2

Hello,

You state:

…as the statement itself outside the form will prompt for the :stuck_out_tongue: parameter?

So how does it get it inside the form?

#3

Have edited the post to add another screenshot

#4

The only thing this shows is a partial SQL statement. Why use a parameter in a form? Lots of other ways to do this without a parameter. Even your question states a ‘filter table’ (which I don’t see). And why is there what appears to be a text search box if you are using a parameter? Other buttons? Very confusing.

#5

The textbox is tied to the filter table and the parameter is used in conjunction with variables so that the form is not read only.

CREATE CACHED TABLE PUBLIC.“tbldiario”(“id” INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,“timeStamp” TIMESTAMP DEFAULT CURRENT_TIMESTAMP,“journal” VARCHAR(7000))

CREATE CACHED TABLE PUBLIC.“Filter”(“FilterID” BOOLEAN NOT NULL PRIMARY KEY,“F1” VARCHAR(255))

#6

Trying to do a mockup db, 7 gives me the irrits, can no longer drag forms between dbs. End up with this form not not be read and can not delete it either.

#7

Have edited the post and added a mockup, ignore the other buttons for now. On occasions I end up with forms that cn not be read nor deleted, any idea what the remedy is here. Thought I could kill it by opening the odb, but the forms have only obj numbers, cn not find the name in the xml files.

#8

The question is not as simple as it may appear. From all I see, you have multiple conditions which need to be combined - possibly in a macro. Then write a query or insert the SQL in the report and execute the report.

More complicated than just print the result.

#9

Well, the conditions serve the purpose of searching by full word, substring regardless of upper or lowercase. Guess I can just print the screen if the records don’t exceed the height of the form. Thanks for your time anyway.

#10

OK. After a quick look at your sample, I am back to an earlier question of mine. Why are you using a Parameter? Just add a second field to the filter table!

#11

Am led to believe that normal filter table renders results read only, I need to be able to filter but also add new records even to a filtered result

#12

Any pointers on how to kill those corrupt forms ?

#13

The problem I have with this question is that the conditions are all appearing one-at-a-time. The entire process and what you are attempting should be presented at once. Gradually finding out information makes one wonder what else is missing? The extra field does not restrict anything. It should be nothing more that a placeholder to be accessed in the SQL. However the sample you presented doesn’t even ask for input for parameter. Again not certain how all this is to work and what result is expected. I do suspect this is possible as I presented. May try something of my own device a bit later.

As for the form, I edited the form, closed it then deleted it.

#14

Another look and I can’t make heads or tails of what you are actually doing. You have a main form and a sub form which has a link of “P1” - the parameter. This is also shown in your second image. Must link table field to table field. Parameter is not a field. To proceed, need clear steps as to what is actually wanted.

#15

@Ratslinger,

Basically all I need is a search form which allows searching by wildcard without the wildcard and also have the option to enter new records.
I did a multifield search feature a long time ago using the very old books sample database example with further help by another sample db from DACM

https://forum.openoffice.org/en/forum/viewtopic.php?f=100&t=42845&p=197503&hilit=+power+filter#p197503

Will attach the database explaining the purpose of the parmeters and variables in the edit

For the current dtabase I took the first form 1 Text Box Search as a guide.

Pls do not spend too much time on this as the form is working and can always screendump.

#16

Hello,

Sorry for all the confusion. Don’t know where you got filter table renders results read only.

Attached is a sample. Now uses just a query. Attached a report & button on form to run report (make it a bit easier to test).

Did some quick testing on a split DB - seems OK.

Sample ------ Mockup.odb

#17

@Ratslinger WOWWW, thank you! I seem to have an affinity to make simple things very complicated

#18

@gkick,

Just glad it appears to be what you wanted.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.