Online Scrum Tools – Part 3 – Import Backlog into Trello

http://www.littlebluemonkey.com/blog/online-scrum-tools-part-3-upload-existing-product-backlog-into-trello

https://docs.google.com/spreadsheets/d/1gCBnYeK3f6uXBAV0jPYfFFFcZEjjeKoJ8Fg2h1PLG90/edit?pli=1#gid=680968806

In the previous post about my search for online Scrum tools, I extolled the virtues of Trello and how, for me, it's the perfect tool to manage a product backlog.

But what if you already have an existing backlog, and don't want to spend time re-keying the entries? Well luckily, like most things in 2012, there's an App for that.

Before you get too excited, I should mention that "The App" in this case is a Google Spreadsheet, with a Google Apps Script that leverages the Trello API to import the data. It's simple, effective, and in true agile fashion, it does just enough to get the job done without unnecessary bells and whistles.

So how does it work? Well make a copy of the spreadsheet in your Google Docs/Drive folder and get to work setting it up.

Go to the "Control" sheet and follow the instructions to set up API keys and tokens. Use the options in the Trello menu in the spreadsheet to find and record the IDs of the board and list that you want to add the cards too.

Trello Scrum Backlog Upload Control Sheet

One of the steps is to add your entries into the "Backlog" sheet, the columns of which are worth looking at a little more closely:

Upload Status - Set to blank if you want the row to be imported. It is changed by the script as it processes each row.
Title - The description of the backlog item. This becomes the main text on the generated Trello card.
User Story - The user story (e.g. As a I want to so that ) for the backlog item. This gets recorded in the description field on the Trello card.
Points - The number of story points that have been assigned the story. The script puts this at the start of the title in parentheses. This is how the Trello Scrum Chrome extension handles story points.
Acceptance Criteria - The acceptance criteria for the story. These will be added as a single checklist on the Trello card, with a new line character separating the checklist items.
Comments - These will be added to the card as comments, with a new line character denoting a new comment.
Due Date - This will set the due date of the card. Date formats can be a problem, so it's best to use ISO Format if you can.
Labels - Enter a comma separated list of label that you want added to the card.

Trello Scrum Backlog Upload - Backlog Sheet

Once the script runs, and if all goes well, you will end up with a list full of Trello cards that look something like this:

That's more or less it, easy right? There are, however, a few minor things that are worth noting:

Google Apps Scripts appear to time out at around 6 minutes, so I changed the import script to end gracefully after 5 1/2 minutes. The stories that have been imported will have "Completed" recorded in column A, so you can just repeatedly run the import script until all entries are imported.
I did notice that if you had the Trello board open during the import, then some checklists/comments did not immediately appear on the cards - refresh your browser page, and they should appear.
The Trello cards all have a card number on the bottom right, and it is this unique ID that we use if we don't want to refer to the story by name.
The performance seems to vary depending on the number of comments/acceptance criteria, but I was managing to import around 50-70 items in every import.

So there you have it - if you've skipped to the end of this post to find the good stuff (i.e. the link to the spreadsheet), then you're in luck - you'll find the spreadsheet here.

** Update : I've added a new post on how you can now go the other direction and back up your Trello data in a Google Spreadsheet.

*** Update 7th November 2013 - If you do import a backlog into Trello, then there is another script available to Pimp Your Trello Card and automatically add Google Docs to your cards, Checklists, Descriptions, or even assign costs to your cards. ***

*** Update 7th January 2015 - I've made a few improvements to the script - details here. ***

*** Update 3rd November 2015 - another new version to address an api deprecated by Trello ***

CUSTOM UNOBTRUSIVE JQUERY VALIDATION WITH DATA ANNOTATIONS IN MVC 3

Heads up!

The blog has moved!
The new URL to bookmark is http://blog.valeriogheri.com/

MVC 3 introduced what is called Unobtrusive Client Validation. This feature relies on jQuery and HTML5 custom data attributes. It is very powerful and not so hard to use… with the right tutorial to follow

It’s also very useful because it leverages the power of Data Annotations, a clean way to express View Model rules in my opinion.

