Auto fill form fields using coldfusion

[acoder]

If you look at the queries, look for the clientID, city and notes. You will notice that the quotes begin on one line and end on the next. This is what's causing the problem. You will also notice that no other field has line breaks.

Note: instead of duplicating code like this, it might be an idea to put it in one place and call it wherever required.

[bonneylake]

If you look at the queries, look for the clientID, city and notes. You will notice that the quotes begin on one line and end on the next. This is what's causing the problem. You will also notice that no other field has line breaks.

Note: instead of duplicating code like this, it might be an idea to put it in one place and call it wherever required.

Hey Acoder,

That was the problem, which wow can't believe that small problem caused that ! THANK YOU, THANK YOU!!! I have just 1minor questions left to ask. Well on the customer notes it still has space behind it an it appears in the database is there anyway to make it not have the space behind it, an is it a huge deal for it to have space behind it?

Thank you,
Rach

[acoder]

You mean at the end? Check that the quotes only go around the variable and nothing else - no spaces, line breaks, etc.

[bonneylake]

Hey acoder,

i am definately stuck on this one. I got it to insert correctly the first time, but when i try to get it to update an do the final insert it don't work right. Here is what i have

first time

<!---Inserts Data for General Information and Contact Information.--->
<cfquery name="Update" datasource="CustomerSupport">
exec usp_CS_UpdateticketMaster '#Form.pk_ticketID#','#Form.title#','#Form.priority#','#Form.status#','#Form.last_edited_by#'
,'#Form.custnum#','#Form.compname#','#Form.fname#','#Form.lname#','#Form.address#'
,'#Form.city#','#Form.state#','#Form.zip#','#Form.email#','#Form.priphone#','#Form.secphone#'
,'#Form.custnotes#','#Form.cost_center#','#Form.htpp#'
</cfquery>

