Data Sets
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.
A data set is a saved request for information from the database. They are used across J1 Web to generate reports, run processes, select information you want to view and work with, determine communication recipients, tailor interaction content, and more. Jenzabar delivers commonly used data sets with the system, and you can create custom ones using InfoMaker PBL queries, SQL files, and imported files used as sources (e.g., contact lists) allowing you to leverage your own queries and data.
The Data Sets page provides a centralized location where all these Jenzabar-provided and custom data sets can be managed. On the Data Sets page you can:
Upload queries and select sources to create new data sets.
Control access to data sets.
View and download query columns and syntax to evaluate the query fields and create other similar data sets.
See interaction/automation plan associations to determine where and how a data set is being used and what interactions/automation plans would be impacted by changes.
Note
J1 Web does not offer SQL editing capabilities. Once uploaded, query fields cannot be changed; however, you can download the query syntax, make changes, and then upload it as a new data set.
Type | Classification Options | How Used | Example | Jenzabar-Provided Data Set |
|---|---|---|---|---|
Affiliations | Affiliation Roles | Select individuals, organizations, or entities using database fields as parameters. These data sets can be used when setting up workflows allowing the system to automate the process of assigning To Dos to the appropriate individuals based on their connection to another person or process. | Admissions counselor associated with a candidate can be selected when the candidate’s ID Number is used as a parameter. Automate the assigning of To Dos based on their connection to an individual or process in a workflow. | None |
Charts | Comparative Parts-of-a-Whole | Visually represent system data. This includes comparative charts like bar graphs and parts-of-a-whole charts such as pie and donut charts. They are selected when working with blocks on hub Summary pages. |
| Interaction Analytics |
Data Dictionaries (previously called Data Merge Options) | Communication Data Fields | Customize content and information using database fields. Field placeholders are replaced with actual information when applied. Jenzabar provides several queries that include frequently used data fields including Candidate Details, Name Details, and Student Details. | Insert <<FIRST_NAME>> and <<MAJOR>> fields in an email interaction and the placeholders are replaced with the recipients’ first name and major when the email is sent. | Candidate Details Name Details Student Details |
People/Organization Lists (previously called Recipient) | Group things, individuals, organizations in the database. People / Organization Lists select individuals / organization based on the criteria in the data set. For example, all students majoring in math or with holds on their records. | Group students, communication recipients, account statements and courses, etc. Send all students majoring in math an email about a new course offering or advisors an email about advisees with holds on their records. | Account Statements Original AR Subsidiary Master Original General Details View History Register Original Register Report Original Student Course History Student Course History Original Student Division Master Student Division Master Original Student Master Student Master Original Transfer Subsidiary Balances Original Vendors with 1099s Original Vendors with 1099s who were paid over 600 Original |
From the navigation menu, go to Core, Campus-wide Definitions, Data Sets or use the ‘Find a Page’ search to locate the page by its system name.
The following image shows the Data Sets page with People/Organization Lists data sets selected.
TE_PGMR must have access to tables, columns, and fields referenced in queries being uploaded (TE_PGMR is the system's database-level user account).
Users uploading SQL queries, InfoMaker PBL queries, and imported file sources must have access to the location where the files are stored.
To use InfoMaker PBL queries as data sets, make sure the correct PowerBuilder Services URL is entered in System Settings (System Administration hub, System Settings, Product Installs and Sign In page, PowerBuilder Services URL).
People/Organization Lists queries must include a GOID.
Because data sets results can include confidential information, you can limit who has access to them. For example, a data set may include financial or health information. When you create and edit custom data sets, you can select the modules you want to be able to access the data set. Anyone in a role with access to a selected module can see and use the data set when working in J1 Web (users and role module access are managed on the Core, User Management, Manage System Roles, Edit Role page).
Query Syntax and Columns show you the table and field information the data set uses. You can use this information to troubleshoot issues, download a SQL file version of the query that can be used as a template for creating new data sets, and see which modules have access to the data set.
To view query syntax information for SQL files and InfoMaker queries, select View Details from the Actions button.
You can also export SQL and InfoMaker PBL data set columns to Excel. This can be used to evaluate columns in the query, set up templates for uploading source files, and to determine database field names for use in data merge placeholders. For example, tailoring communications.
People/Organization lists data sets can be used to select groups of people, organizations, and other entities in the system. These groups can be used when selecting students, courses, holds, recipients, account statements, etc. in different areas of the product.
Note
If you want individuals to be able to select different data sets, be sure they are classified and permissioned correctly. Module access permissions are managed on the Edit System Role page.
The following image shows a people/organization lists data set being used to select students that should be included in the Receivables Register report.
The individual running the Register report needs to be in a role with module access enabled for the Register Report queries.
People/Organization lists data sets can be used to determine who receives a communication. For example, a data set based on a query that includes student names, years and terms at your school, and available majors allows you to send targeted emails to incoming students majoring in Science. The data sets are selected when you send/apply an interaction, or an automation plan.
The following image shows people/organization lists data set options available for determining who will receive a communication (Create New Communication window available throughout the system by clicking the Create communication icon 
).
Individuals sending communications to specific groups need to be in a role with module access enabled for the appropriate people/organization lists data sets for those groups. For example, if your Admissions Officers send congratulatory emails to their admitted candidates, they'll need access to a data set that captures admitted students or if your Human Resources Department sends emails to staff, they'll need access to a data set that includes your staff.
When working with templated interactions, data dictionaries make database fields available for customizing your interactions. Fields are shown in your template with placeholders that are replaced with actual information when the interaction is applied/sent. For example, if you insert <<FIRST_NAME>> and <<MAJOR>> fields in an email or text, the placeholders will be replaced with the recipients’ first name and major when the message is sent.
Data dictionaries data sets can be selected when building your interaction templates (Core, Communication Management, Interactions, creating and updating templated interactions).
Individuals creating templated interactions leveraging data sets to tailor content need to be in a role with module access enabled for the appropriate data dictionaries data sets.
When working with automation plans that include templated interactions, the data dictionaries data sets used to tailor content can also be used to create rules within your automation plans. These rules define criteria that determine who receives various interactions and when. For example, a “Welcome Back Students” automation plan might include separate emails for Computer Science and English majors. With rules, you can ensure students receive the appropriate email based on the <<MAJOR_DESCRIPTION>> field.
Rules based on data dictionaries data sets are managed when building your automation plans (Core, Workflow, Automation Plans, creating and updating automation plans).
The following images show a data driven rule being added to an interaction plan. In this example, the email interaction for Computer Science majors won't go out to recipients if their Major is English.
Individuals creating interaction plans and leveraging interactions with data sets to manage rules need to be in a role with module access enabled for the appropriate data dictionaries data sets.
Affiliation data sets use a connection to associate individuals with a workflow. For example, a coach can be associated with a sports scholarship applicant workflow or an advisor can be associated with a dropped course workflow. This just associates the individual in the role with the workflow; permission to view the workflow is handled using role permissions.
Individuals managing workflows and leveraging data sets to automatically assign them to related stakeholders need to be in a role with module access enabled for the appropriate affiliations data sets.
Chart data sets visually represent system data. For example, interaction analytics can be shown in a custom content block.
Individuals setting up custom chart blocks need to be in a role with module access enabled for the appropriate charts data sets.
Select data sets to tailor the information shown when viewing details about an individual or organization. For example, a registrar can select a data set that includes enrollment and hold fields while an HR administrator can select a data set that includes benefits fields.
The following images show a people/organization lists data set options available for changing the information for an individual shown. An advisor may need to see Registration and workstudy HR views for their advisee.
Individuals viewing detailed information about individuals in the system need to be in roles with module access enabled for the people/organization lists data sets.
Data set information can be shown as real-time lists in the data set list block.
The images below show a people/organizations lists data set classified as a data set list being used to show a list of all available courses in a Data Set List block.
 |
When the data set list block is configured, a simple query uploaded to the Data Sets page is selected. Simple query limits:
SELECTS only
Single statement
Display only
Simple queries can include HTML to link to websites, email addresses, etc.
Sample simple query with HTML:
SELECT firstname+' '+lastname, <a href="mailto:'+email+'">'+email+'</a>;' from AlternateContactMethod where email is not null
Individuals setting up custom data list blocks need to be in a role with module access enabled for the appropriate people/organization lists data sets.
There are several ways to find a data set:
From the Queries for drop-down, select Affiliations, Charts, Data Dictionaries, or People/Organization Lists.
Access filter options by clicking the filter icon or link.
Select the checkboxes for the filters you want to use and then select or enter your search criteria.
Click the Apply button.
Note
You can save and reuse commonly used filters.
Click the Name or Type column header to sort.
Note
This option is only available for SQL and InfoMaker PBL data sets.
A detailed view of the data set shows the SQL statement details, modules with access to the data set, and interactions/ automation plans associated with the data set. This information allows you to confirm the data set includes the fields you want and shows you where and how a data set is being used and what interactions/ automation plans would be impacted by changing or deleting it.
Access the Data Sets page.
From the Queries for drop-down, select Affiliations, Charts, Data Dictionaries, or People/Organization Lists.
Find the data set using the filters and/or by clicking on a Name, Type, or Classification to sort.
From the Actions drop-down, select View Details. View Details window appears.
Note
Data Merge queries do not show associated automation plans because they can only be associated with interactions.
To download a SQL or InfoMaker data set as a SQL file, click Download query syntax. You can evaluate the query fields and use it as a basis for creating other data sets.
Access the Data Sets page.
From the Queries for drop-down, select Affiliations, Charts, Data Dictionaries, or People/Organization Lists.
Find the data set you want to update permissions for.
From the Actions drop-down, click Edit. The Edit Data Set window appears.
From the Select Modules Allowed Access to Data Set, add or remove modules you want to be able to access the data set. Anyone in a role with access to a selected module can see and use the data set when working in J1 Web.
Click the Update button. Your changes are applied and the Edit Data Set window closes.
Access the Data Sets page.
From the Queries for drop-down, select Affiliations, Charts, Data Dictionaries, or People/Organization Lists.
Find the SQL File, InfoMaker, or Source data set you want to download to Excel.
From the Actions drop-down, click Download columns to Excel. Your .xlsx or .zip file is saved in the file location that you have set in your browser settings.
Warning
Use caution when editing a data set that is in use. Changes can impact recipients or the merged data fields used for customized content. To see a list of interactions and automation plans associated with a data set, select View Details from the Actions drop-down.
Access the Data Sets page.
From the Queries for drop-down, select Affiliations, Charts, Data Dictionaries, or People/Organization Lists.
Find the data set you want to update.
From the Actions drop-down, click Edit.
If the query is associated with any interactions or automation plans, a confirmation pop-up window appears. Click Yes, edit data set to proceed.
In the Edit Data Set window, make the needed changes. You can rename the query, select a different classification, add/remove modules with access to the data set, or select a different source, SQL file, or PBL query.
Remember data sets are used across the system and changes can impact interactions, automation plans, and other users utilizing it.
Click Update. Your changes are saved and the Edit Data Set window closes.
Note
You cannot delete data sets that are associated with interactions or automation plans. You can reassign the interactions/automation plans associated with the data set to another data set and then delete it. You also cannot delete Jenzabar-provided data sets.
Access the Data Sets page.
From the Queries for drop-down, select Affiliations, Charts, Data Dictionaries, or People/Organization Lists.
Find the query you want to delete.
From the Actions drop-down, click Delete.
Click Yes, delete query in the confirmation pop-up.
Click Yes, delete.
Not entirely. Desktop only uses InfoMaker queries. Data sets in Jenzabar One Web can not only be created from InfoMaker but can also come from SQL queries and source codes.
Yes, your IT Administrator can upload them into the system and ensure you belong to a role with access to them. They may need to make some modifications to get the columns the system expects.
SQL queries must be created outside of J1 Web and uploaded into the system from the Data Sets page.
Data set files are stored in the database.
Typically your IT Department or System Administrator creates the SQL query files. You can collaborate with them on the fields you’d like to use for customizing interaction content or recipients.
Yes, you can select a query in InfoMaker PBLs.
This could mean the PowerBuilder Services URL isn't entered correctly in the System Administration hub, System Settings, Product Installs and Sign In page, PowerBuilder Services URL. Contact your campus support team for more information.
Yes, just like Desktop, Jenzabar provides commonly used data sets for J1 Web. We’ll provide additional data sets with each release so be sure to check in.
Yes, for most Jenzabar-provided data sets, you can make some updates.
For InfoMaker-based data sets, you can change who has access to it using the Select Modules Allowed Access to the Data Set option and the PBL query being used. You cannot change the name, classification, type, or PBL.
For SQL-based data sets, you can change who has access to it using the Select Modules Allowed Access to the Data Set option. You cannot change the name, classification, type, or SQL file.
Edit and Delete options are only available for queries you uploaded. You cannot edit or delete the Jenzabar-provided data sets. You also cannot Delete queries associated with interactions or automation plans as this might impact functionality.
If a query isn't associated with any interactions or automation plans yet, the View option is unavailable.
The View associated interactions and sets option is available for people/organization list queries because those queries can be associated with both interactions and automation plans. The View associated interactions option is available for merge data fields queries because they can only be associated within the body and text of an interaction.
You can use the following query to see the data sets an interaction was applied to:
select distinct d.Code, d.Description, d.AppID from ManagedTaskSetDefinition s join ManagedTaskSetManagedTasks t on s.AppID = t.ManagedTaskSetDefinitionAppID join ManagedTaskDefinition d on t.ManagedTaskDefinitionAppID = d.AppID where ManagedTaskTargetListDefinitionAppID = '[The App Id Of the Data Set]'
To see the data fields included in the Jenzabar-provided data sets, select View Details from the Actions menu.