1

Export child table

I might be wrong but there doesn't seem a native way how to export records in a child table. Seems the best option atm is to use the ninext from Jacques TUR https://forum.ninox.com/t/x2hbnct/using-a-button-to-export-multiple-records#y4hst7p .
 

Or?

13 replies

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

    What do you mean by "exporting records to a child table"? 

    - Copy records to the same database?

    - Copy records to another database? 

    • Fred
    • 1 yr ago
    • Reported - view

    Do you mean you want to export a child table from the parent?

    You can always just go to the actual table and export the data.

    • Cloud DevOps Fullstack Engineer
    • Martin_Mueller
    • 1 yr ago
    • Reported - view

    Oh sorry yes. Exporting to like excel or csv :). 

    Happy new year and thank you so much for being so supportive <3.

    • Cloud DevOps Fullstack Engineer
    • Martin_Mueller
    • 1 yr ago
    • Reported - view

    Jacques TUR I am trying to implement the export button using ninext as you described in the post, I posted:

    var fileName := "myExport2.csv";
    var exportSettings := {
        sep: ",",
        qut: """",
        lf: "%0D%0A",
        numberFormat: "point",
        dateFormat: "locale",
        header: true,
        sepHeader: false,
        encoding: "utf8"
    }
    var data := #{:text:callback
        window.util.renderCSV(window.database, window.ui.currentView.table, exportSettings)
            .then((textData) => {
                callback(textData);
            })
    }#;
    createTextFile( this, data, fileName );
    

    But it looks like the new line separator is wrong as I get the complete result in one line without new lines. 

    I added a debugger but I struggle to find out how I can reverse engineer window.util.renderCSV(...) . How you do it normally :) ?

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

      Martin Mueller good job !! This will help those who want to automate the export.

      try with lf : urlDecode("%0D%0A") to get a real ascii code.

    • Cloud DevOps Fullstack Engineer
    • Martin_Mueller
    • 1 yr ago
    • Reported - view

    Jacques TUR nice that works :). Can you tell me how did you know about urlDecode(...) and the other functions likes renderCSV(...) ? Is it part of nativeJS ?

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

      Martin Mueller urlDecode is a standard function of Ninox script : https://docs.ninox.com/en/script/function-overview/functions/urldecode
      I used it when I needed to use special characters and to recreate the chr() function found in most languages.
       

      If you are doing exports, these functions could be useful. Although they are easily created with NativeJS, they have the advantage of being in Ninox script : 

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

      As for renderVSV, I found it by tracing the Ninox code. I finally found the objects that Ninox uses (ui, util, database, queries...) and understood some of their functions. It takes time but it pays off in the end.

    • Cloud DevOps Fullstack Engineer
    • Martin_Mueller
    • 1 yr ago
    • Reported - view

    And it is a bit more tricky in my case because, I do ask for exporting the child table to csv and not the `window.ui.currentView.table` . So what I try is to grab that element without success so far:

    var fileName := Kunde.Firma + "-" + Kunde.Standort + "-Stueckliste.csv";
    var exportSettings := {
            sep: ",",
            qut: """",
            lf: urlDecode("%0D%0A"),
            numberFormat: "point",
            dateFormat: "locale",
            header: true,
            sepHeader: false,
            encoding: "utf8"
        };
    #{:text:callback
    debugger;
    //var table = window.ui.currentView.table;
    // find the tab bar of top level form.
    var components = (widgets.popupEditorStack.length > 0 ? widgets.popupEditorStack[widgets.popupEditorStack.length - 1] : ui.sideEditor && ui.sideEditor).editor.currentTab.components;
    // console.log(components);
    var table = components && components.find( c => c.field.caption == "ProjektStueckliste" );
    var realTable;
    if (table) {
        // table.$headCells[numCol].innerText = newColTitle
        // table.$["nx-table__head__cell"][numCol].innerText = newColTitle
        let list = table.list[0];
        realTable = table.list[0].firstChild;
        }
    window.util.renderCSV(window.database, realTable, exportSettings)
        .then((textData) => {
            NinoxDocumentInteraction.openFileWithServer(fileName, 'text/csv', textData, !1, !0)
            callback(textData)
        })
    }#
    

    So I guess what I am trying here is quite tricky. I don't think, I can use renderCSV(...) for popups.

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

    You have several solutions:

    1 : Automatically open the table you want to export using ui.openTable 

    var tableName := "Invoice";
    var tabName := "All Invoices";
    var fileName := "myExport2.csv";
    var exportSettings := {
        sep: ",",
        qut: """",
        lf: "%0D%0A",
        numberFormat: "point",
        dateFormat: "locale",
        header: true,
        sepHeader: false,
        encoding: "utf8"
    }
    var data := #{:text:callback
        //save current view display
        var ns = ui.currentView.getNavigationState();
        // retrieve type Id from tableName
        var tid = database.schema.findType(tableName) && database.schema.findType(tableName).id;
        // retrieve view from type Id and tab Id
        var view = tid && Object.values(database.views).find(v => { return v.type == tid && v.caption == tabName })
        //Open table with specifique tab
        if (view) {
            ui.openTable(tid, view.id);
            window.util.renderCSV(window.database, window.ui.currentView.table, exportSettings)
                .then((textData) => {
                    callback(textData);
                })
        }
        else
            callback(`view ${tableName}:${tabName} not found !`)
        //re open previous view
        ui.openTable(ns.tid, ns.vid);
    }#;
    createTextFile( this, data, fileName );
    

     

    2 : Simulate the display of a table and export the object thus constructed

    var fileName := "myExport2.csv";
    var tableName := "Customer";
    var tabName := "All customers";
    var exportSettings := {
        sep: ",",
        qut: """",
        lf: "%0D%0A",
        numberFormat: "point",
        dateFormat: "locale",
        header: true,
        sepHeader: false,
        encoding: "utf8"
    }
    var data := #{:text:callback
        debugger;
        // duplicate table object from ui.views.table.table
        var table = Object.assign({}, ui.views.table.table, Object.getPrototypeOf(ui.views.table.table))
    //    var table = Object.assign({}, ui.currentView.table, Object.getPrototypeOf(ui.currentView.table))
        // clear display function
        table.createCells = () => { };
        table.updateRows = () => {};
        table.updateHead = () => {};
        table.updateFoot = () => {};
        table.retainSelection = () => {};
        // retrieve type Id from tableName
        var tid = database.schema.findType(tableName) && database.schema.findType(tableName).id;
        // retrieve view from type Id and tab Id
        var view = tid && Object.values(database.views).find(v => { return v.type == tid && v.caption == tabName })
        if (view) {
            // initialize the table
            table.setConfig(view.config, {});
            // load the rows
            table.load(() => {
                //render CSV;
                util.renderCSV(database, table, exportSettings)
                    .then((textData) => {
                        callback(textData);
                    })
            })
        }
        else
            callback(`view ${tableName}:${tabName} not found !`)
    }#;
    createTextFile( this, data, fileName );
    

    3 : Build the string yourself by compiling all the fields you want to appear in your file. This may be the simplest solution in the end, right?

    • Cloud DevOps Fullstack Engineer
    • Martin_Mueller
    • 1 yr ago
    • Reported - view

    Thanks Jacques TUR you are amazing !!!

    The ui.openTable suggestion from you gave me a nice idea where I allow to open the child table view (which is normally hidden) by setting the filter to filter project-specific rows. Then the user can use the native export mechanism which is great :). 
     

    var tableName := "ProjektStueckliste";
    var tabName := "Projekt";
    var projektNummer := Projektnummer;
    var firma := Kunde.Firma;
    var standort := Kunde.Standort;
    #{:text:callback
        debugger;
        //save current view display
        var ns = ui.currentView.getNavigationState();
        // retrieve type Id from tableName
        var tid = database.schema.findType(tableName) && database.schema.findType(tableName).id;
        // retrieve view from type Id and tab Id
        var view = tid && Object.values(database.views).find(v => { return v.type == tid && v.caption == tabName })
        //Open table with specifique tab
        if (view) {
            view.config.cols[6].filter = firma;
            view.config.cols[7].filter = standort;
            view.config.cols[8].filter = projektNummer;
            ui.openTable(tid, view.id);
        }
        else
            callback(`view ${tableName}:${tabName} not found !`)
    }#

    I have just one last problem. After I click the button the spinner doesn't stop and the side isn't available anymore. Though the table view is loaded correctly :).
     

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

      Martin Mueller An error must have occurred and the code didn't go all the way and therefore didn't hide the spinner. Look in the development console, you should see the error.

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

      Jacques TUR Oh yeah, I just saw that you removed the callback after ui.openTable. So the NativeJS function never stops.

    • Cloud DevOps Fullstack Engineer
    • Martin_Mueller
    • 1 yr ago
    • Reported - view

    I removed the callbacks. That solved it. Thanks a lot :)! 

Content aside

  • Status Answered
  • 1 Likes
  • 1 yr agoLast active
  • 13Replies
  • 215Views
  • 3 Following