<!---Updates/Inserts Contacts table (should only update in this case unless they create a new customer number right here which they shouldn't)--->
<cfquery name="insertcontacts" datasource="CustomerSupport">
exec usp_CS_UpdateInsertcontacts  '#Form.custnum#','#Form.compname#','#Form.fname#', '#Form.lname#','#Form.address#','#Form.city#','#Form.state#','#Form.zip#','#Form.email#','#Form.priphone#','#Form.secphone#',
'#Form.custnotes#'
</cfquery>

update

<!---Updates TicketMaster Table--->
<cfquery name="Update" datasource="CustomerSupport">
exec usp_CS_UpdateticketMaster '#Form.ID#','#Form.title#','#Form.priority#','#Form.status#','#Form.last_edited_by#'
,'#Form.custnum#','#Form.compname#','#Form.fname#','#Form.lname#','#Form.add1#'
,'#Form.city#','#Form.state#','#Form.zip#','#Form.email#','#Form.priphone#','#Form.secphone#','#Form.custnotes#','#Form.cost_center#'
,'#Form.htpp#'
</cfquery>

<!---Updates/Inserts Contacts table (should only update in this case unless they create a new customer number right here which they shouldn't)--->
<cfquery name="insertcontacts" datasource="CustomerSupport">
exec usp_CS_UpdateInsertcontacts  '#Form.custnum#','#Form.compname#','#Form.fname#', '#Form.lname#','#Form.add1#','#Form.city#','#Form.state#','#Form.zip#','#Form.email#','#Form.priphone#','#Form.secphone#','#Form.custnotes#'
</cfquery>

final submit

<!---Inserts Data for General Information and Contact Information.--->
<cfquery name="Update" datasource="CustomerSupport">
exec usp_CS_UpdateticketMaster '#Form.ID#','#Form.title#','#Form.priority#','#Form.status#','#Form.last_edited_by#'
,'#Form.custnum#','#Form.compname#','#Form.fname#','#Form.lname#','#Form.address#'
,'#Form.city#','#Form.state#','#Form.zip#','#Form.email#','#Form.priphone#','#Form.secphone#'
,'#Form.custnotes#','#Form.costcenterid#','#Form.htpp#'
</cfquery>

<!---Updates/Inserts Contacts table (should only update in this case unless they create a new customer number right here which they shouldn't)--->
<cfquery name="insertcontacts" datasource="CustomerSupport">
exec usp_CS_UpdateInsertcontacts  '#Form.custnum#','#Form.compname#','#Form.fname#', '#Form.lname#','#Form.address#','#Form.city#','#Form.state#','#Form.zip#','#Form.email#','#Form.priphone#','#Form.secphone#'
,'#Form.custnotes#'
</cfquery>

Thank you,
Rach

[acoder]

When you say it doesn't work right, what happens? Are there any errors?

[bonneylake]

When you say it doesn't work right, what happens? Are there any errors?

Hey Acoder,

i think i know what it is, but not sure what to do about it. On the first page the cursor appears directly behind whatever you added in the notes. However when you get to the second page it appears underneath whatever you wrote. When i took that space out it didn't add it to the table for either the update or the final insert. So is there a way to make the curser appear behind the text added in a textarea field?

Thank you :),
Rach

[bonneylake]

Hey Acoder,

I figured out the problem to the notes. Turns out this is how i had it

<textarea maxlength='500' onkeyup='return ismaxlength(this)' onkeydown='return ismaxlength(this)'rows="4" cols="60" name="custnotes" id="notes">#Form.custnotes#
</textarea>

this is how it needed to be

<textarea maxlength='500' onkeyup='return ismaxlength(this)' onkeydown='return ismaxlength(this)'rows="4" cols="60" name="custnotes" id="notes">#Form.custnotes#</textarea>

Crazy or what?

But i got two more question. Lately i have run into the problem where when i go to create a new ticket. Lets say i already have a value in the contact table called 22222. Anyway if i go to type the value of 22 in the field instead of the drop down box changing to blank (since 22 does not exist) it will still say 222222, is there anyway i can get it so that it will go blank in the drop down box if the field don't exist in the table?

Anthe last question i am not sure if this is possible but wanted to ask. If someone starts typing a number in the field it starts adding what it thinks i am guessing behind it an was wondering if there was anyway to still get the drop down box to populate but not add what it thinks i am typing in in the field box?

Thank you again for all the help :),
Rach

[acoder]

I figured out the problem to the notes.
Crazy or what?

Those small nuances can make all the difference.

But i got two more question. Lately i have run into the problem where when i go to create a new ticket. Lets say i already have a value in the contact table called 22222. Anyway if i go to type the value of 22 in the field instead of the drop down box changing to blank (since 22 does not exist) it will still say 222222, is there anyway i can get it so that it will go blank in the drop down box if the field don't exist in the table?

So you want this to happen as soon as you start typing and not when you focus away from the field?

Anthe last question i am not sure if this is possible but wanted to ask. If someone starts typing a number in the field it starts adding what it thinks i am guessing behind it an was wondering if there was anyway to still get the drop down box to populate but not add what it thinks i am typing in in the field box?

Yes, this is possible. Just remove the call to the function/code which adds to the field.

[bonneylake]

Those small nuances can make all the difference.

So you want this to happen as soon as you start typing and not when you focus away from the field?

Yes, this is possible. Just remove the call to the function/code which adds to the field.

Hey Acoder,

Yes i would like this to happen when i am typing afraid there get confused other wise if they continue to see it as they type.

an which part do i remove? do i remove it out of the field itself

<input type="text" name="custnum" id="clientID" value=""  ONKEYUP="autoComplete(this,this.form.customer,'value',false)"/>
<SELECT NAME="customer" id="options"
onChange="this.form.custnum.value=this.options[this.selectedIndex].value;">
<option value="" selected></option>
<cfoutput query="getcustnum">
<option value="#fk_custNum#">#fk_custNum#</option>
</cfoutput>
</SELECT>

or the javascript?

var ajax = new sack();
	var currentClientID=false;
	function getClientData()
	{
		var clientId = document.getElementById('clientID').value;
		if (clientId != "") {
			currentClientID = clientId
			ajax.requestFile = 'getClient.cfm?custnum='+clientId;	// Specifying which file to get
			ajax.onCompletion = showClientData;	// Specify function that will be executed after file has been found
			ajax.runAJAX();		// Execute AJAX function			
	}}

	

	
	function showClientData()
	{
	    var formObj = document.forms['page1'];
		var resp = ajax.response.split(",");
      
      if (ajax.response == "") { // you may want to trim here just in case
          formObj.cust_company.value = "";
		  formObj.fname.value = "";
		  formObj.lname.value = "";
		  formObj.add1.value = "";
		  formObj.city.value = "";
		  formObj.state.value = "";
		  formObj.zip.value = "";
		  formObj.email.value = "";
		  formObj.pri_phone.value = "";
		  formObj.sec_phone.value = "";
		  formObj.notes.value = "";
		  
      } else {
          var resp = ajax.response.split(",");
          formObj.cust_company.value = resp[1];
		  formObj.fname.value = resp[2];
		  formObj.lname.value = resp[3];
		  formObj.add1.value = resp[4];
		  formObj.city.value = resp[5];
		  formObj.state.value = resp[6];
		  formObj.zip.value = resp[7];
		  formObj.email.value = resp[8];
		  formObj.pri_phone.value = resp[9];
		  formObj.sec_phone.value = resp[10];
		  formObj.notes.value = resp[11];
      }
	}

Thank you :),
Rach

