New Ninox 4 rule: an if … end inside a [ … ] filter or a where clause may return empty without raising an error. The fix is to compute the value before the filter or to use a for loop.
Could you please provide a general example of such a dataset query using an `if ... end` filter?
Rafael_Sanchis
gestern
Gemeldet - anzeigen
The rule
Inside a Ninox filter, whether [ … ] or where …, the condition is evaluated once for each record of the filtered table. If you put an if … then … else … end expression in that condition, Ninox 4 may not evaluate it correctly. And the serious part is that it throws no error: the filter simply finds no records, and the result comes out as 0 or empty.
That's why it's hard to detect: the formula saves without errors and looks correct, but the number is wrong.
Real example: Total Days
❌ Version that failed (returned 0 with 5 approved days):
sum(employees.absences[number(type) = lt and number(status) = 2 and
The problem is in year(if … end) = yr, an if placed inside the filter brackets.
✅ Correct version (returns 5): move the if out of the filter and loop through the records:
let tot := 0;
for a in employees.absences do
if number(a.type) = lt and number(a.status) = 2 then
let d := if a.date_start != null then a.date_start else a.date_partial end;
if year(d) = yr then
tot := tot + a.hrs_absence
end
end
end;
tot / 8
In the loop, each condition is a normal statement, not part of a filter, so Ninox evaluates it without problems.
Customer Support Ninox
uwe_groegor
vor 23 Stunden
Gemeldet - anzeigen
Hello Rafael,
I have built a simple database that reproduces your issue involving an 'if ... end' statement within a filter. I have also recorded a video showing the table structure, the scripts, and how it functions. In this setup, the records are displayed correctly, even with the 'if ... end' filter in place. Therefore, I am unable to determine why it is not working for you.
TableviewScripts:
let vSearch := searchstring;
let vintex := intern_extern;
select tabelle1 where if vintex then plz_extern else plz_intern end = vSearch
let vTable := (select tabelle1);
let vSearch := searchstring;
let intex := intern_extern;
vTable[if intex then plz_extern else plz_intern end = vSearch]
sum(employees.absences[number(type) = lt and number(status) = 2 and if date_start != null then year(date_start) else year(date_partial) end) = yr].hrs_absence) / 8 The problem is in year(if … end) = yr
Rafael_Sanchis
vor 21 Stunden
Gemeldet - anzeigen
Thanks Uwe, your test is helpful. You're right that an if inside a filter works in general, so my "rule" was too broad. The difference in my case is that the if is wrapped in a function, year(if date_start != null then date_start else date_partial end) = yr, and the filter is on a relationship path (employees.absences[...]) inside sum(). That combination returned 0 with no error. I've solved it by moving the date logic into a formula field (effective_date) and filtering on year(effective_date). If useful, I can share a minimal database that reproduces it.
Rafael_Sanchis
vor 20 Stunden
Gemeldet - anzeigen
Thanks Uwe, your test helped me narrow it down. A plain if ... end inside a filter does work, as you showed. The failing case is an if ... end used as a function argument inside the filter:
employees.absences[... and year(if date_start != null then date_start else date_partial end) = yr]
This returns 0 with no error. Without the if, year(date_start) = yr in the same filter returns the correct result (5). Could you test year(if ... end) inside the filter in your database to confirm?
As a workaround, I now compare the dates against date(yr, 1, 1) and date(yr, 12, 31), which works correctly.
