[Solved] Using Form Properties

[ouchd'greyt]

Hi everyone,

please help me, i'm just a beginner,,i just want to know how did it happened in Form Properties.?

hurting my head how to show or view with the matter

how can i showed or viewed the Link with master link

Thanks..

[Villeroy]

You are referring to my spreadsheet download/file.php?id=31689 linked to my database download/file.php?id=31296 as "PowerFilter"?
Tools>Options>Databases>Registered DBs... register PowerFilter.odb as PowerFilter.
---------------------------------------------------------------------
Create the main form for the filter criteria
Data source PowerFilter, content type SQL, content: SELECT * FROM "Filter" WHERE FID=1 [2 dates and 2 integer in record number 1 of table "Filter"]
---------------------------------------------------------------------
Open the form navigator (toolbar "Form Design", button #6)
Right-click your criteria form and add a new form which will be a dependent subform on that form.
Now fill out the form properties in the data tab.
Data source PowerFilter, content type Query, choose the right query name. A query name is perfectly equivalent to an SQL string but more handy for testing and re-using.
The master/slave fields are a little bit tricky.
Enter the 4 master fields separated by Shift+Enter without entering any quotes. These are field names from the parent form.
Enter the 4 slave fields separated by Shift+Enter without entering any quotes. These are parameter names from the subform's query to be substituted by the values from corresponding parent fields.

[ouchd'greyt]

Hi Villeroy,

Thank you for your response,appreciated on it.
but, what do you mean in "SQL string but more handy for testing and re-using."

But, somehow, i understand a little bit now, and question why do we need to input field name/ parameter name in the master/slave fields?

Thanks you so much!

[Villeroy]

The subforum for Base examples containes dozends of examples with forms, subforms and list boxes. Most subforms show data related to the selected main form record. This way you represent the related tables of a relational database within forms. You can edit data of some table represented by the main form and related records from other tables.

One of the most frequently asked questions about Base forms is about the possibility to use form controls for filter criteria.
First answer is that form controls are made for editing field data rather than anything else.
The second answer to this question explains the built-in form based filter mechanism which switches your form into a "criteria input mode" and shows the filtered records when you switch back to normal mode.
"Power filtering" is the third answer. If form controls edit field data and subforms show data matching the currently selected data of the parent form then you can put the criteria into a distinct dummy record of some table, make shure that this distinct record is always the selected record and then filter the actual data by that dummy record. This way you create your own filtering tool combining a main form for the filter criteria and a subform for the filter result. Since the criteria are stored in a distinct addressable record, you can use the same criteria for reports, other forms in Calc, Writer or elsewhere.
----------------------------------------------------------------------------
Why using a parameter query? Since Base can only edit one record of one table at a time, Base can only edit a record set (query) if the query is based on a single table including its primary key.
Open the queries in my database.
qAll merges all the text data from all three tables by their common ID numbers. It is not editable:

Code: Select all

SELECT "D"."D" AS "Date", "P"."N" AS "Person Name", "C"."N" AS "Category", "D"."V" AS "Value" 
FROM "Categories" AS "C", "Data" AS "D", "Persons" AS "P" 
WHERE "C"."ID" = "D"."CID" AND "P"."ID" = "D"."PID"

qFiltered selects all data for the filtered database range in the spreadsheet. It uses all tables and record #2 of the filter table:

Code: Select all

SELECT "D"."D" AS "Date", "P"."N" AS "Person Name", "C"."N" AS "Category", "D"."V" AS "Value" 
FROM "Categories" AS "C", "Data" AS "D", "Persons" AS "P", "Filter" AS "F" 
WHERE "C"."ID" = "D"."CID" AND "P"."ID" = "D"."PID" AND "F"."FID" = 2 
  AND ( "D"."PID" = "F"."INT1" OR "F"."INT1" IS NULL ) 
  AND ( "D"."CID" = "F"."INT2" OR "F"."INT2" IS NULL ) 
  AND ( "D"."D" >= "F"."D1" OR "F"."D1" IS NULL ) AND ( "D"."D" <= "F"."D2" OR "F"."D2" IS NULL ) 
ORDER BY "Date" DESC

selects all the text data from all 3 data tables where matching any given data in row #2 of the filter table

qFiltered_Form is the record which is shown in the editable data form grid.

Code: Select all

SELECT "PID", "CID", "D", "V", "ID"
FROM "Data" 
WHERE ( "PID" = :paramP OR :paramP IS NULL ) 
  AND ( "CID" = :paramC OR :paramC IS NULL ) 
  AND ( "D" >= :paramD1 OR :paramD1 IS NULL ) 
  AND ( "D" <= :paramD2 OR :paramD2 IS NULL )

In the form grid, the person ID "PID" and the category ID "CID" are represented by 2 listboxes which display the person or category name of the respective PID or CID. "ID" is the primary key of the "Data" table. The FROM clause includes one table "Data". The record set is editable. You edit one table "Data" which is filtered by 4 parameters. The form/subform mechanism substitutes the 4 parameters with the parent form's corresponding field values.
Master-->Slave (meaning)
----------------------------------------
INT1 --> paramP (the person ID)
INT2 --> paramC (the category ID)
D1 --> paramD1 (the from-date)
D2 --> paramD2 (the until-date)

qListboxCategories

Code: Select all

SELECT "N", "ID" FROM "Categories" ORDER BY "N" ASC

selects all categories with the names in the first field and the ID in the second field ordered by name. This is re-used in all listboxes where categories are shown. Of course you can define the above SELECT statement as SQL statement to each list box but it saves a little bit of work when you simply use the predefinde query named "qListboxCategories".
Same with "qListboxPersons" which fills all listboxes where you are supposed to select a person.

The last query "qMinMax" has informational character. It shows the range of dates stored in the date field of the data table, just in case you wonder about applicable filter criteria.

Code: Select all

SELECT MIN( "Data"."D" ) AS "Min Date", MAX( "Data"."D" ) AS "Max Date" FROM "Data"

[ouchd'greyt]

Thank you again, very much appreciated for this and thank you for your time to explain this one.
but do you have any simple sample regarding textbox searching not listbox that's for Person Name, a lots of trying, a lots thinking and the result was simply nothing when doing it.
i watched a lot of video sample and on youtube, but their always doing it in Writer not in the Calc, and i want to do it on Calc.

Need help.
Thank you very much.

[Villeroy]

The forms are the same in Writer and Calc. The forms that are embedded in a Base document use Writer.
Get toolbars "Form Design" and "Form Controls", get the form navigator window (button #5 on "Form Design") and build up your form hierarchy. The underlying document has nothing to do with it. It is just a "carrier".

[ouchd'greyt]

Villeroy: thanks a lot (^_^)