Page Comparison

...

1. The query must be SQL based
2. The database must be hosted in a location that the Forms inMotion server can access

Step-by-step guide

...

1. Modify the web.config file for your Forms inMotion server to contain the query information.  This means
   

   1. Add a new connection string to the connectionStrings collection for your query server.  In the example below, in addition to the default formsserver connection string, I have added another one called queryserver to be used in our query.  You can choose any name you like for this that is valid XML and is not "formsserver" since that is reserved for the application.

   2. Add a new queryDefinition section to the existing queryDefinitions section in the formsinMotionConfiguration section of the web.config file.  A full example of the modifications 1a and 1b is shown here.  Note that parameters are definited with @parametername format in both the querytext and parameter definition sections.

      Code Block

      <connectionStrings> <add<formsinMotionConfiguration>   ...   <queryDefinitions> <queryDefinition name="formsserverdepartmentsForWhichYouCanProxy" providerNamequerytext="System.Data.SqlClient" connectionString="Data Source=sql-dev;Initial Catalog=Forms_inMotion;User ID=myuser;password=pass;"/> <add name="queryserver" providerName="System.Data.SqlClient" connectionString="Data Source=sql-dev;Initial Catalog=ERPSystem;User ID=myuser;Password=pass;"/> </connectionStrings>

      Add a queryDefinition section to the formsinMotionConfiguration section of the web.config file.  A full example of this section is shown below

      Code Block

      <formsinMotionConfiguration xmlns="urn:FormsBuilderServer" > <authenticationmethod type="application" domain=""/> <debug debugfile="c:\temp\formsserverlog\formsserverdebuglog22.txt" level="0"/> <license activationcode="AAAAAAAAA-AAAAAAAAA-AAAAAAAAA-AAAAAAAAA-AAAAAAAAA-AAAAAAAAA-AAAAAAAAA-AAAAAAAAA" customername="Joe Customer" licensecode="AAAAAA-AAAAAA-AAAAAA-AAAAAA-AAAAAA"/> <email smtpserver="mail.mymailserver.com" smtpport="25" smtpuser="" smtppass="" smtpsenderaddress="FORMS-INMOTION-DO-NOT-REPLY@myserver.com"/> <queryDefinitions>select ADUserName,Department from dbo.ADDepartment where ADUserName = @username" connectionstringname="onbasetest" > <parameters> </parameters> </queryDefinition> <queryDefinition name="testqueryOne" querytext="SELECT * FROM cc_SSN_AutoFill_vw where (SSN = @ssn or CCN = @casenum) and DOB = @dateofbirth" connectionstringname="onbase" > <parameters> <parameter name="@ssn" datatype="String" /> <parameter name="@casenum" datatype="String" /> <parameter name="@dateofbirth" datatype="DateTime" /> </parameters> </queryDefinition> <queryDefinition name="currentUserID" querytext="select ADUserName,UID from dbo.ADUser where ADUserName = @username" connectionstringname="onbasetest" > <parameters> </parameters> </queryDefinition> <queryDefinition name="userIDFromEmpID" querytext="select ADUserName,UID from dbo.ADUser where UID=@employeenum" connectionstringname="queryserveronbasetest" > <parameters> <parameter name="@employeenum" datatype="String" /> </parameters> </queryDefinition> </queryDefinitions> </formsinMotionConfiguration> ...   <connectionStrings> <add name="formsserver" providerName="System.Data.SqlClient" connectionString="Data Source=SQLVMD11,1517; Initial Catalog=Forms_inMotion_V2;User Id=Forms_inMotionUser;Password=formsinMotionPass"/> <add name="advantage" providerName="System.Data.OracleClient" connectionString="Data Source=servername;User id=tst;Password=mypass;"/> </connectionStrings>

      
      

      Info
      title Reserved Word: username
      When building your query, there is one special parameter @username. If this is included in the querytext, the server will automatically replace it with the name of the logged in user prior to merging any of the other parameters supplied from the form.

      
      

      Info
      title Querying an Oracle DB
      Oracle expects the parameters to be referenced with a : instead of an @ symbol. An example Oracle query might be: Code Block language xml <queryDefinition name="getAllFYAccounts" querytext="SELECT dept_cd, dept_nm FROM admown.r_dept_fy WHERE fy=:fYear" connectionstringname="advantage"> <parameter> <parameter name="fYear" datatype="String" /> </parameters> </queryDefinition>

      
      

2. With the web.config setup.  You can turn your attention to the HTML form itself to make the query call.  All you need to do is call the query function and specify a callback.  An example that would talk to the setup in 1b above would be:  The query is performed by calling a native API function inside your javascript called Forms_inMotion.  Your parameters will be passed into the function as an array.  You also must specify the callback javascript function that will receive the results of the query.  An example is below.  Here we initialize a new parameters array.  Add a single parameter (note that the @ syntax persists here as well) at the 0th position.  This parameter value is a JSON object with keys for name and value.  Then the function is called

   Code Block
   var empID = $("#employeenum").val()12345; var parameters = []; parameters[0] = { "name":"@employeenum","value":empID }; Forms_inMotion("query","userNameLookupCallback","userIDFromEmpID", parameters, "xml");

   Here you can see the Forms_inMotion function call is made with the following parameters:

   1. "query" is always used for a query call

   2. the name of the javascript callback function that we will be usingwill receive the results of the query

   3. the name of the query exactly as definited defined in our web.config file, in this case userIDFromEmpID as shown above

   4. and a parameters array.  Pass in an empty array if you have no parameters to use.  Also make sure that the names match exactly what you defined in the web.config file.

   5. XML or JSON can be requested as the return type depending on your application.  The example below shows processing a response with XML.  XML will be the default if none is specified.

3. Upon return, the data will be pushed to the callback function you specified in 2b 2 above.  An example of this would be like the one below

   Code Block
   language js linenumbers true
   function userNameLookupCallback ( data ) { var parser = new DOMParser(); var xmlDoc = parser.parseFromString(data, "application/xml"); var recordNodesList = xmlDoc.getElementsByTagName("record"); var j; var recordCount = recordNodesList.length; if (recordCount>0) { for (j=0; j<recordNodesList[0].attributes.length; j++) { switch (recordNodesList[0].attributes[j].nodeName.toUpperCase()) { case "ADUSERNAME" : $("#employeeid").val(recordNodesList[0].attributes[j].nodeValue); return; } } } }

   Note that the data is passed back as a chunk of xml.  So the method above takes the data xml content, runs it through the parser, then uses standard DOM manipulation to loop through results looking for tags.  
   

1. 
   
   For reference a sample of return XML results can be seen below

   Code Block
   language xml title XML Results of Employee Query
   <?xml version="1.0"?> <xml> <results> <record authnum="3520049" fname="JOHN" lname="SMITH" AMT="723.40" sdate="11/17/2014 12:00:00 AM" apr="" empid="108594"/> <record authnum="3520050" fname="JOHN" lname="SMITH" AMT="723.40" sdate="11/17/2014 12:00:00 AM" apr="" empid="108594"/> <record authnum="3520051" fname="JANE" lname="SMITH" AMT="723.40" sdate="11/17/2014 12:00:00 AM" apr="" empid="108594"/> <record authnum="3520052" fname="JANE" lname="SMITH" AMT="723.40" sdate="11/17/2014 12:00:00 AM" apr="" empid="108594"/> <record authnum="4374468" fname="JOHN" lname="SMITH" AMT="" sdate="7/25/2015 12:00:00 AM" apr="" empid="108594"/> </results> <recordcount>5</recordcount> </xml>

   
   

Related articles