disconnected.jpg

Designing a Disconnected Mobile Application to Work with SharePoint

Matthew Chestnut is a Senior Consultant at ThreeWill. He has over 20 years of software development experience around enterprise and departmental business productivity applications. He has a proven track record of quality software development, on-budget project management and management of successful software development teams.

When approached by a customer to design a mobile application to be used in the field by employees at locations throughout the state, our initial thoughts centered on browser-based data entry via web forms and SharePoint lists. The application needed the ability to capture data via a data entry form with data validation along with the ability to calculate totals, something HTML and JavaScript are more than capable of, especially when coupled with SharePoint lists or document libraries.

However, while gathering additional requirements from the customer and their subject matter experts, it became apparent that approach would not work as the mobile application needed capabilities that a browser-based solution would not provide:

  • Must function in a completely disconnected fashion (i.e. no Wi-Fi or cellular data)
  • Must have the ability to attach photos to the collected data
  • Must capture signatures of the employee and business
  • Need high-fidelity printing of form to leave with business including the data collected on premise

So, back to the system architecture drawing board! Further research led us down the path which we are currently pursuing. It is still a design-in-progress, so we don’t have all the answers yet. But I wanted to share with you how we are now leaning towards a PDF file-based approach.

The common PDF file has been around for many years providing for high-resolution display of documents across a multitude of devices: be it printers, computer displays, mobile phones and tablets. Another use for PDF forms are for fillable PDF forms where users can enter data into a PDF form in designer-specified locations.

The bottom line? All mobile client PDF processing applications are not created equal. It seems that each vendor of mobile applications that support the PDF standard only support a subset of functionality across all mobile operating systems, whether it be iOS, Android or Windows.

Here are the top 5 things we needed for the design of our mobile SharePoint application:

1. Printing the PDF – what you see is what you get

This is certainly where the PDF format shines. You get what you expect as long as you have a wireless printer (think Bluetooth) connected to your mobile device. One form for data entry and for printing, it can’t get any simpler than this.

2. A signature can be a simple freehand graphic or fully-verified electronic identity

The ability to add a signature to a document means different things to different people. It could be as simple as using your finger or stylus to write your name in a blank space on the form or it could be that a certificate is added to the document that confirms that you are who you say you are. For us, the finger/stylus approach is acceptable and certainly doable in a disconnected mode.

3. File attachments

The PDF standard allows for attaching/embedding other content in the PDF file itself, but your mobile client must support that capability. Many do not. We needed the ability for user to attach photos to the document for subsequent transmission, to the SharePoint document library.

4. JavaScript support for customized validation and calculations

Be aware that calculated fields are available in PDF forms, but only if the mobile client supports it. You can also do custom validation or alter the PDF form at runtime via JavaScript. Many mobile clients support JavaScript on iOS but not on Android, or vice-versa. Be sure to test on all platforms before deciding on your approach.

5. User Workflow

In our application, the user needs the ability to create a number of forms for different businesses and name and store them appropriately while disconnected so that when connected, the data is transferred correctly to SharePoint. Mobile device operating systems like iOS and Android seem to abstract or hide the file system from the user. The mobile applications used to open PDF files have no concept of your application’s workflow; they simply open the file, let you change the form fields and let you save it, many times on top of itself without allowing you to change the name of the document. Some mobile applications allow you to email the PDF form directly (to a SharePoint email-enabled document library) or allow you to “share” the content of a service like Google Drive, DropBox or One Drive. This may require user training to make certain the users know where their documents reside on their mobile device.

A PDF file is more than just a pretty way to view or read documents…

In summary, PDF fillable forms provide a great way to capture user data in a disconnected mobile environment – just be sure you understand the limits!

ThreeWill has the ability and experience to transform standard SharePoint lists and forms into enterprise level applications. Contact us to let us know how we can help you.

read more
Matthew ChestnutDesigning a Disconnected Mobile Application to Work with SharePoint
customer.jpg

Top 5 Reasons to Manage Customer Experience in SharePoint

Matthew Chestnut is a Senior Consultant at ThreeWill. He has over 20 years of software development experience around enterprise and departmental business productivity applications. He has a proven track record of quality software development, on-budget project management and management of successful software development teams.

Customer Experience in SharePoint

Customers interact with a business in numerous ways, whether browsing through the store or online catalog, asking questions in person or through email, purchasing product at point-of-sale or online and returning or exchanging merchandise after the sale. At each of these touch points is an opportunity to capture information about the experience your customer has with the process or people. Whether the customer experience is entered directly by the customer or via an astute employee, here are the top 5 reasons Microsoft SharePoint can assist with capturing this information:

  1. Lists provide a versatile repository for structured data

    SharePoint lists are the place to record the “who, what, when and where” of the customer interaction. As needs change, the list data can be enhanced, in place, to capture additional information about the customer experience without the need for data conversion or migration. Files attached to the list can help document the experience, whether they are photographs, email correspondence, or other content. SharePoint lists contain the “database” of customer information.

  2. Search for similar customer experiences

    Different customers often have similar experiences. SharePoint full-text search can be used to identify other related interactions to consolidate or remove duplicate information. Search can be executed interactively or programmatically depending on how it best fits the processing workflow. Results ranking can be used to allow the most pertinent results appear at the top of the results list. User experience can be greatly enhanced by integrating search results directly into the data entry screens, enabling search-as-you-type or search for duplicates at the initial point of data entry.

  3. Workflows allow for custom routing, review and approval

    SharePoint workflows are a method to automate, augment and enhance business processes. These workflows can send email notifications of newly added items, trigger approval steps as list items are reviewed and data is changed, and perform a myriad of other tasks, many without the need for custom programming, to support the business processes.

  4. Retention policies can trigger reminder emails or even archive older content

    SharePoint retention policies are triggers on list data or documents that occur when an item is deemed to have “expired.” Typical uses for retention policies include moving content to an archive repository when the content reaches a certain age and to send email reminders based on a number of days since the last interaction or the number of days an item has remained in a particular state or queue. Retention policies run once a week by default, but can easily be configured to run once a day.

  5. Integration with Microsoft Office

    A standard feature of SharePoint lists is the ability to export of data into Microsoft Excel. Additionally, Microsoft Access can read, query, process and report on SharePoint data, in real time, without the need to export and process the data locally. Microsoft Word can process Excel data, allowing for robust mail merge capability. Via the OpenXML SDK for Microsoft Office (software development kit) programmatic creation of Excel, Word and PowerPoint slides provide unlimited possibilities for presentation of customer experience data.

Summary

Microsoft SharePoint provides a rich out-of-the-box infrastructure for data related to customer interactions and experiences (as well as for many any other data-related needs.) The combination of list data, full-text search, workflows, retention policies and integration with Microsoft Office can cover a wide range of customer experience use cases, empowering your organization to streamline your business data collection and processing.

