Trello as an Interface via Webhooks

So I’m a bit particular about how I keep my finances in order.  To the point that I wrote my own web-based ledger software to help myself keep it all straight.  Yeah, there are third-party solutions out there, but I wanted something that worked exactly the way I wanted.

Every couple days I pull receipts out of my pocket and go through my email inbox and drop new entries in my ledger.  Every couple weeks I manually reconcile the ledger with my banking statements.  Manual processes – ew – but it’s important enough to me that I do it.

The issue I ran into was transactions that I didn’t have a receipt for.  Tim Horton’s drive-thru or gas station pumps with a broken receipt printer or the Flint Firebirds’ souvenir shop using SquareCash.  No receipt means no ledger entry means confusion when I go to reconcile.

I could have made a mobile version of the ledger entry form but I really didn’t want to. As such I decided I could fix the issue by keeping faux-receipts electronically in Trello.  A single list.  Card title is the place I spent the money, description is the amount, a label to represent the account.  Once there’s a ledger entry, archive the card.

And that would have been enough.  I decided to take it a step further and automate things.

I use a webhook subscribed to that single list to look for new cards, get the data from them, automatically add the record to my ledger, then archive the card. I’m essentially using Trello’s app as an interface for my own so that I don’t have to make a mobile interface.

It’s a bit hacky but I figured I’d throw some of the code out here since I feel like there’s not a lot of Trello webhook documentation out there.  Unlike my usual, I’m going to redact some of the code as it deals with my financial system and I’d prefer not to put that out there.

<?php
  require_once 'trello_api.php';
  $trello = new trello_api($trello_config['key'], $trello_config['secret'], $trello_config['token']);

  $data = json_decode(file_get_contents('php://input'));

  if (($data->action->type == 'createCard') AND ($data->model->id == $data->action->data->list->id)) {
    // CREATE WEBHOOK ON CARD
    $webhook = $trello->request('POST', '/1/webhook', array('description' => ('Temporary callback for Card ' . $data->action->data->card->id), 'callbackURL' => 'http://www.example.com/webhook_callback.php', 'idModel' => $data->action->data->card->id));
    $trello->request('POST', ('/1/card/' . $data->action->data->card->id . '/actions/comments'), array('text' => ('Webhook ID: ' . $webhook->id)));
  }

  if (($data->model->id == $data->action->data->card->id) AND (($data->action->type == 'createCard') OR ($data->action->type == 'updateCard') OR ($data->action->type == 'addLabelToCard'))) {
    // ASSIGN VARIABLES
    $date = date('Y-m-d', strtotime($data->action->date));
    $label = trim($data->model->name);
    $amt = trim(str_replace('$', '', $data->model->desc));

    $category = get_category_id($label);
    $account_id = get_account_name($data->model->labels[0]->name);

    $details = array(array('category_id' => $category, 'amt' => $amt));

    if (($account_id) AND ($category) AND (is_numeric($amt))) {
      // DATA LOOKS GOOD, REMOVE WEBHOOK, ARCHIVE CARD, AND CREATE ENTRY
      $comments = $trello->request('GET', ('/1/cards/' . $data->model->id . '/actions'), array('filter' => 'commentCard', 'fields' => 'data'));

      $webhook_id = 0;
      foreach ($comments AS $comment) {
        if (strstr($comment->data->text, 'Webhook ID: ')) {
          $webhook_id = str_replace('Webhook ID: ', '', $comment->data->text);
          break;
        }
      }

      if ($webhook_id) {
        // FOUND A WEBHOOK, EVERYTHING WORKING AS IT SHOULD
        $trello->request('DELETE', ('/1/webhook/' . $webhook_id));
        $trello->request('PUT', ('/1/card/' . $data->model->id . '/closed'), array('value' => true));
        create_ledger_entry($account_id, $date, $label, $amt, $details);
      } else {
        // DIDN'T FIND A WEBHOOK, STUFF IS BROKEN
        $trello->request('POST', ('/1/card/' . $data->model->id . '/actions/comments'), array('text' => 'No webhook found.'));
      }
    } else {
      // DATA LOOKS NOT GOOD, NOTE IN CARD
      $msg = '';
      if (!$account_id) $msg .= 'Invalid account selected' . "\n\n";
      if (!is_numeric($amt)) $msg .= 'Invalid amount selected: ' . $amt . "\n\n";
      if (!$category) $msg .= 'No category found' . "\n\n";

      if ($msg) $trello->request('POST', ('/1/card/' . $data->model->id . '/actions/comments'), array('text' => trim($msg)));
    }
  }

  unset($trello);