[acoder]

For the first question, you may have to change the autoComplete function. You could default to the first option to begin with.

I may have misunderstood the second question. If you meant that it appears below the field with the previous entries, then you can use 'autocomplete=" off"' as an attribute for the field.

[bonneylake]

For the first question, you may have to change the autoComplete function. You could default to the first option to begin with.

I may have misunderstood the second question. If you meant that it appears below the field with the previous entries, then you can use 'autocomplete=" off"' as an attribute for the field.

Hey Acoder,

well the first question i asked was working correctly because it does leave the field blank.Think i just got confused by something else. But i was wondering, for some reason if the drop down box is blank it will still allow the user to autofill if you click on the button an it puts undefined in all the fields an was wondering if there was a way to disable the button until they actually choose a field in the drop down box?


Well on my second question, what i am trying to do is when a user starts typing in the field it starts guessing what i am typing behind it (its in blue what it thinks i am typing) but anyway i was wondering if i could make it so it would stop guessing what i am typing behind it, but still autofill the drop down box?i don't know if its possible but just wanted to ask.

here is what i have

Customer Number*:<input type="text" name="custnum" id="clientID" 
value="#fk_customer_number#"  ONKEYUP="autoComplete(this,this.form.customer,'value',false)" size="20"/>
<cfset fk_customer_number = #fk_customer_number#>
</cfoutput>
<SELECT NAME="customer" id="options"
onChange="this.form.custnum.value=this.options[this.selectedIndex].value;"/>
<option value="" selected></option>
<cfoutput query="getcustnum">
<option value="#fk_custNum#"<cfif #fk_custNum# is #fk_customer_number#>selected</cfif>>#fk_custNum#</option>
</cfoutput>
</SELECT>
<input type="button" class="custnum" name="insert" value="Insert Contact" 
 onclick="getClientData();"/>

Thank you again for all the help :),
Rach

[acoder]

In the getClientData() function, you could check for the value. If it's empty, don't make the request.

As for the guessing, this is most probably caused by the auto-complete function. If you remove the second part of the function which creates a text range and selects the text, it shouldn't guess any longer.

[bonneylake]

In the getClientData() function, you could check for the value. If it's empty, don't make the request.

As for the guessing, this is most probably caused by the auto-complete function. If you remove the second part of the function which creates a text range and selects the text, it shouldn't guess any longer.