ThreeWill has the ability and experience to transform standard SharePoint lists and forms into enterprise level applications. Contact us to let us know how we can help you.

read more
Matthew ChestnutTop 5 Reasons to Manage Customer Experience in SharePoint
outside.jpg

SharePoint List Forms with jQuery

Tim is a Senior Consultant at ThreeWill. He has 15 years of consulting experience designing and developing browser-based solutions using Microsoft technologies. Experience over the last 8 years has focused on the design and implementation of SharePoint Intranets, Extranets and Public Sites.

SharePoint List Forms with jQuery

SharePoint provides some great “out of box” features to get a departmental application up and running quickly. However, there are often requirements that fall outside the “out of box” capabilities. jQuery, along with many jQuery plugins that have been written, is a great tool that can be used to help fill in these gaps.

Here are a few ways I have used jQuery to supplement what SharePoint gives me for free:

  • Enforce field-level permissions in a SharePoint form
  • Create parent/child relationships between fields in a SharePoint form
  • Enforce custom validation in a SharePoint form such as conditionally required fields
  • Add “autocomplete” to a text box to filter down valid entries as the user types in the field

First of all, why would I want to enforce field-level permissions? Let’s suppose I create a custom list in SharePoint that I want to use for both submitting and approval of expenses. I would need the person submitting the expense to be able to add and update entries in this list, and I would also need the “expense approver” to also update the same list items. However, I wouldn’t want the “submitter” of the expense to be able to fill out fields that are specific to the “expense approver.” Within SharePoint, I can create a custom SharePoint Group in which I place users who are “Approvers.” I can then add jQuery to my SharePoint form that determines if the current user is a member of the custom Approvers group created above. If not, I can disable the field. If so, I can leave the field enabled. Note this is not true security but is a nice way to help guide users as they input data as part of a departmental or inter-departmental process. There are ways for a malicious user with the right permissions to work around this by creating a new add or edit form for this list that doesn’t contain the jQuery. However, an event receiver (server-side code) can easily be added to validate who is updating these fields on the server and reject the change if the user doesn’t have the appropriate privileges.

A second scenario that jQuery can help solve is the desire to have a parent/child relationship within a SharePoint form. A simple example of this would be the relationship between States and Counties. If I select the state of Georgia, I should only see counties in the Counties field that relate to Georgia. Otherwise, a user might select state/county combinations that are invalid which would require extra time and money to determine which is correct.

A third and final scenario to discuss in this blog is the desire to have conditionally required fields. By default, SharePoint allows configuration of a required field, but this field is required all the time. What if a field should only be required conditionally? What if the Expense Approved Date is required for the expense to be approved? When the submitter of the expense enters the expense, there is no way they can know the Approved Date nor would it even be appropriate for them to enter it since they are not the approver. The ability to implement a conditionally required field solves this problem.

Implementation Details for Programmers or Power Users

By default, SharePoint creates 3 forms for a new custom list:

  • DispForm.aspx
  • EditForm.aspx
  • NewForm.aspx

Each of these forms can be edited with SharePoint Designer to add in the appropriate code to “wire up” jQuery. I typically use the PlaceHolderAdditionalPageHead ContentPlaceHolder.

The example below demonstrates:

  • Referencing the jQuery file that is stored locally in the SiteAssets folder. The minified version of the jQuery file has been downloaded and added to a scripts sub-folder within the SiteAssets library. In this scenario, SPServices and blockUI javascript libraries are also being used so they have been downloaded locally and are also referenced.
  • An application javascript file called myApplication.js where application code will go.
<asp:Content ContentPlaceHolderId="PlaceHolderAdditionalPageHead" runat="server">
	<SharePoint:DelegateControl runat="server" ControlId="FormCustomRedirectControl" AllowMultipleControls="true"/>
	<SharePoint:UIVersionedContent UIVersion="4" runat="server"><ContentTemplate>
		<SharePoint:CssRegistration Name="forms.css" runat="server"/>
	</ContentTemplate></SharePoint:UIVersionedContent>
	<!—add my stuff here →
        <script src="/SiteAssets/scripts/jquery-2.1.3.min.js" type="text/javascript"></script>
	<script src=”/Siteassets/scripts/jquery.SPServices-2014.01.js" type="text/javascript"></script>
	<script src="/Siteassets/scripts/blockUI.js" type="text/javascript"></script>
	<script src="/SiteAssets/scripts/myApplication.js" type="text/javascript"></script>
</asp:Content>

Flow of Code

