Solved

Need an intelligent, spreadsheet style grid

Posted on 2003-12-10
16
385 Views
Last Modified: 2013-12-24

In a cfform I've got a cfgrid component that list rows of employees with columns representing hours worked for each day in a week with a Total Hours column as the last column. The user enters in hours for each day and when they press submit the action page adds them all together and populate to totals.  Works fine.

Client now wants the grid to display a running total in the Total Hours field as they enter values in each day, so that if they enter 8 for Monday, the Total Hours column for that row will show 8, then they enter 8 in Tuesday's Hours and the Total Hours increments to 16.   This needs to happen dynamically as they enter values without having to press submit.

I don't think cfgrid can do this.  Does anyone have any ideas on the best way to accomplish this?

Thanks,
Stephen
0
Comment
Question by:Waterstone
  • 6
  • 6
  • 4
16 Comments
 
LVL 12

Expert Comment

by:jyokum
ID: 9918144
<script>
var wk=new Array('sun','mon','tue','wed','thu','fri','sat');

function sumTime(row){
      var t=0;
      tot=eval('document.frmData.total'+row);
      for(i=0;i<=6;i++){
            fld=eval('document.frmData.'+wk[i]+row);
            t+=fld.value*1;
      }
      tot.value=t;
}
</script>

<form name="frmData">
<table border="1" bordercolor="#CCCCCC" cellpadding="0" cellspacing="0">
<tr>
      <td>Employee</td>
      <td>Sun</td>
      <td>Mon</td>
      <td>Tue</td>
      <td>Wed</td>
      <td>Thu</td>
      <td>Fri</td>
      <td>Sat</td>
      <td>TOTAL</td>
</tr>
<cfset row=1>
<cfoutput>
<cfloop list="bob,sally,jane,billy" index="emp">
<tr>
      <td>#emp#</td>
      <td><input type="text" name="sun#row#" onChange="sumTime(#row#);" style="width: 50px;"></td>
      <td><input type="text" name="mon#row#" onChange="sumTime(#row#);" style="width: 50px;"></td>
      <td><input type="text" name="tue#row#" onChange="sumTime(#row#);" style="width: 50px;"></td>
      <td><input type="text" name="wed#row#" onChange="sumTime(#row#);" style="width: 50px;"></td>
      <td><input type="text" name="thu#row#" onChange="sumTime(#row#);" style="width: 50px;"></td>
      <td><input type="text" name="fri#row#" onChange="sumTime(#row#);" style="width: 50px;"></td>
      <td><input type="text" name="sat#row#" onChange="sumTime(#row#);" style="width: 50px;"></td>
      <td><input type="text" name="total#row#"></td>
</tr>
<cfset row=row+1>
</cfloop>
</cfoutput>
</table>
</form>

you would probably be changing this to loop over a recordset instead of a list
0
 

Author Comment

by:Waterstone
ID: 9918237
jyokum,

Thank you for such a swift reply.  You are right, I'd need to create a table based on the recordset returned by a cfquery.  The recordset has an entry for each employee with a field for each day and some total field.  I would need to populate the table grid with the existing records, then update the records with the new values.  I do this with CFgrid now.  How difficult is it to replace the list with a cfquery record set?

I can send along the cfquery code if it would help.

Thanks again for sharing your expertise.

Stephen
0
 
LVL 11

Expert Comment

by:hart
ID: 9918252
<CFQUERY NAME="Qry_GetEmployees" DATASOURCE="DsnName">
      Your query which gets the employee names and their daily work hours...
</CFQUERY>

