Featured Topics Education Series: Communications Setup in Campus Solutions - Simple Tips and Tricks

By Marije Bottinga posted 10-02-2018 06:36 AM


Title: Communications Setup in Campus Solutions - Simple Tips and Tricks
Author: Marije Bottinga, University of Amsterdam, the Netherlands

Blog Series Overview:
The Featured Topics Education Blog Series is sponsored and hosted by the HEUG Campus Community Advisory Group, with the intention to assist and educate the user community about featured topics of interest to the community.  To view other articles in the series, please go to the Article Index for the Featured Topics Blog Series.

Article Overview:
In this article you will find tips and tricks to improve your communication setup in Campus Solutions. No customization is needed to apply these tips and tricks. Using these tips and tricks will provide you the opportunity to save time with your communication setup.

The Campus Community Advisory Group has asked me to share some insights about my institutions communication setup in Campus Solutions. Let me first start by introducing myself: my name is Marije Bottinga and I have worked at the University of Amsterdam in the Netherlands since 2011. In 2015, when I became a functional application manager in the Student Administration department, I was having to reply to a multitude of phone calls and e-mails, and so I started to look for a more effective and efficient way to communicate with students.

At the University of Amsterdam, as with most institutions in the Netherlands, we use an academic year calendar, and while we have both Dutch and foreign students, more than 50 percent of our roughly 50,000 annual applicants are from abroad. To accommodate our unique situation, every academic year we use roughly 60 letter codes in Campus Solutions, each of which are in 2 languages.  Additional constraints that we need to consider for our communications include government and institutional regulations and policies that require quick adaption. So, on one-hand, we require a very solid communication setup to handle the large amounts of email communications that we need to send out, while on the other hand our setup needs to be very flexible. And, at the same time, we aim to send out personalized emails. So, we have had quite some challenges that we needed to find effective and efficient ways to overcome.

The initial idea of this blog was to share the University of Amsterdam’s story about how we build and generate communications in multiple languages, but as the advisory group and I discussed the scope, we quickly realized that we have a lot of unique experiences to share in this area, and thought it best to split our story into a series of 4 blog articles, of which this is the first.  Future blogs will cover topics including: sub-templates and signatures, choices, and multi-language communications.  My hope for this first blog, covering simple tips and tricks, is that you will be able to learn from our experiences and enhance your communication generation in Campus Solutions.

Using Letter codes

Since we use 60 letter codes per academic year, we have the need to be able to find everything very quickly.  What we do to be more efficient and more easily keep all of the corresponding pieces of a letter coordinated and easy to find, is that we create and utilize a letter code - we use a 3 digit number - as a unique identifier.  We then name the speed key, the queries, the report definition, the CommGen RunID, the event id, and the 3C RunID after the letter code. So, for example, if your letter code is ‘123,’ this will also be your speed key and ‘123’ will also be contained in the name of the data source query and assignment query. In this example, the report definition will be ‘123_MAIL’ and the CommGen RunId will be ‘123_GENERATE’. Additionally, your template can be ‘123_confirmation_v1.0.rtf’.  By using a standard naming convention, that include the unique letter code, you should always easily figure out what pieces belong to each specific letter code.

Staying Organized

In our department, we work with a couple of people on the same communication set up, requiring that we always need to keep each other up to date and have the ability to easily find all the details about a communication letter. To this end, we maintain an excel file with all of the details about each communication - one worksheet per academic year. In that sheet, we have the letter codes, queries, run IDs, event IDs, the requests for setting up the emails, the deadlines, and other relevant information about each communication.

Also, because we do all of our original setup in lower environments before shifting all the setup to production, we use a color scheme to differentiate between our prod and non-prod environments. In our case, blue is for our acceptation environment and green is for production.

Another tip is to give version numbers to your templates each time you have a new version to upload. That way you can easily compare templates between your prod and non-production environments to ensure they have the same versions.

Additionally, making folders on a shared document system for each letter code to store the original documents,  xml files, previous versions, and other documents like notes you made while building your queries, can be very helpful.

In conclusion, we have found that staying organized will save you time and frustration when managing many different letter codes.

Using students’ email addresses on your letters

Since the email address field in Campus Solution does not have an effective date, once a student has changed their email address it can be very hard to figure out which email address you sent a letter to.

