Order by descending

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.

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

 

let xVar := (select Clients)

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.

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.

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.

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);

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

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().

 

 

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

 

 

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.

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.

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

....

Oops, I made a mistake in this line :

var sortedTable := (Table order by (

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!

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'}-;

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

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.

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

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

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?

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.

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