Hey Acoder,

Got the auto-complete working correctly (yay). But i am lost on the getClientData. I can tell it already checks clientID, but not sure how to make it check that both the clientID and customer both have a value.

here is what i did try,but i had to have missed something because didn't work

var ajax = new sack();
	var currentClientID=false;
	var currentCustomer=false;
	function getClientData()
	{
		var clientId = document.getElementById('clientID').value;
		var customer = document.getElementById('customer').value;
		if (clientId != "" & customer != "") {
			currentClientID = clientId
			currentCustomer = customer
			ajax.requestFile = 'getClient.cfm?custnum='+clientId;	// Specifying which file to get
			ajax.onCompletion = showClientData;	// Specify function that will be executed after file has been found
			ajax.runAJAX();		// Execute AJAX function			
	}}

here is what the code originally looks like.

var ajax = new sack();
	var currentClientID=false;
	function getClientData()
	{
		var clientId = document.getElementById('clientID').value;
		if (clientId != "") {
			currentClientID = clientId
			ajax.requestFile = 'getClient.cfm?custnum='+clientId;	// Specifying which file to get
			ajax.onCompletion = showClientData;	// Specify function that will be executed after file has been found
			ajax.runAJAX();		// Execute AJAX function			
	}}

Thank you,
Rach

[acoder]

The id of the customer drop down is "options", so:

var clientId = document.getElementById('clientID').value;
var customer = document.getElementById('options').value;
if (clientId != "" && customer != "") {

[bonneylake]

Hey Acoder,

IT WORKS! THANK YOU THANK YOU!!!!

I got one last question i was hoping you could help me out with. I don't know if i need to start a new thread for this but its dealing with the same page so thought i would try asking. if i need to start a new thread for this let me know :).

But anyway i been working on trying to understand the URL method of retrieving information. I been able to get one table of information, but now i need to get 3 tables of information at the same time an i am running into trouble. Each table has a number in common with the other, each has a field that holds the number (but fields have different name). For example ticketMaster table has the field pk_ticketID which holds the number 1, serial has the field pkb_fk_ticketNo which holds the number 1, and parts has the field fk_ticketNo which holds the number 1. Right now i can get the correct table information for ticketMaster but for the serial an parts i get all the tables that are in those tables instead of just the one record needed, which in this case is the one record that holds the number 1.

Right now here is what i have

<!---Shows what was previously entered into table ticketmaster--->
<cfquery name="ticket" datasource="CustomerSupport">
		SELECT pk_ticketID,title,priority,status,
cost_center,fk_customer_number,
customer_company,customer_Fname,customer_Lname,
customer_add1,customer_city,customer_state,
customer_zip,customer_email,customer_pri_phone,
customer_sec_phone,customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
        WHERE pk_ticketID = #URL.pk_ticketID#
</cfquery>

<cfquery name="serial" datasource="CustomerSupport">
		SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type,
software_hardware,resolution,resolution_date,
         verification_date,rma_data,
type_hardware_failure,dept_responsibility,resolution_verified_by FROM dbo.tbl_CS_serial
</cfquery>

<cfquery name="parts" datasource="CustomerSupport">
		SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,
part_returned,defective,submission
         FROM dbo.tbl_CS_parts
</cfquery>

<form name="page1" id="page1" action="saveticket1edit.cfm?<cfoutput query="ticket">pk_ticketID=#pk_ticketID#</cfoutput><cfoutput query="serial">&pkb_fk_ticketNo=#pkb_fk_ticketNo#</cfoutput><cfoutput query="parts">&fk_ticketNo=#fk_ticketNo#</cfoutput>"
method="POST" onSubmit="return validate_form();">
</form>

If you could let me know what i am doing wrong i would really appreciate it, i been looking online for days an can't find anything online that does anything i am trying to do. But again THANK YOU so much for the help :) on the autofill i really really appreciate it!!!

Thank you again for all the help :),
Rach