Add LookUp

PaperSave also allows you to set a profile field that uses a query to perform a lookup operation and fetch the values from the database.


If you have selected the "Add LookUp" option while adding a profile field, then you can add query for lookup by clicking Add Lookup button on the toolbar. The Set Lookup For window will be displayed. Lookup allows you to set queries which can use the profile fields defined for a particular document type. Depending on the result of the query you can determine the action to be taken in the workflow.


Click on image to magnify/shrink
Click on image to magnify/shrink


· Type the query for lookup in the Query String box.
· Type the IP address or name of the server where the database being queried is located in the Server Name / IP box.
· Type the name of the database to be queried in the Database box.
· If Use SQL Authentication option is selected, then provide the UserName and Password in the respective boxes.
· Click Test. If the connection to the specified database is established following notification will be displayed.



All passwords will be stored in plain text within PaperSave Database. Because of this, it is recommended to use Windows Authentication instead of SQL Authentication for increased security.




· In below displayed screen, once the connection with the database is successfully established, the "Display Field" & "Value Field" drop down list will be enabled. Select the Display Field and Value Field items from the drop down list.


Click on image to magnify/shrink
Click on image to magnify/shrink


Lookup Execution Type:


ü Execute LookUp At: There are 2 ways to execute Lookup, viz. Server Side and Data Access Service. Selecting Server Side option will fetch the lookup values from server. Selecting Data Access Service option will fetch the lookup values via Data Access Service.



· Data Access Web Service is recommended to be used only if you are using PaperSave on Cloud.
· Please make sure to install Data Access Web Service setup on your machine, if you wish to fetch the Host Data from cross Domain.
· Below 3 options will get enable only if you have selected Data Access Service option.


ü Data Access Serivce URL: Enter Data Access Service URL.


ü User Name: Enter User Name (User that has the rights to access Data Access Web Service).


ü Password: Enter Password.


Lookup execution type


Click Test to test the connection to data access service. If the connection is successful, you should see below message prompt. Click OK to exit the window.


Data access service message


Lookup Data Entry Configuration: Under Look Up Data Entry Configuration Panel, the "Allow User to type a value in the field" option will be selected by default.


ü If "Allow User to type a value in the field" option is selected, then while adding documents from host application user will be allowed to type the text manually in the respective profile field without clicking on "Look up" search icon.
ü If "Force User to select a lookup value" option is selected then while adding documents from host application user will be automatically prompted to Look Up window as soon as he/she types the text manually in the available text box and press "Enter" key to move to other field value. But if no text is entered in the text box, then user need to click on "Look up" search icon to get directed to Look up screen. Incase no text is entered in the text box and you press "Enter" key, even at that time you will be directed to Look Up window to select the Profile Field.


Lookup Data Entry Configuration



Look up for Data Entry Configuration will only get enable if Display Type is selected as "Text Box" or "Text Area" for the Type "String" and if Display Type is selected as "Text Box" for the Type "Number" under Profile Field Detail Section.


· Once all the details are entered, click Update button to save the details.


Update Look up List


You can also use profile fields created in the lookup query. To use the profile field, you need to use following syntax in the query:




Profile Field with LookUP query:

When user uses, profile.Field_Name in lookup query it will replace value with quotes. If user wants to use value for table name or column name only without the quotes, then user must have to use square bracket such as [profile.Field_Name]. This feature also supports number and date conversion as well for SQL queries.



Let us consider an example where profile fields named 'Country', 'State', and 'City' with display type 'Drop Down list' are created. Also consider that following queries are used in the lookup for each profile field.


For Country: select distinct country from 'tablename'

For State: select distinct state from 'tablename' where country =

For City: select distinct city from 'tablename' where state = profile.state


Considering the above queries, when the user selects a country from the list, depending on the country selected the list of state will be displayed. And, depending on the state selected, the list of the city for the selected state will be displayed.


Using Profile.Field_Name as Table or column name.


For Country : select distinct country from []

In above query PaperSave will replace the value without quotes, so query will be "select distinct country from tablecountry"


This example demonstrates how you can use profile fields of a Document Type in lookup query. You can even use these profile fields in lookup query for columns in 'Table Type' profile fields.

In 'Table Type' profile fields, you can use profile fields as well as other columns in the table in lookup query.



The columns of a 'Table Type' profile field cannot be used in the lookup query of any other profile fields.