Tuesday, July 25, 2017

SharePoint Workflows: Going Against the Flow #1

My latest project has me working with SharePoint workflows. In this case, I have some JavaScript that I wrote that uses PDF.js to see if a PDF document has been signed, how many signatures are present, and which names are associated with those signatures. (My thanks to PKJIS for a working example that gave me a good launching point.) The JavaScript uses the SharePoint API to retrieve the list of people that are supposed to sign the document and the list of people that are supposed to be notified when a signature is affixed to the document before storing this information as metadata for the document.

These modifications to the metadata then fire off a SharePoint workflow. If you've worked with SharePoint workflows, you know that they have some serious shortcomings, especially if you can't use Visual Studio to build workflow functionality in C#.

The JavaScript stores the names of the people who signed the document in a "Multiple Lines of Text" field; each name is on its own line. The metadata for the document includes a field called "Last Count" that is only updated by the SharePoint workflow. When the document is changed, the SharePoint workflow counts the number of lines in the Signatures field and compares that count with "Last Count". If the two values are different, an email is sent out to the person who should sign the document next and the people that are monitoring the signatures.

Here is what the workhorse portion of the workflow looks like:

  1. Set Variable: CountSignatures to 0.
  2. then Set Variable: CountCompleted to No.
  3. then Set Variable: IndexNextSignature to 0.
  4. Loop: through Signatures
    The contents of this loop will run repeatedly while Variable: CountCompleted equals No
    1. Copy from Current Item:Signatures, starting at Variable: IndexNextSignature (Output to Variable: TempSignatures)
    2. If Variable: TempSignatures is not empty
      1. Replace \n in Variable: TempSignatures (Output to Variable: TempSignatures)
      2. Find <br> in Variable: TempSignatures (Output to Variable: FindTag)
      3. then Calculate Variable: CountSignatures plus 1 (Output to Variable: CountIncremented)
      4. then Set Variable: CountSignatures to Variable: CountIncremented
      5. If Variable: FindTag is greater than or equal to 0
        1. Set Variable: LengthNextSignature to Variable: FindTag
        2. then Calculate Variable: LengthNextSignature plus 4 (Output to Variable: LengthIncremented)
        Else
        1. Set Variable: CountCompleted to Yes
      Else
      1. Set Variable: CountCompleted to Yes
    3. If Variable: CountCompleted equals No
      1. Calculate Variable: IndexNextSignature plus Variable: LengthIncremented (Output to Variable: IndexIncremented)
      2. then Set Variable: IndexNextSignature to Variable: IndexIncremented

I can do all of this in one line of JavaScript code:

var countSignatures = signatures.replace(/\n+/g, "<br>").split("<br>").length;

What's more, I don't have to worry about whether the signatures variable is empty or not. SharePoint throws an exception if you try to use the "Find Substring in String" action and either string is empty. If Substring is empty, you get a "Divide-by-Zero" error message; if String is empty, you get a "Value cannot be null" message.

Thursday, November 17, 2016

Mobile App Development

What's been happening?

For the last couple of months, I have continued looking for work. Meanwhile, I have been expanding my skill set and adding my new skills to my resume. I've also taken on a few contracts as a freelance web developer. I just released my first project for Bliss Carpet Cleaning. This project uses PHP, a language I'd only dabbled in before. I've got another PHP project in the pipelines as well as a couple of projects built on Joomla.

What's next?

I've been playing in my mind with an idea for a mobile app and my wife suggested another one a few days ago. I haven't done mobile app development before, but I've been intrigued by it for quite a while. I've got Cordova and Android Studio set up on my computer and I'm going to figure out what it takes to make a mobile app run. There's going to be a lot of reading in my immediate future and I hope to share some of what I learn here.

Why Cordova?

I chose to go with Cordova because I felt that it would be the best way for me to get a mobile solution out. As an accomplished web developer, this incorporates an existing toolset. However, I don't want a pure web app because all of my ideas rely on native functionality of the mobile device and I would need a native app or a hybrid app to access that functionality.

Wednesday, August 17, 2016

Adventures in Three.js - Part 1

Introduction

Ever since I completed my undergraduate degree, I've done very little development at home. I've never had a lot of motivation to do personal projects, even though I understand some of the benefits associated with that. While personal growth is possible in a work environment, it is often stunted by deadlines, conventions, and accepted technologies. Nowhere is this more limiting than a government environment.

As a result, much of my personal growth has stemmed from the creation and refinement of pattern libraries. At each job, I would introduce patterns that would automate or simplify various tasks. Developing such patterns encourages growth because you aren't writing code to meet a specific need once, you're writing code to meet a specific need in several places. It requires forethought, asking questions like: "When will this be used?" "How might someone need to change the visual appearance?" "Is there a faster way to do this without sacrificing flexibility?" "Does the flexibility unduly increase the complexity?"

Some of the patterns I've revisited have included:

