0

Sort and display all times in order.

 

Hello everyone! In my table, I’ve set up two time fields (as shown in the image). I’m wondering if I can create a code snippet in the ‘View’ table to display all the entries of time1 and time2 in sequential order. I tried some code, but it wasn’t successful. Thanks for your help!

let getCopy := (select 'table');
let pailie := for xx in getCopy do
        let times := [];
        if xx.'time1' != null then
            times := times + [xx.'time1']
        end;
        if xx.'time2' != null then
            times := times + [xx.'time2']
        end;
        let sortedTimes := sort(times);
        let formattedTimes := for time in sortedTimes do
                format(time, "YYYY-MM-DD")
            end;
        formattedTimes
    end;

45 replies

null
    • Fred
    • 1 mth ago
    • Reported - view

    If you don't want to go the child table route then here is what you need to do make your original code work:

    let getTimeRecs := (select Data);
let getTime1 := getTimeRecs.Time1;
let getTime2 := getTimeRecs.time2;
let allTimesSorted := sort(array(getTime1, getTime2));
let newDataTable := for loop1 in allTimesSorted do
        let rec := first(getTimeRecs[Time1 = loop1 or time2 = loop1]);
        {
            id: rec.Id,
            date: format(loop1, "YYYY,MM,DD"),
            name: rec.name
        }
    end;