<HTML>
<HEAD>
<TITLE>Report</TITLE>
<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript">
<!--
function CalTotal(sfield,eid)
{
      if(!isNaN(eval("document.frm." + sfield + ".value")) || eval("document.frm." + sfield + ".value") > 24)
      {
            alert("Please enter work hours for the day properly")
            eval("document.frm." + sfield + "").focus();
            eval("document.frm." + sfield + "").select();
            return false;
      }
      else
      {
            eval("document.frm.tot" + eid + "").value = parseInt(eval("document.frm.mon" + eid + ".value")) + parseInt(eval("document.frm.tue" + eid + ".value")) + parseInt(eval("document.frm.wed" + eid + ".value")) + parseInt(eval("document.frm.thu" + eid + ".value")) + parseInt(eval("document.frm.fri" + eid + ".value")) + parseInt(eval("document.frm.sat" + eid + ".value"));
      }
}
//-->
</SCRIPT>
</HEAD>
<BODY>
<FORM name="frm" action="actionpage.cfm" method="post">
      <TABLE CELLSPACING="0" CELLPADDING="0" BORDER="1" WIDTH="100%">
            <TR>
                  <TD>Employee Name</TD>
                  <TD>Monday</TD>
                  <TD>Tuesday</TD>
                  <TD>Wednesday</TD>
                  <TD>Thursday</TD>
                  <TD>Friday</TD>
                  <TD>Saturday</TD>            
                  <TD>Total</TD>
            </TR>
            <CFOUTPUT QUERY="Qry_GetEmployees">
                  <TR>
                        <TD>#employeename#</TD>
                        <TD><INPUT Type="text" Name="mon#employeeid#" Value="#value from database for monday#" OnBlur="CalTotal('mon#employeeid#','#employeeid#')">&nbsp;hrs</TD>
                        <TD><INPUT Type="text" Name="tue#employeeid#" Value="#value from database for tuesday#" OnBlur="CalTotal('tue#employeeid#','#employeeid#')">&nbsp;hrs</TD>
                        <TD><INPUT Type="text" Name="wed#employeeid#" Value="#value from database for wed#" OnBlur="CalTotal('wed#employeeid#','#employeeid#')">&nbsp;hrs</TD>
                        <TD><INPUT Type="text" Name="thu#employeeid#" Value="#value from database for thur#" OnBlur="CalTotal('thu#employeeid#','#employeeid#')">&nbsp;hrs</TD>
                        <TD><INPUT Type="text" Name="fr#employeeid#" Value="#value from database for fri#" OnBlur="CalTotal('fri#employeeid#','#employeeid#')">&nbsp;hrs</TD>
                        <TD><INPUT Type="text" Name="sat#employeeid#" Value="#value from database for sat#" OnBlur="CalTotal('sat#employeeid#','#employeeid#')">&nbsp;hrs</TD>                        
                        <CFSET nTotalHrs = Add up all the values of the days like this [Evaluate(value from database for monday + value from database for tuesday).. etc]>
                        <TD><INPUT Type="text" Name="tot#employeeid#" Value="#nTotalHrs#" DISABLED>&nbsp;hrs</TD>
                  </TR>                  
            </CFOUTPUT>            
      </TABLE>
</FORM>
</BODY>
</HTML>

PS: Remeber, if there is no value in the database for mon / any other day please show 0 by default in the text boxes...

other wise u will have to check for each and every text box while calculating...


Regards
Hart
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 11

Expert Comment

by:hart
ID: 9918259
use parseInt while adding numerical vaues in javascript other wise it will just append it as string...

0
 
LVL 12

Expert Comment

by:jyokum
ID: 9918284
> use parseInt while adding numerical vaues
OR just multiply by 1, same effect

Waterstone,
send your current query. I could make up an example but may as well use your code if you already have something
0
 
LVL 11

Expert Comment

by:hart
ID: 9918314
oh sorry jyokum...
0
 
LVL 11

Expert Comment

by:hart
ID: 9918349
small changes in my post,

Name="fr#employeeid#" should be Name="fri#employeeid#"

and change this line
if(!isNaN(eval("document.frm." + sfield + ".value")) || eval("document.frm." + sfield + ".value") > 24)


to

if(isNaN(eval("document.frm." + sfield + ".value")) || eval("document.frm." + sfield + ".value") > 24 || eval("document.frm." + sfield + ".value")  == '')

also change onBlur() to OnChange()...


PS : Jyokum the thing was i was typing my code in the intranet and i had not refreshed the question, but then after i posted my code i saw 2 comments one from u and the one of waterstone. :-)

PS : WaterStone, plz do award the points to jyokum :-)

Regards
Hart

0
 

Author Comment

by:Waterstone
ID: 9919506

jyokum,

Good morning!  Here's the query I use to load the cfgrid.

<!---  =================================================   --->

  <cfquery name="GetRecords" datasource="mtcdata" username="username" password="password">
         select  CompanyCode,
                           DepartmentCode,
                                 WeekEndingDate,
                                 CONVERT(varchar, WeekEndingDate, 10) AS WeekEndDate ,
                           LastName,                          
                               EmployeeNumber,
                               JobRSN,
                              FirstName,                            
                      RegHours,
                                OTHours,
                                DoubleHours,
                                TotalHours,
                                SunRegHours,
                                MonRegHours,
                                TueRegHours,
                                WedRegHours,
                                ThuRegHours,
                                FriRegHours,
                                SatRegHours,
                                Approver                          
    from TimeCard  
    Where CompanyCode = '#COCODE#' AND                 
                  WeekEndingDate = '#WeekTime#'
    Order by LastName, FirstName, EmployeeNumber
  </cfquery>

and here's the cfgrid code so ou can see how the fields are presented.

