Allow user to update ColdFuson Cells-CodePudding

I am trying to allow my CFTABLE to be editable by the user. right now, I have a table outputting table based off the documents Active status in our database via them selecting a radio option. What is the best way to go about making each cell editable? Eventually, I would like the user to be able to edit each cell, and have that change be pushed to our database. We used to have CFGRID for this. but CFGRID no longer works. I was thinking possibly converting it to datatables? Thoughts?

OUTPUT OF CURRENT PAGE:

Code:

<CFFORM ACTION=#cgi.SCRIPT_NAME# METHOD="POST">

    <center>
    
    <b><font size="4" face="britannic bold" color="black">Select if you want to view All, Active, or Non-Active Documents:</font></b><br><br>

    <table>
    <tr>
        <td align="center"><font size="4" face="britannic bold" color="black">Sort by:</font><br></td>
    </tr>
    <tr>
        <td align="left"><input type="Radio" NAME="order" value="All" checked>Active and Non-Active<br></td>
    </tr>
    <tr>
        <td align="left"><input type="Radio" NAME="order" value="Active">Active Only<br></td>
    </tr>
    <tr>
        <td align="left"><input type="Radio" NAME="order" value="Non-Active">Non-Active Only<br></td>
    </tr>
    </table>

    <tr><td>
    <br><INPUT TYPE="submit" NAME="view" VALUE="View Report">
    </td></tr>

    </center>

</CFFORM>


<cfif form.view is "View Report"> <!-- checks to see if view report button has been hit -->

    <cfif form.order is "All"> <!-- if Active and Non-Active button hit, it will display both types of documents -->
    <cftable query="get_all_docs" colheaders="yes" border="yes" headerlines="yes" htmltable="yes">

    <cfcol header="SITE" text="#SITE#">
    <cfcol header="section" text ="#sections#">
    <cfcol header="TAB" text="#TAB#">
    <cfcol header="DOC_NUMBER" text="#DOC_NUMBER#">
    <cfcol header="DESCRIPTION" text="#DESCRIPTION#">
    <cfcol header="REV_LEVEL" text="#REV_LEVEL#">
    <cfcol header="REVISION_DATE" text="#REVISION_DATE#">
    <cfcol header="REVIEW_DATE" text="#REVIEW_DATE#">
    <cfcol header="ADMIN" text="#ADMIN#">
    <cfcol header="ACTIVE" text="#ACTIVE#">

    </cftable>
    <!-- end All if -->

    <cfelseif form.order is "Active"> <!-- if Active Only is hit, will only display active docs -->
    <cftable query="active_doc" colheaders="yes" border="yes" headerlines="yes" htmltable="yes">

    <cfcol header="SITE" text="#SITE#">
    <cfcol header="section" text ="#sections#">
    <cfcol header="TAB" text="#TAB#">
    <cfcol header="DOC_NUMBER" text="#DOC_NUMBER#">
    <cfcol header="DESCRIPTION" text="#DESCRIPTION#">
    <cfcol header="REV_LEVEL" text="#REV_LEVEL#">
    <cfcol header="REVISION_DATE" text="#REVISION_DATE#">
    <cfcol header="REVIEW_DATE" text="#REVIEW_DATE#">
    <cfcol header="ADMIN" text="#ADMIN#">
    <cfcol header="ACTIVE" text="#ACTIVE#">

    </cftable>
    <!-- end Active elseif -->

    <cfelseif form.order is "Non-Active"> <!-- if Non-Active Only is hit, will only display non-active docs -->
    <cftable query="inactive_doc" colheaders="yes" border="yes" headerlines="yes" htmltable="yes">

    <cfcol header="SITE" text="#SITE#">
    <cfcol header="section" text ="#sections#">
    <cfcol header="TAB" text="#TAB#">
    <cfcol header="DOC_NUMBER" text="#DOC_NUMBER#">
    <cfcol header="DESCRIPTION" text="#DESCRIPTION#">
    <cfcol header="REV_LEVEL" text="#REV_LEVEL#">
    <cfcol header="REVISION_DATE" text="#REVISION_DATE#">
    <cfcol header="REVIEW_DATE" text="#REVIEW_DATE#">
    <cfcol header="ADMIN" text="#ADMIN#">
    <cfcol header="ACTIVE" text="#ACTIVE#">

    </cftable>
    <!-- end Non-Active elseif -->
    </cfif>
    <!-- end nested if statements  -->
</cfif>

Update route for datbase:

