Re: [libreoffice-users] LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
On 02/22/2014 01:11 PM, Jim Seymour wrote: Hi There, I've researched and experimented with this, and I'm out of ideas. For starters: LibréOffice 3.5.7.2, Build ID: 350m1 (Build: 2) Before anybody suggests a newer version: I'm currently on Ubuntu 10.04 LTS (Lucid Lynx), so I'm stuck with it. (Even my much newer Linux Mint desktop install at work is still 3.x, I believe.) I have a database with a couple tables, as in create table hdwr_assets ( atag text primary key, serno text unique, status_id int references hdwr_status, . . . ); -- e.g.: deployed, in stock create table hdwr_status ( status_id serial primary key, descr textnot null, ); The plan was to create a form that could query and alter hardware assets, selecting values for status_id from a drop-down list, the available selection being from the hardware status table. Near as I can tell: The Forms Wizard simply isn't that smart? Interestingly: A Query created in Design View DTRT, wrt FK references, but I can't persuade the Forms Wizard to do so. In fact: FK references in the Forms Wizard appear to be thoroughly broken--at least in the version of LO I have? Is there any way to accomplish what I want, or am I going to have to resort to Real Coding (Java, HTML+PHP+JS or whatever)? Thanks, Jima There is a difference between a database form and a query or view. The last two objects are used to manipulate the data of the database to produce an output in table form: columns (fields) and rows (records). This is what you really want to do: use the data in your tables to produce and output that is useful for your purposes. Forms are Writer documents that can access data in table form and display it. You can also use forms to input data into the underlying table. It will display a query but not act like one. So, you have the query that you need to provide you with the information. Use the form wizard to display it. Use the query as the data source. The first page of this wizard has a drop down list of data sources (tables or queries). --Dan -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ 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
Re: [libreoffice-users] LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
Jim Is it that you want a single form, comprised of sub forms, whereby you can filter assets according to their status i.e. deployed or in stock; from the results given select a specific asset which presents the result, of that one asset, in a sub form from which the asset can be amended? Alex On Saturday 22 Feb 2014 13:11:52 Jim Seymour wrote: Hi There, I've researched and experimented with this, and I'm out of ideas. For starters: LibréOffice 3.5.7.2, Build ID: 350m1 (Build: 2) Before anybody suggests a newer version: I'm currently on Ubuntu 10.04 LTS (Lucid Lynx), so I'm stuck with it. (Even my much newer Linux Mint desktop install at work is still 3.x, I believe.) I have a database with a couple tables, as in create table hdwr_assets ( atag text primary key, serno text unique, status_id int references hdwr_status, . . . ); -- e.g.: deployed, in stock create table hdwr_status ( status_id serial primary key, descr textnot null, ); The plan was to create a form that could query and alter hardware assets, selecting values for status_id from a drop-down list, the available selection being from the hardware status table. Near as I can tell: The Forms Wizard simply isn't that smart? Interestingly: A Query created in Design View DTRT, wrt FK references, but I can't persuade the Forms Wizard to do so. In fact: FK references in the Forms Wizard appear to be thoroughly broken--at least in the version of LO I have? Is there any way to accomplish what I want, or am I going to have to resort to Real Coding (Java, HTML+PHP+JS or whatever)? Thanks, Jim -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ 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
Re: [libreoffice-users] LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
Alex, Thanks for the follow-up. Ideally there'd be one form for the asset(s), wherein each value could be entered or altered, the ones being foreign key references presenting a list from the desired column in the table to which the FK references. For example: I can do an ad hoc query that looks roughly like select ..., s.desc as status, ... from hdwr_assets h left join hdwr_status s on s.status_id = h.status_id...; and a field named status will show in the output. Ideally, I'd like to be able to do that. In fact: I *can* do that in Queries - Create Query in Design View), but not in a form? Behind the scenes: When you clicked on a list box for status in the assets form, a list derived from descr in the status table would be presented. When a selection is made, the related status_id value would be placed in the status_id field for the selected hardware asset. A sub-form would (could?) be presented, allowing one to alter the status table. Btw: When I try to associate hdwr_assets.status_id to hdwr_status.status_id using the Forms Wizard, execution results in Error: The data content could not be loaded. Error: SQL Status: 22023 The column index is out of range: 1, number of columns: 0. Information: The SQL command leading to this error is: SELECT * FROM public.hdwr_status WHERE ( status_id = :link_from_status_id ) Which is not valid SQL. I would have thought this functionality would be present. Even the most basic of database normalization in the most trivial of databases is going to result in multiple tables and FK references. Thanks, Jim On Sun, 23 Feb 2014 09:22:32 + Alex McMurchy mcmurchy1917-libreoff...@yahoo.co.uk wrote: Jim Is it that you want a single form, comprised of sub forms, whereby you can filter assets according to their status i.e. deployed or in stock; from the results given select a specific asset which presents the result, of that one asset, in a sub form from which the asset can be amended? Alex On Saturday 22 Feb 2014 13:11:52 Jim Seymour wrote: Hi There, I've researched and experimented with this, and I'm out of ideas. For starters: LibréOffice 3.5.7.2, Build ID: 350m1 (Build: 2) Before anybody suggests a newer version: I'm currently on Ubuntu 10.04 LTS (Lucid Lynx), so I'm stuck with it. (Even my much newer Linux Mint desktop install at work is still 3.x, I believe.) I have a database with a couple tables, as in create table hdwr_assets ( atag text primary key, serno text unique, status_id int references hdwr_status, . . . ); -- e.g.: deployed, in stock create table hdwr_status ( status_id serial primary key, descr textnot null, ); The plan was to create a form that could query and alter hardware assets, selecting values for status_id from a drop-down list, the available selection being from the hardware status table. Near as I can tell: The Forms Wizard simply isn't that smart? Interestingly: A Query created in Design View DTRT, wrt FK references, but I can't persuade the Forms Wizard to do so. In fact: FK references in the Forms Wizard appear to be thoroughly broken--at least in the version of LO I have? Is there any way to accomplish what I want, or am I going to have to resort to Real Coding (Java, HTML+PHP+JS or whatever)? Thanks, Jim -- Note: My mail server employs *very* aggressive anti-spam filtering. If you reply to this email and your email is rejected, please accept my apologies and let me know via my web form at http://jimsun.LinxNet.com/contact/scform.php. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ 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
Re: [libreoffice-users] LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
Jim You stated that you could generate a query to do what you require. Have you based your form on the query? Alex On Sunday 23 Feb 2014 10:05:11 Jim Seymour wrote: Alex, Thanks for the follow-up. Ideally there'd be one form for the asset(s), wherein each value could be entered or altered, the ones being foreign key references presenting a list from the desired column in the table to which the FK references. For example: I can do an ad hoc query that looks roughly like select ..., s.desc as status, ... from hdwr_assets h left join hdwr_status s on s.status_id = h.status_id...; and a field named status will show in the output. Ideally, I'd like to be able to do that. In fact: I *can* do that in Queries - Create Query in Design View), but not in a form? Behind the scenes: When you clicked on a list box for status in the assets form, a list derived from descr in the status table would be presented. When a selection is made, the related status_id value would be placed in the status_id field for the selected hardware asset. A sub-form would (could?) be presented, allowing one to alter the status table. Btw: When I try to associate hdwr_assets.status_id to hdwr_status.status_id using the Forms Wizard, execution results in Error: The data content could not be loaded. Error: SQL Status: 22023 The column index is out of range: 1, number of columns: 0. Information: The SQL command leading to this error is: SELECT * FROM public.hdwr_status WHERE ( status_id = :link_from_status_id ) Which is not valid SQL. I would have thought this functionality would be present. Even the most basic of database normalization in the most trivial of databases is going to result in multiple tables and FK references. Thanks, Jim On Sun, 23 Feb 2014 09:22:32 + Alex McMurchy mcmurchy1917-libreoff...@yahoo.co.uk wrote: Jim Is it that you want a single form, comprised of sub forms, whereby you can filter assets according to their status i.e. deployed or in stock; from the results given select a specific asset which presents the result, of that one asset, in a sub form from which the asset can be amended? Alex On Saturday 22 Feb 2014 13:11:52 Jim Seymour wrote: Hi There, I've researched and experimented with this, and I'm out of ideas. For starters: LibréOffice 3.5.7.2, Build ID: 350m1 (Build: 2) Before anybody suggests a newer version: I'm currently on Ubuntu 10.04 LTS (Lucid Lynx), so I'm stuck with it. (Even my much newer Linux Mint desktop install at work is still 3.x, I believe.) I have a database with a couple tables, as in create table hdwr_assets ( atag text primary key, serno text unique, status_id int references hdwr_status, . . . ); -- e.g.: deployed, in stock create table hdwr_status ( status_id serial primary key, descr textnot null, ); The plan was to create a form that could query and alter hardware assets, selecting values for status_id from a drop-down list, the available selection being from the hardware status table. Near as I can tell: The Forms Wizard simply isn't that smart? Interestingly: A Query created in Design View DTRT, wrt FK references, but I can't persuade the Forms Wizard to do so. In fact: FK references in the Forms Wizard appear to be thoroughly broken--at least in the version of LO I have? Is there any way to accomplish what I want, or am I going to have to resort to Real Coding (Java, HTML+PHP+JS or whatever)? Thanks, Jim -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ 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
Re: [libreoffice-users] LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
[N.B.: Switching to in-line reply format to eliminate the cruft and because the question stands on its own.] On Sun, 23 Feb 2014 16:42:44 + Alex Kempshall alexkempsh...@btinternet.com wrote: Jim You stated that you could generate a query to do what you require. Have you based your form on the query? [big snip] Thanks for the follow-up, Alex. (Another Alex. I'm sensing a trend, here ;).) If I understand you correctly: Yes. In fact: I made it far simpler than queries I can easily do ad hoc with the PostgreSQL command-line interface (psql). For example: Here's a VIEW (a VIEW, in Postgresql, is a query that's saved, such that it looks like a table) that employs several LEFT JOINs, the last three of which are simply to get to the hostname(s) associated with a hardware asset: SELECT a.atag, h.hostname, a.owner, s.descr AS status, d.mfgr, d.model, a.comments FROM hdwr_assets a LEFT JOIN hdwr_status s ON s.status_id = a.status_id LEFT JOIN asset_descr d ON d.descr_id = a.descr_id LEFT JOIN mac_addrs m ON m.atag = a.atag LEFT JOIN ip_addrs i ON i.mac_addr = m.mac_addr LEFT JOIN hostnames h ON h.ip_addr::inet = i.ip_addr::inet; Why the indirection/abstraction? Because there can be multiple MAC addrs/asset, multiple IPs/MAC, and multiple hostnames/IP. *That* might be a bit much to expect of a graphical query/form design tool. (Tho it sure would be nice if it was handled.) All I'm trying to do, so far, is a single level of indirection, as it were. (That is also in that query. You can see it in the 's.descr as status and ON status_id bits.) Regards, Jim -- Note: My mail server employs *very* aggressive anti-spam filtering. If you reply to this email and your email is rejected, please accept my apologies and let me know via my web form at http://jimsun.LinxNet.com/contact/scform.php. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ 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
[libreoffice-users] LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins
Hi There, I've researched and experimented with this, and I'm out of ideas. For starters: LibréOffice 3.5.7.2, Build ID: 350m1 (Build: 2) Before anybody suggests a newer version: I'm currently on Ubuntu 10.04 LTS (Lucid Lynx), so I'm stuck with it. (Even my much newer Linux Mint desktop install at work is still 3.x, I believe.) I have a database with a couple tables, as in create table hdwr_assets ( atag text primary key, serno text unique, status_id int references hdwr_status, . . . ); -- e.g.: deployed, in stock create table hdwr_status ( status_id serial primary key, descr textnot null, ); The plan was to create a form that could query and alter hardware assets, selecting values for status_id from a drop-down list, the available selection being from the hardware status table. Near as I can tell: The Forms Wizard simply isn't that smart? Interestingly: A Query created in Design View DTRT, wrt FK references, but I can't persuade the Forms Wizard to do so. In fact: FK references in the Forms Wizard appear to be thoroughly broken--at least in the version of LO I have? Is there any way to accomplish what I want, or am I going to have to resort to Real Coding (Java, HTML+PHP+JS or whatever)? Thanks, Jim -- Note: My mail server employs *very* aggressive anti-spam filtering. If you reply to this email and your email is rejected, please accept my apologies and let me know via my web form at http://jimsun.LinxNet.com/contact/scform.php. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ 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
