Dynamically change an icon based on a column value

I am working on Oracle Apex v21.1. I'm trying to create a small "dashboard" table for the admin.

Use case : When departments are ready to submit their inventory, they can click on a button "Validate Inventory" which will then display the information that their inventory is ready to the table dashboard of the admin. The admin can then check the dashboard, see who's finished and who's not. The dashboard will display the name of the departments, the validation date, the state of the inventory (Ready / Not ready) in two differents colours (green = ready / red = not ready). If the inventory is not ready, a e-mail icon is appearing in the #LINK column which give the admin the possiblity to send an email to the departement. If it's ready, a simple "check" is displayed.

Here's what I already have and what I can do :

The validate button works, departments can change the state of their inventory by pressing on it, here's the code : UPDATE inv_tb_dashboard SET etat_dashboard = 1, date_dashboard = SYSDATE WHERE fk_service_dashboard IN (SELECT pk_service FROM inv_tb_service WHERE UPPER(code_service) = v('APP_USER'));
I can dynamically change the color of the table row based on the value of state (0 for false, 1 for true) here's the JS Code :

    $(document).ready(function() {
        $("td:nth-child(3)").each(function() {
            if ($(this).text() === "Non validé") {
                $(this).parent().children().css({'background-color': '#d88882'});
            }
            else if($(this).text() === "Validé"){
                $(this).parent().children().css({'background-color': 'lightgreen'});
            }
        });
    });

I can change the icons, but here's the part that I'm not happy about and that I want to improve and make cleaner. Actually, my table has a icon_dashboard column, this is the column that display the two icons that I mentioned above. I have a Dynamic Action on Page Load, which execute this code :

UPDATE inv_tb_dashboard 
SET 
icone_dashboard =
'<a href="mailto:'||email_dashboard||'?Subject=RAPPEL - Inventaire communal"><span aria-label="Mail"><span class="fa fa-envelope-o" aria-hidden="true" title="Mail"></span></span></a>'
WHERE etat_dashboard = 0;
UPDATE inv_tb_dashboard 
SET 
icone_dashboard =
'<span aria-label="Mail"><span class="fa fa-check-circle-o" aria-hidden="true" title="Mail"></span></span>'
WHERE etat_dashboard = 1;

Yes. As you can see, I'm inserting the HTML tags in the column in static text, based on the value of etat_dashboard which is the state mentioned above (0 false / 1 true). That's really not a clean way to do it and I would like to get something working better.

Instaed, I would like to know if it is possible to change dynamically the value of the #LINK# column, with a JavaScript or jQuery Expression, and set the icons has I did on my DA on Page Load ? This could let me get rid of the icon_dashboard column.

Do not hesitate to ask questions for more details.

Thank you in advance,

Thomas

Solution

Instead of having an "icon_dashboard" column in your table and updating its value to the needed value you could add a CASE statement to your select. That way the dynamic action to update the table is not needed. Select would be something like:

SELECT 
  <standard_columns>,
  CASE 
    WHEN etat_dashboard = 0 THEN '<a href="mailto:'||email_dashboard||'?Subject=RAPPEL - Inventaire communal"><span aria-label="Mail"><span class="fa fa-envelope-o" aria-hidden="true" title="Mail"></span></span></a>'
    WHEN etat_dashboard = 1 THEN '<span aria-label="Mail"><span class="fa fa-check-circle-o" aria-hidden="true" title="Mail"></span></span>'
  END as icone_dashboard
FROM inv_tb_dashboard

It's not removing the html from the query but it's simplifying the process a lot. In the end you have to ask yourself - what has the least maintenance in the future, adding a single line to the query or a custom javascript action.