"-------------------------------------------------------------------------";
let Xstbody := newDataTable.("
          <tr onclick=ui.popupRecord('" +
    raw(first(getTimeRecs[number(Id) = number(Id)])) +
    "')>
            <td>" +
    date +
    "</td>
            <td>" +
    name +
    "</td>
          </tr>");
"-------------------------------------------------------------------------";

    Lines 1 - 4, I created an array of the data from the Time1 and time2 fields.

    Lines 5 - 12, Using the new array from above, I created a JSON that tracks the record Id, the date, and the name associated from the record. It works in the dataset since there are no duplicate dates. If you have a dataset with duplicate dates then that is another solution. :)

    Line 16, I used the record Id from the JSON to find the first record where the date matches either Time1 or time2. Then I used the raw() command to just return the table and record Id. So now the ui.popupRecord() now knows which record to open.

      • gold_cat
      • 1 mth ago
      • Reported - view

       The new code in Program 3 is indeed not displaying.

      fred-woshiv1.ninox
      • Fred
      • 1 mth ago
      • Reported - view

      I think you uploaded the wrong version. I dont see a 3 in test.

      • gold_cat
      • 1 mth ago
      • Reported - view

       Confirm again

      Fred-woshiv2.ninox
      • Fred
      • 1 mth ago
      • Reported - view

      You need the whole bottom part with html(). Take your code posted that was having issues. Then take my part and replace it the top part (matching the lines).

      • gold_cat
      • 1 mth ago
      • Reported - view
      let getTimeRecs := (select Data);
let getTime1 := getTimeRecs.Time1;
let getTime2 := getTimeRecs.time2;
let allTimesSorted := sort(array(getTime1, getTime2));
let newDataTable := for loop1 in allTimesSorted do
        let rec := first(getTimeRecs[Time1 = loop1 or time2 = loop1]);
        {
            id: rec.'身份标识',
            date: format(loop1, "YYYY,MM,DD"),
            name: rec.name
        }
    end;
"-------------------------------------------------------------------------";
let Xstbody := newDataTable.("
          <tr onclick=ui.popupRecord('" +
    raw(first(getTimeRecs[number('身份标识') = number('身份标识')])) +
    "')>
            <td>" +
    date +
    "</td>
            <td>" +
    name +
    "</td>
          </tr>");
"-------------------------------------------------------------------------";
html("
    <style>
</style><table class='table table-striped table-hover  caption-top'>
  <thead class='table-primary'>
    <tr>
            <th scope='col'>Date</th>
            <th scope='col'>Name</th>
          </tr>
        </thead> <tbody >" +
Xstbody +
"</tbody><tfoot>
  </tfoot>
</table>
");
debugValueInfo(Xstbody)

      I've tried multiple times, but Xstbody indeed doesn't return any data. Am I doing something wrong?

       

    • Fred
    • 1 mth ago
    • Reported - view
     said:
    I've tried multiple times, but Xstbody indeed doesn't return any data. Am I doing something wrong?

     1) for the xstbody formula field, change the debugValueInfo() to:

    debugValueInfo(newDataTable)

    What does that return?

    2) What field is

    身份标识
    • gold_cat
    • 1 mth ago
    • Reported - view

     
     

     said:
    What does that return?

     this👇 

    any([{"id":"A3","date":"2024,10,10","name":"Xiao Ru"},{"id":"A7","date":"2024,10,12","name":"Li Yanping "},{"id":"A1","date":"2024,10,15","name":"Xiao Ru"},{"id":"A2","date":"2024,10,16","name":"Jiang Ningwei "},{"id":"A4","date":"2024,10,16","name":"Jiang Ningwei "},{"id":"A6","date":"2024,10,17","name":"Yang Daibing "},{"id":"A3","date":"2024,10,17","name":"Xiao Ru"},{"id":"A5","date":"2024,10,19","name":"Xiao Li "},{"id":"A5","date":"2024,11,01","name":"Xiao Li "},{"id":"A1","date":"2024,11,04","name":"Xiao Ru"},{"id":"A1","date":"2024,11,04","name":"Xiao Ru"},{"id":"A4","date":"2024,11,05","name":"Jiang Ningwei "},{"id":"A4","date":"2024,11,05","name":"Jiang Ningwei "},{"id":"A6","date":"2024,11,18","name":"Yang Daibing "}])

     

     said:
    2) What field is

     "身份标识" is the Chinese display for "Id."

    • Fred
    • 1 mth ago
    • Reported - view

    I don't know what is up. I have:

    let getTimeRecs := (select Data);
let getTime1 := getTimeRecs.Time1;
let getTime2 := getTimeRecs.time2;
let allTimesSorted := sort(array(getTime1, getTime2));
let newDataTable := for loop1 in allTimesSorted do
        let rec := first(getTimeRecs[Time1 = loop1 or time2 = loop1]);
        {
            id: rec.Id,
            date: format(loop1, "YYYY,MM,DD"),
            name: rec.name
        }
    end;
"-------------------------------------------------------------------------";
let Xstbody := newDataTable.("
          <tr onclick=ui.popupRecord('" +
    raw(first(getTimeRecs[number(Id) = number(Id)])) +
    "')>
            <td>" +
    date +
    "</td>
            <td>" +
    name +
    "</td>
          </tr>");
"-------------------------------------------------------------------------";
html("
    <style>
</style><table class='table table-striped table-hover  caption-top'>
  <thead class='table-primary'>
    <tr>
            <th scope='col'>Date</th>
            <th scope='col'>Name</th>
          </tr>
        </thead> <tbody >" +
Xstbody +
"</tbody><tfoot>
  </tfoot>
</table>
    ")

    and I see:

    • Fred
    • 1 mth ago
    • Reported - view

    so I did a bit more poking around to see why your code doesn't work.

    I copied your code into a text editor, Sublime Text, and I got:

    Notice all the html code for non breaking space. When I copied my code I see:

    tab marks instead of non-breaking spaces.

    So I went back into your code in Ninox and cleaned up all of the "spaces" and the code started working.

    Are you adding spaces to make your code more readable?

      • gold_cat
      • 1 mth ago
      • Reported - view

           Thank you for Fred reply.

      It’s strange. When I upload the database to the cloud, it displays correctly, but it doesn’t work in the offline version on my Mac.

      I didn’t add the spaces myself; this is the style automatically generated by Ninox.

      iShot_2024-11-12_09.17.48 - 01
      • gold_cat
      • 1 mth ago
      • Reported - view

       I found some additional issues. When I click on a list item, it doesn’t correctly navigate to the corresponding item, and the display window automatically generates some spaces. (Cloud version, same code)

      iShot_2024-11-12_09.58.52 - 01
    • Fred
    • 1 mth ago
    • Reported - view
     said:
    When I click on a list item, it doesn’t correctly navigate to the corresponding item,

    oops, sorry, my mistake. I shouldn't have used 'id' for the JSON key name. Ninox keeps autocorrecting to 'Id' so it looked like it was doing something but it wasn't.

    Try this out:

    let getTimeRecs := (select Data);
let getTime1 := getTimeRecs.Time1;
let getTime2 := getTimeRecs.time2;
let allTimesSorted := sort(array(getTime1, getTime2));
let newDataTable := for loop1 in allTimesSorted do
        let rec := first(getTimeRecs[Time1 = loop1 or time2 = loop1]);
        {
            xid: rec.Id,
            date: format(loop1, "YYYY,MM,DD"),
            name: rec.name
        }
    end;
"-------------------------------------------------------------------------";
let Xstbody := newDataTable.("
          <tr onclick=ui.popupRecord('" +
    (
        let t := this;
        raw(first(getTimeRecs[Id = t.xid])) +
        "')>
            <td>" +
        date +
        "</td>
            <td>" +
        name +
        "</td>
          </tr>"
    ));
"-------------------------------------------------------------------------";

    Line 8, I changed the key value to 'xid' so there would be no autocorrect.

    Line 17, was needed because Ninox couldn't find the xid key without it. Don't know why, but there is probably a good reason why Ninox can't read the key inside a Ninox function. Smarter minds can enlighten us.

    Line 18, now we reference the variable from line 17.

      • gold_cat
      • 1 mth ago
      • Reported - view

       Thank you, Fred! The cloud version is working now! The current issue is that the local version isn’t working...

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

    I imagine this is not possible to fix. ( The undefined ) 

      • gold_cat
      • 1 mth ago
      • Reported - view

       Can your list be displayed properly?

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

       

      Hi I can display correctly my view and the link work correctly.

      • gold_cat
      • 1 mth ago
      • Reported - view

       This issue is puzzling; I even re-downloaded the client, but it still won’t display.

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

       

      Resolve

    • Fred
    • 1 mth ago
    • Reported - view
     said:
    The current issue is that the local version isn’t working...

    Yeah, it looks like the app with local stored DB doesn't like using JSON data to build html. If both environments are important then it looks like you will have to use the child table, which might seem annoying, but will make things easier in the long run.

      • gold_cat
      • 1 mth ago
      • Reported - view

       Thank you, Fred, for providing the solution.

Login to reply

Content aside

  • Status Answered
  • 1 mth agoLast active
  • 45Replies
  • 191Views
  • 6 Following