Filtering on multiple values

[Diggger5]

Hi there!
nuAddBrowseFilter is a very good feature. But do you think it is possible to filter on multiple values within a column? Like in excel or google sheets. And maybe filter on "empty" values?

[kev1n]

Hi,

Yes, empty values should be filterable too — I’ll check if this is already possible with the current function and will let you know.

As for filtering multiple values within a column, I already have a draft implementation, but I still need to test, refine and integrate it before I can share something for testing.

[kev1n]

To filter on "empty" values, add e.g. ["-1", "(Blanks)"] in your array.

Example:

Code: Select all

  const inputTypes = [
    ["-1", "(Blanks)"],
    ["nuDate", "nuDate"],
    ["nuNumber", "nuNumber"],
    ["nuScroll", "nuScroll"],
    ["nuAutoNumber", "nuAutoNumber"];

Then the WHERE condition in your browse SQL will look sth. like:

Code: Select all

WHERE sob_input_type = '#nu5bad6cb377a01b0_filter#'
      OR left('#nu5bad6cb377a01b0_filter#', 1) IN ('#', '')
      OR (sob_input_type = '' AND '#nu5bad6cb377a01b0_filter#' = '-1')

You will need the latest nuforms.js as there was a small bug.



SQL explanation


The `#nu5bad6cb377a01b0_filter#` value is a placeholder (filter of column)
At runtime, it is replaced with the actual filter value from a hash cookie.

SQL Condition

Code: Select all

WHERE sob_input_type = '#nu5bad6cb377a01b0_filter#'
   OR LEFT('#nu5bad6cb377a01b0_filter#', 1) IN ('#', '')
   OR (sob_input_type = '' AND '#nu5bad6cb377a01b0_filter#' = '-1')

Condition 1:

Code: Select all

sob_input_type = '#nu5bad6cb377a01b0_filter#'

Matches rows where `sob_input_type` exactly equals the filter value.

Condition 2:

Code: Select all

LEFT('#nu5bad6cb377a01b0_filter#', 1) IN ('#', '')

Takes the first character of the filter value and checks if it is `#` or empty (`''`).
This is often used for special cases like:
- `#` = show all (no filter set, hence the Hash Cookie is not resolved)
- `''` = no filter applied

Condition 3:

Code: Select all

sob_input_type = '' 
AND '#nu5bad6cb377a01b0_filter#' = '-1'

Matches rows where the field is empty and the filter equals `-1` (“filter for blank values”).

[kev1n]

But do you think it is possible to filter on multiple values within a column? Like in excel or google sheets.

It's now on Github. See viewtopic.php?p=31437#p31437

[Diggger5]

Hi Kev1n,

Sorry for this noob question, but I'm stuck.
In my form, I have set up a dynamic list for filtering on some columns. The multiselect filter works with the following settings, but I can't set up filtering on blank values.
Browse SQL:

Code: Select all

WHERE
(
        (rend_ertekesitokod = '#66192bd91eefa91_filter#' OR LEFT('#66192bd91eefa91_filter#',1) IN ('#','') OR LOCATE(rend_ertekesitokod, '#66192bd91eefa91_filter#') > 0 )
        AND
        (rend_options = '#66192bd91f60507_filter#' OR LEFT('#66192bd91f60507_filter#',1) IN ('#','') OR LOCATE(rend_options, '#66192bd91f60507_filter#') > 0 )
        AND
        (rend_stat = '#66192bd91fc3266_filter#' OR LEFT('#66192bd91fc3266_filter#',1) IN ('#','') OR LOCATE(rend_stat, '#66192bd91fc3266_filter#') > 0 )
)

Browse Custom code:

Code: Select all

nuAddBrowseFilter('66192bd91eefa91').nuSearchableMultiPopup({ items: getKER() });
nuAddBrowseFilter('66192bd91f60507').nuSearchableMultiPopup({ items: getOPTIONS() });
nuAddBrowseFilter('66192bd91fc3266').nuSearchableMultiPopup({ items: getSTATUSZ() }); 

Before Browse:

Code: Select all

$sqlKER = function() use ($getDistinctrendelesekColumnQuery) {
  return $getDistinctrendelesekColumnQuery('rend_ertekesitokod');
};
$sqlOPTIONS = function() use ($getDistinctrendelesekColumnQuery) {
  return $getDistinctrendelesekColumnQuery('rend_options');
};
$sqlSTATUSZ = function() use ($getDistinctrendelesekColumnQuery) {
  return $getDistinctrendelesekColumnQuery('rend_stat');
};

$KER = nuEncodeQueryRowResults($sqlKER(), [], ['']);
$OPTIONS = nuEncodeQueryRowResults($sqlOPTIONS(), [], ['']);
$STATUSZ = nuEncodeQueryRowResults($sqlSTATUSZ(), [], ['']);

$filterJS = "
  function getData(data) {
    return JSON.parse(atob(data));
  }
  function getKER() {
    return getData('$KER');
  }
  function getOPTIONS() {
    return getData('$OPTIONS');
  }
  function getSTATUSZ() {
    return getData('$STATUSZ');
  }

How do I set the ["-1", "(Blanks)"] array?

nuBuilder DB: V.4.9.1-2025.08.13.00
nuBuilder Files: V.4.9.1-2025.08.13.02

Thanks a lot!

And a small bug in this version:
nu-label-position="top" attributes don't work in objects.

[kev1n]

Hi,

I'm not entirely sure whether you're passing only a string array or text/value pairs to the select element. This would also become clear if you shared your getDistinctRendelesekColumnQuery()

And a small bug in this version:
nu-label-position="top" attributes don't work in objects.

Thanks, it's fixed on Github.

[kev1n]

The core form have been updated. Some filters also show an "(empty") value now. Update is on Github.

