Re: [libreoffice-users] success: form with detail subform; working list boxes in main form

Hi :)
I think it's a good idea to post a bug-report about list-boxes only working from 
tables.  They really need to work from Queries too
http://wiki.documentfoundation.org/BugReport

I don't think there are any devs currently working on Base but there will be.  
There is a chance of documentation catching up with the app within this small 
window of opportunity.  It does need a lot of good people so it's good to see 
you building the team up by encouraging other good people :)
Good luck!
Regards from
Tom :)





________________________________
From: Tom Cloyd <tc@tomcloyd.com>
To: LibreOffice User's Help Forum <users@global.libreoffice.org>
Sent: Thu, 23 June, 2011 9:36:04
Subject: [libreoffice-users] success: form with detail subform; working list 
boxes in main form

This is very early in my learning period, but I seem to have achieved a small 
breakthrough.

It now appears that if list boxes are to work reliably, or at all, in a form 
with a subform, they must be created using raw tables - i.e. view or queries 
cannot be used. Likewise for the main form and the subform. Use only raw table, 
not views or queries. This means that one cannot take advantage of aliases set 
up in view or queries, but that's no great loss. Aliases mainly affect labels, 
in a form, and they can be altered via the control's property dialog.

A remaining problem is how to get sorted list box source tables. If I have a 
list box that has 250 possible items, I need them sorted, but I cannot see how 
to do this. So far, using a view to achieve this has resulted in screwy or 
totally nonfunctional list boxes. I suspect the SQL needs adjusting, but that's 
a bad solution for your average user. I shouldn't have to be an SQL wizard to 
get a sorted list box.

In any case, my initial, test case database is working perfectly. It going into 
immediate use, as it details storage of various things I own (books, clothes, 
etc.) in various containers, in various rooms, and even various buildings, etc.

So, the procedure I used tonight was this:

1. Set up main table, with foreign key field for one or more detail forms.

2. Set up at least one detail table.

3. Use the main menu Tool>Relationship functionality to link detail table 
primary key to relevant main table foreign key. (Do not know if this is 
absolutely necessary or not, but it doesn't hurt anything.)

4. Launch form designer wizard. Use the main and detail tables set up above. Do 
NOT use any view or query. Set up main table to display display one record at a 
time, as set of text boxes linked to record fields,. Set up the subform as a 
grid matrix (or whatever it's called), so multiple detail records will show. 
Main table order can be set as a property, and subform can be ordered once the 
form is executed, using sort options available in the interface of the activated 
(executing form); they will persist from session to session!

5. In the form designer, replace all main record field text boxes with list 
boxes, using the list box wizard (be sure the "use wizard" icon on side of page 
is depressed, to get the wizard). As before, use raw tables, not views.

6. In executing the form, one can click the icon at the bottom of the interface 
which has the tool tip description that begins "Activates an additional table 
view..." to get a very useful view of the main database at the top of the form. 
It is especially interesting to note that in this view, for every field to which 
one attaches a list box in the main form, the main table foreign key is replaced 
by the data field to which it refers, once you make a selection from a list box. 
This makes a database filled with foreign keys sudden very readable. Where the 
foreign keys have not been instantiated, it is readily apparent.

This seems to work without a problem (so far).

If this is the most I get Base to do for me, it's worth the price of admission 
and then some, but I suspect it can do more!

I hope this description of what I have found to work helps some other folks 
struggling with forms and list boxes, and I for damn sure an going to try to get 
the essential information into the documentation, if that proves to be 
appropriate.

All constructive comments welcome...

Tom





-- Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
-- 
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted