Display comma separated values from an SQLite database in LibreOffice Base

[M-Rick]

I have an SQLite database used in QGIS with geographic data.
Some of the fields can store multiple values.

They are list values stored in a separate table. The name of the value is taken from the primary key of each item.
And QGIS stores them in the related field like this {3,4,9,10,11,15,16}.
For example, for a table ‘list1’
1 AA
2 BB
3 CC
etc.

How in LibreOffice base I can create the same checkboxes in a new form from this QGIS database?
I cannot even manage a way to get multiple values.

[RobertG]

Checkboxes will only save one value in a simple field. The field you describe is used as an array. Havent used this type before, because it isn’t possible in the internal databases, but could be it will work with a listbox, which allows multi selection.

[Wanderer]

I assume the data is actually stored as a text-string, maybe also using some of the capabilities to handle json with sqlite.
.
It would be possible to extract “fields” for display with string routines like INSTR like

SELECT  INSTR(array, ',4,')>0 as isolated FROM table;

A bit clumsy - as 4 is also in 14 you have to search including separators. As consequence for the first and last element you have to REPLACE { and } with , before searching. And you may use CTEs to split the array - as described here The Simplest SQLite Common Table Expression Tutorial « Expensify Blog
.
For the moment I have no idea how to “change” this values in a form without using macros.

[M-Rick]

The choices from LibreOffice developers is inconsistent to use a list view to store multiple items. Checkboxes are used in any database for multiple choices, like in FileMaker even if it remains one of the worst database software.

[M-Rick]

The number itself is not big deal. I can add a new column using longer numbers to make them unique, or replace the actual ones with 3 digits numbers, or use the words values instead.
But where in the LibreOffice Base I can insert this code? There is no SQL field in the controls for checkboxes. But there is one in the listbox.

[Ratslinger]

Hello,
SqLite does not have support for arrays → Arrays. That is coming from the extensions. I have little issue with this in PostgreSQL.
Have SqLite set up but would help if you could post a sample of your SqLite file.

[RobertG]

Relational databases will use tables, which are connected by foreign keys. This is the way “arrays” where build there. Arrays in PostgreSQL will look like {2,4,…}. You could get the different values in PostgreSQL by something like SELECT field[2] from table. It will show the second content of {3,4,…}, so ‘4’ here. But this won’t work in SQLite.

[Ratslinger]

@RobertG

But this won’t work in SQLite.

Already knew all that. Noted PostgreSQL as I worked with this previously using QGIS ( Loading geometry in Base causes hanging, crash ). Was not expecting the same process in SqLite.
.

Since SqLite does not handle arrays as a field type, and not wanting to go through the QGIS process again, asked for sample to see what may be able to be done.

[Villeroy]

Seems to be a wrongly implemented many-to-many relation. Right today I was confronted with another SQLite DB having the same issue. Instead of mapping primary keys of two lists in a third list, they store separated text values which is a performance nightmare.
I may be possible to handle this with a macro feeding a temporary mapping table.

[Ratslinger]

@Villeroy

Seems to be a wrongly implemented many-to-many relation.

.
Tend not to agree. In this instance you would have SqLite with SpatiaLite added and also QGIS. This all relates to geometry and fields not normally in the database. If I recall correctly, the geometry field created for PostgreSQL shows up in Base as something completely different.
.
Here is some of what I have for the SqLite version:

.
This is a simple field in a SqLite database with co-ordinates.
.
What I am trying to avoid is matching the OP set-up. Sample would be of much help.

[Villeroy]

OK. That’s something different.