Design Patterns: Data Layers

Design Patterns in FileMaker is a set of articles which discuss common data modelling problems in the context of FileMaker.

Data layers simplify common data modelling problems. Data layers share the same core data and typically include several extra attributes which are unique to that layer.

What is the Problem?

The need for data layers arises when we have several discrete uses for a core set of data. We don’t want to replicate the core data for each use case because it makes extra work in maintaining tables and fields, business logic, scripts and layouts.

Many FileMaker databases start small and grow, rather than being planned from the top down. We often start with a fairly broad data set, such as “contacts”. Over time, the amount of data grows and the function requirements of the database increase too. At some point we want to be able to differentiate between the type of contacts. A common need is to separate our Customers from our Suppliers.

It is possible to do this in many ways. The most common being to control the found sets, either manually or by scripting. That works perfectly well and is the most common method because it is the easiest and most direct means of grouping data within a table. When you do this the user interface will provide you with feedback about those actions. In the screen shot below we can see a portion of the status bar. The current record indicator show that there are three records in the found set from a total of four records and that we are on record number two.

The status bar includes the found count and total record count. If that weren’t enough, the doughnut graph provides another representation of those numbers for visual thinkers.

Unfortunately this information can be confusing. Users may not realise that the suppliers and customers are both subsets of the same data set. Especially as the search buttons may look and behave like navigation buttons, as they are in the image above. The application gives the impression that the different groups, such as customers and suppliers, are really different data sets. When faced with this users may think that the application has not shown them the full found set. If so, that undermines their trust in the application. If they try to correct this, by showing all records, they will end up with the wrong data set. That has a great potential to cause problems and will further undermine their trust in the application.

Many system designers have handled this situation by restricting user access to the status bar. This is a fairly radical move because the status bar contains standard elements of the user interface which are desirable and useful. And, in fact, as soon as the status bars is hidden from the user, the designer will have to provide all of those tools again. This takes a lot of effort, for designers and programmers. It would be nice to be able to make use of the status bar without confusing our users.

Better Alternatives

Data layers provide a simple way to provide the functionality that we want. It does this without needing duplicated data. It allows us to expose the status bar to the user safely. That frees us from having to reproduce those functions and gives the user standard, reliable functionality.

We will using a Job system to illustrate how that works. At the core of the system is a Jobs table which carries data for all jobs. That table stores all the jobs that have been discussed and planned ( such as quotes), all the jobs that are active and all the jobs that have been finished and invoiced. However, our business often wants to be able to handle these as if they were completely separate data sets. We do not want to have all our quotes mixed in with our invoices. How do we do that?

Data layers provide a way of sharing a core set of data, while generating different data sets that have unique properties. A data layer is a new data table and records have a one-to-one relationship with the core data. A data layer will link to the core data and have fields that store the data that is unique to it.

A simple ERD showing core data with three different data layers

In this design pattern we link the data layer table with the data core table using the ID from the data core table. In other words, both the data core and the data layer share the same ID. In FileMaker this is achieved by the following steps:

  • Set the ID field in the Data Core table to text and auto-generate an UUID
  • Set the ID field in the Data Layer table to text. Do not generate an ID.
  • Allow record creation on both sides of the relationship

We now use the data layer tables as the user interface .

Powerful Advantages

FileMaker has many powerful behaviours that we can take advantage of when we use this design pattern. They are especially useful if you are retro-fitting this design pattern to an existing system.

The first thing to be aware of is that most existing layouts can be duplicated and re-purposed with very little effort. Re-purposing a core data table for use as a data layer table involves these steps

  1. Duplicate the layout
  2. Change the underlying Table Occurrence to the data layer table
  3. Change the layout name, text and field labels to reflect the new purpose
  4. In the core data table, find the correct set of records
  5. Import the IDs from the core data table into the ID field for the data layer
  6. repeat for each data layer

Depending on the specifics of your application this may be all you need to do. Some scripts may need to be tweaked but the data relations are all in place and work. You can now extend the behaviour of each layout to include the fields and functions which are specific to that data layer.

In the next article we’ll discuss Key Tables, which take advantage of under-the-hood performance features, and provide an even more compelling reason for using Data Layers.

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.

( 
Sale::subtotalOfGoods 
- 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.

Wrapup

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