[Base]: Form Based Filter for Array Data Element

[athrun_zala]

Hi!

I’m using LibreOffice 5.1.4.2 as front-end for a postgresql database and so far, everything is working great, except that I am unable to use Form Based Filter to filter for specific elements contained in an array field.

For example:
Column A is filled with data that looks like {a,b,c,d,e,f}

Now I wish to filter all records whose Column A contains ‘a’ as an element of the Column A array.

Here’s how I attempt to go about it:

1. I click Form Based Filter and type in “IN (‘a’)” in the Column A field box
2. I click on a different field to ensure the filter parameter is applied
3. I click on apply filter.

The form gets stuck on the record I am on.

Form cannot display succeeding or preceding records.

It seems that the records were not filtered either.

The filter option buttons get greyed out.

I have to close the form to regain functionality.

Any help on how to go about filter array fields will be much appreciated.

thanks in advance!

[Ratslinger]

The first try at this was to use HSQLDB for the test. Created table & arrays OK but almost nothing else worked. Specific queries did work successfully.

Switched systems and used PostgreSQL to created table with array field. Set up form and had no success using filtering. Ran across mostly same problems as mentioned in the question. Also had many crashes.

Did have success creating my own filter. Created single record table to hold selection criteria. Used this in SQL selection criteria for main table (with Run SQL command directly turned on) using:

select posts.id, title, post, tags from posts, filter where tags[1] = filter.selection;

with tags[1] being the first position in the array.

Since Base is essentially set around HSQLDB v1.8 (no array capability), this may not be the only problem using array fields.

[athrun_zala]

Thanks for the response!

I guess there is no going around turning to SQL commands.