The Winona State IPAR Blog
IPAR
Exchanging Data With Hobsons Connect, Part 2
In my last post on this subject, I provided an overall framework for exchanging data with Hobsons Connect via web service calls. In this post, I’ll provide specific examples of how to call web services from PowerShell. Although the examples in this post were written with Hobsons Connect product in mind, similar web services are available for the Retain product and in fact many of the functions are identical.
Hobsons Connect uses SOAP-based web services for communication. SOAP, short for “Simple Object Access Protocol”, encapsulates input and output information in XML constructs. There are five main steps for dealing with SOAP-based calls in PowerShell
- Instantiate a Web Service object. This is done through a command such as the following:
$ws=New-WebServiceProxy -uri $WSURL
where $WSURL is a string representing the address of the web service — https://Services02.askadmissions.net/ws/bridge.asmx?wsdl for example
2. Form the input XML. Parameters or inputs to the function call are passed as XML strings. Below is a simple example of an XML string assigned to a string variable.
$InputXML = ‘<attributes><attribute><name>firstname</name></attribute></attributes>’
Hobsons provides a number of different web service functions and the parameters for each call varies. Fortunately, Hobsons has created a document that describes each call and provides some examples. This web service documentation is available at the link below:
http://static.askadmissions.net/_EMTFILES/messagecenter/articles/webservices.pdf
3. Call the Web Service function. In the function call, you pass credentials and input XML strings. An example of a GetContact call is shown below:
[String]$ReturnValue = $ws.GetContact($ClientName, $PassKey, $InputXML, $AttributeXML)
In most Connect service calls, you’ll need to supply a “Client Name” and “Pass Key” as credentials. These are represented in the call above by the $ClientName and $PassKey variables respectively, and are unique strings provided to you by Hobsons. In this case, we also have two input strings, $InputXML and $AttributeXML.
4. Capture the output XML returned by the service call. In the sample call above, output is returned in the $ReturnValue variable.
5. Finally, you parse the output XML and do something with it. You will see how this is done in some of the examples that follow.
Let’s look at some real examples of code to see how these steps work together. This first example illustrates how to call the GetAllAttributes function. This function is useful to determine the internal name of attributes which you might want to use in other web service calls. Within the Hobsons Connect application you are able to see the “Mapped Name” of each attribute. The Mapped Name is the user friendly name you define for attributes and use in the application interface provided by Hobsons — “Application Status” for example. However, you must use the corresponding “Internal Name” when working with attributes in web services. Internal names are assigned by the system and are based on the type of attribute — text, numeric, or date. For example, the internal name for “Application Status” might be “text359″.
#****** Get and display all contact attributes ***********
$ws=New-WebServiceProxy -uri $WSURL # *** Instantiate the web service. ****
[String]$ReturnValue = $ws.GetAllAttributes($ClientName, $PassKey) # ***Call the Web Service ***
write-Host $ReturnValue # *** Display the raw XML ***
[xml]$ReturnValueXML = $ReturnValue # ***Convert the string to an XML variable ***
# *** Crawl through the XML and display mapped and internal names ***
$nodelist = $ReturnValueXML.selectnodes("/result/returndata/attributes/attribute")
Foreach ($node in $nodelist) {
if ($node.type -eq "attribute") {
write-host ($node.mappingname."#cdata-section" + ', ' + $node.name)
}
if ($node.type -eq "folder") {
write-host ''
write-host ('Folder = ' + $node.name) #*** Display Folder Names if encountered ****
write-host '------------------------------------------------------'
}
}
The XML returned by the function call will look something like the following:
<result> <code>1</code><ErrorMessage /> <returndata> <attributes> <attribute> <type>attribute></type><level /> <name>text289</name> <mappingname><![CDATA[SendViewBook]]></mappingname> <datatype>Varchar2</datatype> <size>30</size> <validationexp /> </attribute> </attributes> </returndata> </result>
Each attribute description begins with a <attribute> tag. The <type> tag is used to identify whether the information refers to a folder or attribute. Notice the <name> and <mappingname> tags, as well as additional tags that identify the type and size of the attribute.
Below is a partial view of what a formatted view of the output might look like after the XML is parsed. In this case, some information such as the type and size of the attribute have been left out.
Folder = Area of Study and Interests
——————————————————
PreferredCampus, text229
ProgramInterest1, text253
ProgramInterest2, text255
Folder = International
——————————————————
InternationalStudent, text295
Arrival Dates, date1483
Flight Arrival Info, text1485
Mailing Tracking No, numeric1581
Now let’s look at sample code for searching for a contact record using the GetContact web service call. In the previous example, our only inputs were our Hobsons Client Name and Passkey. However for the GetContact call we have two more input parameters. The first of these is the search criteria, which consists of one or more attribute values used for comparison. Each criterium in the search consists of three parts: the internal name of the attribute, a value for comparison, and the type of comparison (equals, greater than, etc.). The example code below illustrates how to construct an XML search string to match on first and last name:
$InputXML = '<attributes>' ` +'<attribute><name>firstname</name>' ` +'<value>Scott</value>' ` +'<operator>equals</operator>' ` +'</attribute>' ` +'<attribute><name>Lastname</name>' ` +'<value>Guthrie</value>' ` +'<operator>equals</operator>' ` +'</attribute>' ` +'</attributes>'
When more than one attribute is included in the search criteria, results for contacts which meet all conditions will be returned. That is, there is an implied “AND” between the attribute conditions. Hobsons also provides a function called GetContacts (easily confused with GetContact) which returns contacts meeting any of the conditions. That is, there is an implied “OR” between the attribute conditions.
In addition to search criteria, an XML string containing a list of attributes to returned with the contact information also needs to be provided in the call. The internal names rather than the mapped names from the Connect application interface must be specified. Below is an example which references both fixed (standard) attributes and custom attributes.
$AttributeXML = '<attributes>' ` +'<attribute><name>city</name></attribute>' ` +'<attribute><name>state</name></attribute>' ` +'<attribute><name>text141</name></attribute>' ` +'<attribute><name>ID</name></attribute>' ` +'</attributes>'
Finally, you can put these input strings together with the ClientName and PassKey to make the function call and complete the program as shown below:
[String]$ReturnValue = $ws.GetContact($ClientName, $PassKey, $InputXML, $AttributeXML)
The output XML is contained in $ReturnVal. If you simply print this variable out, you’ll get something like:
<result> <code>1</code> <ErrorMessage></ErrorMessage> <returndata> <count>1</count> <contacts> <contact> <attribute> <name>city</name> <value><![CDATA[WestSalem]]></value> </attribute> <attribute> <name>state</name> <value><![CDATA[WI]]></value> </attribute> <attribute> <name>text141</name> <value><![CDATA[10755997]]></value> </attribute> <attribute> <name>ID</name> <value><![CDATA[601]]></value> </attribute> </contact> </contacts> </returndata> </result>
In order to make practical use of this information, you will likely have to parse the attributes out into individual variables. This can be done in several ways. The sample code below shows you how to assign the output to an XML variable and use object nomenclature along with the “where” verb to split each attribute into a separate variable.
$City =($ReturnValueXML.result.returndata.contacts.contact.attribute | where {$_.name -eq "city"}).value."#cdata-section"
$State =($ReturnValueXML.result.returndata.contacts.contact.attribute | where {$_.name -eq "state"}).value."#cdata-section"
$WarriorID =($ReturnValueXML.result.returndata.contacts.contact.attribute | where {$_.name -eq "text141"}).value."#cdata-section"
$ConnectID =($ReturnValueXML.result.returndata.contacts.contact.attribute | where {$_.name -eq "ID"}).value."#cdata-section"
In addition to querying data from Connect, you can also use web service calls to create or update contacts. To create a new contact, you first form the XML containing the attributes and values you want the new contact to use. A simple example appears below:
[string]$OutputXML = "<attributes>"` + "<attribute><name>firstname</name><value><![CDATA[Scott]]></value></attribute>" ` + "<attribute><name>middlename</name><value><![CDATA[Alan]]></value></attribute>" ` + "<attribute><name>lastname</name><value><![CDATA[Guthrie]]></value></attribute>" ` + "<attribute><name>address1</name><value><![CDATA[123 Atwood Dr.]]></value></attribute>"` + "<attribute><name>city</name><value><![CDATA[Mankato]]></value></attribute>" ` + "<attribute><name>state</name><value><![CDATA[MN]]></value></attribute>" ` + "<attribute><name>email</name><value><![CDATA[sguthrie@winona.edu]]></value></attribute>" ` + "<attribute><name>sourcecode</name><value><![CDATA[New Prospect]]></value></attribute>" ` +"/<attributes>"
Note the use of CDATA strings; while not always required, it is good form to use these to avoid any attribute values from interfering with the XML markup language.
Once you have formed your attribute XML, you simply call the CreateContact web service to establish a new contact in Connect with these attributes:
[string]$wsResponse = $ws.CreateContact($ClientName, $ClientPassword, $OutputXML)
In this example, the variable $wsResponse will contain an XML string denoting if the operation was successful or not. Below is what the response looks like when a new contact is successfully created. The unique ContactID of the new contact (12345 in this case) is returned and a Code value of 1 indicates success.
<result> <code>1</code> <ErrorMessage></ErrorMessage> <returndata> <contactid>12345</contactid> </returndata> </result>
A code of 2 indicates that a contact already exists. This determination is made based on the duplicate checking rules you have established in Connect. For example: First Name + Last Name + eMail. When a duplicate contact is found, the existing ContactID will be returned in the XML and attribute values will not be updated.
Updating an exising contact is similar to adding a new contact. You start out by forming the attribute XML, including only those attributes which need to be changed. An example of changing an address is shown below:
[string]$UpdateXML = "<attributes>"` + "<attribute><name>address1</name><value><![CDATA[707 Market St.]]><value></attribute>"` + "<attribute><name>city</name><value><![CDATA[Anytown]]></value></attribute>" ` + "<attribute><name>state</name><value><![CDATA[WA]]></value></attribute>" ` + "<attribute><name>sourcecode</name><value><![CDATA[Update Prospect]]></value></attribute>" ` +"/<attributes>"
To update, simply call the UpdateContact web service as in the example below:
[string]$wsResponse = $ws.UpdateContact($ClientName, $ClientPassword, $ConnectID, $UpdateXML)
Note the inclusion of the $ConnectID variable in this call. This variable must contain a string containing the contact’s existing Connect ID. If you do not have the Connect ID, you can use the previously mentioned GetContact service call to obtain it.
As with other service calls, Connect will return an XML string noting success or failure. An example is shown below:
<result>
<code>1</code>
<ErrorMessage></ErrorMessage>
</result>
If the value of code is anything other than 1, the call failed. The contents of ErrorMessage may give you a clue as to what the problem is.
There are many more web service calls available than I’ve shown, but hopefully this post gives you a framework to get started.
Using Excel to Offer Smarter Online Reports
Our IPAR group produces many online reports that help administrative offices at WSU to do their job more effectively. These reports can quickly retrieve lists of student data from our central student data repository, ISRS, that meet a variety of criteria. These results can be exported to Excel for further processing.
One example is our International Office’s Admitted Students report. This report allows the user to select a term and retrieve a list of international student’s that have been admitted for that term. The report shows ID number, student name, VISA status, some financial information, etc that is stored in ISRS. Much of this data is meant to indicate if the student has completed all the steps required for them to enroll at WSU.
The International Office finds this report quite useful, but there is data not stored in ISRS that they need to keep track of as well. Has the student booked an airline ticket? Have they checked into the office when they get here? What day are they scheduled for an orientation session? There is a whole list of data they need to keep track of that isn’t stored in ISRS, and doesn’t need to be. This information will never be reported on, and is irrelevant after the student has enrolled for classes.
So the practice has been that at some point the International Office runs the Admitted Student report and exports it to Excel. They then add columns for the extra information they need to keep track of. They sort the data, color code rows for various purposes and generally use this Excel document to keep track of their students between the time they are admitted and they enroll. They are quite happy with this procedure, except that it is difficult for them to keep track of when new students have been admitted and need to be added to this Excel document. They also have a hard time keeping track of when data in ISRS changes and therefore needs to change in the Excel document.
IPAR considered several options for helping the International Office here, from full CRM solutions, custom software, to utilizing SharePoint lists and/or Access databases. Our primary motivator was to come up with a solution that was quick and easy to maintain and that would be a significant improvement to the International Office’s situation, if not a perfect solution.
We and they are pretty happy with what we came up with. IPAR developed a macro-driven Excel document that can read (but not write) data from the ISRS database. The Excel document contains the same data as the Admitted Students report contains, but also allows the International Office to add their own data. Whenever they hit the “Refresh” button, any newly admitted students are added to the file. Any data from ISRS that has changed is updated, and highlighted in green (indicating to the International Office that they should re-assess the student’s status). Any student’s that are no longer admitted are highlighted in red, and the International Office can delete these students once they have assured themselves that the change in admit status wasn’t made in error. The data can be sorted, color coded and so on without breaking the functionality.
More technical details about how this spreadsheet works can be found here. A copy of the spreadsheet, including the macros we wrote but scrubbed of any actual student data, can be found here.
This is a pretty new tool for us, and I’m sure we have bugs to work out, but I am very optimistic. This type of “smart” Excel document can be used in many situations across our campus, especially on the administrative side of the house. My biggest concern at this point is that these spreadsheets become too popular, and that supporting them becomes too time-intensive. But, our plan is to stick to just this one for now and learn from the experience, and take it slow from here.
