PaperSave also allows you to set a Workflow 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 Workflow Fields defined for a particular Workflow. Depending on the result of the query you can determine the action to be taken in the workflow.
|·||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 message will be displayed.|
|·||Click OK. 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.|
|·||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.|
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.|
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 = profile.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 [profile.country]
In above query PaperSave will replace profile.country 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.