1

Order by descending

When selecting records you can't specify descending?

38 replies

null
    • Customer Service Ninox
    • Maria_pasini
    • 2 yrs ago
    • Reported - view

    Hello Jesper,

    Please try using the code:
    rsort() To sort an array in descending order.

    I hope this helps

      • SvenSteffen_Arndt
      • 1 yr ago
      • Reported - view

      Maria you don't need to do "Order by Desc" - the idea is to select the data with the select function. Then put the data in a view field and configure the view field so that you order by any data field in the way you like.

    • UMAKERS
    • Jesper_Ordrup
    • 2 yrs ago
    • Reported - view

    Thanks Maria,

    rsort(array) - sorts an array in descending order

    Im not sure how to specify which field i want to sort on. The records are complex types with multiple fields and I might want to sort on the name or the date field.

    Jesper

    • Fred
    • 2 yrs ago
    • Reported - view

    Hi Jesper -

     

    if you put your selection into a variable, let xVar := (select Table1 where field1 = true).field1.

     

    Then you can rsort, rsort(xVar). You can put that in a variable, let xRSort := rsort(xVar), so you can then use it later in your script.

     

    Don't know why we can't just do something like rOrder by and be done. Anyways, Good luck.

    • UMAKERS
    • Jesper_Ordrup
    • 2 yrs ago
    • Reported - view

    Thanks Fred,

    Your example solves a different usecase. At least I think. The outcome of this will result in a list of field1 that are sorted in decending order. What I'm looking for is sorting the records so I can loop them all.

    let xVar := (select Clients).Name;
    let xRSort := rsort(xVar);
    xRSort

    This would return a list of names, not records.

    I think its not possible directly at least.

     

    I've done a workaround that actually works. Method was to use Item method to get each element in a list using an indexcounter that was counted down from maxlength. But it sure is less intuitive and not for everybody :-)

    • UMAKERS
    • Jesper_Ordrup
    • 2 yrs ago
    • Reported - view

    If anyone wants an example of my workaround, let me know

    • Fred
    • 2 yrs ago
    • Reported - view

    If you want to be more flexible, then you can change your select:

     

    let xVar := (select Clients)

    • Sean
    • 2 yrs ago
    • Reported - view

    Fred,

    That by itself is an array of records, but you still need to specify the fields you want to see. If you simply concat(xVar), you will get a string of record Id's.

     

    Here's a solution that returns Name and Address fields sorted by Name...

     

    let xVar := (select Vendor);
    join(rsort(xVar.(Name + " @ " + Address)), "
    ")

     

    The result of the join() function seems to be a little buggy, e.g., I would get the same values at the beginning and the end of the list, but some would be missing. After I deleted the join() and saved and added the join() back in and saved, it worked correctly.

    • Sean
    • 2 yrs ago
    • Reported - view

    It's not the join() function. It does the same thing with the concat() function. When I switch between sort() and rsort(), I get the error.

    • Sean
    • 2 yrs ago
    • Reported - view

    It appears to be a bug in the Formula field. Once focus has moved from the current form by selecting a different database, table, tab, etc, and you return to the form with the above formula, it is rendered correctly.

    • Kaan_Dikmen
    • 2 yrs ago
    • Reported - view

    When the field I'm working with is of a number data type, I use:

      let xVar := select SomeTable order by (SomeNumber * -1);

     

    In your case you could try 'enumerate' your records with a helper Formula field (because "Select Table reverse order by Field" doesn't exist!!! Grumble...):

      "// placing a Formula field in Vendor Table";

      let sortedRecords := (select Vendor order by Name);

      let counter := 1;

      let position := 0; 

      for r in sortedRecords do

    if r.Name = Name then

          position := counter

        end;

        counter := counter + 1

      end;

      position

     

    Now each record in Vendor will have a HelperField with this formula that should give it its index in an array sorted by Name.

     

    Then, it should be as simple (!) as: 

        let xSorted := select Vendor order by (HelperField * -1);

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    Nice idea. It can be extended to dates: if you need to reverse sort on a date field, you can write, for example:

    (select SomeTable) order by today() - SomeDate

    • Sean
    • 2 yrs ago
    • Reported - view

    Just to be clear, I'm not saying that sort() and rsort() don't work. I am saying that the Formula field does not refresh the information that extends beyond the bottom of the field window unless the form view refreshed. That can be done by selecting another Tab or Table, but it shouldn't be necessary and I think it is a bug that Ninox should fix. The image below shows two identical sorts on the same data, but "Sort 1" has just been changed from sort() to rsort().

     

    Screen Shot 2021-06-29 at 11.47.18 PM

     

    The next image demonstrates what I am referring to...

     

    Screen Shot 2021-06-29 at 11.48.08 PM

     

    Finally, Ninox, I can no longer expand the comment window by clicking and dragging on the lower right corner. That used to work. I'm using Safari on macOS Big Sur.

    • Sean
    • 2 yrs ago
    • Reported - view

    I can expand the comment window before I type anything in it, but once I click inside the window it snaps back to a fixed size.

    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

    Hello everyone,

    you can use this solution to sort a table by multiple fields and each in ascending or descending order. Just change the < or > symbols to change the sorting direction of each field.

    < : ascending
    > : descending

    var table := (select Contacts);
    var sortedTable := (t order by (
                         var rec := this;
                         count( table[
                              Nom < rec.Nom or
                              (Nom = rec.Nom and 'Prénom' > rec.'Prénom')
                              ];
                          )
             ));

    sortedTable.(---{nom} {'Prénom'}---);

    Résult :

    ABRAMOWITZ Vinciane
    AGAUGUE LE JOSSEC Jeanne
    AGUERRE Colette
    ALBINET Frédéric
    ALEXANDRE Séverine
    ALLAIN Marie
    ...
    BERDUCOU Emilie
    BERDUGO Julia
    BERGERON Carolle
    BERGIER Marie-Laure
    BERNARD Stéphanie
    BERNARD Florence
    BERNARD Alice
    BERTAUD Sophie
    BERTELOOT Loic

    ....

    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

    Oops, I made a mistake in this line :

    var sortedTable := (Table order by (

    • Sean
    • 2 yrs ago
    • Reported - view

    Jacques,

    That is amazing. Is count() being used to iterate?! What was your inspiration for the code and the language it was in?

     

    I wonder if the developers imagined the creative ways their product/language would be used. Thank you for sharing!

    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

    Thanks Sean, I've been inspired by the different ways of coding I've found in this forum and especially by yours. Your many posts helped me a lot when I first started with Ninox and I think they help many other people. Thank you so much for that!!!.

    Then, when I don't understand well how the language works, I use the debugger to trace the Ninox script interpreter. And I found a lot of new ways to use the Ninox code. I will tell you about them.

    To answer your question, the count counts records that match the criteria (LastName < LastNameSearch) or (LastName= LastNameSearch) and (FirstName < FirstNameSearch). This value is the sort index of the record in the table.
    The iteration is done with the "t order by" instrcution which will search the sort value for each record.

    This solution is not bad, but I found even better:
    As a "Table order by" statement returns a sorted table, it is possible to use it as a table. And so you can sort again on this table.
    For example you can write :
    ((select Contacts order by 'Firstname') order by Lastname).(---{Lastname} {'Firstname'}-);

    This will display a list sorted by Last Name, then by First Name. You can even remove parentheses and simplify the code:
    (select Contacts order by 'First Name' order by Last Name).---{Last Name} {'First Name'}---;

    This makes it possible to sort on several fields in succession.

    For example, to sort by postcode (in France the postcode is numeric), you just need to add a - to the field to be sorted:
    (select Contacts order by -number('Postcode')).---{'Postcode'}---;

    To sort the texts in a descending way, you need to do the same thing, i.e. invert the ASCII value of each character. This is what this function does:

    function reverseAscii(t : text) do
    var ascii : = urlDecode("%C3%BF%C3%BE%C3%BD%C3%BC%C3%BB%C3%BA%C3%B9%C3%B8%C3%B7%C3%B6%C3%B5%C3%B4%C3%B3%C3%B2%C3%B1%C3%B0%C3%AF%C3%AE%C3%AD%C3%AC%C3%AB%C3%AA%C3%A9%C3%A8%C3%A7%C3%A6%C3%A5%C3%A4%C3%A3%C3%A2%C3%A1%C3%A0%C3%9F%C3%9E%C3%9D%C3%9C%C3%9B%C3%9A%C3%99%C3%98%C3%97%C3%96%C3%95%C3%94%C3%93%C3%92%C3%91%C3%90%C3%8F%C3%8E%C3%8D%C3%8C%C3%8B%C3%8A%C3%89%C3%88%C3%87%C3%86%C3%85%C3%84%C3%83%C3%82%C3%81%C3%80%C2%BF%C2%BE%C2%BD%C2%BC%C2%BB%C2%BA%C2%B9%C2%B8%C2%B7%C2%B6%C2%B5%C2%B4%C2%B3%C2%B2%C2%B1%C2%B0%C2%AF%C2%AE%C2%AD%C2%AC%C2%AB%C2%AA%C2%A9%C2%A8%C2%A7%C2%A6%C2%A5%C2%A4%C2%A3%C2%A2%C2%A1%C2%A0%C2%9F%C2%9E%C2%9D%C2%9C%C2%9B%C2%9A%C2%99%C2%98%C2%97%C2%96%C2%95%C2%94%C2%93%C2%92%C2%91%C2%90%C2%8F%C2%8E%C2%8D%C2%8C%C2%8B%C2%8A%C2%89%C2%88%C2%87%C2%86%C2%85%C2%84%C2%83%C2%82%C2%81%C2%80%7F~%7D%7C%7Bzyxwvutsrqponmlkjihgfedcba%60_%5E%5D%5C%5BZYXWVUTSRQPONMLKJIHGFEDCBA@? %3E=%3C;:9876543210/.-,+*)('&%25$#%22!%20%1F%1E%1D%1C%1B%1A%19%18%17%16%15%14%13%12%11%10%0F%0E%0D%0C%0B%0A%09%08%07%06%05%04%03%02%01%00");
    var rev := "";
    for i in t do
    rev := rev + item(ascii, 255 - index(ascii, i))
    end;
    rev
    end;

    Then just call the function in the close order by. The example below sorts the records by Last Name in a descending order and by First Name in an ascending order:
    (select Contacts order by 'First name' order by reverseAscii(Name)).---{Name} {'First name'}-;

    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

    And here are the other things I found:

    It is possible to create an array of JSON objects:

    var Table := (select Contacts).{
        "LeNom": Nom,
        "LeNom": 'Prénom',
        "Departement": substr('Postcode', 0, 2),
        id: number(ID)
        };
    Table;

    result :
    {"LeNom":"TOLEDANO","LePrénom":"Abraham","Departement":"30","id":"1362"}
    {"LeNom":"MOREL","LePrénom":"Adeline","Departement":"69","id":"1363"}
    {"LeNom":"RIONDET","LePrénom":"Adrien","Departement":"75","id":"1364"}
    {"LeNom":"AMAT","LePrénom":"Agnès","Departement":"13","id":"1365 »}
    ...

    This object table can be used as a record table. For example you can use the Order By clause:

    Table order by LeNom

    result :
    {"LeNom":"ABRAMOWITZ","LePrénom":"Vinciane","Département":"34","id":"1772"}
    {"LeNom":"AGAUGUE LE JOSSEC","LePrénom":"Jeanne","Département":"56","id":"1586"}
    {"LeNom":"AGUERRE","LePrénom":"Colette","Département":"33","id":"1999"}
    {"LeNom":"ALBINET","LePrénom":"Frédéric","Département":"92","id":"1972 »}
    ...

    We can also filter the object array :
    Table[substr(text(LeNom),0,3) = "BAR"]

    result :
    {"LeNom":"BARAN","LePrénom":"Diane","Département":"31","id":"1499"}
    {"LeNom":"BARNAS","LePrénom":"Fouzia","Département":"13","id":"1544"}
    {"LeNom":"BARD","LePrénom":"Marie-Catherine","Département":"75","id":"1656"}
    {"LeNom":"BARBANCEY","LePrénom":"Soizic","Département":"26","id":"1814"}
    {"LeNom":"BARRET","LePrénom":"Emilie","Département":"69","id":"1970"}
    ...

    now let's see something a little more complicated, a grouping by Nom :

    var GroupByName := unique((Table order by LeNom).LeNom ).(var n := this;
         var recs := Table[LeNom = n].id;
        { Nom:n,
           "nbPrénom":count(recs),
           "Prénoms":recs,
            nomId: first(recs)

        }

    );
    GroupByName order by -number('nbPrénom');

    result :
    {"LeNom":"BERNARD","nbPrénom":3,"Prénoms":[1746,1875,2318],"nomId":1746}
    {"LeNom":"BERTRAND","nbPrénom":3,"Prénoms":[1702,2018,2042],"nomId":1702}
    {"LeNom":"FABRE","nbPrénom":3,"Prénoms":[1636,1664,2027],"nomId":1636}
    {"LeNom":"FONTAINE","nbPrénom":3,"Prénoms":[1532,1602,1931],"nomId":1532}
    {"LeNom":"BAILLY","nbPrénom":2,"Prénoms":[2273,2274],"nomId":2273}
    {"LeNom":"BOURGOIN","nbPrénom":2,"Prénoms":[1689,2353],"nomId":1689}

    Here we filter the Contacts table by unique Country. If a country is present more than once, only the first one is taken. The result of this query can be used as a source for a dynamic choice field. This can be used to display the list of Countries present in the Contacts table:

    var Table := ((select Contacts) order by Pays).{
                 pays: Pays,
                id: number(ID)
    };
    var ListeDePays := unique(Table.pays).(var p := this;
                                   record(Contacts,number(Table[pays = p].id))
                               );
    ListeDePays.--- { Pays } { ID } ---;

    result :

     Australie G1981
    Espagne G1979
    FR G2073
    GUADELOUPE G1761
    GUYANE G1774
    Guadeloupe G2140
    LUXEMBOURG G2080
    Pays Bas G2015
    SOUTH AFRICA G2027
    South Africa G1996
    USA G2337

    • Sean
    • 2 yrs ago
    • Reported - view

    Jacques,

    Wow! That is a lot to digest and thank you for sharing. I sure hope other users can find this information in the future. I had never considered using compound "order by" clauses. I think Alain Fontaine commented in another thread on being surprised at what one could throw at the Ninox interpreter. I'm not sure the developers know entirely what it's capable of.

     

    Does your debugging indicate that most of the Ninox interpreter is written in JavaScript? Also, the output formatting you use looks a lot like Template Literals in JavaScript. Is that where you got the idea?

     

    By the way, the issue I described previously in this thread about the Formula field not updating the list view ordering still exists with your sorting algorithm. There is a bug with the Formula field.

    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

    Yes I agree, it's a lot to take in at once :o)

    Can you give me the thread where Alain talks about what can be given to the Ninox interpreter?

    Ninox is written entirely in JavaScript and can be accessed from the browser debugger. Only data management and printing of documents are on a server. Everything else is local and potentially accessible. I was able, for example, to modify the Eval function to run JavaScript code, or to print a defined list of records (currently you can only print one record or the whole table). There are many things you can do with Ninox.

    For your problem, I did not succeed in reproducing it which prevents me from finding the solution. It seems to be a stealthy bug that could depend on your configuration (desktop or cloud).

    For indexing according to a sort on several fields, you can put this code in a formula. It returns the index number of the record according to the sorting requested:

    var index := 0;
    var Table := (((select Contacts) order by 'First Name') order by Last Name).ID;
    while index < count(Table) and item(Table, index) != this.ID do
    index := index + 1
    end;
    index

    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

    Oops, here is the English translation !

    Yes I agree, it's a lot to take in at once :o)

    Can you give me the thread where Alain talks about what can be given to the Ninox interpreter?

    Ninox is written entirely in JavaScript and can be accessed from the browser debugger. Only data management and printing of documents are on a server. Everything else is local and potentially accessible. I was able, for example, to modify the Eval function to run JavaScript code, or to print a defined list of records (currently you can only print one record or the whole table). There are many things you can do with Ninox.

    For your problem, I did not succeed in reproducing it which prevents me from finding the solution. It seems to be a stealthy bug that could depend on your configuration (desktop or cloud).

    For indexing according to a sort on several fields, you can put this code in a formula. It returns the index number of the record according to the sorting requested:

    var index := 0;
    var Table := (((select Contacts) order by 'First Name') order by Last Name).ID;
    while index < count(Table) and item(Table, index) != this.ID do
    index := index + 1
    end;
    index

    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

    Sean, maybe to solve your problem you could try to create a global variable?

    To do this you create a GlobalVars table in which you add a "Vendros" formula which contains for example :

    select Vendor order by Name

     

    Then, in your Vendor table, you add a formula that fetches the sorted table from your global variables and displays it:

    var xVar := first(select GlobalVars).Vendros;

    join(rsort(xVar.(Name + " @ " + Address)), "
    ")

     

    Tell me if this fixes the bug?

    • Sean
    • 2 yrs ago
    • Reported - view

    Jacques,

    I get the stupid Ninox 404 message when I search on the obvious keywords compiler and interpreter, https://ninox.com/en/forum/ideas-and-suggestions-5abb9f4f45eda7ea1e75ed02/change-your-404-message-6110af5979b37e7d1c60e807 , so I can't find the thread with Alain's comment based on that. Also, because Ninox decided to diminish the http() function, https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/http-function-isnt-working-6030ae29ac80b9548fa31144 , the forum scraper I developed with Ninox no longer works and Alain's comment must have been after that.

     

    If you have the Mac app its easy to duplicate the sort issue. Use any sort algorithm you like and display the list in a Formula field and scroll up or down to make sure the order is correct. Next, edit the formula and change the sort order to the opposite order and after you save that change scroll up and down in the Formula field. You should see that the view has not been refreshed beyond the initial view window. Switch to another table or tab and when you come back to the Formula field it will be refreshed.

    • Sean
    • 2 yrs ago
    • Reported - view

    Maybe you can find a workaround for the http() function. ;)

Content aside

  • 1 Likes
  • 1 yr agoLast active
  • 38Replies
  • 3351Views
  • 4 Following