How to work conditional formatting with joined sql tables and complex custom table

How to work conditional formatting with joined sql tables and complex custom table

SkrockiSkrocki Posts: 21Questions: 9Answers: 0

I am self-taught by stackoverflow and webslesson and phppot, plus a few dozen plugins I've tried, so please forgive my lack of depth with regards to coding knowledge. I just have an idea for a website that would help with my everyday work and am optimistic that I can sell subscriptions to other guys in my field if I do a good enough job, so for about 3 years now I have been putting this together.

I have been trying off and on for several weeks now to get a few simple things working. I've been hesitant to ask on here because everyone says to "show the code". The problem is my stuff is so embedded in my server that putting bits and pieces of code on here is not going to help my real-world problem. So I have spent great time and energy to create a sandbox that I hope is somewhat secure and at the least it is isolated from my actual data. I used dummy data that is all fictional names and addresses, so PII is not an issue. If an asshole fucks with my database or server by finding any hole (which I'm sure most pros could do) it is fine because this is on a dedicated instance and will not affect my production. But please don't. I love datatables and it is so close to perfect but a few critical things are not working. I'm hoping that with a real-world version of my use, one of you pros can figure out what I'm doing wrong.

https://sandbox.claimpenguin.com/public/dataTable/claimtracker.php

  1. So I can't get conditional formatting to work at all. I just want to assign different formatting to rows based on the "Status" column (current_status db field). Why is it not working at all?

  2. I would like to put a bold line across the whole row, every time the Inspection day changes. I've attached a old table I did on my own and got help from someone on stackoverflow to get the bold line to work perfectly. I have no idea how to make that work with datatables. (I had to blur out that screenshot because it does have real info I need to protect).

and

  1. I have not been able to make any datatable work in a modal. I have a page that uses probably 25 lists that are customizable by the user and I would prefer to use datatables as a modal with 25 different search criteria. I have asked this question before, and a couple of generous geniuses offered some suggestions but I have yet to make it actually work. I understand this third item might need more detail and I can work on it some more and extract the error logs, so this is my lowest priority issue of the three I've listed here.

I did pay for editor and I will pay for support if necessary, but I have seen some pretty complex questions answered right here in the Forum so I figured I would try this first. I also can't afford to pay for a massive support plan for $500 or more when I really just think I need an hour or two sharing my screen on Zoom to fix these issues that are probably very simple for those of you who understand more of the code than I do.

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 60,962Questions: 1Answers: 9,939 Site admin
    Answer ✓

    Hi,

    Thank you for taking the time to provide a test case. It makes a massive difference to how we can help. For example I can tell you immediately why your conditional formatting isn't working. You have:

    if (data[11] === 'Closed') {
    

    But the data value is an object, not an array in your table. Use:

    if (data.deets_activity.current_status === 'Closed') {
    

    I would like to put a bold line across the whole row, every time the Inspection day changes.

    I don't quite understand this I'm afraid. Do you mean on edit, or an update from the server, or a time change locally or something else?

    I have not been able to make any datatable work in a modal

    Sounds like the table might not be in the DOM when the DataTable is initialised, and thus can't find the table tag? We'd need an example to say for sure.

    Allan

  • SkrockiSkrocki Posts: 21Questions: 9Answers: 0
    edited March 2022

    That answer to Question 1 is perfect and worked as expected. Thank you for that. I have 2 follow-ups on that topic:

    1. How can I apply across the whole row? You'll see I revised my code to reflect your corrections, and I added a reference to a class rather than putting the css inline. The class creation is working, but again it only applies to the individual cell and I'd like the whole row to be conditionally formatted.
    2. When I change Status inline, the format doesn't change until I refresh the page. Is there some way to attach an ajax call to refresh the row in real-time once it is changed?

    Regarding the bold bottom row after each Inspection date, I'll explain the best I can...

    The purpose is to subtly group each day's activity at a glance, for practical use. It is strictly a UI visual aid.

    That cell could be edited inline (side note: when editing that cell inline the time does not show up in the popup, only the date, how did I mess that up?) or on a separate page linked by the "Edit" link which goes to a disabled page in this sandbox.

    Whenever the date changes (time is ignored for this use I'm only grouping by the date), whether inline or from the claim details page, the bold line will be on the bottom of the last matching date when the datatable renders. Here again I'd like that re-render to occur as soon as the change is made inline or when I re-focus on that page after being in the claim details page (which opens in a separate window, leaving the datatable open and waiting for me to return to it. Did my screenshot come through before? I'll try to attach it again because that demonstrates exactly how I want the claims grouped.

    Let's shelf the modal for now. I'll need to get the claim details page configured to work on the sandbox to show you what I have going on.

    Thank you so much for your quick and accurate help! Do you have a Patreon or somewhere I can forward some dough to show my appreciation? Thanks, Steve

  • allanallan Posts: 60,962Questions: 1Answers: 9,939 Site admin
    Answer ✓

    How can I apply across the whole row?

    Well your selector is specifically picking out a single cell:

    $("td:eq(11)", row).addClass('closed');
    

    Remove the :eq(11) if you want to select all cells in the row. Note that this might conflict with the row selection highlighting. You will need to consider how to handle two different things using the same property (background colour) to show state.

    When I change Status inline, the format doesn't change until I refresh the page

    It does (if you go into the Closed state), but coming out of it, you don't have a removeClass() for an else statement, so you'd need to add that in case the class now needs to be removed.

    The purpose is to subtly group each day's activity at a glance

    Something like this: https://datatables.net/extensions/rowgroup/examples/initialisation/simple.html ?

    Allan

  • SkrockiSkrocki Posts: 21Questions: 9Answers: 0

    Awesome. The whole row is working perfectly. I apologize if my questions seem remedial, I knew what the td was and the 11, but didn't know what eq meant, or what the syntax should be. To me, reason would dictate that I change the td to tr for the row, so that's why I'm confused by stuff that is 2nd nature to you. Thanks for that help and the refresh info. I actually had started with my else statements, and now that I have them all in it is working perfect on that area too.

    You link led to the group tutorial. That is in the ballpark, and I have explored it previously, but it is not subtle. The problem is the header between groups. Sometimes I may have only 1 or 2 inspections per day, and then the headers would become very confusing. The heavy line at the bottom of each group/day is the perfect way to subtly indicate each group. Is there a way to group without showing a header and just showing a heavy line on the bottom of the last item in each group?

    Thanks again, these are issues I have spent a lot of time on, so your quick easy replies are making it so much better for me!

  • allanallan Posts: 60,962Questions: 1Answers: 9,939 Site admin

    RowGroup is doing the grouping logic, so how about something like: http://live.datatables.net/watoyedi/1/edit .

    That uses a sneaky little bit of CSS to achieve the aims:

    tr.dtrg-group {
      display: none;
    }
    
    tr.dtrg-group + tr > td {
      border-top: 2px solid black !important;
    }
    

    First style is to hide the grouping row from the display, while the second one takes the next row after the grouping row, and adds a top border to the cells in it.

    Allan

  • allanallan Posts: 60,962Questions: 1Answers: 9,939 Site admin

    Do you have a Patreon or somewhere I can forward some dough to show my appreciation?

    I forgot to address this point. First of all - thank you! We actually don't have a Patreon. Maybe I should consider that.

    At the moment what we do have is the "Supporter" packages which are indented for that sort of "thank you" :).

    Allan

Sign In or Register to comment.