Create conditional formatting on dates in cells

JoeHardstaff

I am constructing a training matrix. I use FileMaker so it gathers the data first via a script and puts it in JSON variables and passes to the datatable. It creates the column headings using a loop to create enough headings for the amount of courses. Each row is a staff member. First it gets the training courses that have been added by the user, this is variable in amount. Then it goes through each user one by one checking their expiry date for the certificate this builds the json array. This bit I have working fine.

My question sounds quite simple but I have spent a good chunk of today trying to get this to work. So all I want to do is have the cell colour change based on the value of the date. Green = expiry date is greater than today, Amber = expiry equals today or Red = expiry is less than today.

I have attached an image of what I have already, demo data. I tried to create a working model in codepen etc but couldn't do it.

https://dropbox.com/s/9dr8kdh5k16mvum/image1.png?dl=0

kthorngren

The first this I would do is choose a datetime library to make it easier to compare teh date to the current date. Luxon and moment.js are popular libraries. Next is to look at using createdRow to perform the comparison and apply the appropriate styles. See this example.

Then it goes through each user one by one checking their expiry date for the certificate this builds the json array.

I'm not clear what this is exactly doing. If you the array has the style info maybe we can use that.

If you still need help then please start with this example:
https://live.datatables.net/

Update it to use an example of your JSON array data. You might need to remove the tbody from the HTML tab and adjust the `-tag thead. We don't need all your data or full solution. Just need something that shows your data. Then we can offer more specific suggestions.

Kevin

JoeHardstaff

Hi kthorngren

Thank you for taking the time to look at my issue.
I have been looking at createdRow but it's not clear how I would use it. In the example that changes the font for the salary, I understand that just about but that is based on a specific column (salary). I need it to check all but the first column.

This is my layout described:

Staff Course1 Course2 Course3. etc etc
names. expiry of certificate. expiry of cert. etc etc

The course headings can be different for each setting. They have their own, so the columns can be just a few or quite a lot.

This is an example of the JSON that creates the headers:
[{"data":"name","title":"Staff"},
{"data":"Exp1","title":"GDPR"},
{"data":"Exp2","title":"First Aid"},
{"data":"Exp3","title":"Test course"},
{"data":"Exp4","title":"QTS"},
{"data":"Exp5","title":"Cyber Security"}]

the Exp number is generated via a loop as we don't know how many training courses there will be. This also applied the same loop to the data.

This is an example of the JSON data that is collected from the database:
[{"Exp1":"04/06/2023","Exp2":"31/05/2023","Exp3":"-","Exp4":"-","Exp5":"-","name":"Joe Test"},
{"Exp1":"03/10/2022","Exp2":"20/12/2022","Exp3":"03/04/2023","Exp4":"-","Exp5":"-","name":"Ali Becker"},
{"Exp1":"-","Exp2":"-","Exp3":"-","Exp4":"-","Exp5":"-","name":"Andy Robertson"},
{"Exp1":"-","Exp2":"-","Exp3":"-","Exp4":"-","Exp5":"-","name":"Trent Arnold"},
{"Exp1":"-","Exp2":"20/12/2022","Exp3":"-","Exp4":"-","Exp5":"-","name":"Jurgen Klopp"},
{"Exp1":"07/05/2023","Exp2":"-","Exp3":"-","Exp4":"-","Exp5":"-","name":"Raj Singh"},
{"Exp1":"-","Exp2":"-","Exp3":"-","Exp4":"-","Exp5":"-","name":"Raheem Sterling"},
{"Exp1":"-","Exp2":"-","Exp3":"-","Exp4":"-","Exp5":"-","name":"Michael Wilson"},
{"Exp1":"04/04/2023","Exp2":"31/03/2023","Exp3":"-","Exp4":"-","Exp5":"-","name":"Joe Hardstaff"},
{"Exp1":"-","Exp2":"-","Exp3":"-","Exp4":"-","Exp5":"-","name":"Fake Staff"},
{"Exp1":"-","Exp2":"-","Exp3":"-","Exp4":"-","Exp5":"-","name":"Case Study"},
{"Exp1":"-","Exp2":"-","Exp3":"-","Exp4":"-","Exp5":"-","name":"Jordan Henderson"}]

I have added this to the example file you linked to in the same format as i have it generated.

https://live.datatables.net/zupareje/1/edit

Any help would be hugely appreciated.

Joe

kthorngren

Thanks for the test case. The first thing to do is figure out how many columns contain Exp fields. Based on the above its the number of columns - 1. Next build the Exp key using a loop index to access the data. Last is to compare the date to today or whatever you need.

I opted to use moment.js in this updated test case:
https://live.datatables.net/zupareje/2/edit

You may need to change how the number of Exp fields are in the row but its just a matter of using standard Javascript methods.

Kevin

JoeHardstaff

Kevin, YOU ARE A DIAMOND.
Thank you so much. I am now able to continue to develop this and add a RAG rating.

Joe