A look up takes the value supplied by the source question, passes the information back to the database asynchronously, and return values to be assigned to other inputs on the page.

A look up data source can return multiple columns from multiple tables, but the restriction is that a single row is returned from the query. This row is almost always associated with a value provided by the source question. This value can be referenced in the data source query using the parameter "@@LookUpValue".

Sample SQL:

SELECT NAME_MASTER.FIRST_NAME, NAME_MASTER.LAST_NAME, BIOGRAPH_MASTER.BIRTH_DTE
FROM NAME_MASTER
JOIN BIOGRAPH_MASTER ON NAME_MASTER.ID_NUM=BIOGRAPH_MASTER.ID_NUM
WHERE NAME_MASTER.ID_NUM = @@LookUpValue

If the column you are selecting from is a char type or nchar, RTRIM() the column values. Not trimming may result in unexpected effects when interacting with the form.

1.    Log in to the campus portal as an administrator and access J1 FormFlow - Forms.

2.         Click Manage Forms. The Manage Forms page appears.

3.         Click Site Administrators.

4.         From the toolbar, click Data Sources. The Data Source page appears.

5.         Click the Add icon to the right of the DataSource drop-down list. The previously inactive fields become active.

6.         In the Name field, enter a name for the datasource you want to create.

7.         From the Use Database drop-down, select the database you want to pull the data from. This could be your J1 database or another external database you are using with J1 FormFlow - Forms.

8.         If the data source will be used as a list to control access to the form, select the Is Access Control List checkbox.

9.      If you do not want the data source to be executed unless a user is logged in, select the Require Authentication checkbox. This may be useful if the data source contains sensitive information. Jenzabar recommends using string replacers in your data source query to further tailor the results that will display to the user.

10.      In the DataSource Query field, enter your SQL query that will select and/or filter the information in your database that you want to make available in your form.

11.      Click Save DataSource.

12.      Click Done.

13.      Click Manage Forms. The Manage Forms page appears.

14.      Click on the form you are working with.

15.      Click on the tab you want to work with.

16.      Right-click on the textbox, drop-down, or hidden question and select Properties. The Edit Item window appears.

17.      Click the Look Up tab.

18.      From the Look Up DataSource drop-down, select the datasource you want to use to look up information. The table columns associated with the data source appear with corresponding drop-downs.

19.      From the drop-downs, select the Unique IDs of the form questions/fields you want to auto-populate with information from the matching table columns. Example

20.      If you want your look up to execute immediately when the form is accessed, select the Execute on load checkbox. By default, the lookup will execute when the value of the question changes.

21.      If the questions/fields being populated should trigger the question/field's events, select the Trigger After Set checkbox.

22.   If you want to save the text a user manually inputs in a field that utilizes a lookup, select the Preserve user input checkbox. If there is a default value in the field, that would also be preserved. If you use the default value setting and pre-populate the field with @@LastName for example, it considers that user input and the value wouldn't be overwritten if this setting is checked. If this is not selected, the text pre-populated by the lookup will overwrite any text that the user inputs.

23.      Click Save changes.