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
    • 2 wk ago
    • Reported - view

    There is no time data in time2.

    Do you mean you want to sort Date then Time?

      • gold_cat
      • 2 wk ago
      • Reported - view

      Hi, Fred, My example is not very precise; I just need the dates sorted, and the times can be ignored.

    • Database Consultant
    • Kirkness_Associates_Ju
    • 2 wk ago
    • Reported - view

    Hi 

    There are a couple of issues here:

    • you are creating an array of dates and trying to add these view element which is not possible - view elements are designed to display records which can then be linked through from the view.
    • I have discovered that the array needs to be initialised with a type in order for the addition of elements to work - so let times := [text(void)].

    I have created the following code which will display these dates in a formula field (with each on a separate line):

    let getTimeRecs := (select Table);
    let times := [text(void)];
    for timeRec in getTimeRecs do
        if timeRec.'Time 1' != null then
            times := array(times, [format(timeRec.'Time 1', "YYYY-MM-DD")])
        end;
        if timeRec.'Time 2' != null then
            times := array(times, [format(timeRec.'Time 2', "YYYY-MM-DD")])
        end
    end;
    let sortedTimes := sort(times);
    join(sortedTimes, "
    ")

    Not sure if this will help you or not but feel free to come back.

      • gold_cat
      • 2 wk ago
      • Reported - view

       Thanks for the help! After several adjustments, the following code works, and I need to insert it into a table. However, the date sorting functionality is lost. Could you help me with this new issue? Thank you!

      let getTimeRecs := (select 'Table');
      let timesText := "";
      for timeRec in getTimeRecs do
          if timeRec.'Time 1' != null then
              timesText := timesText + "<tr><td>" + format(timeRec.'Time 1', "YYYY/MM/DD") + "</td>"
          end;
          if timeRec.'Time 2' != null then
              timesText := timesText + "<tr><td>" + format(timeRec.'Time 2', "YYYY/MM/DD") + "</td>"
          end;
      end;
      let sortedTimes := sort(timesText);
      html("
          <style>
      </style>
      <div style='max-height: 500px; overflow-y: auto;'>
        <table class='table table-striped table-hover caption-top' style='table-layout: fixed; width: 100%;'>
          <thead class='table-primary'>
            <tr style='position: sticky; top: 0; background-color: #f8f9fa;'>
              <th scope='col'>日期</th>
            </tr>
          </thead>
          <tbody>" +
      sortedTimes +
      "</tbody>
          <tfoot>
            <tr style='position: sticky; bottom: 0; background-color: #f8f9fa;'>
              <th colspan='3' style='color:#4169FB' class='text-end'>
                <mark>" +
      cnt(getTimeRecs) +
      "</mark> 个
              </th>
            </tr>
          </tfoot>
        </table>
      </div>
      </div>")
      
    • Database Consultant
    • Kirkness_Associates_Ju
    • 2 wk ago
    • Reported - view

    Hi 

    You seem to have changed the code so that times are no longer an array so won't sort. I have combined the code I had last time with a simple one column table structure which works for me (note the for loop going through the sorted array to build the data area of the table):

    let getTimeRecs := (select Table);
    let times := [text(void)];
    for timeRec in getTimeRecs do
        if timeRec.'Time 1' != null then
            times := array(times, [format(timeRec.'Time 1', "YYYY-MM-DD")])
        end;
        if timeRec.'Time 2' != null then
            times := array(times, [format(timeRec.'Time 2', "YYYY-MM-DD")])
        end
    end;
    let sortedTimes := sort(times);
    let tableData := "";
    for time in sortedTimes do
        tableData := tableData + "<tr><td>" + time + "</td></tr>"
    end;
    html("<table border='1' style='border-collapse: collapse; width: 100%;'>
    <tbody>
    <tr>
    <td style='width: 100%;'>Column Header</td>
    </tr>" +
    tableData +
    "</tbody>
    </table>")

    Hope this helps

      • gold_cat
      • 13 days ago
      • Reported - view

       Thank you, my friend, that is useful for me!

      • gold_cat
      • 13 days ago
      • Reported - view

       Hi, can code be added to the table to enable clicking and jumping to the corresponding data?

    • Database Consultant
    • Kirkness_Associates_Ju
    • 13 days ago
    • Reported - view

    There is a way I've found (not done this before) which opens the record in a view (I made the view in question a Form View so that no record list showed) - but I haven't discovered how to make it a popup:
     

    let getTimeRecs := (select Table);
    let times := [text(void)];
    for timeRec in getTimeRecs do
        if timeRec.'Time 1' != null then
            times := array(times, [format(timeRec.'Time 1', "YYYY-MM-DD") + " #" + timeRec.Id])
        end;
        if timeRec.'Time 2' != null then
            times := array(times, [format(timeRec.'Time 2', "YYYY-MM-DD") + " #" + timeRec.Id])
        end
    end;
    let sortedTimes := sort(times);
    let tableData := "";
    for time in sortedTimes do
        if time then
            tableData := tableData + "<tr><td>" + first(split(time, "#")) +
                "</td><td><a href = 'https://app.ninox.com/#/teams/{TeamID}/database/{DatabaseID}/module/C/view/{ViewID}/node/C" +
                last(split(time, "#")) +
                "/tab/0'>Link</a></tr>"
        end
    end;
    html("<table border='1' style='border-collapse: collapse; width: 100%;'>
    <tbody>
    <tr>
    <td>Column Header</td><td>Link</td>
    </tr>" +
    tableData +
    "</tbody>
    </table>")

    As you can see, I added the record ID into the array values preceded with a hash, then spilt it out and used the first and last elements in the table - with the last element being the record ID which I was able to build into a link. You can get the relevant Ids by copying the URL with one of the target records open.

    The if time then section removes a blank row which otherwise appears at the top of the table.

      • gold_cat
      • 13 days ago
      • Reported - view

       Hi, friend! My previous expression was not very accurate. What I want to express is the effect of popping up records. This has been achieved in other tables, but this case is a bit different.Thank you for your assistance!

      let XsTable := (select Data);
      "-------------------------------------------------------------------------";
      let Xstbody := XsTable.---
                <tr onclick="ui.popupRecord('{ 'Id' }')">
                  <td>{ format(Time1, "YYYY-MM-DD") }</td>
                  <td>{ format(time2, "YYYY-MM-DD") }</td>
                </tr>
          ---;
      "-------------------------------------------------------------------------";
      html("<div class='bootstrap5'>
          <style>
      </style><table class='table table-striped table-hover  caption-top'>
        <thead class='table-primary'>
          <tr>
                  <th scope='col'>Time1</th>
                  <th scope='col'>time2</th>
                </tr>
              </thead> <tbody >" +
      Xstbody +
      "</tbody><tfoot>
        </tfoot>
      </table>
      
          </div>")

      This is the code for the pop-up case.

      • Alain_Fontaine
      • 13 days ago
      • Reported - view

       The blank row comes from:

      let times := [text(void)];
      

      This initializes the array with a single element containing an empty string. To produce an empty array, while still setting the type of the elements, you can use:

      let times := slice([""],0,0);
      • gold_cat
      • 11 days ago
      • Reported - view

      Hi , could you please help me with this?

      • Fred
      • 11 days ago
      • Reported - view

      When I do:

      let table1Recs := (select Table1);
      let tableBody := table1Recs.---
          <tr onclick="ui.popupRecord('{ Id }')">
              <td>{ format(Date, "YYYY-MM-DD") }</td>
          </tr>
          ---;
      html("<table>
          <thead>
              <tr>
                  <td>Date</td>
              </tr>
          </thead>
          <tbody> " +
      tableBody +
      "
          </tbody>
      </table>")
      

      It works for me.

      Can you post a sample DB?

      • gold_cat
      • 11 days ago
      • Reported - view

       Hi Fred, I want the first list to also have the click popup feature that the second list has. How can I implement this?

      • Fred
      • 11 days ago
      • Reported - view

      Have you tried copying over the code from 2 to 1 and then make adjustments to the Xstbody?

      • gold_cat
      • 11 days ago
      • Reported - view

       Yes, I have tried many times without success

      • Fred
      • 11 days ago
      • Reported - view

      Post one of your tries and we can figure it out.

      • gold_cat
      • 11 days ago
      • Reported - view

       I can think of it. .

      let getTimeRecs := (select Data);
      let times := slice([""], 0, 0);
      for timeRec in getTimeRecs do
          if timeRec.Time1 != null then
              times := array(times, [format(timeRec.Time1, "YYYY-MM-DD") + " " + timeRec.name])
          end;
          if timeRec.time2 != null then
              times := array(times, [format(timeRec.time2, "YYYY-MM-DD") + " " + timeRec.name])
          end
      end;
      let XsTable := sort(times);
      "-------------------------------------------------------------------------";
      let Xstbody := XsTable.---
                <tr onclick="ui.popupRecord('{ '身份标识' }')">
                  <td>{ times }</td>
                </tr>
          ---;
      "-------------------------------------------------------------------------";
      html("<div class='bootstrap5'>
          <style>
      </style><table class='table table-striped table-hover  caption-top'>
        <thead class='table-primary'>
          <tr>
                  <th scope='col'>Time1</th>
                  <th scope='col'>time2</th>
                </tr>
              </thead> <tbody >" +
      Xstbody +
      "</tbody><tfoot>
        </tfoot>
      </table>
      
          </div>")
      
      • Fred
      • 10 days ago
      • Reported - view

      Review Rafael's and my responses. You will see that ui.popupRecord() uses the special Ninox field Id. You can only use this field, or a variable containing this field, as this is the only way Ninox knows which record to open.

      Your code that creates your array leaves out this very important bit of information. But adding the Id field to your array turns it into a number when we need it to stay in nid (a Ninox format that stores table and record information) form.

      I understand why you created an array as you have two date fields that you need to sort by. You are running into the issue of have two fields that store the same type of data. If you created a child table that stored the date data in separate records then your data would be much easier to work.

      Take a look at the attached DB. You will see that I have created a new child table (DateData) and copied over the date info from the parent to the child (used the new get() command again).

      Now check out the test dashboard and you will see the data in the 1 field.

      Here is the important bit:

      let XsTable := ((select DateData) order by Date);
      "-------------------------------------------------------------------------";
      let Xstbody := XsTable.---
                <tr onclick="ui.popupRecord('{ Data.Id }')">
                  <td>{ format(Date, "YYYY-MM-DD") }</td>
                  <td>{ Data.name }</td>
                </tr>
          ---;
      

      The child table does the same thing you did previously in 11 lines of code in 1, while preserving the table information.

      On line 4, while I'm in the child record, if I want to reference the parent record I can simply use the reference field. You can change it to the child record by just removing 'Data'.

      Just a reminder that if you find yourself using a 1 or 2 after a field name that stores the same type of data, then you should probably create a child table. Not to say there might not be a use for it, but more times than not a child table is the way to go.

    • Rafael Sanchis
    • Rafael_Sanchis
    • 13 days ago
    • Reported - view
    let XsTable := (select Table);
    let Xstbody := XsTable.---
    <tr onclick="ui.popupRecord('{ Id }')">
        <td>{ format('Time 1', "DD-MM-YYYY") }</td>
        <td>{ format('Time 2', "DD-MM-YYYY") }</td>
    </tr>
        ---;
    html("<div class='bootstrap5'><style></style><table class='table table-striped table-hover caption-top'><thead class='table-primary'><tr><th scope='col'>Time1</th><th scope='col'>Time2</th></tr></thead><tbody>" +
    Xstbody +
    "</tbody><tfoot></tfoot></table></div>")
    

    Delete the top two blank lines

    • Rafael Sanchis
    • Rafael_Sanchis
    • 12 days ago
    • Reported - view

    Hi.

    When select the Month on (Seleccion Mes) can't see the Prueba but can see the documents on last 3 month. Some idea ?

      • gold_cat
      • 12 days ago
      • Reported - view
      let XsTable := ((select Datos) order by CodDoc);
      let mesSeleccionado := 'Seleccion Mes';
      let XsTableFiltrado := XsTable[contains(text(format('Rev 0', "MMM dd, yyyy")), text(mesSeleccionado))];
      ...
      
      

       

      Not sure if this has resolved your issue.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 12 days ago
      • Reported - view

       

      Thanks works

    • Fred
    • 10 days 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
      • 10 days ago
      • Reported - view

       

      Thanks to Fred for providing the two methods, and it’s even more exciting that there’s a way that doesn’t require changing the database structure.

      I tried the code above, but it doesn’t seem to display correctly. I checked that newDataTable does return data, but Xstbody does not. Could we explore this further?

      • Fred
      • 10 days ago
      • Reported - view

      sure we can take a look. post the db.

Content aside

  • Status Answered
  • 7 days agoLast active
  • 45Replies
  • 179Views
  • 6 Following