[Solved] Form, which changing query parameters

[SpookyMM]

I now, that is possible use in query: SELECT * FROM "TableX" WHERE "Date">:Date_After.
But I can make a form, where will be Data fields or ListBoxes,
examle:

Table
ID | Name | Car | Date

Form
Listbox: Car
DateField: Date
DataField: Name

And when I choose this 3 parameters - then wil be generated a report.

Is that possible ?

[Villeroy]

http://user.services.openoffice.org/en/ ... hp?id=4690 (form and simple report)
Re: [Solved] Using Filter in Calc V3.1 with dates (forms and reports in spreadsheet)

[RPG]

Hello

Your question is not real clear to me..

But make a query with parameters in the way you want /need.
Use this query in a report
read this link.
I hope this helps you.

Romke

[SpookyMM]

Thank You Villeroy - Your example with form and simple report is solution for my question. RPG: I can't use statement ":parameter" - but Thank You for answer.

I have one question:
In Villeroy's example is one parameter: CHARS. This parameter is setting in table FILTER in first row.
When I can use 2 or more parameters what can I do ? I must create 2 or more tables like FILTER with one row, or one table with more rows, where I place more parameters ?
With first solution will be no problem, but when is possible - better is second solution (because in first solution will be more tables).
My question: how can I get in question suiteble row (parameter).
example:
Table CARS
ID | CAR | DRIVER

Table FILTER
PARAMETER | VALUE
.........................
NAME | JOHN
CAR | Porsche

How can I make question, which get for table CARS all rows with Driver=FILTER(Parameter=Name) and CAR=FILTER(Parameter=CAR) ?

[Villeroy]

The primary purpose of the filter table is to provide a binding for the filtering form control. As a secondary side effect subsequent queries, forms and reports can refer to the stored parameters in that table.
The other example (with a spreadsheet as "database medium") is a more elaborate example, filtering by more than one value in a distinct filter record with Filter.ID=0.

[SpookyMM]

Thank You Villeroy for help

[Villeroy]

In other words:

Code: Select all

SELECT * FROM "TableFilter" WHERE "ID"=0

Code: Select all

SELECT * FROM "TableX" WHERE "Date">:Date_After

Bind a main form to the first query with a date control bound to some date field in that distinct record with ID=0 and allow modification only for this form.
Add a subform bound to the second query and linked to the main form through "DateAfter" in the subform's query and "DateField" in the main form's query.
Both record sets are editable in the form because they are bound to a single table's record set including the primary key.

Code: Select all

SELECT "T".* FROM "TableX" AS "T", "TableFilter" AS "F"
WHERE "F"."ID"=0 AND "T"."Date">"F"."Date"

Unfortunately Base returns a read-only record set from this one because more than one table is involved, but it does not matter when using this for a report.

The following query may deliver a writable version of the same record set (untested):

Code: Select all

SELECT * FROM "TableX" 
WHERE "ID" IN (SELECT "ID" FROM "TableX" AS "T", "TableFilter" AS "F"
WHERE "F"."ID"=0 AND "T"."Date">"F"."Date")

[SpookyMM]

Thank you Villaroy !!
Everything is ok, but when I create more filter tables I must create more forms too.
Maybe can I create one form, and change value in more tables ?

[Villeroy]

Why? Create one filter table with a required ID number (primary key) and as many optional fields for dates, strings, booleans and numbers as you need for one set of filter criteria. My "pivot_times_categories.odb" has table "ListFilter" with fields "DateFrom", "DateTo", "Category"(text) and "Value"(decimal).
You can bind one form's filter to the row where "ID"=0 and another form's filter to the row where "ID"=1.

However, you may need another filter table for a multiple-row filter where "X" IN "F"."FieldX" and "FieldX" contains several criteria values, effectively used as (X1=X OR X2=X OR X3=X OR ...). Until now I never needed this scenario.

[SpookyMM]

Yes..Now I understand Everything is ok, Thank you very much Villeroy.

Have I one question now:

My query
SELECT "Sam_P"."id_o" FROM "Q1" AS "Q1", "Sam_P" AS "Sam_P" WHERE "Sam_P"."id_o" = "Q1"."I1"
I have text box, where I set I1.

I can do:
when I set Q1.I1=1, then:
SELECT "Sam_P"."id_o" FROM "Q1" AS "Q1", "Sam_P" AS "Sam_P" WHERE "Sam_P"."id_o" = 1
when I set Q1.I1=2, then:
SELECT "Sam_P"."id_o" FROM "Q1" AS "Q1", "Sam_P" AS "Sam_P" WHERE "Sam_P"."id_o" = 2
.
.
but, when I set Q1.I1=0, then:
SELECT "Sam_P"."id_o" FROM "Q1" AS "Q1", "Sam_P" AS "Sam_P"

Is possible that ?

[SpookyMM]

That's another example, because in your case is LIKE, so when is LIKE null, then shows all the 9,500 records - mayby same LIKE ''.
In my case ist "=" and that is another. When I set 0, than show no rows - because in table Sam_p is no row with 0.

[Villeroy]

http://www.1keydata.com/sql/sqllike.html
The usual joker chars * and ? have their equivalents % and _ in SQL. % stands for anything or nothing, _ stands for one arbitrary character.
LIKE is used with pattern matching, interpreting the % and _ as jokers:
'Villeroy' LIKE 'V%' => TRUE
'Villeroy' LIKE '_illeroy' => TRUE

whereas the literal comparison with = yields:
'Villeroy' = 'V%' => FALSE
'Villeroy' = '_illeroy' => FALSE

NULL is a special type of data (missing data). Any comparison with NULL returns NULL:
'A'=NULL => NULL
1=NULL => NULL
'A'>NULL => NULL
1<NULL => NULL

Therefore you've got to use the IS operator.
"Field X" IS NULL => TRUE or FALSE

[SpookyMM]

Thank for help