[oS-en] LibreOffice Base: is there a tutorial or doc for table views?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, Is there there a tutorial or doc for LO table views? I can't find it. I have created a table view, and it displays empty, so obviously I need a read of something. (I have a view created in rekall which works in rekall, but not in LO) - -- Cheers Carlos E. R. (from 15.3 x86_64 at Telcontar) -----BEGIN PGP SIGNATURE----- iHoEARECADoWIQQZEb51mJKK1KpcU/W1MxgcbY1H1QUCY1kZ9xwccm9iaW4ubGlz dGFzQHRlbGVmb25pY2EubmV0AAoJELUzGBxtjUfV5BAAmQFtdknhMl5JoBFO9abE /wtmzmOMAJ918hN0pu1syLpaZDRWKh4NUNsIRg== =3NX0 -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Wednesday, 2022-10-26 at 13:28 +0200, Carlos E. R. wrote:
Hi,
Is there there a tutorial or doc for LO table views? I can't find it. I have created a table view, and it displays empty, so obviously I need a read of something.
(I have a view created in rekall which works in rekall, but not in LO)
I forgot: If I click "help" when trying to create a Table View, I get: Could not find Help page (404). That is an error. Possible causes are: The page does not exist and must be created. The page exists, but the Help ID is wrong or missing. Use the Module, Contents, Index and Search selectors to find the right page. The following data could be helpful in locating the error: Help ID: dbaccess/ui/tablesjoindialog/grid1 And, no, I'm unable to search and find help in the help for createing table views. Only this: <file:///usr/share/libreoffice/help/en-US/text/shared/guide/data_tables.html?DbPAR=BASE> +++----------------------- Creating a New Table View Some database types support table views. A table view is a query that is stored with the database. For most database operations, a view can be used as you would use a table. Open the database file where you want to create the new table view. In the left pane of the database window, click the Tables icon. Click Create Table View. You see the View Design window, which is almost the same as the Query Design window. - -----------------------++- And that is all! :-/ Ah, the text " Query Design window." is a link which gives File not found Firefox can’t find the file at /usr/share/libreoffice/help/en-US/text/sdatabase/020010100.html?&DbPAR=BASE&System=UNIX. Check the file name for capitalization or other typing errors. Check to see if the file was moved, renamed or deleted. Brilliant help :-/ - -- Cheers, Carlos E. R. (from openSUSE 15.3 x86_64 at Telcontar) -----BEGIN PGP SIGNATURE----- iHoEARECADoWIQQZEb51mJKK1KpcU/W1MxgcbY1H1QUCY1kbkBwccm9iaW4ubGlz dGFzQHRlbGVmb25pY2EubmV0AAoJELUzGBxtjUfV3JwAn3EIeNQCzQ6lab8z3BlO 78jGtl8GAJ9xHxgLjWSnYzmteqsnCBDnnbE4HA== =7u3W -----END PGP SIGNATURE-----
On 10/26/22 06:35, Carlos E. R. wrote:
You see the View Design window, which is almost the same as the Query Design window. - -----------------------++-
And that is all! :-/
Documentation is always the last to get attention. LibreOffice documentation has never been that great, damn near impossible to find what you need (if it exists) and many times, it simply doesn't exist. If I recall, a table-view is just a temporary table that can exist following a query or join that creates it. Seems like you would need to either connect to the default name of whatever your database uses for table-view or be able to give the view a name and then connect to that. -- David C. Rankin, J.D.,P.E.
On 2022-10-27 23:14, David C. Rankin wrote:
On 10/26/22 06:35, Carlos E. R. wrote:
You see the View Design window, which is almost the same as the Query Design window. - -----------------------++-
And that is all! :-/
Documentation is always the last to get attention.
LibreOffice documentation has never been that great, damn near impossible to find what you need (if it exists) and many times, it simply doesn't exist.
If I recall, a table-view is just a temporary table that can exist following a query or join that creates it. Seems like you would need to either connect to the default name of whatever your database uses for table-view or be able to give the view a name and then connect to that.
No, the views that I have working work instantly all times. I didn't have to do anything else. -- Cheers / Saludos, Carlos E. R. (from 15.3 x86_64 at Telcontar)
On Wed, 26 Oct 2022 13:28:54 +0200 (CEST) "Carlos E. R." <robin.listas@telefonica.net> wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Hi,
Is there there a tutorial or doc for LO table views? I can't find it. I have created a table view, and it displays empty, so obviously I need a read of something.
Let me once again suggest asking on the LO list rather than the openSUSE list, for details about an LO product!
(I have a view created in rekall which works in rekall, but not in LO)
- -- Cheers
Carlos E. R. (from 15.3 x86_64 at Telcontar)
-----BEGIN PGP SIGNATURE-----
iHoEARECADoWIQQZEb51mJKK1KpcU/W1MxgcbY1H1QUCY1kZ9xwccm9iaW4ubGlz dGFzQHRlbGVmb25pY2EubmV0AAoJELUzGBxtjUfV5BAAmQFtdknhMl5JoBFO9abE /wtmzmOMAJ918hN0pu1syLpaZDRWKh4NUNsIRg== =3NX0 -----END PGP SIGNATURE-----
On 2022-10-28 01:14, Dave Howorth wrote:
On Wed, 26 Oct 2022 13:28:54 +0200 (CEST) "Carlos E. R." <> wrote:
Hi,
Is there there a tutorial or doc for LO table views? I can't find it. I have created a table view, and it displays empty, so obviously I need a read of something.
Let me once again suggest asking on the LO list rather than the openSUSE list, for details about an LO product!
That is the last resource. And that help doesn't work are surely openSUSE bugs or problems. -- Cheers / Saludos, Carlos E. R. (from 15.3 x86_64 at Telcontar)
On 2022-10-28 02:03, Carlos E. R. wrote:
On 2022-10-28 01:14, Dave Howorth wrote:
On Wed, 26 Oct 2022 13:28:54 +0200 (CEST) "Carlos E. R." <> wrote:
Hi,
Is there there a tutorial or doc for LO table views? I can't find it. I have created a table view, and it displays empty, so obviously I need a read of something.
Let me once again suggest asking on the LO list rather than the openSUSE list, for details about an LO product!
That is the last resource.
And that help doesn't work are surely openSUSE bugs or problems.
Well, I am asking at the web forum at "https://ask.libreoffice.org", and I got helpful answers. But my post has been marked as spam and hidden! it is here: <https://ask.libreoffice.org/t/lo-base-i-have-problems-with-creating-a-view> -- Cheers / Saludos, Carlos E. R. (from 15.3 x86_64 at Telcontar)
On Fri, 28 Oct 2022 23:58:49 +0200 "Carlos E. R." <robin.listas@telefonica.net> wrote:
On 2022-10-28 02:03, Carlos E. R. wrote:
On 2022-10-28 01:14, Dave Howorth wrote:
On Wed, 26 Oct 2022 13:28:54 +0200 (CEST) "Carlos E. R." <> wrote:
Hi,
Is there there a tutorial or doc for LO table views? I can't find it. I have created a table view, and it displays empty, so obviously I need a read of something.
Let me once again suggest asking on the LO list rather than the openSUSE list, for details about an LO product!
That is the last resource.
And that help doesn't work are surely openSUSE bugs or problems.
Well, I am asking at the web forum at "https://ask.libreoffice.org", and I got helpful answers.
But my post has been marked as spam and hidden!
I did suggest the mailing list, not the forum!
it is here:
<https://ask.libreoffice.org/t/lo-base-i-have-problems-with-creating-a-view>
I gave up at page two that does not exist!
On 2022-10-29 01:20, Dave Howorth wrote:
On Fri, 28 Oct 2022 23:58:49 +0200 "Carlos E. R." <> wrote:
On 2022-10-28 02:03, Carlos E. R. wrote:
On 2022-10-28 01:14, Dave Howorth wrote:
On Wed, 26 Oct 2022 13:28:54 +0200 (CEST) "Carlos E. R." <> wrote:
Well, I am asking at the web forum at "https://ask.libreoffice.org", and I got helpful answers.
But my post has been marked as spam and hidden!
I did suggest the mailing list, not the forum!
Oh. Somebody else told me to use the forum, and that's the one I remembered.
it is here:
<https://ask.libreoffice.org/t/lo-base-i-have-problems-with-creating-a-view>
I gave up at page two that does not exist!
It opens fine here, but it gets converted/redirected to: <https://ask.libreoffice.org/t/lo-base-i-have-problems-with-creating-a-view/83421> A lot of my post is "hidden", you have to click to unhide on "View ignored content." -- Cheers / Saludos, Carlos E. R. (from 15.3 x86_64 at Telcontar)
On 2022-10-26 13:28, Carlos E. R. wrote:
Hi,
Is there there a tutorial or doc for LO table views? I can't find it. I have created a table view, and it displays empty, so obviously I need a read of something.
(I have a view created in rekall which works in rekall, but not in LO)
I found a tutorial. A video, in hindi. I watched it, at least I see the images, but they are not complex enough. And the menus are not what I get. And it appears it is the only tutorial in existence that mentions "views" at all. <https://www.google.com/search?client=firefox-b-e&q=libreoffice+base%3A+how+to+create+views%3F> Anyway, I know why mine doesn't work. See photo: https://susepaste.org/11717952 I have a library table. Focus on Tittle, Autor and Collection, so far. The Autor name is in another table, linked. Same for Collection. If I create a view with only Tittle and Autor: as far as I can see, I get all the books. But the instant I add "collection" table, only those books that have a collection entry show! It generates this SQL - I can not even read it, but some people may: SELECT "Libros"."Titulo", "Libros"."Subtitulo", "Colecciones"."Coleccion", "Libros"."NumEnCol" FROM "Biblioteca"."Libros" AS "Libros", "Biblioteca"."Autores" AS "Autores", "Biblioteca"."Colecciones" AS "Colecciones" WHERE "Libros"."Autor" = "Autores"."Indice" AND "Colecciones"."Indice" = "Libros"."Coleccion" When I manage to double click on the line in the graph that connects Collections to Books, I get the small window shown in the photo. It seems that if I change the type to "Right join" I get the correct result. A text says (hand copied): Contains ALL records from table 'Libros' but only records from table 'Colecciones' where the values in the related fields are matching. Please note that some databases may not support this type. I have to slowly add to the graph each table and connection, and test if it produces the right result, one by one. One nuance of LO is that once I save the table view and close it, if I reopen for edit, I never get this display of tables again. I have to start again from scratch, a new table view. Well, I had it all done, then tried to click to view the SQL, and the thing was destroyed. AH! I clicked on "Clear query". Ok... At least now I know what I have to do. ... Ok... Finally, I get a working view: <https://susepaste.org/75164811> But it does not allow me to save. Clicking on "more" produces this text: SQL Status: 42S21 Error code: 1060 Duplicate column name 'Autor' /home/abuild/rpmbuild/BUILD/libreoffice-7.3.6.2/connectivity/source/drivers/mysqlc/mysqlc_general.cxx:119 That's Chinese to me, I don't understand a word. It works, I get the view with all the data I wanted, but I'm not allowed to save it!? :-/ The SQL generated is this: SELECT "Libros"."Indice", "Libros"."Titulo", "Libros"."Subtitulo", "Libros"."Comentario", "Colecciones"."Coleccion", "Libros"."NumEnCol", "Autores"."Autor", "Autores_1"."Autor", "Autores_2"."Autor", "Generos"."Generos", "Libros"."NumPartes", "Libros"."F_creacion", "Libros"."F_1a_edici", "Libros"."F_edicion", "Libros"."F_adquis", "Libros"."C_adquis", "Adquisicion"."Adquisicion", "Libros"."Cmnt_adquis", "Editoriales"."Editorial", "Libros"."Titulooriginal", "Traductores"."Traductor", "Libros"."Unfinished", "Libros"."NoLeido" FROM { oj "Biblioteca"."Editoriales" AS "Editoriales" RIGHT OUTER JOIN "Biblioteca"."Libros" AS "Libros" ON "Editoriales"."Clave" = "Libros"."Editorial" LEFT OUTER JOIN "Biblioteca"."Colecciones" AS "Colecciones" ON "Colecciones"."Indice" = "Libros"."Coleccion" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores" ON "Autores"."Indice" = "Libros"."Autor" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores_1" ON "Autores_1"."Indice" = "Libros"."SegundoAutor" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores_2" ON "Autores_2"."Indice" = "Libros"."TercerAutor" LEFT OUTER JOIN "Biblioteca"."Generos" AS "Generos" ON "Generos"."Clave" = "Libros"."Genero" LEFT OUTER JOIN "Biblioteca"."Adquisicion" AS "Adquisicion" ON "Adquisicion"."Indice" = "Libros"."L_adquis" LEFT OUTER JOIN "Biblioteca"."Traductores" AS "Traductores" ON "Traductores"."clave" = "Libros"."Traductor" } I see some joins have been changed to left on my back, so I try to edit them on the graph. And if I do, the View stops working! Ok, I copy paste the SQL back from this email to the box, and I get the working view again. I know it works, because I see 304 books, which is at it should. Do you know what is wrong in that SQL so that I'm allowed to save the view? I will leave the View open in case some of you knows or guesses ;-) -- Cheers / Saludos, Carlos E. R. (from 15.3 x86_64 at Telcontar)
On 2022-10-28 14:34, Carlos E. R. wrote:
On 2022-10-26 13:28, Carlos E. R. wrote:
Hi,
...
Well, I had it all done, then tried to click to view the SQL, and the thing was destroyed. AH! I clicked on "Clear query". Ok... At least now I know what I have to do.
...
Ok... Finally, I get a working view:
<https://susepaste.org/75164811>
But it does not allow me to save. Clicking on "more" produces this text:
SQL Status: 42S21 Error code: 1060
Duplicate column name 'Autor' /home/abuild/rpmbuild/BUILD/libreoffice-7.3.6.2/connectivity/source/drivers/mysqlc/mysqlc_general.cxx:119
Well, I asked on <https://ask.libreoffice.org/t/lo-base-i-have-problems-with-creating-a-view/> and someone named Villeroy told me I needed: ]> Try alias names for all duplicates. For instance: ]> “Autores”.“Autor”, ]> “Autores_1”.“Autor” AS “Autor1”, ]> “Autores_2”.“Autor” AS “Autor2”, That was the trick to save, but the query did not produce results. I found out that instead of editing the SQL, I should add an alias in design view, but in a new view, as I was adding the entries. Accidentally I closed the view (after saving it), and it is impossible to return to the table view. I was told: ]> Indeed, LibreOffice can generate views but once they are stored in the database backend, you can’t edit them directly. So I created a new one in minutes, taking into account all I had learned, and finally it all worked (unless I missed something in my joy). I was told this very interesting information: ]> Create a query instead of a view. Create a new query in SQLview, paste your above SQL and add the aliases. The query is stored in the Base document. If there is any reason to store the query in the MySQL database (e.g. to make it usable from Rekall or whatever), you right-click the query icon and choose “Create as view …”. This “exports” the Base query to the underlying database.
When Base interpretes a query with duplicate field names, Base silently adds subsequent numbers to the names (Name1, Name2 etc.) wheras MySQL raises the error you mentioned.
There is a whole chapter of documentation on SQl View, so I will indeed have a look :-) -- Cheers / Saludos, Carlos E. R. (from 15.3 x86_64 at Telcontar)
On 2022-10-29 00:47, Carlos E. R. wrote:
On 2022-10-28 14:34, Carlos E. R. wrote:
On 2022-10-26 13:28, Carlos E. R. wrote:
...
I was told this very interesting information:
]>> Create a query instead of a view. Create a new query in SQLview, ]>> paste your above SQL and add the aliases. The query is stored in the ]>> Base document. If there is any reason to store the query in the MySQL ]>> database (e.g. to make it usable from Rekall or whatever), you ]>> right-click the query icon and choose “Create as view …”. This “exports” ]>> the Base query to the underlying database. ]>> When Base interpretes a query with duplicate field names, Base ]>> silently adds subsequent numbers to the names (Name1, Name2 etc.) ]>> wheras MySQL raises the error you mentioned. Indeed, I used "Create a query in design view" (Query section), before reading the documentation, and I created a working query in minutes, that shows all my 304 books with all the fields, AFAICS. The query saved without a hiccup, and on open to edit it shows again my original graphical design. I'm very happy :-) I don't get what is the difference between views and queries, though. I suspect that one is that a view is saved in the database (in mysql), while the query is saved in the LibreOffice:base file. (The trick Rekall did was that it saves an extra table named __RekallObjects that probably contains the design views) -- Cheers / Saludos, Carlos E. R. (from 15.3 x86_64 at Telcontar)
participants (3)
-
Carlos E. R. -
Dave Howorth -
David C. Rankin