CF - Field value filled in based on another field

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

JohnMac328Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

azadisaryevCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnMac328Author Commented:
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
azadisaryevCommented:
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
azadisaryevCommented:
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
JohnMac328Author Commented:
I had changed everything but did not save the table :|

Thanks for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.