Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CF - Field value filled in based on another field

Posted on 2010-01-13
5
Medium Priority
?
361 Views
Last Modified: 2013-12-16
I have a video library system and I am trying to have a field filled in when the test score is recorded.  The employee is selected and then the video they watched is selected.  I want the field "Test Number" to fill in when the video is selected.  Test number is the number of tests that video requires.  The table is Videolist and has a field that shows how many test each video requires.  The details are then inserted into the Test table.  Any help is appreciated.
<cfif structKeyExists(FORM, "Submit")>
<CFSET new_date = #CREATEODBCDATETIME(Test_Date)#> 


<cfquery name="AddTestDetails" datasource="#datasource#">
                INSERT INTO Test
          (employeeID,VideoID, Test_Number, Test_Score, Test_Status, Credits_Earned, Test_Date)
                VALUES (<cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.employeeID#">,
                        <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.VideoID#">,
                        <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.Test_Number#">,
                        <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.Test_Score#">,
                        <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.Test_Status#">,
                        <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.Credits_Earned#">,
                           <cfqueryparam value="#new_date#" cfsqltype="CF_SQL_TIMESTAMP">
                     
                )

        </cfquery>
	
</cfif>
	


<cfquery name="getAllEmployees" datasource="#datasource#">
   SELECT  EmployeeID, First_Name, Last_Name, Email
   FROM    Employees
   ORDER BY First_Name, Last_Name
 </cfquery>
 

<cfquery name="getAllFilms" datasource="#datasource#">
 SELECT     *
 FROM VideoList 
 </cfquery>

<table class="displayTable">
      <cfform action="AddTestDetails_New.cfm" method="post" format="html">
      <tr>
        <th>Employee</th>
        <th>Video</th>
        <th>Test Number</th>
        <th>Score</th>
     </tr>
      <tr>
        <td><select name="employeeID">
            <cfoutput query="getAllEmployees">
              <option value="#employeeID#">#First_Name##Last_Name#</option>
            </cfoutput>
        </select></td>
        <td><select name="VideoID">
            <cfoutput query="getAllFilms">
              <option value="#VideoID#">#Video_Name#</option>
            </cfoutput>
        </select></td>
         <td>
    
         <cfinput type="text" name="Tests" size="5">

        </td>
       <td><cfinput type="text" name="Test_Score" size="5"></td>
      </tr>
      <tr>
        <th>Test Status</th>
        <th>Test Date</th>
        <th>Credits</th>
      </tr>
      <tr>
         <td><select name="Test_Status">
            <option value='1'#UserPass#>Passed</option>
            <option value='0'#UserFail#>Fail</option>
        </select></td>
        <td><cfinput type="datefield" name="Test_Date"></td>
        <td><select name="Credits_Earned" class="widthed">
            <option value='0'>0</option>
            <option value='1'>1</option>
            <option value='2'>2</option>
            <option value='3'>3</option>
        </select></td>
      </tr>
      <tr>
        <td><input type = "Submit" value = "Submit" name="Submit"></td>
      </tr>
      </cfform>
    </table>

Open in new window

0
Comment
Question by:JohnMac328
  • 3
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
azadisaryev earned 2000 total points
ID: 26304280
there are several ways to do this, and they all require javascript.
here's how to do it using in-page js (as opposed to using ajax).

1) add this js code to your page, making sure it is after your getAllFilms query:
[NOTE: assumes the column that stores number of required tests is called 'numtests' - change as necessary]

