[solved]Adding a database-driven table to a Writer template from a LibreOffice Base macro

[goedible]

Hello,

I have a LibreOffice Base database with a form button that runs a Basic macro to print seed envelopes. The macro works well: it opens a Writer template (Print_Rotated.ott ), loops through the shapes on the DrawPage, and fills text boxes with values from the currently selected record in the Base form.

The macro uses the shape names as database field names. For example, a shape named common_name gets filled with the value from the common_name field. This works well for printing individual seed envelopes.

I would now like to expand this so that the printed envelope also includes a table showing all cultivation practices for the selected plant.

My goal is:

• Select a plant record in a Base form
• Press the print button
• Open the Writer template
• Fill the existing text shapes as before
• Populate a table in the Writer document with multiple rows of cultivation information related to that plant

For example:

Practice	Details
Seed starting	Start indoors 6 weeks before last frost
Transplanting	Plant out after danger of frost
Harvest	Collect mature seed pods

Any suggestions or examples would be appreciated.
here is a link to the database in case you want to download it and try some things. https://www.dropbox.com/scl/fo/jn3vw6944r3yjehotwqrx/AJcsRG0D58ZXx8P35H_GV-w?rlkey=mi4zuwym4bh153bquk64ieusa&st=dt5rfqoz&dl=0

Thank you.

[RobertG]

With following procedure you will need a table called “Printout_Addresses” in your document. Header of the table will be included, also the first (empty) row for content.
Start with something like this:

SUB FillTable
	DIM oDB AS OBJECT, oNewDoc AS OBJECT, oTables AS OBJECT, oTable AS OBJECT, oRows AS OBJECT
	DIM oDatasource AS OBJECT, oConnection AS OBJECT, oSQL_Statement AS OBJECT, oResult AS OBJECT
	DIM stDir AS STRING, stSql AS STRING, stText AS STRING
	DIM i AS INTEGER, k AS INTEGER, inCols AS INTEGER
	oDB = ThisComponent.Parent
	stDir = Left(oDB.Location,Len(oDB.Location)-Len(oDB.Title))
	stDir = stDir & "Print_Rotated.ott"
	DIM args(0) AS NEW com.sun.star.beans.PropertyValue
	args(0).Name = "AsTemplate"
	args(0).Value = True
	oNewDoc = StarDesktop.loadComponentFromURL(stDir,"_blank",0,args)
	oTables = oNewDoc.getTextTables
	oTable = oTables.getByName("Printout_Addresses")
	inCols = oTable.Columns.Count
	i = 1
	oDatasource = ThisComponent.Parent.CurrentController
	If NOT (oDatasource.isConnected()) THEN
		oDatasource.connect()
	END IF
	oConnection = oDatasource.ActiveConnection()
	oSQL_Statement = oConnection.createStatement()
	stSql = "SELECT * FROM ""tbl_Adressen"""
	oResult = oSQL_Statement.executeQuery(stSql)
	WHILE oResult.next
		FOR k = 0 TO inCols-1
			stText = oResult.getString(k+1)	
			oTable.getCellByPosition(k,i).setString(stText)
		NEXT
		oRows = oTable.getRows()
		oRows.insertByIndex(oRows.getCount(),1) 
		i = i + 1
	WEND
	oRows.removeByIndex(oRows.getCount()-1,1)
END SUB

[goedible]

What I did with chat gpt

1. Left my existing FillShapesString macro almost unchanged.
2. Added a call to a second macro:

FillCultivationTable(oNewDoc, oForm)

3. In the Writer template, I inserted a 6-column table and named it CultivationTable .
4. The table contains one blank row (no headers).
5. The FillCultivationTable macro:

• Reads the current record’s latin_name from the form.
• Queries annual_cultivation_dates_view for all matching records.
• Inserts additional rows into the Writer table as needed.
• Fills the table with:
  • Start Month
  • Start Day
  • End Month
  • End Day
  • Practice
  • Description

The SQL used is:

SELECT start_month,
       start_day,
       end_month,
       end_day,
       practice,
       detail
FROM annual_cultivation_dates_view
WHERE latin_name = ?
ORDER BY start_month, start_day;

A tip that saved me some confusion: don’t test a form event macro by pressing the Run button in the Basic editor. The macro expects the event object supplied by the form button, so it should be tested by clicking the button on the Base form.

This approach lets me keep the original envelope-printing code separate from the code that fills the cultivation table, making the macros easier to maintain.

[Villeroy]

@goedible

My goal is:

• Select a plant record in a Base form
• Press the print button
• Open the Writer template
• Fill the existing text shapes as before
• Populate a table in the Writer document with multiple rows of cultivation information related to that plant

With zero code:

• Create a serial letter with a table of mail merge fields as cell contents and make it a template.
• Open the template and the data source window (Ctrl+Shift+F4).
• Select the parameter query and when the parameter substitution dialog pops up, enter the search criterion.
• Select the record selector (gray row header in front of the record).
• Click the icon “Data to fields” on the toolbar above the data source.
• Print the document and/or save it under some name.

More convenient, still with no macro code:

• Add a form linked to a filter record on top of the document where you enter the search criterion into a list box. Make the list box and the confirmation button unprintable.
• Create a mail merge document linked to a query that references that filter criterion.
• Link the above mentioned serial letter to that query.

When using the template:

• Select a criterion from the listbox and store the filter record.
• Print the serial letter.