Skip navigation
Currently Being Moderated

Creating Adobe forms with reader 9 and connecting with database

Nov 11, 2010 11:48 AM

Hello,

 

we are currently using adobe approval 5.0 to create  adobe e-forms, when a user launces  a e-form, it uses a javascript to talk to a MS SQl database server via ODBC connection to auto poputae some clinet information based on the client # user entered.

 

we need to move away from approval 5.0  adobe reader 9 and up, so far we couldn't get adobe reader 9 to talk to a ms sql databse server via ODBC connetion.

 

I need some diretion here to choose the cost effctive way to create e-forms based on the client information in the database server.

 

Please help

 

 

 

The js script we are using:

 

// JScript source code
//// March 17, 2010


global.ConnectionDB=null;

app.IDname = identity.loginName;

//Adding SaveAsPDF to menu
app.addMenuItem({ cName: "SaveAsPDF", cParent: "File",
cExec: "doSaveAsPDF();", 
nPos: 0});

//Saving file as PDF with BRN Name Filename format.
function doSaveAsPDF() {
var brnflag = 0 ;
try {
var brn = this.getField("brn").valueAsString;
var temp = brn.replace(/^\s+|\s+$/g,"");
brn = temp;
if(brn.length < 7) {
  app.alert("...Please input BRN...Could not Save!...");
  brnflag = 1;
}
else { brnflag = 0; }
}
catch(e1) {
  app.alert(e1);
}
if(brnflag == 0) {
try {
var brn = this.getField("brn").valueAsString;
var temp = brn.replace(/^\s+|\s+$/g,"");
brn = temp;
var name = this.getField("name").valueAsString;
var f = this.getField("fileName");
var fName = f.userName;

var savePath = "/c/completed forms/" + brn + "-" + fName + " " + name + ".pdf";

if(fName.indexOf('*') != -1) {
  fName = fName.split('*');
  savePath = "/c/completed forms/" + brn + "-" + fName[0] + " " + name + " " + fName[1] + ".pdf";
}

if(((brn == null)||(brn ==""))&&((name == null)||(name == ""))) {
  app.alert("Missing BRN Number/Name...Could not Save!");
}
else {
this.saveAs({
  cPath: savePath,
  bCopy: false,
});
app.alert({cMsg:"SAVED!", cTitle:"E-Form Save Window",nIcon:2});
}
}
catch(e) {
  app.alert(e);
}
}
}

//connecting to Database
function connectForm() {
try {
  ConnectionDB = ADBC.newConnection("CHRIS_DB");
  if (ConnectionDB == null) throw "Could Not Connect!";
  else { this.validateBRN(); }
 
}
catch(e) {
  app.alert(e);
}
}