<script type="text/javascript">
var arrFilms = [<cfoutput query="getAllFilms">{'videoid':#videoid#, 'numtests':#numtests#}<cfif getAllFilms.currentrow LT getAllFilms.recordcount>,</cfif></cfoutput>]; //create an array of objects of video ids and required tests number

var showNumTests = function(vid) {
  for (var i=0; i<arrFilms.length; i++) {
    if (arrFilms[i].videoid == vid) document.getElementById("Tests").value = arrFilms[i].numtests;
  }
};
</script>

2) add this onChange event to your VideoID select:
<select name="VideoID" id="VideoID" onchange="showNumTests(this.value)">


Azadi
0
 

Author Comment

by:JohnMac328
ID: 26304822
I changed the field to NumTests to make it easier and I get this error just trying to bring up the page.

Variable NUMTESTS is undefined.  
 
 
The error occurred in C:\Inetpub\wwwroot\CF_Library_TestSQL\AddTestDetails_New.cfm: line 47
 
45 :
46 : <script type="text/javascript">
47 : var arrFilms = [<cfoutput query="getAllFilms">{'videoid':#videoid#, 'NumTests':#NumTests#}<cfif getAllFilms.currentrow LT getAllFilms.recordcount>,</cfif></cfoutput>]; //create an array of objects of video ids and required tests number
48 :
49 : var showNumTests = function(vid) {

 

<cfif structKeyExists(FORM, "Submit")>
<CFSET new_date = #CREATEODBCDATETIME(Test_Date)#> 


<cfquery name="AddTestDetails" datasource="#datasource#">
                INSERT INTO Test
          (employeeID,VideoID, NumTests, Test_Score, Test_Status, Credits_Earned, Test_Date)
                VALUES (<cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.employeeID#">,
                        <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.VideoID#">,
                        <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.NumTests#">,
                        <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.Test_Score#">,
                        <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.Test_Status#">,
                        <cfqueryparam cfsqltype="CF_SQL_INteger"  value="#FORM.Credits_Earned#">,
                           <cfqueryparam value="#new_date#" cfsqltype="CF_SQL_TIMESTAMP">
                     
                )

        </cfquery>
	
</cfif>
	


<cfquery name="getAllEmployees" datasource="#datasource#">
   SELECT  EmployeeID, First_Name, Last_Name, Email
   FROM    Employees
   ORDER BY First_Name, Last_Name
 </cfquery>
 

<cfquery name="getAllFilms" datasource="#datasource#">
 SELECT     *
 FROM VideoList 
 </cfquery>

<script type="text/javascript">
var arrFilms = [<cfoutput query="getAllFilms">{'videoid':#videoid#, 'NumTests':#NumTests#}<cfif getAllFilms.currentrow LT getAllFilms.recordcount>,</cfif></cfoutput>]; //create an array of objects of video ids and required tests number

var showNumTests = function(vid) {
  for (var i=0; i<arrFilms.length; i++) {
    if (arrFilms[i].videoid == vid) document.getElementById("Tests").value = arrFilms[i].NumTests;
  }
};
</script>


<body class="twoColElsLtHdr">

<div id="container">
  <div id="header">
    <img src="images/USGI_Masthead.gif" />
</div>
  <div id="sidebar1">
 <cfif isDefined("SESSION.Role") AND SESSION.Role EQ 0 >
<cfinclude template="sidebar_menuUser.cfm">
<cfelse>
<cfinclude template="sidebar_menuAdmin.cfm">
</cfif>
  </div>
  <div id="mainContent">
    <table class="displayTable">
      <cfform action="AddTestDetails_New.cfm" method="post" format="html">
      <tr>
        <th>Employee</th>
        <th>Video</th>
        <th>Test Number</th>
        <th>Score</th>
     </tr>
      <tr>
        <td><select name="employeeID">
            <cfoutput query="getAllEmployees">
              <option value="#employeeID#">#First_Name##Last_Name#</option>
            </cfoutput>
        </select></td>
        <td><select name="VideoID" onchange="showNumTests(this.value)">
            <cfoutput query="getAllFilms">
              <option value="#VideoID#">#Video_Name#</option>
            </cfoutput>
        </select></td>
         <td>
         <cfinput type="text" name="NumTests" size="5">
        </td>
       <td><cfinput type="text" name="Test_Score" size="5"></td>
      </tr>
      <tr>
        <th>Test Status</th>
        <th>Test Date</th>
        <th>Credits</th>
      </tr>
      <tr>
         <td><select name="Test_Status">
            <option value='1'#UserPass#>Passed</option>
            <option value='0'#UserFail#>Fail</option>
        </select></td>
        <td><cfinput type="datefield" name="Test_Date"></td>
        <td><select name="Credits_Earned" class="widthed">
            <option value='0'>0</option>
            <option value='1'>1</option>
            <option value='2'>2</option>
            <option value='3'>3</option>
        </select></td>
      </tr>
      <tr>
        <td><input type = "Submit" value = "Submit" name="Submit"></td>
      </tr>
      </cfform>
    </table>

Open in new window

0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 26304978
is 'NumTests' the name of column/field in your VideoList table in which you store the number of required tests? you should be using the actual name of that column, not just some random name...

Azadi
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 26305029
also, since you have changed the NAME of <cfinput> field to NumTests, you need to change it in my js code as well, in the getElementById() part.

Azadi
0
 

Author Closing Comment

by:JohnMac328
ID: 31676643
I had changed everything but did not save the table :|

Thanks for the help
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
Suggested Courses

577 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