Radically Simple DecisionTables in Google Sheets

Coding complicated logic in an application can be a challenging task for many programmers. This is mostly due to the limited number of options you have when dealing with decisions in software engineering. Most programming languages are equipped with just your typical if-then-else and switch statements. And that's it.

Imagine for example a personal loan application with all that heavy evaluate-if-this-guy-matches-our-qualifying-criteria logic. You'll end up with tons of if-then-else statements all over the place, like:

if (applicantAge > 18) { if ( applicantAge < 30 ) { if ( borrowingAmount > 1000 && borrowingAmount < 5000 ) { if ( creditRating > 0 ) { return 1; } } else { if ( rating <= 0 ) { return -1; } } } else if ( applicantAge > 30 ) { if ( borrowingAmount > 1000 && borrowingAmount < 5000 ) { if ( creditRating > 0 ) { return 1; } } else { if ( rating <= 0 ) { return -1; } } } } else { return 0; }

Using this typical branching technique can lead to a number of issues from reduced scalability and readability to performance penalties for your software.

If you're looking for a strong alternative to the good old but somewhat outdated if-then-else and switch code, you should consider using decision tables.

What are decision tables?

A decision table provides a framework for accurate statement of decision logic, represented in a tabular format. With it, you can define complex cause-and-effect rules in Google Sheets. The tabular representation of a decision table is its biggest advantage, as all the work related to decision logic is done in an easy-to-comprehend spreadsheet.

Using a decision table will enable you to abstract away all the complicated logic of your app and also significantly reduce your code base. Most importantly it will free you from hardcoding your decision logic. Let's take a quick look at the main benefits of switching (pun intended) to Harmony Decision Tables.

Simplify your logic with Harmony Decision Tables

With Harmony Decision Tables you won't need to code your logic with complex nested condition statements, nor will you have to deal with 100+ lines of switch statements anymore. All the heavy decision-making logic of your application will be migrated to a Google Spreadsheet, where you can safely define the logic in an easy-to-use and debug fashion. Here's an example of a decision table:

Harmony Decision Tables

With this type of condition-result syntax you can define every logical relation for your application.

Easy to understand by everyone

Implementing a decision table in your application can streamline your development process by improving the communication between your programmers, testers and analysts and other non-technical personnel. This is due to the fact that everyone can understand the logic in a decision table thanks to its compact, tabular format.

Easy conversion to code

Direct conversion of the logic defined in the Sheet is radically simple with Harmony Decision Tables. Our decision tables service comes with a RESTful API which allows you to get the result of complex logical expression with a single line of code, in cURL for example:

curl --data “applicant.age=20&applicant.gender=male&ccode-redit.score=3&ccode-redit.history_ranking=5&borrowing.amount=5000&lending.type=unsecucode-red”http://decisiontabl.es/api/NIniu2s9fs

The result comes in a nice JSON format like:

{ "case_id":"5El", "facts":[ { "concept":"dialog", "attrib":"approve order", "value":"n_a", "queue":"managers", "because":[ { "concept":"total", "attrib":"order", "op":"g_than", "value":100 } ] }, { "concept":"order", "attrib":"discount", "value":20, "queue":"undefined", "because":[ { "concept":"total", "attrib":"order", "op":"le_than", "value":2000 }, { "concept":"total", "attrib":"order", "op":"ge_than", "value":1001 }, { "concept":"customer", "attrib":"level", "op":"eq", "value":"gold" } ], "is_ref":false } ] }

Are you dealing with complex logical I/O relations and prominent if-then-else logic? Do cause-and-effect relations keep you awake at night? Get a Harmony Decision Tables licence today!


Build a holiday approval web app in Harmony with zero coding

Harmony is our flagship rules and workflow platform that makes web application development radically simple for anyone, including for non-programmers. With Harmony, business owners are now able to launch any web application without involving IT personnel and within hours. The only prerequisite is having a Harmony license and a Google account.

Holiday approval app business model

The modelling and configuration of the business rules and logic is done entirely in Google Sheets, in a natural language. To demonstrate this, we modelled and launched a demo holiday requests approval app. This article will walk you through the process in 7 steps.

