Conditional Formatting due to date and values in data.
Conditional Formatting due to date and values in data.
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
Answers
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
Thanks FR1234.
I'm not very good with Javascript, but it does give me hope.
Thanks again.
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.
You are making me blush
Let us know how you get on Lonnie.
Allan
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.
Formatting.js
Display.js
Any help, pointers, ideas on why the formatting disappears, would be greatly appreciated. Thanks
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
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.
N ot sure I totally understand the requirements. I see this code:
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
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
tocreatedRow
. See this example.Kevin