Using Boolean Data in Mathematical Functions

Database Design Intermediate: Using Boolean Data in Mathematical Functions

If you are shy of maths you should continue reading. Most math-geeks don’t need to be told this stuff, so this section is not really for them, it’s for you. It describes two simple math tricks that are useful, so read on.

A common use of boolean values is in sales. Does sales tax apply? Is a discount voucher being used? In these situations the final price will be affected by the answers to these questions. The need to apply the data in a mathematical equation emphasises the importance of using number values for these questions.

Let’s look at the wrong way first. We have two fields, “applySalesTax” and “applyDiscount.” We’ll assume the worst, instead of using numbers the developer has used a single option value lists. One that says “Incl. Sales Tax” and the other says “Has Discount Voucher.” To evaluate the total price we need this calculation.

- if ( Sale::applyDiscount = "Has Discount Voucher" 
    ; Sale::discount 
    ; 0 
if ( Sale::applySalesTax = "Incl. Sales Tax" 
    ; 1 + Sale::taxRate 
    ; 1 

In this calculation we have the mathematical formula which is subtotal – discount + sales taxes. Within that formula we have two IF statements, one for each field giving us four branches in the logic. Each statement must be correctly formed to ensure that we end up on the right one of the four possible branches. And, of course, each of the four branches must be in the correct position too. All in all we have seven logical elements to maintain.

Now lets look at it using booleans. When we store the data to those questions as a number (boolean) the derivation becomes much easier. In our first example we subtract the discount and then apply sales tax. The entire statement is much simpler, there is no fork in the logic.

( Sale::subtotal - Sale::applyDiscount * Sale::discount ) 
( 1 + Sale::taxRate * Sale::applySalesTax )

The boolean value is being used to full advantage. When “applyDiscount” is zero there is no discount. When “applyDiscount” is one the discount will be subtracted. Similarly, if “applySalesTax” is false (0) then the second part of the formula becomes ( 1 + 0) = 1. So the formula becomes X times 1, which is X. When “applySalesTax” is true (1) the taxRate is added to 1 and the subtotal is increased.

In our second example the discount is obtained by using the boolean “applyDiscount” with the “power” function to modify the discount rate (a percentage).

( Sale::subtotal * Sale::discountRate ^ Sale::applyDiscount ) 
( 1 + Sale::taxRate * Sale::applySalesTax )

The power of zero and one are special cases. The value of a number X raised to the power of one is always X. The value of a number X raised to the power of zero is always one (1). This means that we can safely include the discount rate in our multiplication because when “applyDiscount” is zero, the value of discountRate ^ 0 is one, so the subtotal is not modified. When “applyDiscount” is true (1) the subtotal is multiplied by the discount rate and is reduced accordingly.

What are the benefits?

  • These two methods can be used widely. They are simple and safe methods that have a broad range of applications.
  • Neither of these examples require conditional logic functions.
  • The same equation is valid for all sales regardless of whether they have discounts and/or sales taxes, so the code is more flexible.
  • The code is simpler, so it is more likely to be correct and easier to correct when it is wrong.
  • Neither example uses hard-coded text strings, which means that the code is more resilient.

Make Data Entry Easier by Framing Questions as a Boolean Option

Database Design Basics: Making Data Entry Easier

This article is about making data entry easy and it’s for people who don’t have a computer or maths background. I studied law, literature and communications before taking up computing and it took time to let go of words and to use numbers. Not that I’ve given up on words entirely but I’ve learnt that computers prefer numbers. Learning to use boolean options can improve your database in important ways: ease of input, data integrity,  data storage and data re-use.

When we are performing any sort of data entry on the computer we discover that its easy to make mistakes. Words are comparatively long and hard to spell. On the other hand, 0 (zero) and 1 (one) are easy to type and you know immediately if it is correct.

Ease of Input

Plain language and simple input options make it easy for your users to get the input right. Good design choices can reduce their workload and provide safeguards to prevent simple errors and accidents. When you simplify a question to a Yes/No option the amount of thinking required is reduced. In many cases, you can offer one option so your user only needs to touch the form to answer in the affirmative or the negative. When you are able to do this consider entering the opposite value as the default.

Data Integrity

Ensure that the data being entered is correct. Even the best users make mistakes so you should take it for granted to errors will occur. When you know that there are only two possible values it is very easy to test for mistakes and correct them.

Data Storage

Storing a 1 or a 0 for Yes/True or No/False reduces the amount of storage that is required. The smaller the size of the data set, the less resources are required, the faster all your operations will run.

Database Design Basics: Data Re-use

If you are collecting data that affects numbers then it makes sense that the answers to questions are in numbers too. Even if you are not doing math, once you have data you’ll want to use it in some way. It is at this point, that you start to re-use your data for research, analysis or reporting, that you will discover why well-formed and accurate data is so highly regarded.

The simplest request will be to find a set of records. If you are storing data as a positive/negative statement (see Common Mistakes), you immediately have a problem. While you are searching manually the field format supports the search, however, when you start scripting to run reports you’ll see how the difficulties arise. To create an unambiguous search request it is necessary to search for a unique value. The only unique word is “not” in “Does not have a driver’s licence.” There are no words in “Does have a driver’s licence” that are not in “Does not have a driver’s licence” so it would be necessary to use the whole string to perform the search or you could search for “not” and then flip the found set using the Omit Records step. In comparison, finding a true or false value is simple and unambiguous – search for 1 or 0.

Using positive and negative statements requires the user to read and appraise both options.

When you start scripting to run reports you’ll have to make decisions based on the data. A number can be evaluated to true/false without requiring any conversion. That allows us to use the field value as the test, so we can say

If ( Applicant::hasDriverLicence ) then ...

That is simple and easy to understand. Reading it makes sense, even to non-programmers.

Evaluating wordy data quickly becomes complicated, here we evaluate the data looking for people with a driver’s licence.

If ( LeftWords ( Applicant::hasDriversLicence ; 2 ) = "Does have" ) ) 

That’s much harder to read and much harder to write.

Re-using Data to Enrich the User Interface

FileMaker offers two powerful features for modifying the user interface: conditional formatting and the “Hide object when” option. Both features are able to determine the outcome using recorded data. Data that is in a boolean state is  perfectly suited to these tests.

Conditional Formatting

Conditional formatting is focussed on text style, background colour,  and icon colour.

Conditional formatting is used to change the colour of text. In this case, failures are coloured red.

Conditional Formatting allows us to modify text formatting based on data within the record.

Hide Object When

The ability to hide or show an object allows us great flexibility in the layout design. We are able to show or hide objects conditional on the answers given to earlier questions. In the example below, the field for the driver’s licence number is only shown when it is needed. This allows us to build dynamic interfaces which can respond the users inputs to present different workflows.

The driver’s licence number is only requested when we know that the applicant has a licence.





Using Boolean Data to Control Script Flow

Using booleans to simplify conditional statements to control the user interface we gave examples which used IF. The same logic applies to the CASE function too, and many other built-in functions. You can utilise boolean data to control the flow of scripts too, simply by using the same logic inside the IF and ELSE IF script steps.


Learning to describe your data in boolean form reduces your effort and makes it much easier to provide better solutions.