//validating BRN number
function validateBRN() {
var flag = 0;
var clflag = 0;
try {
  statementBRN = ConnectionDB.newStatement();
  if (statementBRN == null) throw "Could Not Run Query!";

  BRNquery = "SELECT * FROM dbo.AdHoc_Referrals WHERE Reference_Number=" + "'" + this.getField("brn").valueAsString + "'";
  statementBRN.execute(BRNquery);
         global.forBRN = new Array();
         var a = 0;
  var ctnId;
         try {
      for(var a = 0;;) {
                if(statementBRN.nextRow()) throw "BRN ERROR!";                
             rowBRN=statementBRN.getRow();
             var aBRN = rowBRN.Client_Id.value;
             if(aBRN == null)        { aBRN = ""; }
             global.forBRN[a] = aBRN;
      ctnId = aBRN;
      break;
             a++;
      }
      }
      catch(e) {}
}
catch(e) {
  flag = 1;
}

if(flag!=1) {
try {
  ConnectionDB = ADBC.newConnection("CHRIS_DB");
  statementCT = ConnectionDB.newStatement();
  if (statementCT == null) throw "Could Not Run Query!";

  CTquery = "SELECT Client_Name,Client_Number,cast(DOB as char)dob,HCN,HCN_Version, Firstname, Surname, Salutation FROM dbo.AdHoc_Clients WHERE Client_Id=" + "'" + ctnId + "'";
  statementCT.execute(CTquery);
  if(statementCT.nextRow()) throw "ADDRESS ERROR!";
  rowCT=statementCT.getRow();
}
catch(e) { flag = 1; }
}

if(flag!=1) {
try {
  ConnectionDB = ADBC.newConnection("CHRIS_DB");
  statementADD = ConnectionDB.newStatement();
  if (statementADD == null) throw "Could Not Run Query!";

  ADDquery = "SELECT * FROM dbo.AdHoc_Client_Addresses WHERE Client_Id=" + "'" + ctnId + "' AND Address_Type_Id = 2 AND Is_Active = 1";
  statementADD.execute(ADDquery);
  if(statementADD.nextRow()) throw "ADDRESS ERROR!";
  rowADD=statementADD.getRow();
}
catch(e) { app.alert("No Home Address Found...Please Enter The Missing Information...");}
}

if(flag!=1) {
try {
  ConnectionDB = ADBC.newConnection("CHRIS_DB");
  statementPH = ConnectionDB.newStatement();
  if (statementPH == null) throw "Could Not Run Query!";

  PHquery = "SELECT * FROM dbo.AdHoc_Client_EContacts WHERE Client_Id=" + "'" + ctnId + "' AND EContact_Type LIKE 'Home'";
  statementPH.execute(PHquery);
  if(statementPH.nextRow()) throw "ADDRESS ERROR!";
  rowPH=statementPH.getRow(); 
}
catch(e) { app.alert("PHONE NUMBER NOT FOUND...Please Enter The Missing Information...");}
}


if(a > 1) {
  app.alert("...DUPLICATE BRN FOUND!...PLEASE FILL IN MISSING INFORMATION!...");
}
if(flag == 1) {
app.beep();
  nButton=app.alert( {cMsg: "No Matching BRN Found.... Do You Wish To Try Again ?",
        cTitle: "Central CCAC E-Form", nIcon: 2, nType: 2});
  if(nButton==3) {
   try { this.getField("name").value = "";} catch(e) {}
   try { this.getField("dob").value = "";} catch(e) {}
   try { this.getField("clientNum").value = "";} catch(e) {}
   try { this.getField("address").value = "";} catch(e) {}
   try { this.getField("city").value = "";} catch(e) {}
   try { this.getField("postalCode").value = "";} catch(e) {}
   try { this.getField("phoneNum").value = "";} catch(e) {}
   try { this.getField("caseLoad").value = "";} catch(e) {}
   try { this.getField("healthcard").value = ""; } catch(e) {}
   try { this.getField("letter_name").value = ""; } catch(e) {}
   try { this.getField("letter_address1").value = ""; } catch(e) {}
            try { this.getField("letter_address2").value = ""; } catch(e) {}
   try { this.getField("letter_address3").value = ""; } catch(e) {}
  
   // Populating the Case Manager's name
   try {
        var HasDot = app.IDname;
        if(HasDot.indexOf('.') != -1) {
            var CM_Name = app.IDname.split(".");
            this.getField("Signature").value = CM_Name[0].charAt(0).toUpperCase()+ CM_Name[0].substr(1, CM_Name[0].length) + " " + CM_Name[1].charAt(0).toUpperCase()+ CM_Name[1].substr(1, CM_Name[1].length);
        }
        else {
            this.getField("Signature").value = HasDot.replace(HasDot.charAt(0), HasDot.charAt(0).toUpperCase());
        }
   } catch(er) {}

   // Populating the Dates
       var today = new Date();
       today = util.printd("dd/mm/yyyy", today);
   try { this.getField("Signature date").value = today; } catch(er){}  
  
   this.getField("name").setFocus(); 
  }
  else {
   try { this.getField("name").value = "";} catch(e) {}
   try { this.getField("dob").value = "";} catch(e) {}
   try { this.getField("clientNum").value = "";} catch(e) {}
   try { this.getField("address").value = "";} catch(e) {}
   try { this.getField("city").value = "";} catch(e) {}
   try { this.getField("postalCode").value = "";} catch(e) {}
   try { this.getField("phoneNum").value = "";} catch(e) {}
   try { this.getField("caseLoad").value = "";} catch(e) {}
   try { this.getField("healthcard").value = ""; } catch(e) {}
   try { this.getField("letter_name").value = ""; } catch(e) {}
   try { this.getField("letter_address1").value = ""; } catch(e) {}
            try { this.getField("letter_address2").value = ""; } catch(e) {}
   try { this.getField("letter_address3").value = ""; } catch(e) {}


   this.getField("brn").setFocus();
  }  
  return true; 
}
else {
  this.fillForm();
}
}

