How to use libreoffice mailmerge to display the entire tables data on one page and make it updatable?

[TheSmallest]

I created a sample database (Test.odb (3.7 KB)) and a writer file (TEST.odt (10.1 KB)) which should serve as a report.

I would do it with the report-builder that base offers but I couldn’t figure out how to achieve my goal.
Here’s what I want: I would like the report to look like this:

Name1 at the value 1
Name2 at the value 1
Name3 at the value 2
Name4 at the value 10

Name1, Name2… as well as the numbers are fields of my database table. If I open control+shift+f4 I can select the table and all values and insert them as fields. All values will be inside my report (like outlined above). However, the number (value) will change from 1 to 100 over some time (I change them in the database file). The values don’t update in writer, however, if I update the database file, e.g. change the value from Name1 to 100. Even tools > update > update all doesn’t work.

If I turn on mailmerge, and switch to the next mail merge entry and back again, it removes the lines “Name2” to “Name4” and only displays “Name1” which is the current mail merge entry.

So how can I display all those values (and a lot more from other tables) inside my report like outlined above and make them updatable? If mailmerge isn’t the right tool or report-builder can somehow do the same, it would suffice.

Versions are: 7.4.5.1 Libreoffice Community and 7.4.7.2 Libreoffice Community, both from the official debian repository.

[ajlittoz]

Isn’t this what you are looking for?
Test.odb (7.0 KB)

Report1 yields something like your example.

[TheSmallest]

No, I’m sorry. I tried to simplify it as much as possible and made it actually quite impossible to understand. Here is a picture describing what I want to do:

Here is the same file as a PDF: P0.pdf (14.6 KB)

The colors represent the tables in this database file: P1.odb (4.4 KB)

And here is what my current writer document looks like: P2.odt (21.0 KB)
As you can see the writer document doesn’t display some values even though it should. Also, if I update my database (e.g. update the value ValueA from the blueArea table) then writer isn’t able to update and properly display everything as I outlined in the picture / PDF above.

[TheSmallest]

Since no one could give an answer or even hints I’m guessing mailmerge and report-builder can’t do that.

If you know a workaround with macros it’d help too! Maybe I can somehow use macros to set the data rows that are being read… I’ll investigate further in that regard. If someone has a clue I’d be thankful to read it since my macro programming skills are very very limited.

If it’s still unclear what I try to achieve, feel free to ask, thank you!

[ajlittoz]

I had a look at the DB contents. What strikes me is the difference between the green and purple areas (this is only an example and you could also compare other pairs).

Data for the green area comes from a single record while you display several records in your purple area: every “item” comes from a different record. Though you have explcit NextRecord requests in your purple area, it looks like there is an implicit NextRecord in the green area after first field retrieval. Strangely enough, after I add a second record in gree,Area, all the fields are correctly displayed (record 1) but I can’t revert to initial record (record 0).

I suggest you look very carefully to your field insertions. When I needed to create such reports from a DB I always used the report generator but I defined queries to know exactly what I extracted from the DB.

[TheSmallest]

@ajlittoz

Though you have explcit NextRecord requests in your purple area, it looks like there is an implicit NextRecord in the green area after first field retrieval.

What exactly does that mean? Does that mean that the greenArea “wants” to create / display a new record?

@ajlittoz

I suggest you look very carefully to your field insertions. When I needed to create such reports from a DB I always used the report generator but I defined queries to know exactly what I extracted from the DB.

In my real databse I also have queries but I still have the same problem. E.g. report-builder seems to need one single query (or table) to get data fields out of. That’s why I created a single query from all table fields that I need. However, since e.g. the purple area from my picture contains 5 rows and my green one contains 1 row only the query duplicates green row for all purple row entries.

@ajlittoz

but I defined queries

Does that mean you think something like my example above should be possible with normal queries and the report-builder?

[ajlittoz]

@TheSmallest

report-builder seems to need one single query (or table) to get data fields out of.

Generally queries involving several TABLEs are quite complex and need keyword JOIN to to return all relevant fields/records spread on various TABLEs. I don’t think your TABLEs, at least in the small example, have the needed data to “link” the tables. Thus usually involves KEYs and FOREIGN KEYs.

@TheSmallest

Does that mean you think something like my example above should be possible with normal queries and the report-builder?

Yes, but as I mentioned above, the query can be tricky to debug.

If you aren’t familiar with SQL and queries, I recommend you first study carefully the SQL language.

[TheSmallest]