?>

As I said, I’ve replaced some of the actual code with obscured-away psudo-functions. I’ll point those out those spots as necessary while going through this piece-by-piece.

require_once 'trello_api.php';
$trello = new trello_api($trello_config['key'], $trello_config['secret'], $trello_config['token']);

$data = json_decode(file_get_contents('php://input'));

Right off the bat we pull in my Trello API wrapper class (which really could use some love, maybe I’ll get to that sooner or later) and instantiate an object that we’ll use later. Then we pull in the data Trello posted to us via the input stream.

if (($data->action->type == 'createCard') AND ($data->model->id == $data->action->data->list->id)) {
  // CREATE WEBHOOK ON CARD
  $webhook = $trello->request('POST', '/1/webhook', array('description' => ('Temporary callback for Card ' . $data->action->data->card->id), 'callbackURL' => 'http://www.example.com/webhook_callback.php', 'idModel' => $data->action->data->card->id));
  $trello->request('POST', ('/1/card/' . $data->action->data->card->id . '/actions/comments'), array('text' => ('Webhook ID: ' . $webhook->id)));
}

Webhooks subscribed to a list don’t give us all the details of cards on the list, so if we detect a card creation and we’re certain we’re getting data from the webhook attached to the list, we add a webhook to the new card. This is done with a POST to /1/webhook passing in a description (which is optional and doesn’t really matter), callbackURL (same as the URL of this script, though obscured here), and idModel (the ID of the card). For future reference, we then post the ID of the newly-created webhook to a comment on the card via POST to /1/card/<card_id>/actions/comments with text set as needed.

if (($data->model->id == $data->action->data->card->id) AND (($data->action->type == 'createCard') OR ($data->action->type == 'updateCard') OR ($data->action->type == 'addLabelToCard'))) {
  // ASSIGN VARIABLES
  $date = date('Y-m-d', strtotime($data->action->date));
  $label = trim($data->model->name);
  $amt = trim(str_replace('$', '', $data->model->desc));

  $category = get_category_id($label);
  $account_id = get_account_name($data->model->labels[0]->name);

  $details = array(array('category_id' => $category, 'amt' => $amt));

The rest of the code only fires if we’re receiving from the new webhook attached to the card, and if it’s triggered by either a card creation (which should never happen since the webhook won’t have been created yet), card update (which happens when I set the card’s description), or label addition (which is how I define what account the transaction is for). We only care about those actions because they match the ones I take on the front-end.

From there, we use attributes of the card to build the transaction. The transaction date is whatever the action is taking place. The label is the name of the card. The amount comes from the card description. The category is determined based on the label. The account_id is determined based on the first label selected. A single-item, multidimensional array (the actual interface handles more complex data) is assembled from this data.

if (($account_id) AND ($category) AND (is_numeric($amt))) {
  // DATA LOOKS GOOD, REMOVE WEBHOOK, ARCHIVE CARD, AND CREATE ENTRY
  $comments = $trello->request('GET', ('/1/cards/' . $data->model->id . '/actions'), array('filter' => 'commentCard', 'fields' => 'data'));

  $webhook_id = 0;
  foreach ($comments AS $comment) {
    if (strstr($comment->data->text, 'Webhook ID: ')) {
      $webhook_id = str_replace('Webhook ID: ', '', $comment->data->text);
      break;
    }
  }

If we got an account ID and a category and the amount is numeric, we know we got good data and we’re ready to try to add the record. First, though, we make a GET call to /1/cards/<card_id>/actions with filter set to commentCard and fields set to data, so that we can get all of the comments posted to the card. We loop through them until we find the one where we stored the card webhook ID and then we save that ID off. There’s probably a better way to do this.

if ($webhook_id) {
  // FOUND A WEBHOOK, EVERYTHING WORKING AS IT SHOULD
  $trello->request('DELETE', ('/1/webhook/' . $webhook_id));
  $trello->request('PUT', ('/1/card/' . $data->model->id . '/closed'), array('value' => true));
  create_ledger_entry($account_id, $date, $label, $amt, $details);

If we found a webhook ID (and we always should), we delete the webhook, archive the card, and create the ledger entry. The first is accomplished with a DELETE request to /1/webhook/<webhook_id>. The second is a PUT to /1/card/<card_id>/closed with value set to true.

} else {
  // DIDN'T FIND A WEBHOOK, STUFF IS BROKEN
  $trello->request('POST', ('/1/card/' . $data->model->id . '/actions/comments'), array('text' => 'No webhook found.'));
}

If we didn’t get a webhook ID, something is seriously broken so we log that finding to the card as a comment via a POST to /1/card/<card_id>/actions/comments with text set to “No webhook found.”

    } else {
      // DATA LOOKS NOT GOOD, NOTE IN CARD
      $msg = '';
      if (!$account_id) $msg .= 'Invalid account selected' . "\n\n";
      if (!is_numeric($amt)) $msg .= 'Invalid amount selected: ' . $amt . "\n\n";
      if (!$category) $msg .= 'No category found' . "\n\n";

      if ($msg) $trello->request('POST', ('/1/card/' . $data->model->id . '/actions/comments'), array('text' => trim($msg)));
    }
  }

  unset($trello);
