0

Sort json?

Hi everyone -

I'm trying to create an HTML table but want to be able to sort it, probably by using a choice field that would modify the order. I can't just sort the select because one of the fields is a sum() of a collection of records. I have this code:

let getData := (select Reservations);
let monthList := sort(unique(getData.month_calc));
for loop1 in monthList do
    let x := getData[month_calc = loop1];
    {
        monthName: first(x.month_name),
        Nights: sum(x.Nights)
    }
end

which gives me the following data:

[{"monthName":"January","Nights":5},{"monthName":"March","Nights":10},{"monthName":"July","Nights":13},{"monthName":"August","Nights":13},{"monthName":"September","Nights":3},{"monthName":"October","Nights":8},{"monthName":"November","Nights":4}]

I would like to sort by Nights or by monthName.

Is there another way of doing this?

11 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    Can you send a sample database?

    • Rafael Sanchis
    • Rafael_Sanchis
    • 1 yr ago
    • Reported - view

    Fred

    I don't know if this is what you need, I have this HTML table and by 'Busqueda por Categoria'  (Multiple Choice dynamic) I can search my spenses by each category, example by Gas, Water etc

      • Fred
      • 1 yr ago
      • Reported - view

      Looks good. I need to sort not search.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

       😣 sorry

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr ago
      • Reported - view

        Wow, that dashboard looks great! Even with the sunrise and sunset and moon phase.... I love it!

      • Kruna
      • 1 yr ago
      • Reported - view

      RafaelHI Rafael, would you mind to sharing (of course without data) this db or at least the table(s) involved. I am trying to accomplish (reading other posts too) this too, but I am getting stucked somehow, thnx.

      Saludos Kruna

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr ago
      • Reported - view

        Yes, That would be incredibly kind of  👍

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      hola Steven, Fred.  Comparte la DB, está en español lo siento.

      I hearst improvements

      GP.ninox
    • Ninox developper
    • Jacques_TUR
    • 1 yr ago
    • Reported - view

    a JSON variable is like table result. You use functions sum, order, first, last…

    To sort your result, just add « Order by Nights » after the for loop :

    or, use one variable like « var res := for i…. » and after sort res as you whant : « if sortByNights then res order by Nights else res order by monthName. 

    • Fred
    • 1 yr ago
    • Reported - view

    Thanks . I was hoping for something simple and you have provided. I did not know you could add order by to the end of a for loop.

    Here is the final code:

    let getData := (select Reservations);
let monthList := sort(unique(getData.month_calc));
for loop1 in monthList do
    let x := getData[month_calc = loop1];
    {
        monthName: first(x.month_name),
        Nights: sum(x.Nights)
    }
end order by -number(Nights)

    For some reason Ninox does not treat Nights as a number field so I had to add the number() command before Ninox would accept the " - " sign to do a reverse sort.

    Here is what the table looks like after the sort:

    Excuse the red background, I took the code from another field and haven't bothered to fuss with the formatting while I worked on making things work.

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

       all values in JSON are supplied as any. This is why you need to convert them before using them.

      To sort your table by Nights and Months, you can double the order by function: order by monthName order by Nights.

      In the event of a tie between two Nights values, the months will be sorted.

Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 11Replies
  • 189Views
  • 5 Following