Sub form does not return data from linked table

[glennp]

Just got oo connected to Postgres 9.1 by using the JDBC driver, and tried to set up my first OO form with a sub form to accomplish data entry into two linked tables residing in the Postgres9.1 database. ( yes Pk and unique constraints are set in the db) The parent table (public.person) loads all the records. The sub form (public.personal_history) loads nothing, and returns an error box that says: "The data content could not be loaded. The column index is out of range:1, number of columns:0". The sub form is a many to one relationship with the parent table, so has 1:M records for each unique record in the parent table.

FYI, The SQL statement behind the tables shows up in the error message as:

SELECT "roleid_pk", "personid_fk", "role", "description", "location", "orgid_fk", "circa", "commence_date", "end_date" FROM "person", "personal_history" WHERE ( ( "personid_pk" = "personid_fk" ) AND ( ( "personid_fk" = :link_from_personid_pk ) ) ) AND ( 0 = 1 )

The '(0=1) and clause is suspicious! why is it even there? I couldnt find access to the sql statment anywhere so couldnt test it.

Microsoft's error message workaround from SQL 2005 regarding "column index out of range:1, number of columns:0" says the star * (Select all) symbol causes this error message, and recommend specifying each field individually in the select statement ( in lieu of the *) symbol. I did a sql statement for a separate manually constructed form/subform field and got the same error message with no data returning to the subform.

So I actually have three questions:
1) how can I get the sub form to return data from the parent form?
2) what is this going on with this (0=1) statement at the end of the SQl statement mean?
3) how do I access the SQL statement hidden behind the forms? (dont see access to it anywhere)

Thanks!

[RPG]

Hello

Start study what a form does.
Use simple queries and use master and slave fields.
I think use this query in your subform

Code: Select all

SELECT "roleid_pk", "personid_fk", "role", "description", "location", "orgid_fk", "circa", "commence_date", "end_date" FROM "person", "personal_history" 

This must be done with the master slave fields

Code: Select all

( ( "personid_pk" = "personid_fk" ) AND ( ( "personid_fk" = :link_from_personid_pk ) ) )

Romke

[RPG]

Hello

I did see later that the query did contain two tables and not one As I suppose. But most of the time you use one table in the mainform and the second in the subform and the master slave fields are for the relation.

Romke

[glennp]

Hi Romke,
Yes, I did set the same master and slave fields in the control properties in each of the PK and foreign key fields, but still get the same error message. BTW, the SQL statement above was created by OO during the form wizard setup, so i do not know how to get at it to modify the AND (0=1) clause. any recommendations where the SQl statement can be found and edited?
Thanks!

[RPG]

Hello

Possible there is a filter active.

You can de-actived or delete the filter with a button on the navigation toolbar.

Romke

[Villeroy]

Try this: Bind the main form and the subform to one table each (no queries). Define the common fields (primary key and foreign key).
With this simplified setup, does the subform include the expected row sets as you move through the main form's row set?

[RPG]

Hello

When I reread your posts in this thread I have the idea you do not understand how forms are working you do suppose they work maybe in the same way as in MS access and maybe they work different.

Forms are based on SQL statements and display a query or table. When you want have more complicated forms then use masterforms and subforms. There is a connection from a subform to the masterform with the relation set with master field and slave field. This has nothing to do with the relation in your database.; this is as far I understand it.

http://user.services.openoffice.org/en/ ... 83&t=28235
In this link I point to several tutorials and it is a simple tutorial.

Romke

[mgg]

I've a problem. I use LibreOffice 4.0.5.2 as client for PostgreSQL 9.3 database.

I created 3 tables (A,B,C) , which have relations one to many.

Look as:

Code: Select all

A -> B
B -> C

Each of table have similar kolumn

Code: Select all

id serial
id_fk integer
value varchar

Table relations by column id to id_fk.

What is my problem?

I'm creating form.

Construct form:

Code: Select all

Main form - table A
    Subform1 - table B
      Subform2 - table C

When I add Link master fields to Link slave fields, because I add relation in my form, when I want to use this form (normal mode), I saw error

Code: Select all

   The column index is out of range: 1, number of columns: 0 SELECT * FROM "public"."B" WHERE ( ( "id_fk" = :link_from_id ) ) AND ( 0 = 1 )

What should I do?

Thank you!

[MTP]

Open your form in edit mode (in base, right-click on the name of the form, and choose "edit").
Right-Click on the problem object and choose "control". This will bring up a properties box.
On the "Data" tab, there will be a place for you to edit the SQL statement.

You may also want to become familiar with the form navigator (toolbar icon looks like a little form with a compass in the upper right). This gives you a visual of what forms and subforms you have going on, and which objects are part of which form and subform. From the form navigator, you can right-click and choose "properties" to open up the properties box.