How to use Javascript to hide any table's columns based on your criteria

API & Customization Show & Tell
1

Updated Feb 24, 2020.

Hi to all,

Here's my gift of the day to the world of Knack users!

Something I wanted for a long time: being able to remove any columns from a table based on my own arbitrary criteria.

The most common use of this would be to remove columns based on the user's roles.

Ex: Remove a column if user roles do not contain 'Admin' or 'Supervisor'.

Or simply hide (but not remove in this case) a column and still benefit from the knack-view-render callback data parameter, where one of the field has some interest in the code but not visually to the user. You should know that if the column is not in the table, you won't get that field from the data parameter.

Here's the code. Just replace the table view and the fields to remove or hide by yours.

Enjoy!

Norm

/////////////////////////////////////////////////////////////////////////////
// Code sample to remove or hide a table's columns based on your own arbitrary criteria

const MY_TABLE_VIEW = 'view_123';

//Need to determine each role by clicking on them in the builder and checking the address bar.
var appRoles = {
    'Admin': 'object_16',
    'Manager': 'object_17',
    'Supervisor': 'object_30',
    'Quality Control': 'object_44'
}

//////////////////////////////////////////////////////////////////
$(document).on('knack-view-render.' + MY_TABLE_VIEW, function (event, view, data) {
    console.log(data);

    var userRolesArray = Knack.getUserAttributes().roles;
    console.log('Current User Roles :');
    console.log(userRolesArray);

    //Only Admin role can see these columns.  Hide for all others.
    if (!userRolesArray.includes(appRoles['Admin']))
        removeTableColumns(view.key, true, ['field_123', 'field_134', 'field_421']);
});

/*//////////////////////////////////////////////////////////////////
Removes or hides any table's columns, including those
with Action, Edit and Delete.

Input parameters:

    - view: must be a view.key string, ex: 'view_123'
    - remove: true removes elements from DOM, false only hides them.  Useful to hide them when you need to access data, but not secure.
    - columnsArray: must be an array of 1-based integers, ex: [5, 2, 1] to remove 1st, 2nd and 5th columns.  Order MUST be decreasing.
    - fieldsArray: must be an array of strings, ex: ['field_456', 'field_789'].  Order is not important.

    You may use both arrays at same time, but columnsArray has precedence.
*/
//////////////////////////////////////////////////////////////////
function removeTableColumns(view, remove = true, columnsArray = [], fieldsArray = []) {
    var header;
    var cell;
    var column;

    if (view === null || view === 'undefined' || ((fieldsArray && fieldsArray.length === 0) && (columnsArray && columnsArray.length === 0))) {
        console.log('Called removeTableColumns with invalid parameters.');
        return;
    }

    if (columnsArray && columnsArray.length > 0) {
        columnsArray.forEach(function (el) {

            //Remove Header
            header = $('#' + view + ' > div.kn-table-wrapper > table > thead > tr > th:nth-child(' + el + ')');
            if (remove)
                header.remove();
            else
                header.css({ 'display': 'none' });

            //Remove each data field
            cell = $('#' + view + ' > div.kn-table-wrapper > table > tbody > tr > td:nth-child(' + el + ')');
            if (cell.length > 0) {
                if (remove)
                    cell.remove();
                else
                    cell.css({ 'display': 'none' });
            }
        })
    }

    if (fieldsArray && fieldsArray.length > 0) {
        $("div.kn-table.kn-view." + view + " table.kn-table thead tr th").each(function (index) {
            header = $(this)[0].className;

            if (fieldsArray.some(function (v) { return header.indexOf(v) >= 0; })) {
                if (remove)
                    $(this).remove(); //Remove header.
                else
                    $(this).css({ 'display': 'none' });

                //Remember each columns where cells muse be removed.
                column = index + 1;

                //Remove each row's data.
                $("div.kn-table.kn-view." + view + " table tr:not(.kn-table-group)").each(function () {
                    cell = $(this).find('td:nth-child(' + column.toString() + ')');
                    if (cell.length > 0) {
                        if (remove)
                            cell.remove(); //Remove cell.
                        else
                            cell.css({ 'display': 'none' });
                    }
                })
            }
        })
    }
}