[Diggger5]

Hi,

This is my getDistinctRendelesekColumnQuery():

Code: Select all

$getDistinctrendelesekColumnQuery = function($column) {
  return "SELECT DISTINCT `$column` FROM `rendelesek` WHERE IFNULL(`$column`,'') <> '' ORDER BY `$column`";
};

When I remove this it works almost fine:
WHERE IFNULL(`$column`,'') <> ''

But as I see it only filters for "BLANK" values and not NULL.
What would be the correct code?

Thank You!

[kev1n]

Your browse SQL should also include IFNULL() in the WHERE clause.

E.g.

Code: Select all

    ((sus_language = '#nu5bad6cb3746cc1b_filter#' OR LEFT('#nu5bad6cb3746cc1b_filter#',1) IN ('#','')) OR (IFNULL(sus_language,'') = ''  AND '#nu5bad6cb3746cc1b_filter#' = '-1'))

[Diggger5]

It doesn't want to work properly.
I tried to solve it with chatgpt but we are stupid for it..

My getDistinctrendelesekColumnQuery:

Code: Select all

$getDistinctrendelesekColumnQuery = function($column) {
  return "
    SELECT DISTINCT
      CASE
        WHEN `$column` = '-1' OR IFNULL(TRIM(`$column`),'') = '' THEN '[Üres]'
        ELSE `$column`
      END AS `$column`
    FROM `rendelesek`
    ORDER BY (`$column` = '[Üres]') ASC, `$column`
  ";
};

WHERE:

Code: Select all

((rend_rendelesi = '#66192bd91f0b02f_filter#' OR LEFT('#66192bd91f0b02f_filter#',1) IN ('#','')) OR ((IFNULL(rend_rendelesi,'') = '' OR rend_rendelesi = '-1') AND '#66192bd91f0b02f_filter#' = '-1') OR LOCATE(rend_rendelesi, '#66192bd91f0b02f_filter#') > 0 )

I think ChatGPT made it a bit too complicated.

Please help me!