SELECT with where doesn't work where [] does

Luis
updated 3 days ago
17replies

Guys I am puzzled.

This works

let filterCat := text('Category Filter');
let filterMonth := text('Month Filter');
let filterYear := text('Year Filter');
(select Expense)[contains(filterCat, text('Expense Category')) and
contains(filterMonth, monthName('Expense Date'))]

But this doesnt

let filterCat := text('Category Filter');
let filterMonth := text('Month Filter');
let filterYear := text('Year Filter');
select Expense where contains(filterCat, text('Expense Category')) and
contains(filterMonth, monthName('Expense Date'))

as info: the 3 filter *Filter fields are multiple choice elements

thanks for pointing me to surely the stupid mistake....

- Luis
- 2 days ago
- Reported - view
just to add more context --- may be the issue?

'Expense Category' is a field of the 'Expense and it is a dynamic choice.
- John_Halls
- 2 days ago
- Reported - view
Hi Not sure, bit I think you have the two arguments for contains() the wrong way round, so, for example, the first one should be

contains(text('Expense Category'),filtetrCat)

Regards John
- Luis
  
  yesterday
  
  Reported - view
  Hi John. not really... the first argument is a concatenation of values because the filter is a multiple choice.... As you wrote it doesn't work
  
  thanks for answering
- Fred
- 2 days ago
- Reported - view
Can you upload a sample DB so we can take a look at it?
- Luis
  
  yesterday
  
  Reported - view
  Hi Fred - thanks for the reply. Just to be clear: I should make a copy of the DB (with sample values and upload it?) Where do I upload it? as file attachment?
- Luis
  
  yesterday
  
  Reported - view
  here a sample.
  
  The query is in the "Dashboard"; there is a "View" called 'All Expenses'.
  
  The query has the elements for the 3 filters that are below the view (Category, Month and Year)
  
  Expenses overview sample.ninox
- John_Halls
  
  yesterday
  
  Reported - view
  As far as I have tested both formats work fine.
  
  Regards John
- Luis
  
  yesterday
  
  Reported - view
  really?? They don’t with me… when I use “where” eg none selected, the view doesn’t change…
  
  Nothing - I don’t understand - I tried in a new db , in the full db , in the sample db… it doesn’t change a thing when I use where.
  
  ps: I have ninox local on a Mac… is it different?
- John_Halls
  
  yesterday
  
  Reported - view
  Ah, maybe I am on the web. will be able to test in the same environment as you. Good luck!
- Fred
  
  yesterday
  
  Reported - view
  Yes, I am able to reproduce the issue. On a local DB with the MacOS app, the where just finds all records while the square brackets find according to the selection.
  
  I tried some other tactics and had no luck. I would email Support about this, but I wouldn't hold my breath about them fixing it soon. Well at least they will never tell you they fixed it. You just have to keep trying it new versions.
- Luis
  
  yesterday
  
  Reported - view
  Here is also a version with the WHERE --- the one that doesn't work!
  
  Expenses overview sample WITH WHERE.ninox
- Luis
  
  yesterday
  
  Reported - view
  thank you very much
  
  and thank you guys for your answers and trying to help me. Cheers chaps!
- Rafael Sanchis
- Rafael_Sanchis
- yesterday
- Reported - view
Works fine for me on Android Tablet
- Luis
  
  yesterday
  
  Reported - view
  did you try with WHERE instead of []..? because the version with [] works.... its when I replace the [] with WHERE that it doesn't. I sent a new version with the WHERE....
  
  See my reply to FRED now - DN is called Expenses overview sample WITH WHERE.
- Rafael Sanchis
  
  Rafael_Sanchis
  
  yesterday
  
  Reported - view
  Yes Luis you are right only works with []

Fred
yesterday
Reported - view

Tried a couple of things and found that this works:

let catNum := numbers('Category Filter');
select Expense where contains(catNum, number('Expense Category'))

Seems like the app does not like text in contains in where. Weird.

Luckily Category and Months are easy to get numbers for. For the Year Filter you have first get the text then convert it into a number with something like:

let filterYear := for text in split(text('Year Filter'), ", ") do
        number(text)
    end;

So the code would look something like:

let filterCat := numbers('Category Filter');
let filterMonth := numbers('Month Filter');
let filterYear := for text in split(text('Year Filter'), ", ") do
        number(text)
    end;
select Expense
    where contains(filterCat, number('Expense Category')) and
        contains(filterYear, year('Expense Date')) and
    contains(filterMonth, month('Expense Date'))

You should still report it as text should also work, but at least you have a work around.

- Luis
- 19 hrs ago
- Reported - view
many thanks Fred!!!!!

Welcome to the Ninox community

SELECT with where doesn't work where [] does

17 replies

Content aside

Categories

Overview