Automated tabular data display
ColdFusion provides the <cfgrid> tag to quickly dump the contents of a dataset. Unfortunately, it's a very old tag that has received few updates; it doesn't meet accessibility guidelines and doesn't allow much customization. I've often felt the need to automate this, so it is a pattern I've visited several times. The latest iteration made it easy to start with a basic dump and tweak the code until you got what you wanted. It automated pagination, creation of sort links, grouping of data, and calculating and displaying subtotals.
Form validation
This is a pattern I visited in Delphi and in ColdFusion, addressing many of the same requirements. The latest iteration of this made it easy to: ensure that required fields were not empty; ensure that values matched an accepted pattern; ensure that numeric values fell within a certain range; and ensure that values that should be unique were unique.
Database interaction
This was a pattern I attempted before exposure to CFWheels. The Model implementation by CFWheels is certainly a lot more robust, but I've had a certain amount of luck creating this pattern for myself. My own experience attempting this sort of automation has certainly taught me a lot. My latest iteration of this automatically retrieved the metadata for each field in a specified table and used that metadata to validate any data provided to those fields. It prevented the user from assigning values to calculated fields and automatically retrieved the values for calculated fields and auto-increment fields when a record was inserted or updated. It greatly simplified some of the work I was doing maintaining existing code to manipulate tables with more than 200 fields. Did it add overhead? Yes. Did it reduce development time? Significantly! Did it increase readability? Most assuredly it did.

I subscribe to a couple of newsletters that help me keep up with the web development community. You might understand how exciting it is to see how things are progressing. You might also understand how frustrating it can be if you can't use the technology or play with it much. I often couldn't utilize these technologies because they had usability issues, compatibility problems, or accessibility deficiencies. Nonetheless, I won't be able to keep up if I take the chance to play with the new technology.

What to do?

Developers tend to face some of the same problems that other creators face—writer's block—especially when starting something completely new. The more effort it takes to create a simple application that says, "Hello, world!" the harder it can be to get started. I need a project that motivates me and I need a goal. What challenge will keep me moving forward? Is there a community I can serve?

Of course, for me to get started, I need a simple goal to start with and, for me to keep going, I need to have long-term goals to strive for. My initial goal must be significant enough to overcome the initial writer's block and the long-term goals motivating enough to bring me back to the drawing board.

Dungeon Generator

I was talking with a friend about a project he was working on. He has played Dungeons & Dragons for decades and was working on a dungeon generator to learn PHP and become more familiar with AJAX. His generator works a lot like a text adventure: each room is described, but there is no overall map of the dungeon. I had toyed with this idea for a long time, having played text adventures as a kid and drawing out dungeons for my siblings to crawl, so I wanted to try a different take. Many of the dungeon generators I saw online were two-dimensional, either creating a pure two-dimensional maze and building out rooms with it or generating rooms and opening up passages between them. The deficiency, obviously, was the third dimension.

"This is the perfect opportunity for me to play with a JavaScript 3D library." I'd played with Phaser some in the past, but it doesn't do much in three dimensions. I decided to try out Three.js to see if I could get it to work. Within a week, I had a simple page that would ask for the dimensions of the dungeon, generate a pure three-dimensional maze, display that maze with Three.js, and allow you to fly around the outside of it. You can find the code on Github.

As I'm writing this, the current iteration of this code is creating 5x5x5 cubes and connecting them with corridors. The next thing I want to do is clean up the code. I want to create a Space class that encapsulates some of the code specific to a 5x5x5 space, such as displaying the space, checking for existing connections to the space, and checking to see if any nearby spaces are not connected. After I do that, I want to start building rooms out of these spaces. The easy path would dictate creating rectangular prisms, but an underground dungeon built out of a natural cavern wouldn't reflect this building technique, so I've come up with another algorithm that might simulate that better.