Let’s start the tutorial with a bit of theory (if you’re not interested, you can skip it and go straight to the paragraph “The tutorial” and download the source code from https://github.com/vgheri/DateCustomValidationExample)

What is a custom data attribute

“As per HTML 5 draft specification a custom data attribute is an attribute in no namespace whose name starts with the string “data-“, has at least one character after the hyphen, is XML-compatible, and contains no characters in the range U+0041 to U+005A (LATIN CAPITAL LETTER A to LATIN CAPITAL LETTER Z). Custom data attributes are intended to store custom data private to the page or application, for which there are no more appropriate attributes or elements. These attributes are not intended for use by software that is independent of the site that uses the attributes. It would be inappropriate, however, for the user to use generic software not associated with that music site to search for tracks of a certain length by looking at this data. This is because these attributes are intended for use by the site’s own scripts, and are not a generic extension mechanism for publicly-usable metadata. Every HTML element may have any number of custom data attributes specified, with any value. JavaScript libraries may use the custom data attributes, as they are considered to be part of the page on which they are used.”

This means that any attribute whose name starts with “data-” will be treated as a storage area for private data.
This allows you to write valid HTML markup (passing an HTML 5 validator) while, simultaneously, embedding data within your page.

MVC 3 + jQuery

MVC3’s new jQuery Validation mechanism links jQuery Validation and Validation Attributes Metadata. The magic happens in the jquery.validate.unobtrusive file that takes all data- attributes and works with them.
To exploit this mechanism, we need to create our own Custom Validation Attributes as we’ll see in this article.

How MVC generates the data- attributes:

The custom data attributes are generated by helpers methods like Html.TextBoxFor(), as it knows already all the Data Anotations that the field needs, and if Unobstrutive setting is true (by default in MVC 3) it will generate all data- that he needs.
For each client validation rule(we’ll get there), an attribute is added with data-val-rulename=”message”. Then, for each parameter in the client validation rule, an attribute is added with data-val-rulename-paramname=”paramvalue”.

The tutorial

In this tutorial we will create a custom validation that checks if a certain date is greater than another date entered by the user in a form. For the purpose of this tutorial we will create a model consisting of one class, Project, that obviously has a start date and an end date. Our goal is to force the user to enter an end date greater than the start date of the project.

To better clarify how to create a custom validator, this tutorial is divided in two steps: first we will provide server side validation, and on top of that we will add client side validation.

Step 1: Server side validation

First we create the model

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.ComponentModel;

using System.ComponentModel.DataAnnotations;

using System.Web.Mvc;

namespace DateCustomValidationExample.Models

{

    public class Project

    {

        public string Name { get; set; }

        public string ProjectManager { get; set; }

        [DisplayName("Start date")]

        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}", ApplyFormatInEditMode = true)]

        public DateTime StartDate { get; set; }

        [DisplayName("Estimated end date")]

        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}", ApplyFormatInEditMode = true)]

        [DateGreaterThan("StartDate", "Estimated end date must be greater than the start date of the project")]

        public DateTime EndDate { get; set; }

    }

}

Note that we already added our custom data attribute data annotation even if it’s not yet created. In my opinion this helps us to think more about how we want to use it rather than how it should be built.

Ok, we defined how we want to use the attribute, now we must create it:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.ComponentModel.DataAnnotations;

using System.Web.Mvc;

namespace DateCustomValidationExample.Models

{

    [AttributeUsage(AttributeTargets.Property, AllowMultiple=true)]

    public class DateGreaterThanAttribute : ValidationAttribute

    {

        string otherPropertyName;

        public DateGreaterThanAttribute(string otherPropertyName, string errorMessage)

            : base(errorMessage)

        {

            this.otherPropertyName = otherPropertyName;

        }

        protected override ValidationResult IsValid(object value, ValidationContext validationContext)

        {

            ValidationResult validationResult = ValidationResult.Success;

            try

            {

                // Using reflection we can get a reference to the other date property, in this example the project start date

                var otherPropertyInfo = validationContext.ObjectType.GetProperty(this.otherPropertyName);

                // Let's check that otherProperty is of type DateTime as we expect it to be

                if (otherPropertyInfo.PropertyType.Equals(new DateTime().GetType()))

                {

                    DateTime toValidate = (DateTime)value;

                    DateTime referenceProperty = (DateTime)otherPropertyInfo.GetValue(validationContext.ObjectInstance, null);

                    // if the end date is lower than the start date, than the validationResult will be set to false and return

                    // a properly formatted error message

                    if (toValidate.CompareTo(referenceProperty) < 1)

                    {

                        validationResult = new ValidationResult(ErrorMessageString);

                    }

                }

                else

                {

                    validationResult = new ValidationResult("An error occurred while validating the property. OtherProperty is not of type DateTime");

                }

            }

            catch (Exception ex)

            {

                // Do stuff, i.e. log the exception

                // Let it go through the upper levels, something bad happened

                throw ex;

            }

            return validationResult;

        }

    }

}

