Data Sources
Important
Information and features vary according to the roles to which you belong and the permissions associated with those roles. For more information, contact your module manager or your campus support team.
Important
Jenzabar-provided data sources vary according to your school's licenses to the Candidate feature and J1 Web Events Management.
Note
Any data sources you set up are available to all FormFlow instances within Campus Portal.
Data sources are SQL statements that can be used to populate information in different ways across multiple forms. Data sources can save the form user time completing information you already have in your database, help ensure inaccurate information isn't entered, and can keep questions limited to specific choices. There are five types of data sources you can use:
Options can use information in your database to populate drop-down, radio button, and checkbox lists. For example, you can populate a state drop-down list on your form with a state list from your J1 database. You can set up static options, dynamic options, or cascading options.
Auto Completes provide a typeahead feature that lets form users start typing a field/question response and then pick from a list of options that automatically appear.
Look Ups use a 'trigger' question which completes other fields on the form based on information the form user enters or selects. For example, if a faculty member enters their email address, the first and last name fields on the form can be automatically completed for them based on information you have in your database.
Access Control Lists let you limit who can access your form. For example, you may create a homecoming voting form, but only allow upperclassman to vote.
Waiver Codes can be used when a payment is associated with your form and your school allows the fee to be waived in certain circumstances.
You can use Jenzabar-provided JZB SQL statements or create your own so you can populate your forms with frequently used database information. Data sources are not limited to one form. You can reuse them as needed.
Tip
Prefix a value with ## in the SQL to hold the place for a value to be inserted at execution.
Note
Jenzabar-provided data sources are read-only and cannot be customized. These data sources are prefixed with JZB. For example, "JZB - Event Look Up by ID".
For This Feature to Work
You must have an established database connection set up in FormFlow.
You must have SELECT permissions to the tables you are saving the form information to.
Best Practices
Trailing Spaces. Database columns frequently use CHAR data types, which pad values with trailing spaces. This can cause issues when comparing the user entry with what exists in the database. Extra spaces might prevent it from matching.
Notice
Your database might have "NC" as a state and when the user enters "NC ," trailing spaces might be added.
Use RTRIM() on char and nchar columns to ensure values match.
Avoid Large Data Retrievals. When a data source returns large amount of data from the system, that data source can stop working and other functionality on the page can break. Large data retrievals, often referred to as “payloads”, can be anything from a list of all the organizations in your system, all names from NameMaster, or large amounts of transactional data.
Note
By default, IIS supports a 100 KB maximum of data per request.
Watch Out for Mapping Loops. When using cascading options or look up data sources, you’ll need to pay careful attention to how you map columns and placeholders to questions on the form to ensure you don’t cause an infinite loop.
Notice
With a look up question, you should never map the triggering question as a target question to receive data from the look up. This causes data returned by the query to be set in the triggering question’s field and causes the question to trigger the look up again. Enhancements have been made to prevent users from doing this, but in some versions it is still possible to set the triggering question as a target.
Note
Cascading data sources can only be used with the drop-down question type. A cascading options data source relies on the response from another question on the form to be used as a parameter in the query. Because data sources are global, there isn’t a way to reference a specific question on a form since the same data source could be used with different forms. Instead, placeholders can be added to the query. At the time of execution, the placeholders get replaced with values supplied to questions on the form that are mapped to those placeholders. A data source placeholder always starts with “##” followed by a descriptive name, as seen in the example below.
Follow the steps to Create a Data Source.
Click Manage Forms.
Click on the form you are working with.
Click on the tab you want to work with.
Right-click on the drop-down, radio button or checkbox question you are associating the data source with and select Properties to open the Edit Item pop-up.
Click the Options tab.
Select Dynamic Options.
From the Options Source drop-down, select the auto-complete data source you created.
Select the question you wish to map to the placeholder defined in the query from the placeholder drop-down.
Click Save changes.
Note
Auto complete data sources can be used with Textbox question types to suggest possible values to the user as they are typing. These data sources must have at least one, but a maximum of two, columns returned from the query.
The first column is the “search text”. As the user types, they’ll see values returned from this column that contain the string they have typed in the input. The second column may provide an additional value that will replace the user’s input if selected. For instance, the user may type “Sam St” and upon choosing the provided search text “Sam Stack”, the ID number associated with Sam Stack would be populated in the textbox.
Follow the steps to Create a Data Source.
Click Manage Forms.
Click on the form you are working with.
Click on the tab you want to work with.
Right-click on the textbox question you are associating the data source with and select Properties to open the Edit Item pop-up.
Click the Options tab.
Select Dynamic Options.
From the Options Source drop-down, select the data source you created.
Click Save changes.
Note
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”.
Complete the steps to Create a Data Source.
Click Manage Forms.
Click on the form you are working with.
Click on the tab you want to work with.
Right-click on the textbox, drop-down, or hidden question and select Properties to open the Edit Item pop-up.
Click the Look Up tab.
From the Look Up DataSource drop-down, select the data source you want to use to look up information. The table columns associated with the data source appear with corresponding drop-downs.
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.
Select the Execute on load checkbox If you want your look up to execute immediately when the form is accessed.
Note
By default, the lookup will execute when the value of the question changes.
Select the Trigger After Set checkbox if the questions/fields being populated should trigger the question/field's events.
Select the Preserve user input checkbox if you want to save the text a user manually inputs in a field that utilizes a lookup.
Note
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, 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.
Click Save changes.
Note
An access control list data source is used to restrict who can see a form based on data returned from a query. For example, in the sample SQL below, the user that is currently logged in has an ID number does not exist in the data source, the link in the main view to the form will not be visible. This data source requires only one column to be returned.
Users without an ERP ID number will not be able to see the form. This includes Campus Portal Administrator account, feature-only accounts, and external sign-up users.
Follow the steps to Create a Data Source.
Click Manage Forms.
Click on the form you are working with.
Click Form Settings to open the Form Information pop-up.
Click the Access/Security tab.
Select the appropriate data source from the Limit Access to drop-down.
Click Save changes.
Note
A waiver code data source can be created that will allow the user filling out the form to bypass payment regardless of the payment profiles assigned to a form. A waiver code data source should return only one column with only valid waiver codes. Once assigned to a question, such as a textbox question for a user to enter in their waiver code, if a user enters a value that appears in the results returned from the query, they’ll be able to submit without a payment.
Follow the steps to Create a Data Source.
Click Manage Forms.
Click on the form you are working with.
Click on the tab you want to work with.
Right-click on the textbox question you are using for waiver codes and select Properties to open the Edit Item pop-up.
Tip
You can name this textbox question “Waiver Code”.
Click the Settings tab.
Select the waiver code data source you created from the Waiver Code Data Source drop-down.
Click Save changes.
Double check your data source to ensure that any literal string replacers do not have any apostrophes around them. A correct literal string replacer should read @@Faculty.