<!---  =================================================   --->
<cfgrid name="time_grid"
    height=250
      width=760
    vspace=0
    selectmode="edit"
    query="GetRecords"
      rowHeaders="No"
      colHeaderTextColor="##3D8FDB"
      colHeaderBold="Yes"
      colHeaderAlign = "Center"      
      onValidate="validateHours"
      onError="HoursError">

    <cfgridcolumn name="FirstName"
        header="First  Name"
      width=125
     headeralign="center"
     headerbold="Yes"
       bgColor="##C9D9E9"
       select="No">

    <cfgridcolumn name="LastName"
        header="Last Name"
       width=125
       headeralign="center"
       headerbold="Yes"
         bgColor="##C9D9E9"
         select="No">
        
      <cfgridcolumn name="EmployeeNumber"
        header="Emp No"
      width=50
      headeralign="center"
      headerbold="Yes"
        bgColor="##C9D9E9"
      select="No">         
        
      <cfgridcolumn name="WeekEndDate"
        header="Wk End"
       width=60
       headeralign="center"
       headerbold="Yes"
         bgColor="##C9D9E9"
         select="No">
                                
        <cfgridcolumn name="SunRegHours"
         type="numeric"
         header="Sun"
       width=30
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         textColor = "(CX GT 24 ? red : black)"
         bgColor="##EDF5FD"
         select="Yes">
                                
        <cfgridcolumn name="MonRegHours"
         type="numeric"
         header="Mon"
       width=30
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         textColor = "(CX GT 24 ? red : black)"
         bgColor="##EDF5FD"         
         select="Yes">                                 
                                
        <cfgridcolumn name="TueRegHours"
         type="numeric"
         header="Tue"
       width=30
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         textColor = "(CX GT 24 ? red : black)"
         bgColor="##EDF5FD"
         select="Yes">                                 
                                
        <cfgridcolumn name="WedRegHours"
         type="numeric"
         header="Wed"
       width=30
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         textColor = "(CX GT 24 ? red : black)"
         bgColor="##EDF5FD"
         select="Yes">                                 
                                
        <cfgridcolumn name="ThuRegHours"
          type="numeric"
         header="Thu"
       width=30
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         bgColor="##EDF5FD"
         select="Yes">                                 
                                
        <cfgridcolumn name="FriRegHours"
          type="numeric"
         header="Fri"
       width=30
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         textColor = "(CX GT 24 ? red : black)"
         bgColor="##EDF5FD"
         select="Yes">
        
        <cfgridcolumn name="SatRegHours"
          type="numeric"
         header="Sat"
       width=30
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         textColor = "(CX GT 24 ? red : black)"
         bgColor="##EDF5FD"
         select="Yes">         
        
        <cfgridcolumn name="RegHours"
          type="numeric"
         header="Reg "
       width=40
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         bgColor="##C9D9E9"
         select="No">
                                
        <cfgridcolumn name="OTHours"
          type="numeric"
         header="OT"
       width=30
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         bgColor="##E0EEFC"
         select="Yes">                                 
                                
        <cfgridcolumn name="DoubleHours"
          type="numeric"
         header="Dbl"
       width=30
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         bgColor="##E0EEFC"         
         select="Yes">
                                         
        <cfgridcolumn name="TotalHours"         
         header="Tot"
       width=40
       headeralign="center"
       headerbold="Yes"
         numberFormat="___.__"
         dataAlign = "right"
         bgColor="##C9D9E9"
         type="numeric"
         select="No">         
                        
       <cfgridcolumn name="DepartmentCode"
      display="No">  
        
       <cfgridcolumn name="JobRSN"
      display="No">  
        
    <cfgridcolumn name="CompanyCode"
        display="No">  
        
      <cfgridcolumn name="WeekEndingDate"
        display="No">
        
  </cfgrid>

Best Regrads
0
 
LVL 12

Accepted Solution

by:
jyokum earned 500 total points
ID: 9926157
just for the sake of being lazy, i didn't include all your columns but this should get you going

<cfquery name="GetRecords" datasource="mtcdata" username="username" password="password">
select
      LastName,                      
      EmployeeNumber,
      SunRegHours,
      MonRegHours,
      TueRegHours,
      WedRegHours,
      ThuRegHours,
      FriRegHours,
      SatRegHours              
from TimeCard  
Order by LastName, EmployeeNumber
</cfquery>

<script>
var wk=new Array('sun','mon','tue','wed','thu','fri','sat');