Notice how the attribute extends ValidationAttribute and overrides IsValid method, in which we perform the custom validation.

Now we need aController and aView to test the validation.

Create a HomeController with a simple Index action method, build the project and then add a strongly typed View bound to our model, choosing the “Create” item into the scaffolding menu.

To simplify user interaction, we will use the standard jQuery datepicker component to let the user choose the dates.
In order to use it, we must add the following references to our page (or into the master layout page)

<script src="../../Scripts/jquery-1.x.x.js" type="text/javascript"></script>

<script src="../../Scripts/jquery-ui-1.x.x.js" type="text/javascript"></script>

<link href="../../Content/themes/base/jquery.ui.all.css" rel="stylesheet" type="text/css" />

Where x.x is the version you currently have installed.

To bind the datepicker method to our input fields, we use the following jquery code

<script>

    $().ready(function () {

        $("#StartDate").datepicker();

        $("#EndDate").datepicker();

        return false;

    });

</script>

Then, in order to use server side validation, we need to add the following code to our HomeController to handle form submit

// POST: /Home/

[HttpPost]

public ActionResult Index(Project model)

{

   return View(model);

}

If we run the project and inspect the HTML emitted, this is what we see:

HTML5 emitted with the server side validation

As expected there is no custom data attribute related to our custom rule.

Then we can insert two dates that will trigger the error message and we get the error message as we expected

Validation error message

That’s it for server-side validation.

Client side validation

Now on to the second part of this tutorial, where we will achieve client side validation.
Before we start, a few important notes (taken directly from the blog of one of the developers of Asp.Net MVC 3)

jQuery Validate requires your input elements to be inside of a <form> element in order to be validated. In addition, MVC 3 requires that you have called Html.BeginForm() to render this form, so that it can find its book-keeping object to help render the HTML attributes.

Writing a client-side validator involves two steps:

adding one more method to our DateGreaterThanAttribute
writing some jQuery code to create a validation rule and what is called an adapter, which takes the parameter values from the HTML attributes and turns it into jQuery Validate metadata.

Note that we need to write a custom adapter since the default collection doesn’t come with what we need! Otherwise, there is an adapter collection available at jQuery.validator.unobtrusive.adapters.

Let’s go back to our DateGreaterThanAttribute class and modify it so that it implements the IClientValidatable, which, as MSDN says, provides a way for the ASP.NET MVC validation framework to discover at run time whether a validator has support for client validation.
Since we are implementing it, we need to write the method GetClientValidationRules like the following:

public IEnumerable<ModelClientValidationRule> GetClientValidationRules(ModelMetadata metadata, ControllerContext context)

        {

            //string errorMessage = this.FormatErrorMessage(metadata.DisplayName);

            string errorMessage = ErrorMessageString;

            // The value we set here are needed by the jQuery adapter

            ModelClientValidationRule dateGreaterThanRule = new ModelClientValidationRule();

            dateGreaterThanRule.ErrorMessage = errorMessage;

            dateGreaterThanRule.ValidationType = "dategreaterthan"; // This is the name the jQuery adapter will use

            //"otherpropertyname" is the name of the jQuery parameter for the adapter, must be LOWERCASE!

            dateGreaterThanRule.ValidationParameters.Add("otherpropertyname", otherPropertyName);

            yield return dateGreaterThanRule;

        }

Important: The metadata expressed for this rule will be used by the runtime to emit the HTML5 data-val custom attributes!

Now we need to create the jQuery validation function and the jQuery adapter.

We can create a separate .js file, thus we can reuse the code.
Let’s create a new Folder into /Scripts and call it “Custom”, then let’s create a new script called CustomValidation.js.

Let’s see the code:

/* File Created: January 16, 2012 */