2

Hey Ryan!

Here's your update as promised. Hope you enjoy it.

Norm

3

Yes, that's a good idea. I will work on this and post the result.

See you soon...

Norm

4

Is it possible to use this to hide edit or delete columns on a table based on user role?

5

Important update regarding security !!!

I've realized that it's very easy to hack my initial solution above, by simply right-clicking in the table and Inspect a cell's CSS state. Someone who is just a little clever could easily do a find on display: none to find any object that we'd be trying to hide like a table's cell in our case, and view the underliying information.

So don't use this method to hide important or confidential information. It is only useful to hide (or dynamically alternate between hide and show at will, if that's what you want), for the sake of a better layout in a crammed table as an example.

If you want better security, use this code instead. It uses jquery's remove() function to wipe off all fields completely from the DOM. So there's no way to see hidden data by the back door. A hacker would have to capture the data on-the-fly as it comes in, and before being removed - which would make one's life a bit more complicated.

This updated code is also simpler because you don't need to handle the summary bar. When you remove a header, the summary adapts itself. I found that out by accident, to my great surprise!

Here's the code, enjoy!

Norm



/////////////////////////////////////////////////////////////////////////////
// Code sample to hide a table's columns based on your own arbitrary criteria

const MY_TABLE_VIEW = 'view_123';

//Need to determine each role by clicking on them in the builder and checking the address bar.
var appRoles = {
    'Admin': 'object_16',
    'Manager': 'object_17',
    'Supervisor': 'object_30',
    'Quality Control': 'object_44'
}

//////////////////////////////////////////////////////////////////
$(document).on('knack-view-render.' + MY_TABLE_VIEW, function (event, view, data) {
    console.log(data);

    var userRolesArray = Knack.getUserAttributes().roles;
    console.log('Current User Roles :');
    console.log(userRolesArray);

    //Only Admin role can see these columns.  Hide for all others.
    if (!userRolesArray.includes(appRoles['Admin']))
        hideTableColumns(view.key, ['field_123', 'field_134', 'field_421']);
});

//////////////////////////////////////////////////////////////////
// Input parameters:
//      view must be a string, ex: 'view_123'
//      fieldsArray must be an array of strings, ex: ['field_456', 'field_789']
//////////////////////////////////////////////////////////////////
function hideTableColumns(view, fieldsArray) {
    var header;
    var cell;
    var column;
    var columnsArray = [];

    if (view === null || view === 'undefined' || fieldsArray.length === 0) {
        console.log('Called hideTableColumns with invalid parameters.');
        return;
    }

    $("div.kn-table.kn-view." + view + " table.kn-table thead tr th").each(function (index) {
        header = $(this)[0].className;

        if (fieldsArray.some(function (v) { return header.indexOf(v) >= 0; })) {
            $(this).remove(); //Remove header.

            //Remember each columns where cells muse be erased.
            column = index + 1;
            columnsArray.push(column);

            //Remove each row's data.
            $("div.kn-table.kn-view." + view + " table tr:not(.kn-table-group)").each(function () {
                cell = $(this).find('td:nth-child(' + column.toString() + ')');
                cell.remove();
            });
        }
    });
}

6

Thanks Tony :)

Usually I use promises when I need an operation to complete before continuing. But in such a case, I'm not sure if it's possible. After all, I'm a newbie with only 9 months of Javascript and Web API experience !

If you find out, let me know. If I do, I will update this post.

But I can tell you that I use this method a lot, especially with the Chosen Dropdown object (connected fields), when being used with barcode scanners. It is very reliable.

Cheers,
Norm

7

This is slick thank you love the built-in error checking. I wonder if there is a different way to check for the summary instead of a looping timer?

8

Hi Norman! Does this script still work? I did not manage, but i do admit my knowledge of coding is very limited.

Thanks.