0

Complex criteria for Select

Hi,

I am working on a project which requires a dynamic choice field to be populated by the results of a select based on one or more of four criteria. The criteria are selected at the top of a dashboard. I can set the code to populate the choice field for anyone of the criteria, but to cater for the any one or more scenarios, 12 I think, would result in a humungous block of if..then..else.
I had hoped to solve this by running a series of select statements on the result of the previous one. However, unlike SQL, Ninox doesn't seem to be able to make a temporary table from the results of a select, only an array. Nor does it seem possible to run a select on an array. 
Any suggestions on how to go about this?

On this screen shot the criteria are the 4 fields at the top and the choice field is at the bottom.
 

20 replies

null
    • szormpas
    • 4 mths ago
    • Reported - view

    Hi, try the following:

    let x := text(Filter by Status);
let y := text(Filter by Camera);
let z := text(Filter by Film Stock);
let i := text (Filter by Format);
select Film where Status = x and Camera = y and 'Film Stock' = z and Format = i;
      • Chris_Mullett
      • 4 mths ago
      • Reported - view

       Thanks for this and it would be fine if all criteria were selected. The problem is that will not often be the case. 

      • szormpas
      • 4 mths ago
      • Reported - view

        I see the problem. Instead of many nested if then else you could try the switch condition lilke below:

      let x := text(Filter by Status);
let y := text(Filter by Camera);
let z := text(Filter by Film Stock);
let i := text (Filter by Format);
switch true do
    case text(Filter by Status)="Show All" and text(Filter by format)="All" :
        select Film where Camera = y and 'Film Stock' = z;
    case .....
    • John_Halls
    • 4 mths ago
    • Reported - view

    As you have some "Show All" options you may need to build your where clause as a text string and evaluate it, such as

    let x := text(Filter by Status);
let y := text(Filter by Camera);
let z := text(Filter by Film Stock);
let i := text (Filter by Format);
let theFilter := if x = "Show All" then "true and " else "Status = x and ";
theFilter := theFilter + if y = "All" then "true and " else "Camera = y and ";
theFilter := theFilter + if z = "" then "true and " else "'Film Stock' = z and ";
theFilter := theFilter + if i = "" then "true" else "Format = i"
select Film[eval(theFilter, this)]

    Regards John

      • Chris_Mullett
      • 4 mths ago
      • Reported - view

       Thanks, this looks promising. I'd tried building a select string, but couldn't get it to work, I hadn't used eval. Yet again the mysterious "this" which keeps popping up. I don't really understand its usage. I'll have a play and see where it goes.

    • Fred
    • 4 mths ago
    • Reported - view

    Do your records in Film also have reference fields to Status, Camera, Film Stock, and Format? That will make filtering/search easier.

     said:
    I had hoped to solve this by running a series of select statements on the result of the previous one.

    What Ninox can do is filter arrays. So you could do something like:

    let t := this;
let allRecords := select Film;
let filter1 := allRecords[Status = number(t.'Filter by Status')];
let filter2 := filter1[Camera = number(t.'Filter by Camera')];
etc

    This is not a solution for you question, but just information.

    • Chris_Mullett
    • 4 mths ago
    • Reported - view

    Thanks Fred.  Yes, there are reference fields and filter is interesting, it is similar to the solution I was seeking. Yet again "this", is there a crib sheet for when to use it?

      • Fred
      • 4 mths ago
      • Reported - view

      'this' generally refers to the current record. So I typically use the variable t to store that info. Now I can reference any field that is on that record. I could create a variable for each field that i need to use. That is a good idea if you know you only have 1 or two fields you need to use. But I'm lazy and just use t and then I can reference anything I want later.

      • John_Halls
      • 4 mths ago
      • Reported - view

       We store the current record, this, to a variable because the current record is not available to reference within a select statement, but variables are.

    • szormpas
    • 4 mths ago
    • Reported - view

    The simplest solution I can think of is the following:

     

    let x := text(Choice1);
let y := text(Choice2);
select Film
    where switch true do
    case x != "Show All":
        Status = x
    case y != "All":
        Format = y
    end
      • Ninox developper
      • Jacques_TUR
      • 4 mths ago
      • Reported - view

       The idea is good, but it's important to note that the switch function only returns the first condition that is true. To perform a selection with multiple possibilities, you need to use logic combining OR and AND operators. The OR logic allows each criterion to return either the value to be tested or true, while the AND logic ensures that all pseudo-critical criteria are evaluated.

      Here's how it works:

      let x := text(Filter by Status);
