I am sure this can be done, and I have found hints in various places, but never clear enough to get me off the ground. The problem (schematically) is this.
I have a table called References which gives me links to objects in different publications; some objects may be mentioned in more than one. (The table has this form in order to ensure referential integrity and so that it can be queried both to show what publications refer to any object, and what objects are listed in any publication.) Thus:
Object Reference
Obj1 Source1
Obj2 Source4
Obj3 Source4
Obj3 Source8
Obj4 Source3
Because LibeOffice reports do not support multiple tables (or multi-record objects), I hope to resolve a consequent layout problem by generating a (temporary) table with a single record for each object, which would take the following form in order to provide the same information:
Object Reference
Obj1 Source1
Obj2 Source4
Obj3 Source4; Source8
Obj4 Source3
I envisage doing this by scrolling through the References table line by line, storing the field values in temporary variables. Then whenever a line is encountered that has the same value for ‘Object’ as the previous one, the reference values are concatenated; but whenever it is different, the stored values are written to a new record in the temporary table. The pseudo-code might look like this:
[Connect to source table, set up cursor]
[Execute SQL to create destination table]
REM Initiate variables
Previous = 0
Ref = ""
Loop [Read record]
REM Check that this is a new object, and not first in table
If ("Object" != Previous) and (Previous !=0) then
REM Write preceding record to new table
[INSERT INTO DestTable(1,2) VALUES (Last, Refs);]
REM Reset variables to current record
Previous = "Object"
Refs = "Reference"
REM If same object as previous record, simply concatenate ref.
Else
Refs = Refs & "; " & "Reference"
Endif
[Scroll to next record]
End loop
(And of course there needs to be something to recognise end-of-file and to ensure that the last record is written to the new table.)
[Ha! I am scolded, and rightly, for not providing quite enough information when I was trying to be as concise as possible.] The CONTEXT of this question is using LO Base connected through ODBC to a database in SQLite.
How do I set up a table cursor and cause it to perform this task?