Step 1: Identify your process

Every organisation has it's own process flow based on its unique structure. For example, in our demo approval process we identify three different scenarios to demonstrate the most common processes in which a holiday is approved:

  1. by a single approver - example scenario: all requests from sales agents in the Sales Department are approved by a Sales Manager
  2. by multiple approvers per the four-eye principle according to which an action is taken only if approved by two people. Example scenario: holiday requests from employees of the Finance Department are approved by a Finance Manager and by the company's HR
  3. by a direct manager - example scenario: Sales Manager's requests are approved by the company Manager only.
Step 2 Identify user groups

As a business owner, you hire employees to perform certain roles. Based on this, the employees can be divided in Groups, each participating in the holiday approval process.

Based on our example scenarios discussed above, we identify four different user groups:

  1. Employee - a global concept for all employees who are entitled to holiday, i.e. all employees regardless of their role
  2. Sales Management - one or more employees managing all employees of the Sales Department
  3. Finance Management - one or more employees managing all employees of the Finance Department
  4. HR - all employees of the HR Department

In the Groups sheet of the configuration, make a list of all employee roles taking part in the holiday approval process. It's recommended to list your groups after Harmony's default user access roles Internet and Admin.

Group - Set user roles

In the Users sheet, configure the level of access for each Group's members who will use the application:

  • to all events - grants access to all business events
  • to all cases - grants access to case data generated by all users

Users - Grant access to events and cases

Step 3: Create reference objects

In our demo holiday request approval application, we identify two reference objects - Employee, containing a list of employees, and Department, containing a list of all departments.

  1. Employee - a global concept for all of your employees who are entitled to holiday, i.e. all employees regardless of their role
  2. Department - a global concept that defines the grouping of employees and the management of the approval of holiday requests

In Harmony, reference objects are created and defined in Reference Sheets. To create your reference object, just add a new sheet and name it at your preference, however, note that the name has to contain prefix "REF_".

Alternatively, you could use Harmony's visual UI to create a Reference Sheet from the Harmony menu - Create Sheet:

Create reference sheet via visual UI

Note that the sheet type in the "Create new sheet" dialog must be set to Reference sheet. Reference name object is the name of the reference sheet. Reference key is an unique identifier such as email address.

In our demo model, each employee entry in REF_Employee has the following reference attributes:

  1. Email - unique identifier
  2. Name - employee name
  3. Department - a department as defined in REF_Department
  4. Position - the position of an employee
  5. Manager - the email address of the manager where they will receive holiday requests for approval

In our demo model, each department in REF_Department has the following reference attributes:

  1. Name - unique identifier is the department name
  2. Building - department location
  3. ApprovedBy - scenario as discussed in Step 1
  4. ManagementQueue - configures which Groups to receive the holiday request.

Create reference sheet Department via visual UI

Step 4: Model the relationship between the parties in the process

In our demo scenario, there is one direct relationship - employee manages department, and one reversed relationship - department is managed by employee. Note that the relationships have to be described in the default sheet RK_Model:

Create relationship kernel model

Then navigate to RK_Store, and fill out the values for the subject and the object of the relationship. In our example scenario, five employees serve as manager of departments:

Create relationship kernel model

Step 5: Create a holiday request template in Google Doc

In our example, the document is named Holiday request template. It's a template that Harmony populates with the employees data received from the UI via tags indicated with @ in the text of the document. The tags have to be named as the dialog items holding the required information (See Step 6) per REF Dialogs. Note that the document is saved in the employee's Drive.

Create a template in Google Docs

Step 6: Model a dialog for each step of the approval process

In order to create the pages of the web app, you have to model a dialog for each step of your approval process. Dialogs are modelled in Harmony's Dialogs sheet.

In order to execute our example approval process, we need the following dialogs:

  • Holiday Request - This is the entry point of the holiday approval app. This dialog allows the employee to input start and end day of absence, as well a message to their supervisor. Harmony automatically converts the document to PDF and emails it to the approver's address based on "RK Model" and the subject values defined in "RK Store"