?>

Lastly, if we don’t have all of the things we need to make a ledger entry we log that to the card as a comment. Again, that’s done with a POST to /1/card/<card_id>/actions/comments.

As I said, it’s hacky. It gave me a chance to play with Trello webhooks a bit, though, and was a lot of fun. As I use it more, we’ll see what I did wrong.

Implementing Advanced Trello Functionality via the Trello API

I’ve written pretty extensively on my work with the Trello API and yesterday I got to present the code side of it at TechSmith’s internal development conference, ReCon.

Last year I presented for the first time (when the event was called DevCon) and I think I learned a lot from it that helped me this time around.  I’m pretty sure there were fewer “ums” and “uhs” but it was also a shorter presentation with less time for that.

The shorter presentation meant I had to absolutely blow through the information I wanted to put out there, so I’m worried I didn’t give enough time for it to set in.  It was supposed to be part of a larger presentation, an idea that fell apart late in the process, so I’m not sure what I could have done about that.

I think the biggest adjustment was the most important one.  In my presentation last year, my code slides weren’t very legible.  This time I made sure to focus on that, so there’s less code on each slide and the text is larger.  I couldn’t find a better way to get syntax-highlighted, properly-spaced code on to a Powerpoint slide than with screenshots, though, which seems stupid.

Unfortunately, I made one of the same mistakes as last year, not checking the audio beforehand.  I used a mic that was piped into the room speakers and recorded off an omni-directional mic that picked up everything in the room.  This makes the recording sound like I was in a car wash.

I enjoyed it significantly more than my presentation last year, though, and hope I have as good of a topic to speak on next year.

I referenced “Jenny’s presentation” and she’s posted it.  It covers a lot of the context I left out of mine.

Moving Cards to a Different Board via the Trello API

I’ve been writing a ton of late about the Trello API because I’ve basically been head-down in it for the last week.  Today I discovered my new biggest annoyance with an otherwise awesome tool.

Using the Trello UI, it’s really simple to move a card from one column/list to another on the same board.  Click the card, drag it over, drop.  Done.

Want to move it to a different board?  That’s not so difficult, either.  Open up the card, click the “Move” option, pick your new board and your new list and you’re good.

Moving a card through the API is also pretty easy.  Fire off a PUT request to /1/cards/xxxxxx (where xxxxxx is the card’s ID) with the idList parameter set to the ID of the list you want to move it to.  The documentation for the Trello API says that the idList parameter is optional and the reason is that you can update a card without moving it.

List IDs are globally unique.  You can request information about any list, regardless of what board it’s on, with a GET request to /1/lists/yyyyyy (where yyyyyy is the list ID).

So if I want to move a card to a list, I just need the list ID, right?  Right.  Except when you need more.

It turns out that there’s another optional parameter, idBoard.  If you’re moving a card to a list on a different board, you are required to specify the optional board ID even though the list IDs are unique, so by defining a list ID you automatically know what board that list belongs to.

I got hung up on this for about 30 minutes today and it annoys me.  The list ID is the most specific identifier you can get.  Why require a more general one on top of it?

So, yeah, if you’re moving a card with the API, you have to use idList.  If you’re moving to another board, you have to use idBoard as well.  Hopefully me mentioning it helps someone out.

