Add entry Others to Listbox to filter data

[Anonymous]

Hello,

i have a table with entries and a table with categories.

category-value = 1 assigend

category_value = 0 = not assigened

entry:

id, description
1, 'entry 1'
2, 'entry 2'
3, 'entry 3'

category:

id, category_name, category_value
1, 'category A', 1
1, 'category B', 1
1, 'category C', 0
2, 'category A', 1
2, 'category B', 0
2, 'category C', 0
3, 'category A', 0
3, 'category B', 0
3, 'category C', 0

I want to use the field category_name as a Listbox control to filter entries.

Furthermore I use an set analysis function like COUNT( {$<category_value={1}>} id)

to Count only entries with at least one category.

Is it possible to add an value 'Others' to the listbox control

to select only entries which has no assigend categories SUM(category_value) = 0 ?

I could try with the SQL query....

SELECT
  a.id
  a.category_name,
  a.category_value,
  1 AS SUM_catValue
FROM
  category a
 
  UNION
 
    SELECT
  a.id,
  'Others' AS category_name,
  1 AS category_value,
  SUM(category_value) AS SUM_catValue

FROM
  category a
GROUP BY a.id, 'Others', 1
  HAVING SUM(category_value) < 1

But can I also do it without manipulating the query?

Thanks and best regards.

[sunny_talwar]

Try this list box expression:

=Aggr(If(Sum({1} category_value) > 0, Only({1} category_name), 'Others'), category_name)

[Kushal_Chawda]

may be try this expression

=count({<id = {"=sum(Category_value)=0"}>}id)

[Anonymous]

Thanks,

but with an expression I only get a second column and the Expression is calculated for each entry.

I Need a completely new entry under the categories A, B, C

any idea how I can add entries to a listbox without changing the data table?

[sunny_talwar]

May be make that manipulation in the script? and what would you filter when a user selects Others?

[maxgro]

maybe an option to use the search in the listbox

=sum(category_value)=0