?
Solved

CF - Field value filled in based on another field

Posted on 2010-01-13
5
Medium Priority
?
356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
Suggested Courses

752 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