Linking Child and Parent Cards on Trello – Part 2

Last week I published a bit of code that uses the Trello API to keep parent and child cards synced across a set of boards.  It was a little piece of research that has absolutely taken off around the office so I’ve been expanding on it and demoing it and talking about it and generally losing my mind.

The thing I expanded on most is a flaw that appeared in my original script whereby a user could create a child card outside of the normal workflow and it would never be linked to the parent card.  Obviously “outside of the normal workflow” means it’s already an edge case but that doesn’t mean it’s as uncommon as we’d like, so I came up with a way to handle it.  It does rely on the child card being tagged with the same card tag as the parent but it’s better than nothing.

#!/usr/bin/php -q
<?php
  $trello = new trello_api($GLOBALS['config']['key'], $GLOBALS['config']['secret'], $GLOBALS['config']['token']);

  // LOOP THROUGH EACH CARD ON THE WIP BOARD TO GET THE PARENT CARD TAGS
  $parent = array();

  $card_data = $trello->request('GET', ('/1/boards/' . $GLOBALS['config']['board']['wip_prototype']['id'] . '/cards'));
  foreach ($card_data AS $card) {
    $card_tag = '';
    if (preg_match('|[(.*)](.*)|is', $card->name, $matches)) {
      $card_tag = $matches[1];
    }

    if ($card_tag) {
      $parent[$card_tag] = array('id' => $card->id, 'url' => $card->shortUrl, 'label' => $card->labels[0]->color);
    }
  }

  // LOOP THROUGH EACH CHILD BOARD
  foreach ($GLOBALS['config']['child_boards'] AS $board_name) {
    // LOOP THROUGH ALL THE CARDS ON THE BOARD
    $card_data = $trello->request('GET', ('/1/boards/' . $GLOBALS['config']['board'][$board_name]['id'] . '/cards'));
    foreach ($card_data AS $card) {
      if (!preg_match('|Parent Card: https://trello.com/c/(.{8})|is', $card->desc)) {
        // NO PARENT CARD LINKED
        $card_tag = '';
        if (preg_match('|[(.*)](.*)|is', $card->name, $matches)) {
          $card_tag = $matches[1];
        }

        if ($card_tag) {
          // WE'VE GOT A TAG, LET'S USE IT
          if (is_array($parent[$card_tag])) {
            // WE KNOW THE PARENT THIS BELONGS TO

            // UPDATE THE CHILD CARD
            $trello->request('PUT', ('/1/cards/' . $card->id), array('desc' => (trim('Parent Card: ' . $parent[$card_tag]['url'] . ' ' . $card->desc))));

            // UPDATE THE PARENT CARD LABEL IF NECESSARY
            if (!$parent[$card_tag]['label']) {
              $trello->request('PUT', ('/1/cards/' . $parent[$card_tag]['id'] . '/labels'), array('value' => $GLOBALS['config']['label'][$board_name]));
            }

            // GET THE SLICES CHECKLIST
            $checklist_id = 0;
            $checklist_data = $trello->request('GET', ('/1/cards/' . $parent[$card_tag]['id'] . '/checklists'));
            foreach ($checklist_data AS $checklist) {
              if ($checklist->name == 'Slices') {
                $checklist_id = $checklist->id;
              }
            }

            if (!$checklist_id) {
              // NO SLICES CHECKLIST FOUND, MAKE ONE
              $new_checklist = $trello->request('POST', ('/1/cards/' . $parent[$card_tag]['id'] . '/checklists'), array('name' => 'Slices'));
              $checklist_id = $new_checklist->id;
            }

            // ADD THE CHECKLIST ITEM TO THE SLICES CHECKLIST
            $trello->request('POST', ('/1/cards/' . $parent[$card_tag]['id'] . '/checklist/' . $checklist_id . '/checkItem'), array('name' => $card->shortUrl));
          }
        }
      }
    }
  }

  unset($trello);
?>

As with my previous post, this uses my Trello API wrapper class and pulls in the $GLOBALS[‘config’] array of configuration values from another file.  Also as with my previous post I think it’s commented pretty well but we’re going through the code piece-by-piece anyway.

// LOOP THROUGH EACH CARD ON THE WIP BOARD TO GET THE PARENT CARD TAGS
$parent = array();

