Conditional Formatting due to date and values in data.

Lonnie.Hull · November 2023

I'm wondering if DataTables would be able to replicate what I've built in Excel. I'm only displaying data, but that data needs to be filtered and formatted using code in the browser.

I've built an Excel spreadsheet that imports data from a txt file, and has a second page with a date structure. i.e. Column A (Cycle Code) = 29B, Column B (Start Date) = 12/1/23, Column C (End Date) = 12/3/23. The imported page has Column A (Cycle Code).
My first task is displaying the correct data for the current date. (These tasks actually happen together in Excel, but for simplicity in explaining, I've broken them out into different steps) I output all data for open cycles in the txt file (Column A). i.e. 29A, 29B, 29C, 29D, etc. I then need to compare it to the current date. This is another field in the txt file. (Column . If the current date is equal to or between the start / end dates, the data is kept. The data that is outside that range is then compared to another field Column C. If column C contains "Finshed" or "Closed", the data is not shown, anything else is kept.
Once the data has been filtered by date, the date columns are reviewed again and anything with a Cycle Code before the current date, the row is colored Orange.
Column C is reviewed and the row color is changed due to the value of the cell. i.e. Finished = Green, Closed = Pink, Current = Yellow.

This data would be refreshed on the screen every five minutes.

I can post examples of the data if need be, but I'm hoping this gives everyone an idea of what I'm attempting to accomplish.

Thanks for looking

rf1234 · November 2023

Conditional formatting and the like is easily done with Data Tables.

I use "rowCallback" to analyze each row's data and change the formatting or add text or do something else. Here are a couple of examples from my own coding. You can do anything, really
https://datatables.net/reference/option/rowCallback

rowCallback: function (row, data) {
    if ( $('td:eq(2)', row).text() <= '' && $(row).text() > '' ) {
        var msg = lang === 'de' ? 
                        "Hier klicken!" : 
                        "Click here!";
        $('td:eq(2)', row).html( '<span class="fontThick">' + msg + '</span>' );
    }
}

rowCallback: function (row, data) {
//is only executed for visible rows: all non-visible rows are not being checked!!
    if ( inboxTasksPage ) {
        $(row).removeClass('odd even bg-danger-strong bg-warning-strong bg-success-strong');
        if ( data.ctr.task_status > "3" ) { //4 = contract has tasks and at least one of them is overdue
            $(row).addClass('bg-danger-strong');
//                $(row).addClass('bg-danger');
        } else if ( data.ctr.task_status > "2" ) { //3 = contract has tasks and one is due in less than one week 
            $(row).addClass('bg-warning-strong');
        } else { //2 = not overdue (1 = "no tasks that aren't done" is filtered out above!!)
            $(row).addClass('bg-success-strong');
        }
    }
    // Set the checked state of the checkbox in the table (only needs to be set if inboxExpPage!)
    if ( inboxExpPage ) {
        $('input.editor-read', row).prop( 'checked', data.ctr.exp_read == 1 );
    //rows that aren't done yet are in bold text
        if ( ! $('input.editor-read', row).is( ':checked' ) ) {
            $(row).addClass('fontThick');
        }
    }
}

rowCallback: function ( row, data ) {
    // Set the checked state of the checkbox in the table
    $('input.editor-read', row).prop( 'checked', data.offer_has_govdeptview.read == 1 );
    $('input.editor-deleted', row).prop( 'checked', data.offer_has_govdeptview.deleted == 1 );
//rows that aren't done yet are in bold text
    if ( ! $('input.editor-read', row).is( ':checked' ) ) {
//               $('input.editor-read', row).closest('tr').addClass('fontThick');
       $(row).addClass('fontThick');
    }
}

Lonnie.Hull · November 2023

Thanks FR1234.

I'm not very good with Javascript, but it does give me hope.

Thanks again.

rf1234 · November 2023

I'm not very good with Javascript, but it does give me hope.

Neither am I! When I got started with coding again back in 2017 I thought I needed to hire a front-end programmer. Then I discovered Data Tables and Editor - and was able to do it all myself.

allan · November 2023

You are making me blush

Let us know how you get on Lonnie.

Allan

Lonnie.Hull · December 2023

I've worked on this and I have a somewhat working file. My issue is with my two js files. They work perfectly fine when I run them seperately, but when I add both of them to my HTML, only one will work and it's the Display.js.

<html>
<head>
    <title>Awning Department Cycle Board Test</title>

    <script type="text/javascript" src="\\jquery-3.3.1.min.js"></script>

    <link rel="stylesheet" type="text/css" href="\\jquery.dataTables.css" />
    <script type="text/javascript" src="\\jquery.dataTables.js"></script>

    <link rel="stylesheet" type="text/css" href="\\select2.min.css" />
    <script type="text/javascript" src="\\select2.min.js"></script> 
  
    <style type="text/css">
        caption {
            background-color: BLUE;
            color: white;
            font-family: "Times New Roman", Times, serif;
            font-size: 20px;
            border-style: solid;
            border-width: 1px;
            border-color: white;
            text-align: center;
        }
    </style>
</head>
<body>
    <div id="demo"></div>
 
    <table id="example" class="display">
        <caption>Awning Cycle Info as of 12/4/2023 3:28:01 PM</caption>
        <thead>
            <tr>
                <th>Cycle Code</th>
                <th>Job Number</th>
                <th>Job Name</th>
                <th>Operation</th>
                <th>Piece Rate</th>
                <th>Type</th>
                <th>Num Letters</th>
                <th>Status</th>
                <th>Employee</th>
            </tr>
        </thead>
        <tbody>
            
            <tr>
                <td>30E</td>
                <td>105769-02</td>
                <td>BANK OF SUN PRAIRIE STADIUM-MADISON</td>
                <td>APPLY TO MEDIA</td>
                <td>0</td>
                <td>BK</td>
                <td>25</td>
                <td>Future</td>
                <td></td>
            </tr>
            
            <tr>
                <td>30E</td>
                <td>105559-02</td>
                <td>DUAL JERSEY MIKE'S-BETTENDORF IA</td>
                <td>POST ROUTE CHECK</td>
                <td>0</td>
                <td>BK</td>
                <td>18</td>
                <td>Future</td>
                <td></td>
            </tr>
            
            <tr>
                <td>30F</td>
                <td>105764-02</td>
                <td>CRASH CHAMPIONS 0217 SEATTLE WA</td>
                <td>APPLY TO MEDIA</td>
                <td>0</td>
                <td>BK</td>
                <td>32</td>
                <td>Future</td>
                <td></td>
            </tr>
            
            <tr>
                <td>30F</td>
                <td>105892-02</td>
                <td>PIZZA RANCH - KANSAS CITY, MO WALL SIGNS & BLADE</td>
                <td>APPLY TO MEDIA</td>
                <td>0</td>
                <td></td>
                <td></td>
                <td>Future</td>
                <td></td>
            </tr>
            
            <tr>
                <td>30F</td>
                <td>105766-02</td>
                <td>CLUB CHAMPION-WILLISTON VT</td>
                <td>APPLY TO MEDIA</td>
                <td>0</td>
                <td>RV</td>
                <td>13</td>
                <td>Future</td>
                <td></td>
            </tr>
            
            <tr>
                <td>30G</td>
                <td>105791-02</td>
                <td>CARLE HEALTH KNOXVILLE L1</td>
                <td>APPLY TO MEDIA</td>
                <td>0</td>
                <td></td>
                <td></td>
                <td>Future</td>
                <td></td>
            </tr>
            
            <tr>
                <td>RUSH</td>
                <td>105762-02</td>
                <td>WO AMAZON-VANDALIA OH-102490</td>
                <td>APPLY TO MEDIA</td>
                <td>0</td>
                <td></td>
                <td></td>
                <td>Finished</td>
                <td>PADILLAA</td>
            </tr>
            
            <tr>
                <td>RUSH</td>
                <td>105762-02</td>
                <td>WO AMAZON-VANDALIA OH-102490</td>
                <td>APPLY TO MEDIA</td>
                <td>0</td>
                <td></td>
                <td></td>
                <td>Finished</td>
                <td>SANCHEZGOMEZS</td>
            </tr>
            
            <tr>
                <td>RUSH</td>
                <td>105762-02</td>
                <td>WO AMAZON-VANDALIA OH-102490</td>
                <td>APPLY TO MEDIA</td>
                <td>0</td>
                <td></td>
                <td></td>
                <td>Finished</td>
                <td>PADILLAA</td>
            </tr>
            
            <tr>
                <td>RUSH</td>
                <td>105762-02</td>
                <td>WO AMAZON-VANDALIA OH-102490</td>
                <td>APPLY TO MEDIA</td>
                <td>0</td>
                <td></td>
                <td></td>
                <td>Finished</td>
                <td>SANCHEZGOMEZS</td>
            </tr>
            
        </tbody>
        <tfoot>
            <tr>
                <th>Cycle Code</th>
                <th>Job Number</th>
                <th>Job Name</th>
                <th>Operation</th>
                <th>Piece Rate</th>
                <th>Type</th>
                <th>Num Letters</th>
                <th>Status</th>
                <th>Employee</th>
            </tr>
        </tfoot>
    </table>

<script type="text/javascript" src="\\Formatting.js"></script>
<script type="text/javascript" src="\\Display.js"></script> 

</body>
</html>

Lonnie.Hull · December 2023

Formatting.js

"use strict";

$(document).ready(function () {
    var currentDate = new Date();

    // Initialize DataTable only if it's not initialized yet
    if (!$.fn.DataTable.isDataTable('#example')) {
        $('#example').DataTable({
            "paging": false,
            "ordering": false,
            "info": false,
            "initComplete": function (settings, json) {
                // External data from text file (replace this with your actual data)
                var externalDataCombined = {
                    data: [
                        ["Cycle Code", "Start Date", "End Date"],
                        ["26A", "11/14/2023", "11/15/2023"],
                        ["26B", "11/14/2023", "11/15/2023"],
                        ["26C", "11/14/2023", "11/15/2023"],
                        ["26D", "11/14/2023", "11/15/2023"],
                        ["26E", "11/14/2023", "11/15/2023"],
                        ["26F", "11/14/2023", "11/15/2023"],
                        ["26G", "11/14/2023", "11/15/2023"],
                        ["27A", "11/14/2023", "11/15/2023"],
                        ["27B", "11/14/2023", "11/15/2023"],
                        ["27C", "11/14/2023", "11/15/2023"],
                        ["27D", "11/14/2023", "11/15/2023"],
                        ["27E", "11/14/2023", "11/15/2023"],
                        ["27F", "11/14/2023", "11/15/2023"],
                        ["27G", "11/14/2023", "11/15/2023"],
                        ["28A", "11/14/2023", "11/15/2023"],
                        ["28B", "11/14/2023", "11/15/2023"],
                        ["28C", "11/27/2023", "11/28/2023"],
                        ["28D", "11/29/2023", "11/30/2023"],
                        ["28E", "11/17/2023", "11/22/2023"],
                        ["28F", "11/27/2023", "11/30/2023"],
                        ["28G", "11/17/2023", "11/30/2023"],
                        ["29A", "12/1/2023", "12/4/2023"],
                        ["29B", "12/5/2023", "12/6/2023"],
                        ["29C", "12/7/2023", "12/8/2023"],
                        ["29D", "12/11/2023", "12/12/2023"],
                        ["29E", "12/1/2023", "12/6/2023"],
                        ["29F", "12/7/2023", "12/12/2023"],
                        ["29G", "12/1/2023", "12/12/2023"],
                        ["30A", "12/13/2023", "12/14/2023"],
                        ["30B", "12/15/2023", "12/18/2023"],
                        ["30C", "12/19/2023", "12/20/2023"],
                        ["30D", "12/21/2023", "12/26/2023"],
                        ["30E", "12/13/2023", "12/18/2023"],
                        ["30F", "12/19/2023", "12/26/2023"],
                        ["30G", "12/13/2023", "12/26/2023"]
                    ],
                    map: {}
                };

                // Map external data to an object for quick lookup
                for (var i = 1; i < externalDataCombined.data.length; i++) {
                    var cycleCode = externalDataCombined.data[i][0];
                    var startDateParts = externalDataCombined.data[i][1].split('/');
                    var endDateParts = externalDataCombined.data[i][2].split('/');
                    externalDataCombined.map[cycleCode] = {
                        startDate: new Date(startDateParts[2], startDateParts[0] - 1, startDateParts[1]),
                        endDate: new Date(endDateParts[2], endDateParts[0] - 1, endDateParts[1])
                    };
                }

                // Function to format the rows
                function formatRows() {
                    $('#example tbody tr').each(function (i) {
                        var currentRow = $(this); // Store the reference to the current row
                        var cycleCodeValue = $('td:eq(0)', currentRow).text(); // Assuming "Cycle Code" is at index 0
                        var statusValue = $('td:eq(7)', currentRow).text(); // Assuming "Status" is at index 7

                        // Introduce a delay of 0 seconds (no delay)
                        setTimeout(function () {
                            // Use the correct variable name here: externalDataCombined.map
                            if (externalDataCombined.map.hasOwnProperty(cycleCodeValue)) {
                                var startDate = externalDataCombined.map[cycleCodeValue].startDate;
                                var endDate = externalDataCombined.map[cycleCodeValue].endDate;

                                if (endDate < currentDate && (statusValue === "Finished" || statusValue === "Closed")) {
                                    currentRow.hide();
                                } else if (endDate < currentDate && !(statusValue === "Finished" || statusValue === "Closed")) {
                                    currentRow.css('background-color', 'orange');
                                } else if (currentDate >= startDate && currentDate <= endDate) {
                                    // Do nothing, keep the row
                                } else {
                                    currentRow.hide();
                                }
                            }

                            // Color the rows based on status
                            if (statusValue === "Finished") {
                                currentRow.css('background-color', 'rgb(0, 128, 0)');
                            } else if (statusValue === "Closed") {
                                currentRow.css('background-color', 'rgb(255, 192, 203)');
                            } else if (statusValue === "Current") {
                                currentRow.css('background-color', 'rgb(255, 255, 0)');
                            } else if ($('td:eq(4)', currentRow).text().toUpperCase().indexOf("3 WOF") !== -1) {
                                currentRow.css('background-color', 'rgb(173, 216, 230)');
                            }
                        }, 0);
                    });
                }

                // Call the formatRows function within initComplete
                formatRows();
            }
        });
    }
});

Lonnie.Hull · December 2023

Display.js

document.addEventListener('DOMContentLoaded', function () {
    var table = $('#example').DataTable();
    var batchSize = 10;
    var displayTime = 5000; // 5 seconds
    var pageIndex = 0;

    function showRows(start, end) {
        table.rows().every(function (rowIdx) {
            if (rowIdx < start || rowIdx >= end) {
                this.nodes().to$().hide();
            } else {
                this.nodes().to$().show();
            }
        });
    }

    // Set the default page length to display all records
    table.page.len(-1).draw();

    function displayNextBatch() {
        var startRow = pageIndex * batchSize;
        var endRow = Math.min(startRow + batchSize, table.rows().count());

        // Unhide all rows before displaying the next batch
        showRows(0, table.rows().count());

        // Hide rows not in the current batch
        showRows(startRow, endRow);

        // Wait for the specified time after displaying the batch
        setTimeout(function () {
            // After waiting, move to the next batch or finish
            if (endRow < table.rows().count()) {
                pageIndex++;
                displayNextBatch();
            } else {
                // If it's the last batch, reload the page
                location.reload();
            }
        }, displayTime);
    }

    displayNextBatch();
});

Any help, pointers, ideas on why the formatting disappears, would be greatly appreciated. Thanks

kthorngren · December 2023

I copied your code into this test case:
https://live.datatables.net/ceyuvuga/1/edit

I commented out the call to displayNextBatch() because it doesn't work when in edit mode. What specifically is not working the way you want?

Kevin

Lonnie.Hull · December 2023

kthorngren,

The anticipated performance would be for the formatting script to change the color of each row, and or hide it depending on the externalData variable, then have the Display script show a certain number of formatted rows (batch) for a designated time. It will continue to cycle through all the records, then when the server outputs a new file, the data will be refreshed. Both work as expected independently. I'm believing that the formatting script is working, but when the display script interacts with the "example" table, it clears out the formatting. I'm not certain exactly how to confirm that though. Thanks for looking at this.

kthorngren · December 2023

N ot sure I totally understand the requirements. I see this code:

    function showRows(start, end) {
        table.rows().every(function (rowIdx) {
            if (rowIdx < start || rowIdx >= end) {
                this.nodes().to$().hide();
            } else {
                this.nodes().to$().show();
            }
        });
    }

Looks like you are trying to hide the rows using jQuery. Datatables doesn't support this and doesn't know about this. So the next draw (sorting, searching or paging) will cause Datatables to show the rows. Not sure if this is what you mean by the formatting being cleared.

It looks like you are loading all the data and trying to setup something to continually page through the table every 5 seconds. Is this correct? If so then use the Datatables paging functionality and use the page() API to roll through the pages.

Kevin

kthorngren · December 2023

Also in initComplete you are hiding the rows which again is not supported. One option is to filter out these rows in the server script to return the data you want. Another is to create a search plugin to filter the rows based on the multiple conditions.

EDIT: Here is a date range plugin you can start with. Add the other conditions to the plugin or create a second to handle these conditions.
https://datatables.net/plug-ins/filtering/row-based/range_dates

I would look at moving all the formatting code from initComplete to createdRow. See this example.

Kevin

Conditional Formatting due to date and values in data.

Conditional Formatting due to date and values in data.

Answers

Howdy, Stranger!

Categories

DataTables

Conditional Formatting due to date and values in data.

Conditional Formatting due to date and values in data.

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables