Ask Your Question

Patrick Demets's profile - activity

2021-05-02 22:16:32 +0200 received badge  Notable Question (source)
2021-04-18 02:15:03 +0200 marked best answer base join error updating current record

Apparently, my SQL is rusty. I need help (again!)

Cutting down to bare minimum, I have two tables (# = PK; * = not null; & = FK):

book (#id, *book_title, &author_id)    -- note: author_id is nullable
author (#id, *name)

book FK: book.author_id = author.id

My (minimum) query:

SELECT book.id, book.book_title, book.author_id,
  author.id, author.name
FROM book
LEFT OUTER JOIN author ON book.author_id = author.id;

Query returns expected results (i.e., "book" records with authors and without authors), in Datasheet view (or whatever it's called in LO Base). However, when I try to update the column (in Datasheet view) "book.author_id", I get the error message "Error updating the current record". I can see the author's name fetched and populated in the Datasheet, but when I move to another row, I get the error.

AFAIK, if the PK of the referenced table and the FK of the referencing table (i.e., the "link") are both in the query, then it should be updateable/editable.

What am I doing wrong? Thx

2021-04-18 02:15:00 +0200 commented answer base join error updating current record

Thanks again Ratslinger! I didn't realize I was looking at that very same issue you mentioned; I didn't really grok it,

2021-04-17 04:58:54 +0200 asked a question base join error updating current record

base join error updating current record Apparently, my SQL is rusty. I need help (again!) Cutting down to bare minimum,

2021-04-11 04:03:40 +0200 received badge  Popular Question (source)
2021-04-11 01:06:50 +0200 commented answer Base form sort on lookup value, not key value?

@Ratslinger, Again, I owe you many thanks (for pointing out the obvious that I clearly missed). I glossed over the part

2021-04-11 01:01:55 +0200 marked best answer Base form sort on lookup value, not key value?

Windows 10 Pro; LO 6.4.7.2; PostgreSQL 10

In Base Forms, is there a way to sort on the actual values from a (Table Control) lookup column, rather than on the key?

I have an "author" master table (*id, author_txt) with PK "id", and a "legacy_book" table with a foreign key column "author_id" that has a relationship to "author.id". In a Table Control, I have a field column that sources from "book.author_id", and it fetches values for author names via a SQL query (not a stored query) defined as "SELECT author_txt, id FROM ams.legacy_author" and bound column is 1. The lookup works fine by itself (showing authors' names), but not when I try to sort by authors' names.

Problem is that when I sort on this column, it sorts by the "id" and not by the fetched "author_txt" values. The issue is that the "id" column values are disjointed and not ordered in synchronicity with authors' names, yielding a perfectly fine numerically ordered list, but completely useless for alphabetical authors' names. I cannot for the life of me figure out how to fix this. I tried to create a Table Control column that is unattached to the underlying table (and sort by that column), but cannot see how to populate it from the fetched lookup values (unless perhaps with macros &/or LO BASIC, which is a whole 'nother can o' worms).

For example, the table "author" could look like this:

id  author_txt
--  ----------------------------
1   Andrews
2   Clive
5   Zubboff
6   Mackenzie
12  Francisco

I can achieve this with zero problems using a SQL query, joining the two tables, directly to the database, but the form stumps me.

Any suggestions? I feel I'm missing something utterly simple.

2021-04-10 21:12:42 +0200 asked a question Base form sort on lookup value, not key value?

Base form sort on lookup value, not key value? Windows 10 Pro; LO 6.4.7.2; PostgreSQL 10 In Base Forms, is there a way

2021-03-31 04:43:24 +0200 received badge  Notable Question (source)
2021-03-28 06:04:58 +0200 marked best answer Why LO Base created a new ODB database when new PostgreSQL connection?

Windows 10 Pro LO 6.4 PostgreSQL 10

I created a new connection to an existing active PostgreSQL 10 database, and I noticed that Base created a separate, but somehow linked, ODB database (in a folder of my choice). All PostgreSQL tables were migrated to this new ODB database (that I dumbly named "New Database" because I didn't know what I was doing). I created a form front-end to some tables, and I'm able to add/change/delete rows. Referential Integrity works as defined, server-side triggers and sequences work fine. Any changes in this ODB New Database are reflected in my PostgreSQL database (when looking at it with pgAdmin4).

My best guess is that the ODB database stores all "LO Base"-related objects, like forms, reports, stored queries, etc., that are important to Base but that PostgreSQL doesn't know about. But why duplicate the data? Also, can this ODB database be considered stand-alone? Say for example, I dropped the whole PostgreSQL database, would the ODB database still work?

Thanks, Patrick

2021-03-27 19:03:42 +0200 received badge  Popular Question (source)
2021-03-27 00:13:51 +0200 commented answer Why LO Base created a new ODB database when new PostgreSQL connection?

Ok, thanks.If I get this correctly, the ODB database is just a transparent layer over the PostgreSQL database (at least

2021-03-26 23:14:31 +0200 asked a question Why LO Base created a new ODB database when new PostgreSQL connection?

Why LO Base created a new ODB database when new PostgreSQL connection? Windows 10 Pro LO 6.4 PostgreSQL 10 I created a

2021-01-23 16:27:39 +0200 marked best answer Add Styles to Formatting (Styles) toolbar?

Windows 10 Pro 64-bit LibreOffice 6.4.7.2

How can I add more styles to the Formatting (Styles) toolbar? Currently (LO 6.4), I have the following built-in styles showing in the Formatting (Styles) toolbar: Default Paragraph, Heading 1, Heading 2, Heading 3, Tet Body, Quotations, Preformatted

For example, I'd like to add Heading 4, or a new style I created. In MS Word, I'd add a style to the Style Gallery. Can this be done in LibreOffice? Thx

2021-01-23 16:27:27 +0200 commented answer Add Styles to Formatting (Styles) toolbar?

You may have a point. I usually display all styles, in alpha order. I then find myself bouncing up and down, a very long

2021-01-23 14:48:01 +0200 received badge  Popular Question (source)
2021-01-23 14:00:41 +0200 commented answer Add Styles to Formatting (Styles) toolbar?

I've been using LO (and especially Writer) since v5.3, trying to migrate from MS Office but never quite satisfied. Those

2021-01-23 02:17:07 +0200 asked a question Add Styles to Formatting (Styles) toolbar?

Add Styles to Formatting (Styles) toolbar? Windows 10 Pro 64-bit LibreOffice 6.4.7.2 How can I add more styles to the F

2020-12-16 23:31:22 +0200 received badge  Famous Question (source)
2020-11-02 23:17:06 +0200 received badge  Famous Question (source)
2020-10-31 10:21:51 +0200 received badge  Famous Question (source)
2020-10-31 10:21:51 +0200 received badge  Notable Question (source)
2020-08-06 16:42:27 +0200 received badge  Notable Question (source)
2020-07-28 21:47:50 +0200 received badge  Famous Question (source)
2020-05-04 22:24:39 +0200 received badge  Student (source)
2020-05-04 22:23:48 +0200 received badge  Famous Question (source)
2020-04-27 15:02:09 +0200 received badge  Notable Question (source)
2020-03-03 00:39:41 +0200 received badge  Popular Question (source)
2020-02-10 23:15:46 +0200 received badge  Popular Question (source)
2020-02-06 17:55:50 +0200 received badge  Notable Question (source)
2020-02-04 20:24:20 +0200 received badge  Popular Question (source)
2020-02-01 15:45:20 +0200 received badge  Enthusiast
2020-01-31 21:55:35 +0200 marked best answer LO BASE autovalue vs. FB generator trigger

I'm trying to duplicate the LO BASE "autovalue" functionality manually by creating a generator (sequence) and using a Before Insert trigger on a table to populate its ID (integer, not null) column. I was especially wondering if the manual method will result in BASE showing the column as "autovalue", or can that only be achieved by making it that way explicitly only with BASE. Anyone know? Thx

2020-01-31 21:45:26 +0200 marked best answer BASE doesn't show tables created with Firebird SQL (embedded)

Hello, I'm a newbie with LO Base, trying to move from MS Access. I'm no expert in Access, but I do have considerable experience with enterprise DBMS (mostly Oracle), mainly on the design/modeling side, not so much as DBA.

In Base, I've created a table using Firebird (embedded) SQL (Tools > SQL...) The table doesn't show up in the Tables list. In order to show the table, I must exit LO Base and come back in, then it's visible in the Tables list.

Am I doing something wrong? Is there any way to get tables created with Firebird SQL to show right after creation?

If you're wondering, I'm using SQL, rather than the LO Base Design View or wizard because I'm creating default timestamp columns (RecordCreateTMS and RecordUpdateTMS [still working on the latter]) and I want to create this in one CREATE statement instead of creating the table and then ALTERing the column(s).

One additional question: if I'll be creating triggers and other database objects, is it better to use a standalone installation of Firebird and just connecting to it, rather than going through LO Base front end with the embedded Firebird?

2020-01-31 21:45:26 +0200 received badge  Scholar (source)
2020-01-31 21:40:37 +0200 received badge  Popular Question (source)
2020-01-30 17:05:46 +0200 edited question LO BASE autovalue vs. FB generator trigger

LO BASE autovalue vs. FB generator trigger I'm trying to duplicate the "LO BASE "autovalue" functionality manually by

2020-01-30 15:14:13 +0200 received badge  Notable Question (source)
2020-01-26 00:18:24 +0200 received badge  Popular Question (source)