$card_data = $trello->request('GET', ('/1/boards/' . $GLOBALS['config']['board']['wip_prototype']['id'] . '/cards'));
foreach ($card_data AS $card) {
  $card_tag = '';
  if (preg_match('|[(.*)](.*)|is', $card->name, $matches)) {
    $card_tag = $matches[1];
  }

  if ($card_tag) {
    $parent[$card_tag] = array('id' => $card->id, 'url' => $card->shortUrl, 'label' => $card->labels[0]->color);
  }
}

We loop through all of the cards on our Work in Progress (“WIP”) board and use a regular expression to see if they have a card tag as a prefix (appearing in the pattern of “[TEST4] Test Project 4”). If the card does, we save an array of data about the parent to an array of parent cards for reference by card tag later.

// LOOP THROUGH EACH CHILD BOARD
foreach ($GLOBALS['config']['child_boards'] AS $board_name) {
  // LOOP THROUGH ALL THE CARDS ON THE BOARD
  $card_data = $trello->request('GET', ('/1/boards/' . $GLOBALS['config']['board'][$board_name]['id'] . '/cards'));
  foreach ($card_data AS $card) {
    if (!preg_match('|Parent Card: https://trello.com/c/(.{8})|is', $card->desc)) {

Then we loop through our list of child board names and get every card on that board using a GET request to /1/board/xxxxxx/cards (where xxxxxx is the board ID).  If the card’s description doesn’t match our convention for linking back to a parent, we know we’ve found a rogue card.

$card_tag = '';
if (preg_match('|[(.*)](.*)|is', $card->name, $matches)) {
  $card_tag = $matches[1];
}

if ($card_tag) {
  // WE'VE GOT A TAG, LET'S USE IT
  if (is_array($parent[$card_tag])) {
    // WE KNOW THE PARENT THIS BELONGS TO

We check to see if the card has a tag in its name, using the same regular expression as we did earlier.  If it does, we can use it to move forward. If we know the parent that tag belongs to, we can do even more.

// UPDATE THE CHILD CARD
$trello->request('PUT', ('/1/cards/' . $card->id), array('desc' => (trim('Parent Card: ' . $parent[$card_tag]['url'] . ' ' . $card->desc))));

The first thing we do is fire off a PUT request to /1/cards/yyyyyy (where yyyyyy is the ID of the rogue card) with desc set to the current description with our parent link prepended to it.  This gives our child card the necessary link to the parent.

// UPDATE THE PARENT CARD LABEL IF NECESSARY
if (!$parent[$card_tag]['label']) {
  $trello->request('PUT', ('/1/cards/' . $parent[$card_tag]['id'] . '/labels'), array('value' => $GLOBALS['config']['label'][$board_name]));
}

On the off chance that the parent card doesn’t have a label, we use the fact that we already know what board the child card is on to set one.  That involves a PUT request to /1/cards/zzzzzz/labels (where zzzzzz is the ID of the parent card) with value set to the color name of the label that corresponds to the board.

// GET THE SLICES CHECKLIST
$checklist_id = 0;
$checklist_data = $trello->request('GET', ('/1/cards/' . $parent[$card_tag]['id'] . '/checklists'));
foreach ($checklist_data AS $checklist) {
  if ($checklist->name == 'Slices') {
    $checklist_id = $checklist->id;
  }
}

Then we get ID of the parent card’s “Slices” checklist, as that’s where the parent card links to each of it’s children.  We make a GET request to /1/cards/zzzzzz/checklists and loop through each one until we find the one with the right name, then save that ID off for later.

if (!$checklist_id) {
  // NO SLICES CHECKLIST FOUND, MAKE ONE
  $new_checklist = $trello->request('POST', ('/1/cards/' . $parent[$card_tag]['id'] . '/checklists'), array('name' => 'Slices'));
  $checklist_id = $new_checklist->id;
}

What if we didn’t get a checklist ID?  Then we make one.  We fire off a POST request to /1/cards/zzzzzz/checklists with name set to “Slices” and that gives us back a bunch of data about a newly-created checklist.  We save off the new checklist ID so we can move forward.

// ADD THE CHECKLIST ITEM TO THE SLICES CHECKLIST
$trello->request('POST', ('/1/cards/' . $parent[$card_tag]['id'] . '/checklist/' . $checklist_id . '/checkItem'), array('name' => $card->shortUrl));

And our last step of the loop is to link the parent card to the rogue child.  We fire off a POST request to /1/cards/zzzzzz/checklist/cccccc/checkItem (where cccccc is the “Slices” checklist ID) with name set to the URL of the rogue child card.  Trello’s interface will convert that to the name of the child card when the parent card is viewed.

As I mentioned in my previous post, this is my first pass and I’m sure there’s a better way to do this.  This fixes a gap in that earlier implementation, though, so obviously iterating on it is working.

Linking Child and Parent Cards on Trello via the Trello API

One of the things my team at work always seems to have a problem with is tracking our tasks and how they fit into the bigger picture of our team’s goals.  We had these problems when we were using a bulletin board, we have these problems now that we’re using a single Trello board.

We have a subset of the team currently researching new tools due to the perceived limitations of Trello. Being a developer and having never met a problem that couldn’t be solved by throwing code at it, I decided to see what could be done with the Trello API.

I’ve written about using the Trello API a couple times before.  It’s a powerful tool and it’s something I’ve had a lot of fun with.  This builds off of that work.

One thing that was the lynch-pin for figuring all of this out is the difference between a card’s ID, shortID, and shortLink.  Because shortLink isn’t returned by default when you make a request for a card’s information, I thought that it was just another term for shortID, much as how archived and closed are synonyms within the Trello environment.  It turns out that that’s not true, and since shortLink is exposed in every Trello URL, it opens up a lot of options.

Before getting into the code too much, though, a look at the problem we’re trying to solve…

Right now my team has one “Work in Progress” (or “WIP”) board with ten functional columns.  Requests Not Started, Requests In Progress, Slices Not Started, Slices In Progress, Ready for QA on DEV, QA on Dev, Need to Publish to Stage, QA on Stage, Ready for Publish, and Published.

Requests are large-scale projects.  Slices are parts of a request, which are supposed to be individually-shippable.

When you pick up a request that hasn’t started yet, your first task is to break it into slices.  Those slices then march across the board and when they’re all published the request card jumps over to join them (currently a manual process).  The idea is that the slices show the team a little bit more of what’s in progress while the requests are geared towards external stakeholders.

The problem with that is that it makes for a messy board.  If only we could have a WIP board just for requests, and have the slices flowing along some other path, but still tied back to the request card.  So that’s what I did.

Here’s my informal demo video, with code to follow.

Okay, now on to the code.

#!/usr/bin/php -q
<?php
  $trello = new trello_api($GLOBALS['config']['key'], $GLOBALS['config']['secret'], $GLOBALS['config']['token']);

  // LOOP THROUGH EACH CARD ON THE REQUESTS IN PROGRESS LIST
  $card_data = $trello->request('GET', ('/1/lists/' . $GLOBALS['config']['board']['wip_prototype']['rip_list'] . '/cards'));
  foreach ($card_data AS $card) {
    // GET THE CHILD BOARD
    $child_board_name = '';
    foreach ($GLOBALS['config']['label'] AS $board => $color) {
      if ($card->labels[0]->color == $color) {
        $child_board_name = $board;
      }
    }

    // GET THE CARD TAG
    $card_tag = '';
    if (preg_match('|[(.*)](.*)|is', $card->name, $matches)) {
      $card_tag = '[' . $matches[1] . '] ';
    }    

    // GET THE CHILD TASKS / CARDS
    $children = 0;
    $children_completed = 0;

    foreach ($card->idChecklists AS $checklist_id) {
      $checklist_data = $trello->request('GET', ('/1/checklists/' . $checklist_id));

      if ($checklist_data->name == 'Slices') {
        foreach ($checklist_data->checkItems AS $item) {
          if (preg_match('|https://trello.com/c/(.{8})|is', $item->name, $matches)) {
            // WE HAVE THE CHILD CARD, CHECK IT
            $child_card = $trello->request('GET', ('/1/cards/' . $matches[1]));

            if ($child_card->id) {
              // CARD STILL EXISTS
              $child_card_state = 'incomplete';
              if (($child_card->closed) OR ($child_card->idList == $GLOBALS['config']['board']['qa_prototype']['completed_list'])) {
                $child_card_state = 'complete';
                $children_completed++;
              }

              // UPDATE THE STATUS IF NECESSARY
              if ($child_card_state != $item->state) {
                $trello->request('PUT', ('/1/cards/' . $card->id . '/checklist/' . $checklist_data->id . '/checkItem/' . $item->id . '/state'), array('value' => $child_card_state));
              }

              $children++;
            } else {
              // CARD HAS BEEN DELETED, REMOVE THE CHECKITEM
              $trello->request('DELETE', ('/1/cards/' . $card->id . '/checklist/' . $checklist_data->id . '/checkItem/' . $item->id));
            }
          } else {
            // CARD HASN'T BEEN CREATED YET, DO IT IF WE HAVE A LIST TO POST TO
            if ($GLOBALS['config']['board'][$child_board_name]['ns_list']) {
              $new_card = $trello->request('POST', '/1/cards', array('name' => ($card_tag . $item->name), 'idList' => $GLOBALS['config']['board'][$child_board_name]['ns_list'], 'desc' => ('Parent Card: ' . $card->shortUrl)));

              if ($new_card->id) {
                // UPDATE THE CHECKLIST ITEM WITH THE LINK TO THE NEW CARD
                $trello->request('PUT', ('/1/cards/' . $card->id . '/checklist/' . $checklist_data->id . '/checkItem/' . $item->id . '/name'), array('value' => $new_card->shortUrl));
                $trello->request('PUT', ('/1/cards/' . $card->id . '/checklist/' . $checklist_data->id . '/checkItem/' . $item->id . '/state'), array('value' => 'incomplete'));
              }

              $children++;
            }
          }
        }
      }
    }

    if (($children > 0) AND ($children == $children_completed)) {
      $trello->request('PUT', ('/1/cards/' . $card->id . '/'), array('pos' => 'top', 'idList' => $GLOBALS['config']['board']['wip_prototype']['completed_list']));
    }
  }

  unset($trello);
?>

This makes use of my Trello API wrapper class and has several values (all part of the $GLOBALS[‘config’] array) dumped off to an included configuration file.  I think the comments are pretty good, but let’s go through this a little bit of code at a time.

The whole thing is wrapped in a loop through the cards on the Requests in Progress column of the main WIP board.  We get that by doing a GET request to /1/lists/xxxxxx/cards (where xxxxxx us the list’s ID) and I won’t bother detailing it any further.

// GET THE CHILD BOARD
$child_board_name = '';
foreach ($GLOBALS['config']['label'] AS $board => $color) {
  if ($card->labels[0]->color == $color) {
    $child_board_name = $board;
  }
}

// GET THE CARD TAG
$card_tag = '';
if (preg_match('|[(.*)](.*)|is', $card->name, $matches)) {
  $card_tag = '[' . $matches[1] . '] ';
}

We figure out which board the child cards should go to by looking at the labels and our configured array that matches label colors to child boards.  In this example, the green label belongs to the ecommerce board and the red one to the marketing board.  If the first label on the card matches the color of one of those boards, we’ve got our board.

Then we take a look at the parent card name and look for anything inside square brackets using a regular expression.  That’s the tag that should be applied to child cards, something we use for filtering.

We set the number of children on this card and the number of completed children to zero, values we’ll update as we go.  Then we loop through the checklist IDs we got when we looked up the card data.

$checklist_data = $trello->request('GET', ('/1/checklists/' . $checklist_id));

if ($checklist_data->name == 'Slices') {
  foreach ($checklist_data->checkItems AS $item) {

We use a GET request to /1/checklists/yyyyyy (where yyyyyy is the ID of our checklist) to get more info about that checklist.  If the name of the checklist is “Slices” then we iterate through each item because we know those represent our child cards.

if (preg_match('|https://trello.com/c/(.{8})|is', $item->name, $matches)) {
  // WE HAVE THE CHILD CARD, CHECK IT
  $child_card = $trello->request('GET', ('/1/cards/' . $matches[1]));

  if ($child_card->id) {
    // CARD STILL EXISTS
    $child_card_state = 'incomplete';
    if (($child_card->closed) OR ($child_card->idList == $GLOBALS['config']['board']['qa_prototype']['completed_list'])) {
      $child_card_state = 'complete';
      $children_completed++;
    }

    // UPDATE THE STATUS IF NECESSARY
    if ($child_card_state != $item->state) {
      $trello->request('PUT', ('/1/cards/' . $card->id . '/checklist/' . $checklist_data->id . '/checkItem/' . $item->id . '/state'), array('value' => $child_card_state));
    }

    $children++;

If the text of the checklist item matches the pattern of a Trello card URL, we know it’s an already-linked child.  We get the shortLink from that URL using our regular expression and then make a GET request to /1/cards/zzzzzz, where zzzzzz is that shortLink.  If the card has an ID it means it still exists, so we can do some work with it.

By default, we set the card’s status to incomplete, which is one of the states of a checkItem in Trello. If the card is closed or is in the Published list on our QA board, we know work has been completed and the state can be set to complete. We can also increment our counter of completed child cards by one.  If the state we’ve determined doesn’t match that of the checkItem, we fire off a PUT request to /1/cards/cccccc/checklist/bbbbbb/checkItem/aaaaaa/state (wherre cccccc is the card ID, bbbbbb is the checklist ID and aaaaaa is the ID of the specific checklist item), passing in a value set to whatever we determined above.  This updates our checklist item to match the state of the card itself.  Then we increment the counter of children by one.

} else {
  // CARD HAS BEEN DELETED, REMOVE THE CHECKITEM
  $trello->request('DELETE', ('/1/cards/' . $card->id . '/checklist/' . $checklist_data->id . '/checkItem/' . $item->id));
}

If the card’s ID wasn’t found, we know the card has been deleted.  In that case we fire a DELETE request to /1/cards/cccccc/checklist/bbbbbb/checkItem/aaaaaa to completely remove the related checklist item.

} else {
  // CARD HASN'T BEEN CREATED YET, DO IT IF WE HAVE A LIST TO POST TO
  if ($GLOBALS['config']['board'][$child_board_name]['ns_list']) {
    $name = $card_tag . $item->name;
    $desc = 'Parent Card: ' . $card->shortUrl;
    $new_card = $trello->request('POST', '/1/cards', array('name' => $name, 'idList' => $GLOBALS['config']['board'][$child_board_name]['ns_list'], 'desc' => $desc));

    if ($new_card->id) {
      // UPDATE THE CHECKLIST ITEM WITH THE LINK TO THE NEW CARD
      $trello->request('PUT', ('/1/cards/' . $card->id . '/checklist/' . $checklist_data->id . '/checkItem/' . $item->id . '/name'), array('value' => $new_card->shortUrl));
      $trello->request('PUT', ('/1/cards/' . $card->id . '/checklist/' . $checklist_data->id . '/checkItem/' . $item->id . '/state'), array('value' => 'incomplete'));
    }

    $children++;
  }
}

Now we’re back to if we didn’t find a URL in the checklist item’s name.  In that case, we know the child card hasn’t been created yet so we go on to do that, assuming we actually have a list ID to work with (just in case there’s no label on the parent card or something).

We build the child card’s name by combining the text of the list item with the card tag we defined earlier, then we build the description by just linking to the parent card.  Then we fire that data (along with the list ID) off via a POST request to /1/cards.

That request returns data about the newly-created card.  If the card was successfully created (determined by whether or not we got an ID back), we go back to the checklist item.  We update the name to be a link to the new card, which displays in Trello as that card’s name, and we make sure to set the status to incomplete just in case someone did something stupid.  That’s done with a pair of PUT requests, one to /1/cards/cccccc/checklist/bbbbbb/checkItem/aaaaaa/name with value set to the new card’s shortUrl, the other to /1/cards/cccccc/checklist/bbbbbb/checkItem/aaaaaa/state with value set to incomplete.

At this point we’ve looped through every item on the “Slices” checklist for a single card.

if (($children > 0) AND ($children == $children_completed)) {
  $trello->request('PUT', ('/1/cards/' . $card->id . '/'), array('pos' => 'top', 'idList' => $GLOBALS['config']['board']['wip_prototype']['completed_list']));
}

The last thing we do before moving on to the next card is compare our counts of children and completed children. To account for the fact that a card may have been moved to Requests in Progress before the “Slices” checklist was completed, we require at least one child to exist.  If there are children and they’ve all been completed, we move the parent card over to the Published column of our WIP board with a PUT request to /1/cards/dddddd, where dddddd is the parent card ID, with the pos defined as top and the idList defined as the ID of the published column.

As I mentioned in the video, I’ve got this running once a minute as a cronjob.  I’m sure there’s a better way to do it but this is my first pass so I’m not worried about it.