Yesterday I decided to add some (table type) Views to a Table and then use a filter so each of the new Views showed the selection I wanted. As per usual, I set one of the columns to only have any content for the records I wanted (very simple, based on another field in that table) and as expected, all the unwanted rows were empty in that column. I then added the usual filter of "!=null" which always works and hey presto, ALL the records are still showing, with the unwanted ones clearly empty (i.e. null). So the filter simply wasn't filtering. At all.
I messed with it for hours. E.g. setting the column specifically to show "X" in the unwanted rows and then filtering on !="X" but still no filtering.
All otherTables are still filtering perfectly as they always have, just not this one. I closed and reopened the database, quit and restarted Ninox etc but no change.
Testing one of the problem Views, I altered it to use a different column (so null for unwanted rows) and again filtered on !=null and finally, that worked. I also did the same thing with the third column and that also worked. So…
The problem is limited to that specific column only. Its value is calculated from several fields, but how it obtains its value is surely irrelevant. Filtering is done on the exact value as displayed and no matter what I tried with that column, FILTERING DOES NOT WORK.
Having ascertained the cause of the problem, I was avoided it by filtering on a different column, but yet again disappointed by what appears to me to be utterly bizarre weirdness from Ninox.
Anyone else had an issue with a particular column simply not able to be filtered? Is there some underlying rule about this that enables us to know what columns can and cannot be filtered and perhaps why?
I have no idea if this will solve your problem but...
For ages I could not for the life of me figure out why a formula column simply would not 'stick' when I chose to show it in a view. I learned that by using first(formula) would work. In the weird world of DB and especially the quirks of Ninox who knows if this might lead to a solution :-)
Alan brings up a good point. Can you share a formula that you have issues with?
Ninox does have an annoying habit that if you set a formula for a column (new or existing) that it doesn't like, it simply deletes the column. When trying to fathom some complex code to display what you want, this can be extremely tiresome.
However, that's not what's happening here. The column display perfectly, exactly what I want and always has done. When I added the code to leave it empty for those records I want excluded, that again works perfectly and the column only displays the value for the intended records. The only issue is that filters simply do not work on it. Whatever filter I apply. Even if I leave it so all records show that column's value and filter on something that I can see exists, nope. Still won't filter.
Something is odd about the way Ninox is working here as however the column values are derived, filtering is done on what is actually displayed and if some rows display an 'X', then a filter of !="X" should exclude those rows. But this particular column just won't have it. Not come across this in any other View column, all of which I have used to filter work perfectly.
It's not a complicated problem. Shouldn't show rows with 'X' in that column, but it does and as I said, it 'should' be independent of the derivation of the column values.
It's just text BTW. A concatenation of values in this and other linked table records. As I said, not complicated and filtering should simply work on what is displayed, but does not.
I just tested further and in a table showing a Dynamic Choice column (DCfield), I added another column for text(DCfield), so both columns appear identical. It is possible to filter on the DCfield column, but Ninox gets fancy and provides a popup list of values to select as a filter and hard to know what it's doing in the background. Whatever, it works.
For the text(DCfield) column, filtering does not work. However I try to filter, it simply doesn't and continues to show all records. In fact, no matter what you enter as a filter is ignored.
Another column showing number(DCfield) CAN however be filtered as you would expect.
So my experience is that you cannot filter a column that includes a text(DCfield) element, even when included as part of a longer string.
Really strange indeed. If you replace text() by its explicit development:
concat(for i in numbers('Multiple choice (dynamic)') do record(Table2,i).Text end)
you get exactly the same value, but now filtering works.
That is some great sleuthing UKenGB !
Building on Alain's comment, the record() command is becoming one of my favorite commands when working with dynamic choice/multi choice fields.
Since you said you have a dynamic choice you can use the record() command in your fullDesc field as well.
let xRec := record(DCfieldroottable,number(DCfield)) xRec.fieldname + blah blah blah....
- 10 mths agoLast active