After the page loads,the typical flow of the code is, retrieval of the current user from SharePoint so I can determine which SharePoint groups the current user is a member and then disabling or hiding any fields that may not be applicable to this user. Because this code can take a second or two to run, I use a jQuery plug-in called blockUI to block the screen from user input during this time. I also leverage SPServices (http://spservices.codeplex.com/) to make it easier to consume the SharePoint web services.   You will see both of these referenced in the code excerpts below.

Start script after page is loaded and after the sp.js file is loaded to ensure all form elements exist and the appropriate SharePoint libraries are loaded

$( document ).ready(function() {

ExecuteOrDelayUntilScriptLoaded(InitContext, "sp.js");

});

Populate local user object from the server

function InitContext() {

//block user input while the jQuery code runs

$.blockUI();

try {

// current user object

this.cUser = new Object();

this.cUser.groups = new Array();

this.cUser.id = "";

this.cUser.loginName = "";

var ctx = new SP.ClientContext.get_current();

currentWeb = ctx.get_web();

usrLoad = this.currentWeb.get_currentUser();

usrLoad.retrieve();

ctx.load(this.currentWeb);

ctx.executeQueryAsync(Function.createDelegate(this, this.initContextSucceeded),

Function.createDelegate(this, this.initContextFailed));

}

finally {

}

}

Success Delegate

function initContextSucceeded() {

try {

cUser.loginName = this.usrLoad.get_loginName();

cUser.id = this.usrLoad.get_id();

loadUserGroups(cUser.loginName);

UpdateForm();

$.unblockUI();

}

finally {

}

}
<h3>Code to load SharePoint Groups</h3>
// Retrieve the group membership for the current user

function loadUserGroups(loginName) {

$().SPServices({

operation: "GetGroupCollectionFromUser",

userLoginName: loginName,

async: false,

completefunc: function (xData, Status) {

$(xData.responseXML).find("Group").each(function () {

cUser.groups.push($(this).attr('Name'));

});

}

})

}

Failure Delegate

// Failure callback for initContext async query

function initContextFailed(sender, args) {

$.unblockUI();

alert('Retrieval of initial context failed.\nMessage: ' + args.get_message() + '\nStack Trace: ' + args.get_stackTrace())

}

Enforce Field-Level Permission

The UpdateForm function contains an example of a “field-level” permission based on a user’s role which is indicated by their membership or lack of in a custom SharePoint group called “InventorySpecialist”.

Update Form – Add code here to disable any fields based on user’s membership (or lack of membership) in a particular SharePoint group

function UpdateForm() {

//Disable Status field if user is not in the Inventory Specialist SharePoint group

if ($.inArray('InventorySpecialist',cUser.groups) == "-1" {

$("select[title=Status']").attr("disabled",true);

}

//bind an event handler to the change event of the State drop-down

$("select[title='State']").change(StateChanged);

//trigger the change event

$("select[title='State']").change();

}

Enforce Parent/Child Relationship

The parent/child relationship enforcement is demonstrated using the State/County example in the UpdateForm function. A “change” event handler is bound to the State drop-down such that when the state changes, a new set of County values are populated. The example below is greatly simplified for the purpose of this blog, but the code in the change event handler would most likely make a caml query call to the look-up list associated to the Counties column to get all the counties associated to the State so that the value the user selects will be a valid value when saved back into SharePoint.

Update Form – Add code here to disable any fields based on user’s membership (or lack of membership) in a particular SharePoint group

function UpdateForm() {

//Disable Status field if user is not in the Inventory Specialist SharePoint group

if ($.inArray('InventorySpecialist',cUser.groups) == "-1" {

$("select[title=Status']").attr("disabled",true);

}

//bind an event handler to the change event of the State drop-down

$("select[title='State']").change(StateChanged);

//trigger the change event

$("select[title='State']").change();

}

function StateChanged() {

//get value of state drop-down

var state =$("select[title='State']").val();

//get a reference to the County drop-down to load new counties below

var countySelection = $("select[title='County']");

switch (state) {

case '(None)':

break;

 

case 'Alabama': 

counties = ['Baldwin','Barbour','Bibb'];

break;

case 'Georgia':

counties = ['Cobb','Forsyth','Fulton'];

break;

default:

break;

}

//remove options from County drop-down

$("select[title='County']")

.find('option')

.remove()

;

foreach (county in counties) {

countySelection.append('<option value="' + county + '">' + county + '</option>');

}

}

Add custom validation

The custom validation is demonstrated in the PreSaveItem function. SharePoint automatically calls this function before posting to the server giving the developer a chance to insert any custom validation. In this example, I am ensuring that the user selected a “Location” value in the location lookup. I could easily make this check conditional on whether the current user is a member of the InventorySpecialist SharePoint group by wrapping the condition in another if similar to the one I used in the UpdateForm function where I conditionally disabled the Status drop-down. This would be an example of a “conditionally required” field.

Add validation code which runs on click of Save button

function PreSaveItem()

{

//add form validation logic here

$.blockUI({

message: '<H2 class="ms-stylelabel">Validating Form...</H2>',

centerX: true,

centerY: true,

fadeIn: 400,

fadeOut: 600,

timeout: 0,

overlayCSS: {

backgroundColor: '#000000',

opacity: 0.10

}

});

var returnCode = true;

var firstError = true;

errorMessage = "";

//validate input; set focus to first field in error

if ($("select[title='Location Lookup'] option:selected").text() == ""){

returnCode = false;

//set focus to the field if it is the first error

if (firstError) {

$("select[title='Location Lookup']").focus();

}

firstError = false;

buildErrorMessage("- A valid Location must be selected. ");

}

if (!returnCode) {

alert(errorMessage);

//add form validation logic here

$.unblockUI();

}

return returnCode;

}

Function to build a formatted error message with each error on a separate line

function buildErrorMessage(msg) {

if (errorMessage == "") {

errorMessage = msg;

}

else {

errorMessage += "\n" + msg;

}

}

Here’s a more full listing of the code as you would see it if you opened up myApplication.js in SharePoint Designer.

//Start script after page is loaded and after the sp.js file is loaded to ensure all form elements exist and the //appropriate SharePoint libraries are loaded

$( document ).ready(function() {

ExecuteOrDelayUntilScriptLoaded(InitContext, "sp.js");

});

//Populate local user object from the server

function InitContext() {

$.blockUI();

try {

// current user object

this.cUser = new Object();

this.cUser.groups = new Array();

this.cUser.id = "";

this.cUser.loginName = "";

var ctx = new SP.ClientContext.get_current();

currentWeb = ctx.get_web();

usrLoad = this.currentWeb.get_currentUser();

usrLoad.retrieve();

ctx.load(this.currentWeb);

ctx.executeQueryAsync(Function.createDelegate(this, this.initContextSucceeded),

Function.createDelegate(this, this.initContextFailed));

}

finally {

}

}

//Success Delegate

function initContextSucceeded() {

try {

cUser.loginName = this.usrLoad.get_loginName();

cUser.id = this.usrLoad.get_id();

loadUserGroups(cUser.loginName);

UpdateForm();

$.unblockUI();

}

finally {

Sys.Debug.trace('initContextSucceeded End');

}

}

//Failure Delegate

// Failure callback for initContext async query

function initContextFailed(sender, args) {

$.unblockUI();

alert('Retrieval of initial context failed.\nMessage: ' + args.get_message() + '\nStack Trace: ' + args.get_stackTrace())

}

//Code to load SharePoint Groups</strong>

// Retrieve the group membership for the current user

function loadUserGroups(loginName) {

$().SPServices({

operation: "GetGroupCollectionFromUser",

userLoginName: loginName,

async: false,

completefunc: function (xData, Status) {

$(xData.responseXML).find("Group").each(function () {

cUser.groups.push($(this).attr('Name'));

});

}

})

}

//Update Form – Add code here to disable any fields based on user’s membership (or lack of membership) in a //particular SharePoint group

function UpdateForm() {

//Disable Status field if user is not in the Inventory Specialist SharePoint group

if ($.inArray('InventorySpecialist',cUser.groups) == "-1" {

$("select[title=Status']").attr("disabled",true);

}

//bind an event handler to the change event of the State drop-down

$("select[title='State']").change(StateChanged);

//trigger the change event

$("select[title='State']").change();

}

//Function to change the County values available in the County drop-down based on the selected State

function StateChanged() {

//get value of state drop-down

var state =$("select[title='State']").val();

//get a reference to the County drop-down to load new counties below

var countySelection = $("select[title=’County’]");

switch (state) {

case '(None)':

break;

 

case 'Alabama':

counties = [‘Baldwin’,’Barbour’,’Bibb’];;

break;

case 'Georgia':

counties = [‘Cobb’,’Forsyth’,’Fulton’];

break;

default:

break;

}

//remove options from County drop-down

$("select[title=‘County’]")

.find('option')

.remove()

;

foreach (county in counties) {

countySelection.append('<option value="' + county + '">' + county + '</option>');

}

}

//Add validation code which runs on click of Save button

function PreSaveItem()

{

//add form validation logic here

$.blockUI({

message: '<H2 class="ms-stylelabel">Validating Form...</H2>',

centerX: true,

centerY: true,

fadeIn: 400,

fadeOut: 600,

timeout: 0,

overlayCSS: {

backgroundColor: '#000000',

opacity: 0.10

}

});

var returnCode = true;

var firstError = true;

errorMessage = "";

//validate input; set focus to first field in error

if ($("select[title='Location Lookup'] option:selected").text() == ""){

returnCode = false;

if (firstError) {

$("select[title='Location Lookup']").focus();

}

firstError = false;

buildErrorMessage("- A valid Location must be selected. ");

}

if (!returnCode) {

alert(errorMessage);

//add form validation logic here

$.unblockUI();

}

return returnCode;

}

//Function to build a formatted error message with each error on a separate line

function buildErrorMessage(msg) {

if (errorMessage == "") {

errorMessage = msg;

}

else {

errorMessage += "\n" + msg;

}

}

If you have questions please leave a comment below…

read more
Tim CoalsonSharePoint List Forms with jQuery
get-analytical.jpg

Basic Analytics with SharePoint Designer Workflow and Access Reports

Grant Lewis is an Software Engineer with ThreeWill. Grant’s consulting experience consists of using Microsoft technologies to enhance business operations in a manufacturing environment.

Introduction

Analytics are a valuable resource to evaluate business processes to diagnose problems and implement efficiencies. There are many professional offerings that will provide analytics, however these tools can have high costs, both for licensing and developmental effort. As an alternative to this, you can create basic analytics with SharePoint data using only SharePoint Designer and Microsoft Office.

This is a basic example of simple analytics requiring only out of the box (OOB) SharePoint workflows and Microsoft Access. In this example, I will be interested in capturing and analyzing the timing of items in a SharePoint list as they move through certain statuses; however, this process could be adopted to any number of uses.

This guide is divided into two steps. In the first step, I’ll show how to create a SharePoint Designer workflow to capture and document status change events in a SharePoint list. In the second step, I’ll show how to use Microsoft Access to create some basic reporting on the status events captured by the workflow we created in step one.

Step One: Create a SharePoint workflow to log events

Firstly, I’ll assume that I have a SharePoint list that has a status field. In this example, the status field will have the following states: Cancelled, Implemented, In Progress, On Hold, Pending Update, Received, Rejected, Sent to Approver A, and Sent to Approver B.

Next, we will need a place to store the status change events that we will be capturing. A SharePoint list is a logical place to store these events. Create an Events list to store the following fields:

  • ChangeDate – the date and time when the change occurred
  • ChangedBy – who the change was performed by
  • StatusBefore –the status was before the change
  • StatusAfter –the status following the change
  • SourceID – the list item ID for the item that changed

BASD-1

Because this basic example will be using SharePoint workflows instead of List Event Receivers, we will not have access to the previous state of the status field. Therefore, we need to store the previous status value in the source list. To do this, I added a Previous Status field in the source list to store the previous value of the status field.

Note: Because this field should be used only by a SharePoint workflow, it would be a good idea to hide this from view of the user. My preferred method to accomplish this is to set the ShowInNewForm, ShowInEditForm, and ShowInViewForm properties of the SPField object (http://msdn.microsoft.com/en-us/library/office/microsoft.sharepoint.spfield_members(v=office.15).aspx)to False. These properties are not exposed in the UI so you will need to use PowerShell or SharePoint Manager 2013 (http://spm.codeplex.com/).

The next step is to create the workflow to log the status change events in the Events list. Open SharePoint Designer and create a new List Workflow for the list with the Status information.

For the first step of the workflow, we want to add a condition so that an event is only added to the Events list if the status has changed. Add an “If any value equals any value” condition.

BASD-2

Set the first value equal to the Status field, set the comparison to “not equals”, and set the second value to the Previous Status field.

BASD-3

Next, we need to add an item to the Events list to capture the status change event. Insert a “Create List Item” action beneath the condition.

BASD-4

For “this list,”select the Events list and add the fields that you want to capture. For this example, we will capture the following fields:

  • Modified -> ChangeDate
  • Modified By -> ChangedBy
  • ID -> SourceID
  • Previous Status -> StatusBefore
  • Status -> Current Status

BASD-5

Note: You could also create a link to the item that created the event by storing the Workflow Context: Current Item URL field. This may be useful to have a way to quickly get back to the item in question to get additional information.

The Last Step of the workflow is to store the current value of the Status field in the Previous Status field to signify that we have captured this status change. Add an “Update List Item” action.

BASD-6

Click “this list” and add the Previous Status field and set value to Current Item:Status.

BASD-7

 

The end result of the workflow should look like the below.

BASD-8

Step Two: Create Reports in Microsoft Access

The next step is to use Microsoft Access to connect to your Events list in SharePoint. In Access, select the External Data tab, select the More drop-down, and click SharePoint List.

BASD-9

Enter the URL of the site that houses the Events list and follow the steps of the wizard to connect to the list. If you would like the data to stay current, make sure you select “Link to the data source by creating a linked table” option.

BASD-10

Note: Alternatively, you can use the “Open with Access” option in the SharePoint Ribbon to create a linked table in Access.

BASD-11

Next, we need to create some queries in Access. The first finds the starting and ending time during which a particular item was in a certain status. I named this query StatusStartEndTime.

SQL:

SELECT Events.SourceID, Events.StatusBefore, Events.StatusAfter, Events.ChangeDate,

(

SELECT max(e.changedate)

FROM Events e

WHERE e.StatusAfter = Events.StatusBefore and e.SourceList = Events.SourceList and e.SourceID = Events.SourceID and e.ChangeDate < Events.ChangeDate

) AS PreviousChangeDate

FROM Events

WHERE Events.StatusBefore <> null;

The next query that I create uses the results of the above query to calculate the elapsed time (in minutes) that each item was in a certain status. I call this query StatusMinutes.

SQL:

SELECT StatusStartEndTime.SourceList, StatusStartEndTime.SourceID, StatusStartEndTime.StatusBefore AS Status, sum(DateDiff(&amp;quot;n&amp;quot;, StatusStartEndTime.PreviousChangeDate, StatusStartEndTime.ChangeDate)) AS ElapsedMinutes

FROM StatusStartEndTime

GROUP BY StatusStartEndTime.SourceList, StatusStartEndTime.SourceID, StatusStartEndTime.StatusBefore;

This last query is the building block that will allow us to drill down into some interesting information.

For example, let’s say we would like to know the average, minimum, and maximum time spent in each status. We could create the following SQL query:

SELECT DISTINCT (status), round(avg(ElapsedMinutes)) AS AverageTime, round(max(ElapsedMinutes)) AS MaxTime, round(min(ElapsedMinutes)) AS MinTime

FROM StatusMinutes

GROUP BY status;

BASD-12

We could of course use Access reports to display the data in a more aesthetically appealing manner.

BASD-13

From this information, we can extract information like which states take the longest to complete or which states have the most variability.

This is only the beginning of the analysis that you can perform. There are numerous other cases that could be analyzed with this data. Another useful case would be to find items that stayed in a certain state longer than a defined amount of time. With that information the cases could be investigated individually to determine why those particular items took longer than normal and whether any changes can be made to speed up the process.

Conclusion

While business intelligence and other analytics tools have their place, this example demonstrates that you can do some very basic analysis and reporting using OOB SharePoint and Microsoft Office tools.

read more
Grant LewisBasic Analytics with SharePoint Designer Workflow and Access Reports
clock-alert.jpg

SharePoint Is Half Full – SharePoint Alerts Primer

Tim is a Senior Consultant at ThreeWill. He has 15 years of consulting experience designing and developing browser-based solutions using Microsoft technologies. Experience over the last 8 years has focused on the design and implementation of SharePoint Intranets, Extranets and Public Sites.

In the first post of this series, Do You See SharePoint As Half Full Or Half Empty?, I suggested that most Users of SharePoint do not understand the full potential of the platform due to a lack of training.  Several areas that I mentioned being underutilized included:

  • Custom Lists
  • Notifications and Workflow
  • Security

In the second blog, I focused on some of the powerful benefits that could be experienced by creating and using Custom Lists in SharePoint to manage list data.  We walked through the process of creating a custom list to capture New Employee On-boarding activities and demonstrated the powerful sorting and filtering capabilities provided by SharePoint to make this data more useful.  In this third blog of the series, I’d like to focus on the power of List Alert Notifications to alert people when relevant data is added or updated.

SharePoint Alerts Primer

First, let’s discuss some of the most basic Notifications to which a user can subscribe in SharePoint.  On any list or document library in SharePoint, users can create alerts using the standard SharePoint user interface.  For each alert that a user configures, they can specify to receive either an email or text message (SMS).  They can choose to be alerted for all changes in the list or document library, when new items are added, when existing items are modified or when items are deleted.  The alert can be targeted even further, if desired, to only send alerts when someone else makes the change, or someone else changes an item created by me, or someone else changes an item last modified by me.  And finally, the notification can be configured to be sent immediately, sent as a daily summary or even sent as a weekly summary depending upon how timely you need to know of the changes.  Here’s an example of the simple Alert configuration screen:

OnboardingSimpleAlertConfiguration2

Sometimes the simple alert configuration is not enough and a more targeted notification is desired or required, Alerts coupled with custom list views provide the ability to configure alerts at a very granular level.  In the previous blog, I mentioned the ability to create a custom view on the Employee On-boarding list called “My Employees” where only list items were displayed where I was listed as the Employee Manager.  So, maybe I only want to be alerted when Employees I manage are updated.  The great news is that SharePoint supports alerts to items based on Custom List Views.

Since I cover the creation of Custom List Views in my previous blog and since I feel these are very important, let me first demonstrate how simple it is to create a custom list view and then I’ll demonstrate how to reference the custom list view when configuring an alert.

First, let’s navigate to the Employee On-boarding list and select the Create View option:

CreateCustomView

Now we can select the fields that we want to display as part of our custom view.  To keep my view from being too cluttered, I’ll only pick the columns that I feel are most relevant.

CustomViewFieldSelection

Next, I’ll configure how I want the items to sort when they are displayed.  In my case, I want to sort them by last name in ascending order.  Alternatively, I could have sorted them by hire date descending so that I see employees hired most recently first.  I could also apply a secondary sort if desired.

customViewsort

Next, I’ll apply the Filter criteria to narrow down the items in the list to only those Employees that I Manage.  Since I’m creating this as a public view that others can share, I’m going to use the special value of [Me] which will also resolve to the value of the current user.  I could have also hard-coded my name, but then it wouldn’t be useful to other Managers.  Since I used the more generic criteria [Me], other Managers can use this same view instead of creating separate views.

CustomViewFilter

In addition to selecting the fields to display, sort order and filter criteria, you can also configure grouping, number of items to display per page as well as information on how the custom view should display from a mobile device.  Since these are not relevant to filtering items for my Alert, I just leave the default values.

CustomViewOtherOptions

Having configured a custom view, I can now display the Employee On-boarding list items using my “Employees I Manage” view to test out the results.  To demonstrate that the view is working, I have added the Manager field back to the view so it can be compared to the “current SharePoint user” that is displayed in the top right corner and is boxed in red.  In this simple example, there is only one employee in the list that I Manage.

CustomViewApplied

Now that I have created a Custom View called EmployeesIManage to limit the fields in the list to only the Employees that I manage, I can apply an alert to the custom view so that I am notified by email any time that one of the employees I manage is updated in the Employee On-boarding list.  Note in the screen shot below, there is now a radio button option to select “Someone changes an item that appears in the following view:”.  Now I can select my new custom view from the drop-down list so the alert I am creating only applies to items that meet the criteria defined in my EmployeesIManage custom view.

OnboardingCustomViewAlertConfiguration

Having narrowed down the list of items to which I want to be notified when they are updated, I can configure whether I want an immediate notification when updated or whether a daily or even weekly summary will suffice.

Hopefully you can begin to see how some of these simple yet powerful features of SharePoint can be used to keep the appropriate people up to date when relevant events or relevant data is updated that is in their area of concern.  In the next post, we will discuss more advanced notifications and approvals that can be configured to provide an even more robust solution that not only “notifies” when items are updated but also engages other relevant users into the Employee On-boarding business process at the appropriate time..

read more
Tim CoalsonSharePoint Is Half Full – SharePoint Alerts Primer
lists.jpg

SharePoint Is Half Full – Learn About SharePoint Lists

Tim is a Senior Consultant at ThreeWill. He has 15 years of consulting experience designing and developing browser-based solutions using Microsoft technologies. Experience over the last 8 years has focused on the design and implementation of SharePoint Intranets, Extranets and Public Sites.

In the first blog of this series, Do You See SharePoint As Half Full Or Half Empty?, I suggested that most SharePoint users do not understand the full potential of the platform due to a lack of training.

Several areas that I mentioned being underutilized included:

  • Custom Lists
  • Notifications and Workflow
  • Security

In this post, I’d like to focus in on what I feel are some of the powerful features and benefits that can be experienced by creating and using Custom Lists in SharePoint.

What is a Custom List? A custom list is one to many columns of associated data. Each row in the list might represent a unique employee and each column in the row would capture different information about the employee including their name and other relevant data.

Think Excel Spreadsheet, but a web-based spreadsheet that is accessible by the whole organization.

For example, what if you needed to ensure that every new employee completed the appropriate paperwork (i.e. employee contract, benefits) and received the appropriate resources (i.e. desk, computer) to be able to function in their new job. To accomplish this task, you could create a custom list that consists of the following columns:

  • ID, number
  • Last Name, text
  • First Name, text
  • Middle Name, text
  • Hire Date, date
  • Signed Employee Contract Date, date
  • Completed Benefit Enrollment Package Date, date
  • Completed New Employee Orientation Date, date
  • Received Desk Assignment, yes/no
  • Received Computer, yes/no
  • Type of Computer, choice (desktop, laptop, tablet)
  • Reporting Manager, people picker

I’m sure you can think of other pieces of data that would be helpful or beneficial, but for our purpose here, this should suffice. In this simple example, we defined fields to store a number, text, date and yes/no. In addition, we included a choice field to capture the type of computer the new employee would need. You can imagine if this was a plain text field that you would get a number of different descriptions based on the person entering the date. Choice fields (where the choices that show up in the drop-down are defined as part of the column definition) and Lookup Fields (where the options in the drop-down list come from a column in another list) are great ways to provide a finite number of input options and to ensure consistency among people entering the data. Lastly, we included a People Picker column to capture the Manager of the new employee. People Picker columns provide a list of employees. This list can come from a custom SharePoint group or even from the Corporate Active Directory. The main takeaway is that People Picker columns allow the opportunity to specify a Person with the option to display different types of information about the person including their name, email and other information that is available.

Having created a custom list to store my Employee On-boarding data, I can begin to reap the benefits.

First of all, I get a data input screen that enforces fields that I configured as required when adding the columns. The screen shot below demonstrates a typical input/edit screen that you get for free with SharePoint. Required fields are denoted by a red asterisk.

Onboardinginput

Secondly, I can surface this data easily in the SharePoint site for others to see if they have the right permissions. Below is a typical view of SharePoint list data surfaced on the home page of a sample site.

OnboardingAll

I can also begin to filter down the data to meaningful view. For example, if I need to see what employees have laptops, I can filter the list using the “Type of Computer” column and specify laptop as the filter criteria. I can also add several filters at once to see what employees that report to Manager Bruce Harple have laptops or what Employees that report to Manager Bruce Harple have not completed their Orientation. The screen shot below demonstrates the view of data above but with filters applied to only show employees who report to Bruce Harple that have a Laptop Computer. Notice the funnel icons next to the “Type of Computer” and “Manager” column headings indicating that they are being filtered. Also note the “down arrow” icon next to the Last Name column indicating that the data is being sorted by the “Last Name” column.

OnboardingLaptopHarple

In addition to sorting and filtering, custom list views can be easily configured to only display relevant list items and relevant columns of data. For example, I could create a “My Employee” view that only shows employees where I am listed as the Manager. And maybe I was only concerned with half of the columns in the list and not all of them. I could easily configure this as a custom view using the SharePoint user interface. If the list contained hundreds or thousands of rows of employees, the ability to quickly see “My Employees” by selecting the “My Employee” custom view would come in very handy. On any given list or document library, Private views or Public views can be created. Private views are only available to the user who created the view while Public views are available for all users who have access to the list.

Of course, most of us will not need an Employee On-boarding list to do our jobs. However, most of us participate in some business process that needs to track shared data among a group of individuals either within the same department or across departments.

Office Supply Inventory, Vacation Requests, Help Desk Requests, Purchase Approvals…the list is seemingly infinite of things that need to be tracked. Instead of creating this information in an Excel Spreadsheet on your local machine or on a file share, why not consider creating this as a custom list where data entry and tracking can be shared among the various people who need access to the information?

In the next post in this series, we will discuss how notifications and workflows can be added to this custom list so that the right people can be notified when new employees are hired and they can be brought into the on-boarding process at the right time.

If you have already started using custom lists, please share your experiences below in the comments section. If not, dive in and start enjoying the benefits.

Here’s a good resource to help you get started: SharePoint lists IV: Create a custom list – SharePoint Server – Office.com

read more
Tim CoalsonSharePoint Is Half Full – Learn About SharePoint Lists
half-full.jpg

Do You See SharePoint As Half Full Or Half Empty?

Tim is a Senior Consultant at ThreeWill. He has 15 years of consulting experience designing and developing browser-based solutions using Microsoft technologies. Experience over the last 8 years has focused on the design and implementation of SharePoint Intranets, Extranets and Public Sites.

As a SharePoint Consultant who has consulted at many different companies small and large, I have often experienced negative initial reactions when mentioning SharePoint.

The reasons have ranged from things like “SharePoint is slow” to other things like the “SharePoint navigation is confusing”. And I have to admit, there have been times I have used SharePoint sites that were both slow and confusing. The good news is that both of these can be improved so that they do not need to become a roadblock to using an otherwise useful product. But beyond merely removing these barriers, the good news is that there are many features that SharePoint provides that many users have never come to understand and appreciate and my goal is to help raise awareness of these features.

What I have learned over time is that most users’ concept of SharePoint is that it is simply a document repository…

They see it as a ”a place my boss forces me to put my files that takes more time and effort than storing them locally or putting them on a file share”. SharePoint is generally a product that has been introduced to the organization by the IT department for work teams to collaborate and usually with little or no training on SharePoint provided. And while storing documents is certainly a valid use of SharePoint, there is so much more capability than most users realize or have been given the rights to leverage in their work group or department.

On occasion, I have the opportunity to sit down with motivated employees who like to learn and I describe to them some of the features within SharePoint that I think will interest them.

Most users are surprised to hear about these capabilities and are eager to figure out how they can begin to use them.

My goal in this blog series is to share a few of what I consider to be the most underutilized features of SharePoint that can be leveraged for both personal productivity and business process improvements.

These underutilized features include:

  • Custom Lists
  • Notifications and Workflow
  • Security

After discussing these features individually, I’ll conclude with a discussion of how these features can be combined together to create Business Applications that can help support and automate some of your current business processes.

And before you assume that you need a developer or technical person to take advantage of these features, know that all of these are available to end-users of SharePoint and are configurable through the SharePoint UI or through SharePoint Designer.

Stay tuned. I look forward to sharing more about these underutilized features with you and hearing from you about any questions or comments on these topics.

By the way…

always-full

We thought you would enjoy this take on how different people see the half full/empty glass (source)…

The optimist says the glass is half full.

The pessimist says the glass is half empty.

The project manager says the glass is twice as big as it needs to be.

The professional trainer does not care if the glass is half full or half empty, he just knows that starting the discussion will give him ten minutes to figure out why his powerpoint presentation is not working (@jbutweets – thought you would enjoy this one!)

The consultant says let’s examine the question, prepare a strategy for an answer, and all for a daily rate of…

The engineer says the glass is over-designed for the quantity of water.

The computer programmer says the glass is full-empty.

read more
Tim CoalsonDo You See SharePoint As Half Full Or Half Empty?
tricky-e1425506790935.jpg

Populating a SharePoint List to PDF

Lane is a Senior Software Engineer for ThreeWill. He is a strong technology expert with a focus on programming, network and hardware design, and requirements and capacity planning. He has an exceptional combination of technical and communication skills.

On a recent project we were in a situation where our customer was using Adobe Acrobat as a form authoring tool and we were using data stored in several SharePoint lists and document libraries to generate a PDF from the template they provided us.

The customer wanted to use PDF for the output file because they have a person at each store who would download the latest PDF and print it on a standard printer loaded with pre-perforated paper. We did this using a small Windows application that we built that used the SharePoint Client API for the SharePoint calls and iTextSharp for the PDF generation. If you haven’t used iTextSharp before (or iText, which iTextSharp is just a .Net clone of and I find myself using both names interchangeably) it is very well documented and is surprisingly easy to work with. However, it does have some limitations that required some pretty imaginative solutions to work around. I plan on documenting some of those limitations and how we got around them in this blog post.

First though, a bit of an overview of the application in question.

To protect the names of the innocent I won’t mention what the tool is actually used for or by whom so let’s go with an analogy and say the customer is a restaurant chain and the PDF is their menu. Management has a complex approval and review process for each meal that is on the menu that requires review from many groups across several divisions within the company. Since the data that is being reviewed and approved is the information about the menu item, not the design or appearance of the menu itself, SharePoint made a natural choice for this. Create a custom list with fields for details about the menu item (name, ingredients, calories, price, etc.), slap a SharePoint Designer workflow on the list with all the parties set to approve it, and voila! Now there is one source for the gospel, we have accountability, no more worrying about who has the latest version of the document, and graphic designers can focus on designing a sharp looking menu form and not how many calories are in this week’s tiramisu.

Now came the tricky part.

We have the data in SharePoint but we want it in a PDF using their formatting and layout. We did not want to get in the practice of specifying the layout of the PDF programmatically. We just needed to populate some field variables with our data from SharePoint and copy the PDF to a file on the local disk. This way the form designers can work on tweaking the PDF template separately and then just upload a new PDF template to SharePoint when its ready. The application will pull down a fresh copy of the template each time so changes to the menu layout take place instantly (or could even go through its own review and approval process if needed). If you have ever worked with PDF from a programmatic standpoint though you know that adding text to a PDF document is not straight forward and doing it in the manner we wanted is even less straight forward. Add iText and it’s AcroFields.SetField method to the mix though and it becomes easy as pie (sorry, couldn’t resist).

I am not going to go into the details of how to create a PdfStamper. There are plenty of examples in the iTextSharp documentation of how to do it and its pretty easy. But the gist is instead of the form designer typing in the text for the tiramisu or its price, they would add PDF form fields to their template and name them in a manner we could retrieve through the iText API. Then we set the form field’s value with our string, use iText to flatten the field, and then move on to the next field.

Fair warning, I am pilfering code from several classes and methods and I am not testing. It should be enough to get you close but I do not guarantee that copy and paste will work.

string fieldName = &quot;Price&quot;;//This is the name of the field in the PDF and the name of our field in SharePoint

var form = _pdfResultStamper.AcroFields;
 var fieldKeys = form.Fields.Keys;

string fieldName = (field.TemplateVariableName == null) ? field.Title : field.TemplateVariableName;
 string fieldValue = string.Empty;
 if (fieldKeys.Contains(fieldName))
 {
 form.SetField(fieldName, fieldValue);
 }
 _pdfResultStamper.PartialFormFlattening(fieldName); 

So that’s pretty much it. Throw that into a loop that iterates over each field on the SP ListItem. This will generate a PDF in memory using the template from the designers with the values from the SharePoint list. The next step is to take that memory stream and throw into an array of streams for latter merging. Each memory stream represents one page in the PDF. The menu analogy sort of falls apart here because you wouldn’t really need to do this but for the sake of the analogy imagine that each page in the PDF represents one SharePoint ListItem.

List pdfStreams = new List();
 pdfStreams.Add(new MemoryStream(_pdfResultStream.ToArray()); 

Once you have an array of memory streams that represent your PDF pages, we need to flatten the fields in the PDF so they are no longer editable. For those who are not familiar with flattening in PDF, it is basically what it sounds like. PDF draws form fields as a series of rectangles on a X, Y, Z coordinate. When you flatten it, all those rectangles get squished down to just the text contents (I can hear a few PDF purists screaming at their monitor already, but for the lay person its a close enough explanation…). Its sort of like marking a field read only, but a bit more involved.

 AcroFields formFields = _pdfResultStamper.AcroFields;

//Loop through all the form fields in the PDF template
 foreach (var field in formFields.Fields)
 {
 //This is the field name
 string fieldName = field.Key;
 AcroFields.Item i = field.Value;
 PdfDictionary dict = i.GetMerged(0);

//Determine if the field is hidden in the PDF template. If it is we do not need to flatten it.
 PdfNumber flags = dict.GetAsNumber(PdfName.F);
 if ((flags.IntValue &amp; PdfAnnotation.FLAGS_HIDDEN) != 0)
 {
 continue;
 }
 _pdfResultStamper.PartialFormFlattening(pcfItem.TemplateVariableName);
 } 

Observant readers might notice that we are using PartialFormFlattening. This is because we had the requirement that in some circumstances some fields were to be left editable by the end user. Using the menu analogy if the restaurants in New York could charge different prices for tiramisu than the other restaurants this could be desirable. PartialFormFlattening only flattens the field with the name we pass in.

A quick note about how PDF addresses form fields as its going to be important in the next code block. It is possible to have two fields on a form with the name ‘Price’. Acrobat is perfectly fine with this and in our situation it actually came in really handy. Its a little strange at first if you have been coding for a long time to have multiple fields with the same name. One strange effect of this is when you interact with the form fields like the above examples iText will update ALL of your Price form fields with that name. Again, a situation where the menu analogy sort of falls apart. There are ways to get around this but we didn’t have a need to so I do not have example code showing how.

Now that our PDF is pretty much like we want it in the memory stream we need to write it out to disk. Again, iText makes this easy.

 FileStream outStream = new FileStream(filePath, FileMode.Create);//filePath is a variable passed in that stores a local directory path e.g. C:\Temp\MyFile.pdf
 PdfReader reader = new PdfReader(pdfStreams[0]);
 int pageCounter = 0;
 RenameFields(reader, pageCounter++);
 PdfCopyFields Writer = new PdfCopyFields(outStream);
 AddPages(reader.NumberOfPages, reader, ref Writer);
 foreach (var PdfStream in pdfStreams.Skip(1))
 {
 PdfReader reader2 = new PdfReader(PdfStream);
 //rename PDF fields
 RenameFields(reader2, pageCounter++);
 // Add content
 AddPages(reader2.NumberOfPages, reader2, ref Writer);
 }
 Writer.Close();
 foreach (var Strm in pdfStreams)
 {
 try { if (null != Strm) Strm.Dispose(); }
 catch { }
 }
 outStream.Close(); 

Well, sort of easy. If you pay close attention to the code above you notice we are calling RenameFields on each memory stream. This is because Acrobat does not like it when two PDF pages in a document have form fields with the same name. Our workaround was just to tack on the page number to the end of the fields. For the sake of clarity this is all RenameFields does.

 foreach (string field in reader.AcroFields.Fields.Keys)
 {
 reader.AcroFields.RenameField(field, field + pageCounter.ToString());
 } 

So we should now have a PDF file written to our disk with the data passed in from SharePoint. iText makes this all really easy.

But…

We found later on that iText does not support super scripting text in a form field. Acrobat does, but iText currently does not support it. And we are dealing with currency in our menus (after all, we want people to pay for the food, right?). After much lamentation and many, many internet searches, I discovered that Acrobat has a JavaScript engine that is ECMA compliant. Acrobat actually creates a DOM for its internal content. Even better, you can set rich text form fields with an array of JavaScript objects and specify for some of those objects to have their text contents super scripted. OK, so how do you A) get JavaScript into a PDF and B) how do you create the JavaScript objects?

A) is pretty simple in concept. We have a JavaScript file sitting in SharePoint that we download in our Windows application and read the text contents into a string we store globally. We have a few of the string.Format {0}-style variables where we eventually add a list of PDF form fields that need super scripted text formatted as a JavaScript array. We then use iText to inject the JavaScript into the document write before we write it to disk. When the PDF is opened we first check for a hidden check box field in the document, look if its checked (indicating the first time its been opened), run through our array of fields, add the super script, then check the check box so we don’t run through it again next time its opened. In the above example where we are writing the PDF file to disk, add this code in at line 15.

 Writer.AddJavaScript(PostProcessing.GetSuperscriptJavaScript()); 

Now the PostProcessing class is fairly complex in how its built and utilized, arguably its over engineered for what it does and explaining it would make what is already a long blog post even longer. Suffice it to say it has an array of strings that represent each PDF form field that needs to have its text super scripted. When we call GetSuperscriptJavaScript() we get back a string of JavaScript that is the contents of our file in SharePoint with our JavaScript array (which is a concatenation of our array of strings/field names). Nothing revolutionary. But the really neat part is the JavaScript that is created. Below is that code, and that should address B).

var fields = new Array(&quot;Price0&quot;, &quot;Price1&quot;);
 var readOnlyFields = new Array(&quot;Price1&quot;);

var c = getField(&quot;RunJavaScript0&quot;);
 if(c.isBoxChecked(0)){
 for(var z=0; z &lt; fields.length; z++){
 var f = getField(fields[z]);
 var currTextObj = f.richValue;
 var spans = new Array();
 var spanIndex = 0;

for(var i=0; i &lt; currTextObj.length; i++){
 var origFontSize = currTextObj[i].textSize;
 var currText = currTextObj[i].text;
 var currTextArr = currText.split(&quot;(s)&quot;);

for(var x=0; x &lt; currTextArr.length; x++){
 var t = new Object();
 var fontSizePatt = /^\([0-9]+\)/;
 var text = currTextArr[x];

if((x % 2) == 1){//Only super script odd indexes in the array
 if(fontSizePatt.test(text)){//Check if our superscript uses a custom font size
 var sizeR = fontSizePatt.exec(text).toString();
 console.println(&quot;Parsed custom font size of '&quot; + sizeR + &quot;'&quot;);
 var size = sizeR.substring(1, sizeR.length - 1);
 var sizeI = parseInt(size, 10);
 var newSize = origFontSize * (sizeI / 100);
 console.println(&quot;Setting font size to &quot; + newSize);
 t.textSize = newSize;
 console.println(t.textSize);
 text = text.substring(text.indexOf(&quot;)&quot;) + 1);
 }
 t.superscript = true;
 }

t.text = text;

spans[spanIndex] = t;
 spanIndex++;
 }
 }

f.richValue = spans;
 for(var j=0; j &lt; readOnlyFields.length; j++){
 if(readOnlyFields[j] == f.name){
 f.readonly = true;
 break;
 }
 }
 c.checkThisBox(0, false);
 }
 } 

Ok, so some explanation. Remember earlier I said that in some situations our code is leaving form fields editable. In this code we are marking the field as read only instead of flattening the page because Adobe JavaScript does not have a mechanism to do partial form flattening. It does it at the document level only. Also remember that we are using a hidden check box field in the PDF to determine if the JavaScript should run or not. That should explain the first couple lines. One other key bit of detail is how we flag a string to need super scripting in SharePoint. We wanted the menu writers to have fine grain control over the super scripting. We could have just looked if the field was a price value, trimmed off the last two digits and been done with it. But what if our chefs invent tiramisu2 (imagine that 2 is super scripted)? To give the menu writers that type of control we made a markup syntax where any text within a (s) would be superscripted. Also, we noticed that some fonts do not look right when super scripted, they were too big and looked…off. So our menu writers can also specify a different font size for the super script by adding (xx) after the (s) (where xx is a percentage of the parent containers font). For example, “Our tiramisu(s)(85)2 is the best” would make a string that has a 2 super scripted with a font that is 85% smaller than “tiramisu”.

At the end of the scripts run it produces something along these lines.

 var f = getField(&quot;Price0&quot;);
 var spans = new Array();
 spans[0] = new Object();
 spans[0].text = &quot;Our tiramisu&quot;;
 spans[1] = new Object();
 spans[1].text = &quot;2&quot;;
 spans[1].superscript = true;
 spans[1].textSize = 17;
 spans[2] = new Object();
 spans[2].text = &quot; is the best&quot;;
 f.richValue = spans;  

A quick note we found about changing the font like this. Apparently Acrobat calculates the vertical offset for the super scripted text using the font of the current chunk, not the parent container. The net of this is if your font size difference is drastic, say 20 points vs. 5 points, then the super scripted text renders as if the whole form field is 5 point, meaning the text shows up in the middle of the 20 point text. Its a minor concern, but something worth pointing out.

For More Info

Additional information about the Spans object and how they work can be found here: http://partners.adobe.com/public/developer/en/acrobat/sdk/Acro6JS.pdf (word to the wise, though. That is an older API reference and some things have been deprecated).

Questions about Populating a SharePoint List to PDF?

Leave a comment below…

read more
Lane GoolsbyPopulating a SharePoint List to PDF