let y := text(Filter by Camera);
let z := text(Filter by Film Stock);
let i := text(Filter by Format);

select Film where (x = "Show All" or Status = x)
                            and
                 (y = "All" or Camera = y)
                            and
                 (z is empty or 'Film Stock' = z)
                            and
                 (i is empty or Format = i);

      In this example, each criterion is checked first. If a criterion is not applied (for example, "Show All" for status), the condition is evaluated as true to allow the logic to move to the next criterion. If a criterion is selected, the condition then checks if the record matches the chosen value. This allows you to filter films based on multiple criteria simultaneously.

      • szormpas
      • 4 mths ago
      • Reported - view

        Hi, thanks. I learned something new, which is very useful.

      For example, in Ninox, it's okay to write :

      Select 'Table' where true and true

      and you still get all records of Table!

      • szormpas
      • 4 mths ago
      • Reported - view

       I thought that after the 'where' the condition should be related to the selecting table, but it turns out it can evaluate anything!
      I can't thank you enough!

      • Ninox developper
      • Jacques_TUR
      • 4 mths ago
      • Reported - view

       Yes indeed, the where clause can contain any Ninox code, including variable declarations, another select, etc...

    • Chris_Mullett
    • 4 mths ago
    • Reported - view

    Thanks for your contributions chaps. Cracked it!

    let t := this;
    let myRecordSet := rsort((select Film) order by 'Index Number');
    if t.'Filter by Status' < 7 then
        let myfilter := myRecordSet[Status = number(t.'Filter by Status')];
        myRecordSet := myfilter
    end;
    if t.'Filter by Format' < 4 then
        let myfilter := myRecordSet['Film Format' = number(t.'Filter by Format')];
        myRecordSet := myfilter
    end;
    if 'Filter by Camera' != null then
        let myfilter := myRecordSet[Camera = number(t.'Filter by Camera')];
        myRecordSet := myfilter
    end;
    if 'Filter by Film Stock' != null then
        let myfilter := myRecordSet['Film Loaded' = number(t.'Filter by Film Stock')];
        myRecordSet := myfilter
    end;
    myRecordSet

      • Ninox developper
      • Jacques_TUR
      • 4 mths ago
      • Reported - view

       Congratulations, this code is indeed another option for creating a multi-filter. It requires more code, but it is more readable and therefore easier to maintain.

      Just one detail: the rsort command in the line rsort((select Film) order by 'Index Number'); effectively replaces the order by clause by sorting based on the ID (and not on 'Index Number'). Is this what you intended to do?

      If your goal was simply to reverse the sort order on 'Index Number', you just need to add a - in front of 'Index Number' instead of using rsort.

      Here’s how you could simplify your code:

       

      et t := this;
let myRecordSet := select Film) order by -'Index Number';
if t.'Filter by Status' < 7 then
    myRecordSet := myRecordSet[Status = number(t.'Filter by Status')];
end;
if t.'Filter by Format' < 4 then
    myRecordSet := myRecordSet['Film Format' = number(t.'Filter by Format')];
end;
if 'Filter by Camera' != null then
    myRecordSet := myRecordSet[Camera = number(t.'Filter by Camera')];
end;
if 'Filter by Film Stock' != null then
    myRecordSet := myRecordSet['Film Loaded' = number(t.'Filter by Film Stock')];
end;
myRecordSet
      • Chris_Mullett
      • 4 mths ago
      • Reported - view

       Thanks Jacques. Useful tip re the "-'' to reverse to sort order. I do indeed want to sort by -Index Number not Id. After posting my code I noticed sort wasn't as required and removed the rsort from line 2 and alter the last line to rsort(myRecordSet order by 'Index Number').  I've now changed it to myRecordSet order by -'Index Number'. Thanks also for the "myRecordSet:=myRecordSet......" structure. 

       

      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

       

      It would be interesting to have a sample DB

      • Ninox developper
      • Jacques_TUR
      • 4 mths ago
      • Reported - view

      Since we are talking about it, to reverse the order of a field of numeric type (or date) simply add a - in front. For a text was more complicated. I succeeded by creating a function that reverses the ASCII value of each character in the text: https://forum.ninox.com/t/p8hrzqj? R=h7hraxw

      • Chris_Mullett
      • 4 mths ago
      • Reported - view

      Thanks. Interesting thread, although most of it over my pay grade.

Login to reply

Content aside

  • Status Answered
  • 4 mths agoLast active
  • 20Replies
  • 103Views
  • 6 Following