My aim is not only to come up with a working dungeon generator (there's a lot more to it than generating rooms and passages), but also to talk about the process. What challenges have I met? How did I overcome them or work around them? (In Dungeons & Dragons, finding a way around the challenge can also count as defeating it.)

Friday, February 26, 2016

ColdFusion Val function limitations

I was recently working with some really small numbers in ColdFusion and found that one of my typical shortcuts was actually causing me problems. Since I would retrieve information from the database and the database would return an empty string when the value was NULL, I would use ColdFusion's Val function to quickly make sure that any blank strings would become zeroes before I did arithmetic with them.

As mentioned above, the problem arose when I started working with really small numbers. As I'm sure most ColdFusion developers know, the language is loosely typed. If you want to display the value of a variable, ColdFusion transparently casts the variable as a string. If you want to perform arithmetic on a variable, ColdFusion transparently casts the variable as a number. If you want to perform date manipulation on a variable, ColdFusion transparently casts the variable as a date.

What I found regarding the Val function was that, when I was working with numbers small enough that they would be displayed in "exponent format" (a significand, followed by E, followed by the exponent), Val would only return the significand. Val was taking the string representation of the number and stopping at "E" because that is a non-numeric character. This isn't terribly unexpected, but has reduced the usefulness of the function for me.

Wednesday, December 16, 2015

Undocumented Google Calendar URL Parameter #2

While looking up information about the sprop parameter for Google Calendars, I found a Japanese page with information about adding events to Google Calendar that included the add parameter. The add parameter allows you to define additional attendees. I've tried this with email addresses. You can specify multiple email addresses by separating them with commas. Here is an example:

add=guest@gmail.com,visitor@gmail.com

I'm still looking for a parameter that allows you to specify notification times.

Undocumented Google Calendar URL Parameter #1

While doing some looking into ColdFusion components and research into RFC 5545, Google Calendars, and Yahoo! Calendars, I did some experimenting to see how much I can specify for a calendar event via URL in Google Calendars. I quickly found the action, text, desc, dates, and location parameters. Some additional looking found the sprop and trp parameters.

I opened up a new calendar event and tried to find ways to specify other portions of the form through URL parameters. I eventually found that I could use the recur parameter and specify a full RDATE or RRULE property and value string in URL-encoded format. For instance, if you wanted to create an event that repeated monthly on the second Sunday, you could add:

recur=RRULE%3AFREQ%3DMONTHLY%3BWKST=SU%3BBYDAY%3D2SU
to the URL. That's a URL-encoded form of the following.
RRULE:FREQ=MONTHLY;WKST=SU;BYDAY=2SU

I didn't find this documentation anywhere else, so I'm posting it here for someone else to find.

I'm still looking for ways to specify reminders and attendees. If you know how to do either of these, let me know.

Tuesday, February 3, 2015

Discoveries in Familiar Languages #1

I love it when I'm working in a familiar programming language and find something new, particularly if that something new gives me more power or simplifies a pattern I've been using for a while. Such a discovery happened today.

Many-to-many relationship maintenance

I am constantly setting up and maintaining many-to-many relationships. I often set up the junction table to prevent duplication of relationships by adding a unique index on the appropriate key columns. Here's an example of such a junction table in MS SQL Server.

 CREATE TABLE [dbo].[ContactsGroups](  
  [keyContactGroup] [int] IDENTITY(1,1) NOT NULL,  
  [keyContact] [int] NOT NULL,  
  [keyGroup] [int] NOT NULL,  
  CONSTRAINT [PK_ContactsGroups] PRIMARY KEY CLUSTERED ([keyContactGroup] ASC) ON [PRIMARY]  
 )  

This is a pretty typical, very simple junction table. When I need to maintain this many-to-many relationship, I've typically taken a two-step process: DELETE and INSERT. Here is an example of those two queries as they would appear in ColdFusion.

 <cfquery datasource="..." result="qry_updateContact_groups">  
 DELETE FROM [ContactsGroups]  
 WHERE ([keyContact] = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />)  
 <cfif ListLen(Form.keysGroups) GT 0>  
  AND ([keyGroup] NOT IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keysGroups#" list="true" />))  
 </cfif>  
 </cfquery>  
 <cfif ListLen(Form.keysGroups) GT 0>  
  <cftry>  
   <cfquery datasource="..." result="qry_updateContact_groups">  
   INSERT INTO [ContactsGroups] ([keyContact], [keyGroup])  
   SELECT <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />, [Groups].[keyGroup]  
   FROM [Groups]  
   LEFT JOIN (  
    SELECT [keyGroup]  
    FROM [ContactsGroups]  
    WHERE ([keyContact] = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />)  
   ) [ContactsGroups]  
    ON ([ContactsGroups].[keyGroup] = [Groups].[keyGroup])  
   WHERE (  
    [Groups].[keyGroup] IN (  
     <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keysGroups#" list="true" />  
    )  
   ) AND (COALESCE([ContactsGroups].[keyGroup], 0) = 0)  
   </cfquery>  
   <cfcatch><!--- An exception is thrown when nothing is inserted. ---></cfcatch>  
  </cftry>  
 </cfif>  

As you can see, this involved performing a LEFT JOIN to exclude items that already exist in the table. Today, while looking up a related issue (see the comments in the <cfcatch> block), I found a much easier way to write the same INSERT query.

  <cftry>  
   <cfquery datasource="#Request.ds.site#" result="qry_updateContact_groups">  
   INSERT INTO [ContactsGroups] ([keyContact], [keyGroup])  
   SELECT <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />, [keyGroup]  
   FROM [Groups]  
   WHERE ([keyGroup] IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keysGroups#" list="true" />))  
   EXCEPT  
   SELECT [keyContact], [keyGroup]  
   FROM [ContactsGroups]  
   WHERE ([keyContact] = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />)  
   </cfquery>  
   <cfcatch><!--- An exception is thrown when nothing is inserted. ---></cfcatch>  
  </cftry>  

Note the use of the EXCEPT keyword. This operates very similarly to the UNION keyword, but performs practically the opposite operation. Instead of creating a result set with the results of the first query followed by the results of the second query, it creates a result set with the results of the first query excluding results matching those of the second query. It performs the same task I was doing before, but it's more concise and (bonus!) more readable.