A method that we use to circumvent this issue is to put the student’s email address at the bottom of each of our mailings in the format of: “This email was sent to

Whenever you can, let your data source do the thinking for you and use expressions to derive values. By using expressions, you minimize the necessary adjustments to your template and, if you ever need to debug your template, it is easier to find the necessary derivations and pieces of your output in the data source query.  As a note, to properly use expressions, remember to create expressions as a field to get them into your output.

 For Example, our data source queries contain a lot of simple and effective expressions such as:

  • Acad load
    • DECODE(A.ACAD_LOAD_APPR, 'F', 'full-time', 'P', 'part-time', 'D', 'dual')
  • Date in full
  • Removing extra spaces if a name does not have a prefix

Assigning useful field names to your expressions

When using expressions, Campus Solutions will automatically generate a unique field name, such as EXPR6_6, but this name does not give a clue about what the expression is that you’ve created.  Since these fieldnames are visible when you insert fields in your rtf template, giving your expressions sensible names will make it much easier to set up your rtf templates in the future.

To update the generically created expression name, go to the Query Manager in Campus Solutions, and once you’ve added your expression as a field, click on the tab ‘Fields.’ From there, find your expression and click on ‘Edit.’  Select the radio button ‘Text,’ and adjust your ‘heading text’ and ‘unique fieldname.’ The unique fieldname has to be unique and it cannot contain spaces. So, for example, if you have used an expression to write the birthdate in full, you can call your unique fieldname ‘BIRTHDATE’.  The field name you create will also show up in BI Publisher in your rtf template, thus making it a lot easier when you need to add fields to a template.

Recycle your data source when you can

Sometimes you will need to have different letter codes with different content, but they contain many of the same fields from Campus Solutions. In that case, you can re-use your data source. For instance, we have a data source that only contains the preferred first name, the name of the study program, and the effective date.  We also have a data source for status updates - containing all the checklist items and checklist item descriptions. We have often been able to re-use those data sources for all kinds of status update mailings.

Re-using data sources will save time, and you will be sure to not have any discrepancies in your status update mailings. Also, remember, you do not need to use all fields of your data source query. So, for example, if your data source query contains 10 fields, and you have 6 fields in the first mailing, but you only need 3 fields in the next mailing, that’s perfectly fine.

Testing your template with an xml file

In BI Publisher, you have the ability to import the xml file in an effort to add fields and test your template.  If you import the xml and preview your document, you will see that your fields now contain sample data – they will literally contain the text ‘sample data.’ To make sure you have the right fields, simply open your xml file in a text editor - I personally use notepad ++ - and you can adjust the sample data as necessary. To do this, you will need to look up the fields you are using and replace the sample data with text that makes sense. For instance, for the ‘preferred first name’ field, I would replace the text ‘sample data’ with ‘Preferred’ or a ‘real name.’ When you’re done editing, save your document, and, since your xml is still connected to your rtf template, you can simply preview it again without importing a new xml file. This way you can check whether you’ve selected the correct fields. Also, note that, when you recycle your data source, you only need to adjust one xml file to test all the rtf documents.

Formatting the names of your programs and putting them into a view

In our Campus Solutions environments, we used to email students about their specific program, referring to it as

To do this work, we have a query for all the names of the programs in a view. Every data source query uses that view to get the DESCR field in the output for the program. It is a one-time investment, but it will save you so much time. Every time a name changes or a program is added, you just update one view, and all your data source queries will automatically be up to date.

Let your assignment query send out reminders

We need students to send in a copy of their ID or passport with their application. So we use our assignment query for that mailing to check for applications that are still active, and without an ID verification. It will also check on the letter code to make sure student will not receive that mail every day. To do this, we have a subquery with condition type ‘does not exist’ that checks in ‘communication’ if the letter code is already assigned.

However, if after 4 weeks, students do not have an ID verification yet, and they are still active, we want the same mailing to be sent out again. 

As an example, to do this, simply add the following to your subquery:

Record: comm_dt

Condition type: greater than

Expression:  SYSDATE-28

If the communication date is less than 28 days, it will not show up in your output. As soon as the communication date is longer than 28 days ago, and your student still meets the other query criteria, it will show up in your output. 

Understanding The Communication Generation Process -