Calculate WEEK number within a CFFORM

Posted on 2006-04-18
Last Modified: 2013-12-24
I have a form that collects employee daily project logs and hours and posts them to a SQL table that is linked to the project table. I would like to add a small table off to the side that shows the total hours that the employee has entered into the db for TODAY, THIS WEEK, and LAST WEEK. The date of the log is entered by the employee in a form field called ContactDate. The default on the ContactDate field is today's date.
My first idea was to create a trigger that posted the week number to a field in the SQL table, but I couldn't figure out how to convert the javascript I found into something I could use in SQL.

My next idea was to calculate the week number before the entry form is submitted (using a postback kind of idea). I could then query the db for the employee's name and sum for all hour entries for today (easy to do), this week (calculate today's week number and query for this number in the db), and last week (same method but query for this week's number -1).

However, I am stuck on the execution. If I put in a hidden field and call the #form.ContactDate#, it croaks and says it is undefined.

Here is the script I found to do the calculate week number:
function getWeekNumber(d1)
      var month = Date.getMonth(d1);
      var day = Date.getDay(d1);
      var year = Date.getYear(d1);
      var now = new Date(year, month, day + 1);
      var then = new Date(year, 0, 1);
      var firstDay = new Date(year, 0, 1);      
      var compensation = firstDay.getDay() + 3;      
      return Math.round((((now - then) / 86400000) + compensation) / 7);

Here is the main form code which all works fine - except now I want to add the additional information in a table at the top right of the page. I don't know if anyone will need this to help answer my question but here it is:

<cfset LogProjectSelected ="Yup" />
<cfset LogProjectNumber = '#getProjects.ProjectNumber#' />
<cfform action ="MapRequest_LogAction.cfm?ProjectNumber=#myProjectNumber#" name="LogEntry" method="Post" enctype="multipart/form-data">
    <input type="hidden" name="CreateDate" value="#DateFormat(Now())#"/>

      <select name="ProjectNumber" style="width:450px; text-align:left ">
        <cfoutput query="getProjects">
          <option value=#ProjectNumber#>#ProjectNumber# [#ProjectTitle#] - (CustomerName#)</option>
        </cfoutput> <cfoutput query="getAllProjects">
          <option value=#ProjectNumber#>#ProjectNumber#[#ProjectTitle#] - (#CustomerName#)</option>
    <select name="StaffName" style="font-size:medium; color:#006699; ">
             <option value="#getProjects.staffnameassign#">#getProjects.staffnameassign#</option>
             <option value="Joe">Joe</option>
             <option value="Jim">Jim</option>
             <option value="Tom">Tom</option>
<input type="text" value="<cfoutput>#DateFormat(prestime, "mm/dd/yyyy")#</cfoutput>" name="ContactDate" required="no" size="10" style="text-align:left; vertical-align:middle; color:#CC3300; font-size:medium" />
      <a href="javascript:show_calendar('LogEntry.ContactDate');" onMouseOver="window.status='Date Picker';return true;" onMouseOut="window.status='';return true;"><img border=0 src="NewMapRequest/images/calendar_icon.gif" align="absbottom"></a>
<input name="hrs" type="text" style="font-size:large; color:CC3300; width:100; font-weight:bold;">

<textarea name="LogNotes" cols="45" rows="8"></textarea>

<input type="submit" value="Submit Log Entry" onMouseDown="">
<input type="reset" value="Reset Form">

I am interested in any way to accomplish my goal... total hours by week for this week and last week.
SQL - COLDFUSION - JAVASCRIPT - or whatever... I have no preference.

Question by:Bauerchick

    Author Comment

    Can I use something like DATEPART(ww, ContactDate) inside a trigger?
    LVL 75

    Accepted Solution


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Network it in WD Red

    There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

    This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
    Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
    This video discusses moving either the default database or any database to a new volume.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now