Creating a Chrome River Users & Roles Application in 3E

Chrome River delivers in their words, "... the best expense management software and expense report software with all the modern mobile features users love." 3E, from Thomson Reuters is one of the big competitors for managing legal time and billing. Integrating the two systems comes down to sending some data feeds back and forth to keep everything in sync.

This article assumes in depth knowledge of the 3E IDE, Chrome River data feeds and SQL.

As people change roles, or as different roles are assigned to multiple groups, managing the Chrome River roles in 3E and using the Command column of the Chrome River People feed makes things a bit easier, but you'll need an application to handle it all.

One of the various feeds is the list of People and their roles. In the people feed, there is an optional Command field which reminds me of something I would have made up in my first year of University as a way to use a single column to store some serialised data. It is a comma delimited list of Role, Entity Type and Value each separated with the string __zZz__.

What you end up with in this scenario is something like PartOf__zZz__OFF__zZz__LAX,PartOf__zZz__DEPT__zZz__Billing meaning the user is in the LAX office, in the Billing department. I'm not sure why this wasn't separate child rows, but as an in-house developer at a law firm I tend to have little influence on third party internal decisions and instead work to bend our systems to conform to the specifications. Other options like Office and Department are RGE (rules group exceptions) and FIRMWIDE. My first thought was to build some fancy SQL but decided that would get messy, particularly as we wanted to manage the RGE's in 3E. So we wrote a custom 3E application that manages all aspects within 3E and pushes the various role memberships in the People feed's command column. This post is meant to document the highlights of that application.

The first thing I start with in this type of situation is the command column itself. I need to make sure the application is capable of producing the output we need. Using Chrome River's vocabulary the Command is broken down as [Role]__zZz__[Entity Type]__zZz__[Entity Value]. I needed to make sure I could produce this output. I wanted something simple to query through SQL so opted to build the full Command for each user and store it in its own Archetype. To start with I only wanted to deal with the RGE's but needed to keep the application flexible enough to handle the Office, Department, Firmwide and anything else that came along.

I decided we could use the following Archetypes: ChromeRiverEntity, ChromeRiverEntityRole, ChromeRiverEntityType, ChromeRiverUser & ChromeRiverUserRoleEntity.

The main driving force behind the need for the Command column to be utilized months after the firm had been sending a People feed was that we had a new Entity called INVALL which had to be assigned to every user without wiping out the current roles in Chrome River. The way the feed works is that if you send a single RGE it removes any others that that user had been assigned, so you have to send all of the assigned RGE's for each user in every People feed.

It was reported that manually adding the INVALL role to each new user was sometimes forgotten so we opted to turn on the optional Command column in our QA environment and began testing. The people feed documentation is a little vague and it was quickly learned that specifying the one RGE Entity Type of INVALL removes all other roles for a user like the admin role. A good reminder to not test on your own user account.

Our setup also meant that 'default' users without any special requirements for admin, would only have INVALL assigned. This means that only those special users needed a record in our ChromeRiverUser Archetype. We also opted to continue to use the Office and Department from the date effective Timekeeper data, and not specify it directly as part of this application, however, one could easily do so. This would easily allow Timekeepers to have multiple offices in Chrome River.

In the end we had setup/maintenance processes for the ChromeRiverEntityType (with a single record to hold the RGE option), & ChromeRiverEntityRole (with a single record to hold the PartOf option), ChromeRiverEntity (to hold RSADMIN, INVOICE ...). The main process managed the parent record of ChromeRiverUser and child grid housing the ChromeRiverUserRoleEntity. The resulting form to manage the user's memberships is shown below.

The build command field is updated in the BeforeUpdate event on the parent form to ensure it captures any updates. I also had to create an unbound field on the parent record with default of False which got updated to True in the BeforeUpdate of the child grid. It was a quick 'IsChanged' type field I could update. I'm not sure if it a limitation of the 3E framework, or by design, but updating items in the grid does not appear to flag a change in the parent object's BeforeUpdate code.

Placing this in a separate process (opposed to adding to Timekeeper or User/Role Maintenance) allows us to assign full access to members of the Accounts Payable Admin who does not have full rights to the User/Role Maintenance process.

This setup now allows us a simplistic SQL command that builds the People field populating the Command field from our new ChromeRiverUser Archetype by using the following case statement to ensure everyone gets the proper roles. The order in the else was simply a shortcut to not have to deal with handling trailing or leading commas.

case 
    when coalesce(C_ChromeRiverUser.Command,'') = '' then 'Part Of__zZz__OFF__zZz__' + Office.Code + ',Part Of__zZz__DEPT__zZz__' + Department.Code + ',Part Of__zZz__RGE__zZz__InvAll'
    else 'Part Of__zZz__OFF__zZz__' + Office.Code + ',Part Of__zZz__DEPT__zZz__' + Department.Code + ',Part Of__zZz__RGE__zZz__InvAll,'+C_ChromeRiverUser.command
end 'command',

The application took under 4 hours to piece together, with a little learning along the way. I also utilized my 3E Loader Application to load the data sourced from Chrome River's Entity report which made populating 3E a piece of cake.