<CFIF IsDefined("Form.FIRSTGRID.rowstatus.action") and isArray(Form.FIRSTGRID.rowstatus.action)>
  <cfloop index = "counter" from = "1" to = #arraylen(Form.FIRSTGRID.rowstatus.action)#>
    <cfswitch expression="#Form.FIRSTGRID.rowstatus.action[counter]#">
      <cfcase value="I">
         <cfquery name="query_insert" datasource="DBSTORE"> 

            insert into MDL_MASTER_LIST (active,admin,description,doc_number,is_contact,is_file,review_date,revision_date,rev_level,sections,site,tab)
            values ('#form.firstgrid.active[counter]#','#form.firstgrid.admin[counter]#','#form.firstgrid.description[counter]#',
            '#form.firstgrid.doc_number[counter]#',#form.firstgrid.is_contact[counter]#,#form.firstgrid.is_file[counter]#,
            #createodbcdate(form.firstgrid.review_date[counter])#,#createodbcdate(form.firstgrid.revision_date[counter])#,
            '#form.firstgrid.rev_level[counter]#','#form.firstgrid.sections[counter]#',
            '#form.firstgrid.site[counter]#','#form.firstgrid.tab[counter]#') 

         </cfquery>
      </cfcase>
      <cfcase value="D">
         <cfquery name="query_delete" datasource="DBSTORE">
            DELETE FROM MDL_MASTER_LIST WHERE doc_number = '#form.firstgrid.original.doc_number[counter]#'
         </cfquery>
      </cfcase>
      <cfcase value="U">  
         <cfquery name="query_update" datasource="DBSTORE">
            UPDATE MDL_MASTER_LIST set 
                active = '#form.firstgrid.active[counter]#',
                admin = '#form.firstgrid.admin[counter]#',
                description = '#form.firstgrid.description[counter]#',
                doc_number = '#form.firstgrid.doc_number[counter]#',
                is_contact = #form.firstgrid.is_contact[counter]#,
                is_file = #form.firstgrid.is_file[counter]#,
                review_date = #createodbcdate(form.firstgrid.review_date[counter])#,
                revision_date = #createodbcdate(form.firstgrid.revision_date[counter])#,
                rev_level = '#form.firstgrid.rev_level[counter]#',
                sections = '#form.firstgrid.sections[counter]#',
                site = '#form.firstgrid.site[counter]#',
                tab = '#form.firstgrid.tab[counter]#'
            WHERE doc_number = '#form.firstgrid.original.doc_number[counter]#'
         </cfquery>
      </cfcase>
    </cfswitch>
  </cfloop>
</CFIF>

Thank you for any help! I am newer to coldfusion, so its always a learning curve lol.

CodePudding user response：

I can think of a couple things you could try but they'd be very involved with Javascript and Ajax. Well beyond the scope here. My suggestion would be to find a tool already built and adapt it to your needs.

There are a bunch on this page to check out

This is an excellent commercial product: https://dhtmlx.com/docs/products/dhtmlxSpreadsheet/

CodePudding user response：

Here is the basis of what I did to begin solving my problem using jquery:

Table:

    <table id="MasterDocs">
<thead>
    <tr><th>SITE</th>
    <th>SECTIONS</th>
    <th>TAB</th>
    <th>DOC_NUMBER</th>
    <th>DESCRIPTION</th>
    <th>REV_LEVEL</th>
    <th>REVISION_DATE</th>
    <th>REVIEW_DATE</th>
    <th>ADMIN</th>
    <th>ACTIVE</th>
</tr></thead>
<tbody>
<cfoutput query="get_all_docs">
    <tr>
        <td>#SITE#</a></td>
        <td>#SECTIONS#</td>
        <td>#TAB#</td>
        <td>#DOC_NUMBER#</td>
        <td>#DESCRIPTION#</td>
        <td>#REV_LEVEL#</td>
        <td>#REVISION_DATE#</td>
        <td>#REVIEW_DATE#</td>
        <td>#ADMIN#</td>
        <td>#ACTIVE#</td>
    </tr>
    </cfoutput>
    
</tbody>

</table>

<script type="text/javascript">
    $(document).ready(function(){
        $('#MasterDocs').dataTable({
            "bJQueryUI": true,
            paging: false, // this allows you to pick if you want to filter by how many per page
            ordering: true, // this allows you to filter by col title
            info: false, // Will show "1 to n of n entries" Text at bottom
            
            "aoColumnDefs": [
                { "sWidth": 120, "sClass": "alignCenter", "aTargets": [ 1,3 ] },
                { "sWidth": 120, "sClass": "alignRight", "aTargets": [ 2 ] }
            ]
        });
    });
</script>

Libraries:

 <script type="text/javascript" src="./assets/jquery-3.6.0.min.js"></script>
    <script type="text/javascript" src="./assets/jquery.dataTables.min.js"></script>
    <link rel="stylesheet" type="text/css" href="./assets/jquery.datatables.css"/>
    <link rel="stylesheet" type="text/css" href="./assets/table.css" />

Thank you all for the suggestions, it helped me go in the right direction with this one.