First time here? Check out the FAQ!
asked 2012-05-21 21:52:59 +0200cscj01
Ubuntu 11.10, MySQL 5.1.62. LibreOffice 3.4.4
I have a form with a number of sub-forms (6 to be exact). The master form and one of the sub-forms are populated from tables. The remaining 5 sub-forms are populated from queries. All sub-forms use the same master link field, and the link field is a foreign key in all the tables used directly or referenced by queries.
The sub-form populated from another table synchronizes properly on their link fields. The remaining 4, which are populated with queries do not. In fact, 2 do not populate at all, and the other 3 populate with the first occurrence of the condition in the query. In addition, the values of the 5 sub-forms that do not work correctly do not change when the master form is browsed.
The tables involved are named person, address, telephone, and email. The main form contains information from the person table and uses a person_id field as the key. The subform that is populated from a table uses the address table and has person_id as a foreign key. The other forms use queries for telephone numbers and email addresses and also have person_id as a foreign key. For example, the home telephone number subform uses the following query for population:
select t.number, t.person_id from telephone t where t.type = 'H'
and the data in the subform is number. This query populates the home telephone subform with the first occurrence of a home telephone and does not change when I browse through the person records. The same is true for the work (type = W) and mobile (type = M) subforms.
The email table contains 2 types, P and S. Their queries are similar to the telephone number queries, but these subforms are not populated at all.
In all cases, the link fields are person_id.
The definitions are all correct as far as I can tell (control values and form values; no events or filters). The queries are simple select statements, and they work properly when running them in edit mode.
I have tried deleting the offending sub-forms and adding them back. Nothing changes when I do this. I am not sure what to check next. Any help will be greatly appreciated.
answered 2012-05-21 23:12:56 +0200cscj01
I resolved this by creating views for each type in the tables telephone and email. I also had to set the "Analyse SQL command" to Yes. This seems to be a workaround rather than the proper approach, but it now works.
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-05-21 21:52:59 +0200
Seen: 297 times
Last updated: May 21 '12