// Value is the element to be validated, params is the array of name/value pairs of the parameters extracted from the HTML, element is the HTML element that the validator is attached to

$.validator.addMethod("dategreaterthan", function (value, element, params) {

    return Date.parse(value) > Date.parse($(params).val());

});

/* The adapter signature:

adapterName is the name of the adapter, and matches the name of the rule in the HTML element.

params is an array of parameter names that you're expecting in the HTML attributes, and is optional. If it is not provided,

then it is presumed that the validator has no parameters.

fn is a function which is called to adapt the HTML attribute values into jQuery Validate rules and messages.

The function will receive a single parameter which is an options object with the following values in it:

element

The HTML element that the validator is attached to

form

The HTML form element

message

The message string extract from the HTML attribute

params

The array of name/value pairs of the parameters extracted from the HTML attributes

rules

The jQuery rules array for this HTML element. The adapter is expected to add item(s) to this rules array for the specific jQuery Validate validators

that it wants to attach. The name is the name of the jQuery Validate rule, and the value is the parameter values for the jQuery Validate rule.

messages

The jQuery messages array for this HTML element. The adapter is expected to add item(s) to this messages array for the specific jQuery Validate validators that it wants to attach, if it wants a custom error message for this rule. The name is the name of the jQuery Validate rule, and the value is the custom message to be displayed when the rule is violated.

*/

$.validator.unobtrusive.adapters.add("dategreaterthan", ["otherpropertyname"], function (options) {

    options.rules["dategreaterthan"] = "#" + options.params.otherpropertyname;

    options.messages["dategreaterthan"] = options.message;

});

Let’s look at it in more details:

$.validator.addMethod("dategreaterthan", function (value, element, params) {

    return Date.parse(value) > Date.parse($(params).val());

});

The first parameter for addMethod is the name that identifies the rule and can be different from what we used inside GetClientValidationRules => dateGreaterThanRule.ValidationType = “dategreaterthan”;

In this example is the same, but you can change it.

The second parameter is the actual validator, in this case simply a comparison between two dates.

The adapter instead is tightly coupled with GetClientValidationRules implementation, therefore the first parameter must match the name we used in the code of our custom attribute for the validationType.

The second parameter is an array of parameters name.

In our case we only added one parameter

dateGreaterThanRule.ValidationParameters.Add("otherpropertyname", otherPropertyName);

so our array will have only one parameter. Should we have more, it would look like [“param1”, “param2”, ….]

The third parameter is a function which is called to adapt the HTML attribute values into jQuery Validate rules and messages.

options.rules["dategreaterthan"] //"dategreaterthan", the key to identify the rule, must match what we used in the addMethod as first parameter.

options.rules["dategreaterthan"] = "#" + options.params.otherpropertyname;

we use the # so that the value stored will be something like “#StartDate”, that is ready to be consumed by the validation rule previously wrote to get the value out of the StartDate field.

The javascript is done! Now let’s go back to our View because we need to add all the references we need to run the unobtrusive validation:

<script src="../../Scripts/jquery.validate.js" type="text/javascript"></script>

<script src="../../Scripts/jquery.validate.unobtrusive.js" type="text/javascript"></script>

<script src="../../Scripts/MicrosoftMvcAjax.js" type="text/javascript"></script>

<script src="../../Scripts/Custom/CustomValidation.js" type="text/javascript"></script>

Everything’s done and now we can run the app. If we inspect the HTML that has been emitted this is what we see

HTML emitted with client side validation

and if we run the application we will see the same error message as before, with no form submit!

The source code is available on my github at the following URL: https://github.com/vgheri/DateCustomValidationExample

That’s all for the unobtrusive custom validation with MVC 3.

I hope this helps,

Valerio

Feature	MyISAM	InnoDB
Full Text Indexes	yes	Since MySQL 5.6
Portable tables (tablespaces)	yes	Since MySQL 5.6
Spatial Indexes/RTREE (GIS)	yes	Since MySQL 5.7
Last update for table	yes	Since MySQL 5.7 (http://dev.mysql.com/worklog/task/?id=6658)
Suitable for temp tables	yes	Since MySQL 5.7 Also complex selects uses InnoDBondisk temp tables
Faster count(*)	yes	*Faster in MySQL 5.7 but does not store counter

Bits and Pieces

Monday, 23 April 2018