function initTime(){
      var emps=new Array(<cfoutput>#ValueList(GetRecords.EmployeeNumber)#</cfoutput>);
      var i;
      for(i=0;i<emps.length;i++) sumTime(emps[i]);
}

function sumTime(row){
      var t=0;
      var i;
      tot=eval('document.frmData.total'+row);
      for(i=0;i<=6;i++){
            fld=eval('document.frmData.'+wk[i]+row);
            fld.value>24 ? fld.style.color='red' : fld.style.color='black';
            t+=fld.value*1;
      }
      tot.value=t;
}
</script>

<form name="frmData">
<table border="1" bordercolor="#CCCCCC" cellpadding="0" cellspacing="0">
<tr>
      <td>Employee</td>
      <td>Sun</td>
      <td>Mon</td>
      <td>Tue</td>
      <td>Wed</td>
      <td>Thu</td>
      <td>Fri</td>
      <td>Sat</td>
      <td>TOTAL</td>
</tr>
<cfoutput query="GetRecords">
<tr>
      <td>#GetRecords.LastName# (ID: #GetRecords.EmployeeNumber#)</td>
      <td><input type="text" name="sun#GetRecords.EmployeeNumber#" value="#Val(GetRecords.SunRegHours)#" onChange="sumTime(#GetRecords.EmployeeNumber#);" style="width: 50px;"></td>
      <td><input type="text" name="mon#GetRecords.EmployeeNumber#" value="#Val(GetRecords.MonRegHours)#" onChange="sumTime(#GetRecords.EmployeeNumber#);" style="width: 50px;"></td>
      <td><input type="text" name="tue#GetRecords.EmployeeNumber#" value="#Val(GetRecords.TueRegHours)#" onChange="sumTime(#GetRecords.EmployeeNumber#);" style="width: 50px;"></td>
      <td><input type="text" name="wed#GetRecords.EmployeeNumber#" value="#Val(GetRecords.WedRegHours)#" onChange="sumTime(#GetRecords.EmployeeNumber#);" style="width: 50px;"></td>
      <td><input type="text" name="thu#GetRecords.EmployeeNumber#" value="#Val(GetRecords.ThuRegHours)#" onChange="sumTime(#GetRecords.EmployeeNumber#);" style="width: 50px;"></td>
      <td><input type="text" name="fri#GetRecords.EmployeeNumber#" value="#Val(GetRecords.FriRegHours)#" onChange="sumTime(#GetRecords.EmployeeNumber#);" style="width: 50px;"></td>
      <td><input type="text" name="sat#GetRecords.EmployeeNumber#" value="#Val(GetRecords.SatRegHours)#" onChange="sumTime(#GetRecords.EmployeeNumber#);" style="width: 50px;"></td>
      <td><input type="text" name="total#GetRecords.EmployeeNumber#"></td>
</tr>
</cfoutput>
</table>
</form>

<script>initTime();</script>
0
 

Author Comment

by:Waterstone
ID: 9950189
Thanks for all the help, jyokum.

I'll post another question as a follow up.

What is the best way to use the table to update the database when I add the submit button?
How do I loop through the rows of the table to update the datase using a cfquery?

Thanks for any info you can pas along.
0
 

Author Comment

by:Waterstone
ID: 9952219
Hello jyokum,

Could you clarify one point for me?  Which javascript statement assigns the value of tot to the input field <td><input type="text" name="total#GetRecords.EmployeeNumber#"></td>?

Does tot=eval('document.frmData.total'+row);
connect the variable tot to the field referenced by the eval statement, so that whenever the vsriable tot changes the value is reflected in 'document.frmData.total'+row ?

Thanks,
Waterstone
0
 
LVL 12

Expert Comment

by:jyokum
ID: 9952389
tot.value=t;         << this is the statement that actually assigns is.

the eval statement sets up the reference to the field
0
 

Author Comment

by:Waterstone
ID: 9952434


So as tot changes the value of ="total#GetRecords.EmployeeNumber#" reflects the change?
0
 
LVL 12

Expert Comment

by:jyokum
ID: 9952452
yes

assuming GetRecords.EmployeeNumber = 100...
tot is really just a pointer to document.frmData.total100

if GetRecords.EmployeeNumber = 101

tot = document.frmData.total101
0
 

Author Comment

by:Waterstone
ID: 9952507


So the eval statement is a two-way poniter to the field it references?
Once tot is set up to reference the field, any changes to tot are automatically reflected in the field referenced by the eval statement?

Sorry to be so dense here, just want to be sure I underastand it completely.
0
 
LVL 12

Expert Comment

by:jyokum
ID: 9952521
the javascript eva() function is similar to the coldfusion evaluate() function

http://www.devguru.com/Technologies/ecmascript/quickref/eval.html
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Application launch issue with Apache Tomcat 5 64
Problem to Eclipse 16 132
change time in cron 4 87
How to best troubleshoot slow internet connections via proxy server? 2 87
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question