@ajlittoz I don’t get how join is going to be helpful here. I mean I understand the concept. Say I have table1 with columns id, name, department_id and I have table2 with id, department_name then I would join department_id and department_name to get a query that displays the department name instead of its id while still including all other fields from table1. For this I’d have to use relational databases.

My db isn’t relational and I also don’t have something like above. As a matter of fact, I can get everything from both tables with sql by just using SELECT … FROM ….

@ajlittoz

Data for the green area comes from a single record while you display several records in your purple area: every “item” comes from a different record.

This is my problem. I can’t seem to find a way to get report-builder or mailmerge to produce my desired result (see picture in a comment above), because it won’t “allow me” to display multiple records for one table but only one record for another table.

Would you kindly give me a hint how you think SQL join would help here? Even if I assigned foreign keys I just don’t think I want to join anything. And it wouldn’t allow me to create a report where table1 has 1 shown record and table2 has e.g. 5 shown records (see picture) as far as I understand.

There must be a way to do it but I just can’t seem to figure it out, no matter what I try.

[TheSmallest]

I found a way to easily display all values using an sql code. Basically I was thinking about what you wrote earlier @ajlittoz when you wrote

@ajlittoz

Data for the green area comes from a single record while you display several records in your purple area: every “item” comes from a different record.

I thought: “This wouldn’t be a problem at all, if only I had just one single record. There got to be a way to use sql to “merge” my tables in such a way that only one record exists while still containing all data fields.”
And yes, it worked with the following code:

SELECT
	"greenArea".*,
	(
		SELECT
			"pinkArea"."Name"
		FROM
			"pinkArea"
		WHERE
			"pinkArea"."ID" = 0
	) AS "pinkArea0_name",
	(
		SELECT
			"pinkArea"."Value"
		FROM
			"pinkArea"
		WHERE
			"pinkArea"."ID" = 0
	) AS "pinkArea0_value",
	(
		SELECT
			"pinkArea"."Name"
		FROM
			"pinkArea"
		WHERE
			"pinkArea"."ID" = 1
	) AS "pinkArea1_name",
	(
		SELECT
			"pinkArea"."Value"
		FROM
			"pinkArea"
		WHERE
			"pinkArea"."ID" = 1
	) AS "pinkArea1_value",
	(
		SELECT
			"pinkArea"."Name"
		FROM
			"pinkArea"
		WHERE
			"pinkArea"."ID" = 2
	) AS "pinkArea2_name",
	(
		SELECT
			"pinkArea"."Value"
		FROM
			"pinkArea"
		WHERE
			"pinkArea"."ID" = 2
	) AS "pinkArea2_value",
	(
		SELECT
			"pinkArea"."Name"
		FROM
			"pinkArea"
		WHERE
			"pinkArea"."ID" = 3
	) AS "pinkArea3_name",
	(
		SELECT
			"pinkArea"."Value"
		FROM
			"pinkArea"
		WHERE
			"pinkArea"."ID" = 3
	) AS "pinkArea3_value",
	(
		SELECT
			"pinkArea"."Name"
		FROM
			"pinkArea"
		WHERE
			"pinkArea"."ID" = 4
	) AS "pinkArea4_name",
	(
		SELECT
			"pinkArea"."Value"
		FROM
			"pinkArea"
		WHERE
			"pinkArea"."ID" = 4
	) AS "pinkArea4_value"
FROM
	"greenArea"

That’s not the solution yet, though. If I update a value (e.g. “pinkArea4_value”) to say 100 the following happens:

1. the table in the database file displays the value correctly (100)
2. the sql I wrote correctly gets the value (100)
3. in writer in the window where I got my fields from (control+shift+f4) it updates the value correctly (100)
4. In the writer file itself, even when going to “tools > update > all” the value won’t update.

So it seems that those values are either not updatable or I do something wrong. Especially if I edit formatting and change the design drastically it would be quite a lot of work to redo the entire writer file just to get updated values. Do you know how to fix this?

EDIT: If I go to control+shift+f4 and simply insert “pinkArea4_value” as a field again (after the value had been updated to 100) then it works fine. But the old one won’t update and coexists with the old value (10) in the document.

EDIT 2: If I save and close my writer document after changing the value in the database and then reopen my writer file, then the mailmerge entry is set to -1. I just have to navigate to entry 1 (the only entry available) and it updates all database values.
I don’t know if it’s a bug that “tools > update > all” won’t work but my problem had been solved. Thanks @ajlittoz for trying to help! Your clues led to this solution.