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
vor 1 Stunde
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 30 Minuten
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
