Creating Views

Using views provides a powerful mechanism for defining what student information is passed to fullmeasure. There are two type of views that should be generated: The Contacts View and Audience Views

1. The Contacts View -The first type of view referred to as the Contacts View. There should only ever be one Contacts View and it should be named fullmeasure_student. This view must contain the following headers:

• contact_id
• first_name
• last_name
• email
• mobile_phone_number

The Contacts View can also contain any extra fields that might be useful to users of the fullmeasure platform. For example, many schools include "Application Submitted Date". There is no limit to the number of extra fields, but you should know that any extra fields in the Contacts View will be shown to all users of the fullmeasure platform exactly as they are named. If you name the header "applicationSubmittedDate" it will appear as applicationSubmittedDate in the fullmeasure platform. So you probably want to rename the header. For example: 

SELECT applicationSubmittedDate as `Application Submitted Date` FROM ....

Contacts View From a GUI (e.g., SQL Server, MySQL Workbench, etc.)

Creating a view in most systems is as easy as select "Create new view" from a GUI like SQL server or MySQL Workbench. In that case you just need to write your SQL and save the view as fullmeasure_student. An example query is provided below:

SELECT
 StudentID as contact_id,
 FirstName as first_name,
 LastName as last_name,
 email,
 Phone as mobile_phone_number
FROM
 dbo.Student

Contacts View From SQL

If you do not use a GUI like SQL Server, you may need to create the view using SQL. Below is an example of the SQL syntax for creating a view in MySQL. SQL Server, Postgres, etc. all very slightly in the syntax but a quick google search should return many examples.

CREATE VIEW `ice`.`fullmeasure_student_demo` AS SELECT
 StudentID as contact_id, 
 FirstName as first_name,
 LastName as last_name,
 email,
 Phone as mobile_phone_number
FROM
 dbo.Student

Audience Views

1. Audience Views -The second type of view is referred to as Audience Views. These views are used to define populations that ensure communications via the fullmeasure platform are personalized. Each Audience View should be prefixed with fullmeasure_audience_. The name of the audience will be whatever follows fullmeasure_audience_. For example, a view named fullmeasure_audience_last_name_starts_with_b would have an audience named "Last name starts with b". Notice that the underscores are converted to spaces in the audience name. If we named the view fullmeasure_audience_lastnamestartswithb the audience name would be "Lastnamestartswithb".

Each Audience View should only include a single column named  contact_id. The contact_id used to create the Audience Views should reference the same contact_ids used when creating the Contacts View.

Audience View From a GUI (e.g., SQL Server, MySQL Workbench, etc.)

SELECT
 StudentID as contact_id,
 FirstName as first_name,
 LastName as last_name,
 email,
 Phone as mobile_phone_number
FROM
 dbo.Student
WHERE LastName LIKE 'b%'

Audience View From SQL

CREATE VIEW SELECT
 StudentID as contact_id,
 FirstName as first_name,
 LastName as last_name,
 email,
 Phone as mobile_phone_number
FROM
 dbo.Student
WHERE LastName LIKE 'b%'