Holiday request dialog

  • Document Generated and Document Generation Failed - Therefore, our demo holiday approval app, needs two dialogs to indicate successful and failed PDF generation.

Document generated

Document generation failed

  • Approve request - Enables all authorised employees to approve holiday requests

Approve request dialog

  • Approved request and Rejected request - A dialog that indicates the outcome of Approve Request and notifies the employee whether their request was approved by their manager.

Every dialog is a form containing:

  • DialogName - the name of the dialog
  • DialogItem - the items (fields)
  • Type - Based on the selected type, Harmony visualises a particular control in the browser, such as radio buttons (type "radio"), checklists (type "checkbox") or a text area (type "notes"). Create dialogs
Step 7: Model the rules for execution of the app

Harmony lets you define complex rules in a natural language and vocabulary of your choice. This, however, is with the exception of a number of reserved words and operators. For a full list of Harmony's operators, please navigate to the Support sheet of the configuration.

Harmony rules are defined in the Rules sheet. Each rule consists of condition (left-hand side; LHS) and result (right-hand side; RHS).

  • LHS consists of
    • Rid - unique identifier of your choice
    • Concept - Harmony default concept per the reserved words or as defined by the user
    • Attribute - Instance of the concept
    • Operation - performed by Harmony operator
    • Value - the value of the attribute per the reserved words.
  • RHS consists of all of the above plus:
    • Queue - the event is visible to the specified group
    • Expression

Based on our example process, we modelled the following rules:

1.In the LHS, check if dialog Holiday Request is displayed via Harmony's reserved word "exists". Note that this reserved word doesn't require a value. In the RHS, we get the email of the employee who requests holiday and set approvals count to 0 via reserved word "current".

Create dialogs

2.The second rule in our example configuration is a child case of the first rule, required to cover the "four-eye principle" approval scenario. Therefore, we need to set a concept-attribute pair (c/a pair) as reference objects don't get transferred to child cases.

Create dialogs

3.Next we need to create a PDF document using a holiday request template saved in a Google Doc. Note that the template will be saved in the user's Drive too. Via the expression concat("Holiday request - ", employee.email) to set a default name for the PDF file constructed by a string and the requester's email.

Create dialogs

4.Next, we want to extract the information for the department via a simple expression - {department = employee.department}.

Create dialogs

5.Now we can configure the rules for each of our demo scenarios:

  • direct manager approval - we check in RHS if a Manager has been set up in REF_Employee for the currently logged in user. If such is listed, we configure in LHS that the Approve Request dialog is sent to the queue of the listed Manager. We set required approvals to 1.

Create dialogs

  • single approval - we check in RHS if required approvals is set to 1 for the currently logged user's department. If this is the case, in LHS the request is sent for approval to the department 's manager by selecting extracting the manager's email from REF_Department (column ManagementQueue). The required number of approvals is set to 1.

Create dialogs

  • four-eye principle - we check in RHS if required approvals is greater than 1. If requests for the department are approved by two people, as defined in REF Employee, we have to extract all managers for the department with the expression {@department [managed_by] employee as manager return manager.email}. Required approvals is set to the value of the ApproveBy column in REF Department.

Create dialogs

Next we insert an Approve Request dialog for each of the managers by sending it to their private queue.

Create dialogs

6.Next, we have to define dialogs for every possible outcome of the process. In our demo case the scenarios are approve or reject request. In LHS, we check if dialog Approve Request exists and if the manager approves the request. Then in RHS, the number of approvals is updated via the expression (current.approvals + 1).

Create dialogs

Now we have to check if the number of collected approvals matches the number of required approvals in REF Department. If so, we notify the HR Department that there is an outstanding holiday request by sending it to the HR queue. Finally we revoke any outstanding Approve Request dialogs.

Create dialogs

7.The last rule handles rejected requests. In RHS we check if the request was rejected and if so, we notify the HR Department in RHS by sending a Rejected Request dialog to their queue.

Create dialogs

To demo our Harmony-based holiday request approval app, please request a Harmony license.