//Filling Form with CHRIS info
function fillForm() {

var dt;
var finalAddr = "";
var finalHcn = "";
try { var name = rowCT.Client_Name.value; } catch(e1) { var name =""; }
try { var db = rowCT.dob.value; } catch(e1) { var dt =""; }
try { var ctNum = rowCT.Client_Number.value; } catch(e1) { var ctNum=""; }
try { var addr = rowADD.Street_Name.value; } catch(e1) { addr =""; }
try { var aptNo = rowADD.Suite_Number.value; } catch(e1) { var aptNo =""; }
try { var strNo = rowADD.Street_Number.value; } catch(e1) { var strNo =""; }
try { var pCode = rowADD.Postal_Code.value; } catch(e1) { var pCode =""; }
try { var cty = rowADD.City.value; } catch(e1) { var cty =""; }
try { var cLoad = rowBRN.Caseload.value; } catch(e1) { var cLoad =""; }
try { var hcn = rowCT.HCN.value; } catch(e1) { var hcn =""; }
try { var hcnVer = rowCT.HCN_Version.value; } catch(e1) { var hcnVer =""; }
try { var phone = rowPH.EContact.value; } catch(e1) { var phone =""; }
try { var letter_name = rowCT.Firstname.value + " " + rowCT.Surname.value; } catch(e1) { var letter_name =""; }
try { var letter_room = rowADD.Room_Number.value; } catch(e1) { var letter_room =""; }
try { var letter_add1 = rowADD.Mailing_Line_1.value; } catch(e1) { var letter_add1 =""; }
try { var letter_add2 = rowADD.Mailing_Line_2.value; } catch(e1) { var letter_add2 =""; }
try { var letter_add3 = rowADD.Mailing_Line_3.value; } catch(e1) { var letter_add3 =""; }

    var room = "";
    if((letter_room==null)||(letter_room=="")) { room =""; }
    else { room = ", Room # " + letter_room; }
    if(letter_add3==null) letter_add3="";
if(pCode==null) pCode="";
if(cLoad==null) cLoad="";
if(ctNum==null) ctNum="";
if(cty==null) cty="";
if( phone==null)  phone="";

if(db == null || db == "") { dt = ""; }
else {
  dt2 = util.scand("mmm d yyyy h:MM tt",db);
  dt = util.printd("dd-mm-yyyy",dt2);
}

if(((aptNo == null) || (aptNo == "")) && ((strNo == null) || (strNo ==""))) { finalAddr = addr; }
else if((aptNo == null) || (aptNo == "")) { finalAddr = strNo + " " + addr; }
else if((strNo == null) || (strNo == "")) { finalAddr = aptNo + " - " + addr; }
else {
  finalAddr = aptNo + " - " + strNo + " " + addr;
}

if((hcnVer == null) || (hcnVer =="")) { finalHcn = hcn; }
else { finalHcn = hcn + " Ver: " + hcnVer; }

try { this.getField("name").value = ""; } catch(e) {}
try { this.getField("dob").value = ""; } catch(e) {}
try { this.getField("clientNum").value = ""; } catch(e) {}
try { this.getField("address").value = ""; } catch(e) {}
try { this.getField("city").value = ""; } catch(e) {}
try { this.getField("postalCode").value = ""; } catch(e) {}
try { this.getField("healthcard").value = ""; } catch(e) {}
try { this.getField("caseLoad").value = ""; } catch(e) {}
try { this.getField("phoneNum").value = ""; } catch(e) {}

    //fields for letters
    try {   this.getField("letter_name").value = ""; } catch(e) {}
try {   this.getField("letter_address1").value = ""; } catch(e) {}
    try {   this.getField("letter_address2").value = ""; } catch(e) {}
try {   this.getField("letter_address3").value = ""; } catch(e) {}


try { this.getField("name").value = name; } catch(e) {}
try { this.getField("dob").value = dt; } catch(e) {}
try { this.getField("clientNum").value = ctNum; } catch(e) {}
try { this.getField("address").value = finalAddr; } catch(e) {}
try { this.getField("city").value = cty; } catch(e) {}
try { this.getField("postalCode").value = pCode; } catch(e) {}
try { this.getField("healthcard").value = finalHcn; } catch(e) {}
try { this.getField("caseLoad").value = cLoad; } catch(e) {}
try { this.getField("phoneNum").value = phone; } catch(e) {}

    //fields for letters
    try {   this.getField("letter_name").value = letter_name; } catch(e) {}
try {   this.getField("letter_address1").value = letter_add1 + room; } catch(e) {}
    try {   this.getField("letter_address2").value = letter_add2; } catch(e) {}
try {   this.getField("letter_address3").value = letter_add3; } catch(e) {}

 

// Populating the Case Manager's name
try {
     var HasDot = app.IDname;
     if(HasDot.indexOf('.') != -1) {
         var CM_Name = app.IDname.split(".");
         this.getField("Signature").value = CM_Name[0].charAt(0).toUpperCase()+ CM_Name[0].substr(1, CM_Name[0].length) + " " + CM_Name[1].charAt(0).toUpperCase()+ CM_Name[1].substr(1, CM_Name[1].length);
     }
     else {
         this.getField("Signature").value = HasDot.replace(HasDot.charAt(0), HasDot.charAt(0).toUpperCase());
     }
} catch(er) {}

// Populating the Dates
     var today = new Date();
     today = util.printd("dd/mm/yyyy", today);
  try { this.getField("Signature date").value = today; } catch(er){}
}

 

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points