How do I use the SQL Query field in Form Builder?

Intranet Connections allows users to create SQL Query fields in correlation with FormBuilder to pull data from your application database. As a method of preventing SQL Injection attacks, we only allow users with super administrator privileges to implement the SQL Query field.

Data from this query will be presented in a dropdown field available for users to select when filling out their form response.

 

The following instructions will step users through the process of adding a SQL Query field to an existing form:

Add a SQL Query Field to a FormBuilder form

  1. Log in to Intranet Connections using Super Administrator credentials
  2. Navigate to admin > Applications
  3. Locate and click the FormBuilder application (admin > assets > home site > apps > formbuilder > click 'go to app')
  4. Navigate to the desired form
  5. Click Edit

    edit.png
     
  6. Click the Build tab
  7. Add a new field and select SQL Query from the list of Form Fields
  8. Click add
  9. Fill-out the Label, SQL Query fields and any additional fields (as needed)

    example.png
     
  10. Click save

After completing the steps seen above, an administrator should have successfully used a SQL Query to query the database for user information.

Query Format:

In order for our software to execute a query contained in the SQL query field, users must provide 'OPTIONVALUE' as an alias for a specified column. 

select [your select text column] as OPTIONVALUE from [table name]

Example Queries:

User’s First Name (example of pulling from user profiles)
 
select Firstname as OPTIONVALUE from userprofiles
where UserID = *UserIdentification*
 
NOTE: Common column names for the UserProfiles table include; Firstname, Lastname, UserName, Email.
 
NOTE: *UserIdentification* pulls the UserID of the user that is currently logged in.
 
User’s First and Last Name as single value (an example of joining the values of two different fields)
 
select Firstname + ' ' + Lastname as OPTIONVALUE from userprofiles
where UserID = *UserIdentification*
 
First and Last Name of all users in the software:

Version 12.5 and later

SELECT p.FirstName + ' ' + p.LastName

FROM Person p INNER JOIN Employee e ON p.PersonID = e.PersonID

ORDER BY p.FirstName, p.LastName

 
User’s Supervisor (use the following example to query the supervisor of a logged in user)           
 
select Firstname + ' ' + Lastname as OPTIONVALUE from userprofiles
where UserID IN (Select SupervisorID from Userprofiles where UserID = *UserIdentification*)
 
User’s Phone Number (example of pulling from phonelist)
 
Versions 12.0 and later
select PhoneNumber as OPTIONVALUE from Employee a
Inner Join UserProfile b on a.PersonID = b.personID where b.UserID = *useridentification*
 
User’s Directory Department (example of join statement)
 
Versions 12.0 and later
Select a.Department as OptionValue from EmployeeDepartment a Inner Join Employee b on a.DepartmentID = b.DepartmentID Inner Join UserProfile c on b.PersonID = c.personID where c.UserID = *useridentification*
 
 
Can you pull information from another database?
 
Yes, you can. To do so, you will need to create a datasource for your other database within either ColdFusion or Railo. Within the SQL query field, click the 'more properties' option to expand and locate the 'Datasource' field where you can specify another datasource you wish to connect to for this field.

Referenced by:

Have more questions? Submit a request

0 Comments

Article is closed for comments.