Pages

Saturday, January 15, 2011

Managing a List of Paying Members on the Cloud

Do you pay your alumni class dues? The news is that younger generations find less value in alumni associations.

Anyways, various membership fees are today more often payable online. And in order to cut back on communication expenses, a lot of organizations send reminders by email. Organizations with few members can process information for each member, at a fixed time of the year, and then one-by-one check off every member who paid. Email reminders can benefit from templates.




For organizations with many members, on the other hand, inputting each member's information could be unrealistic. In this case we suggest inputting the info from a Google Spreadsheets.


FYR:
"Submit Your Requests in Our Webform, and We'll Process Them All"



Spreadsheet Settings in Questetra BPM Suite SaaS Edition

1. Test the workflow reception (optional)
After activating the workflow to be used, check the URL address found in the [Version Detail] screen.

Example)

https://s.questetra.net/XXXXXXXX/System/Event/MessageStart/start?processModelInfoId=ZZ&nodeNumber=WW&key=YYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

In our sample workflow, there are four necessary input fields: data[0] (Member), data[1] (Email address) and data[2] (Amount to bill). So the system should automatically start a new process when it receives an access like:

https://s.questetra.net/XXXXXXXX/System/Event/MessageStart/start?processModelInfoId=ZZ&nodeNumber=WW&key=YYYYYYYYYYYYYYYYYYYYYYYYYYYYYY&data[0].input=Barack+Obama&data[1].input=barack@example.com&data[2].input=5000

As a test, copy & paste this long address into your browser (you should get a blank page), and make sure a new task is created in your task list.


2. Prepare a list of paying members in Google Docs
Log into Google Docs and create a new file from [Create new > Spreadsheet]. Give the spreadsheet a title (e.g., Member_List) and create three questions (Question Titles: "Member," "Email address," and "Ammount to bill."

You should get a spreadsheet with "Member" in column A, "Email address" in column B and "Amount to bill" in column C.

3. Get ready to send the list to Questetra BPM Suite
Open the spreadsheet, and create a new script from [Tools > Scripts >Script editor]
Replace...


function myFunction() {
}

...with...


function startWorkflow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = 100; //provisional setting: Max 100 members
  
  var dataRange = sheet.getRange(startRow, 1, numRows, 3);
  var data = dataRange.getValues();
  for ( var i = 0; i < data.length; ++i) {
    var row = data[i];
  
    var member = row[0];
    var email = row[1];
    var amount = row[2];
  
    var url = "https://s.questetra.net/XXXXXXXX/System/Event/MessageStart/start";
    var payload = 'processModelInfoId=ZZ';
    payload += '&nodeNumber=WW';
    payload += '&key=YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY';
    payload += '&title=' + encodeURIComponent(member + ' : ' + amount);
    payload += '&data[0].input=' + encodeURIComponent(member);
    payload += '&data[1].input=' + encodeURIComponent(email);
    payload += '&data[2].input=' + encodeURIComponent(amount);
    var params = {
      method: 'post',
      payload: payload
    };
    UrlFetchApp.fetch(url, params);
  }
}

...and save. Make sure you replace "XXXXXXXX", "YYYYYYYYYYYYYYYYYYYYYYYYYYYYYY", "ZZ" and "WW" with your own info. If you are not in Japan, you will probably want to also change "JST" to your own timezone.

4. Run the script! 
Click [ (Run sellected script)] button on the Script Editor, and you will find members' information input from a Google Spreadsheets and see as many first tasks as the number of rows (Max. 100).

* You can select [tool] > [script] > [manager...] and run this script on Script Manager.