Chin's Musings - Correct Data Format

Welcome to another installment of Chin's Musings! In this series, Systems Controller Chin Chuah will create helpful rundowns of various system features and processes to help you better understand the SmartSimple platform. These articles are geared toward experienced SmartSimple system users.

For further information on correct data formats in your SmartSimple system, please visit our Wiki or click any of the accompanying links in this article.

What are data formats and why are they important?
When storing any information into any system (not just limited to SmartSimple) it's important to understand what type of information you're dealing with and how to format it accordingly. The purpose of this is to store information in a meaningful way such that you can perform calculations or analysis on it.

Take the example of someone's name. The easiest way to store this would be to have a single field called "Name" for us to store our full name into. However, the problem occurs when we try to manipulate or analyze this name field. We can't separate it into first/last name if we want to sort our contacts by last name, or if we want to address them as Mr./Mrs. lastname.

Solving this problem is simple. Use multiple fields that force the user to be specific and enter the data as such. We can do this by using separate fields for first name, middle name, last name, and or even a title field for Jr/Sr.

How can data be formatted correctly?
For information to be meaningful and useful, it must be formatted and stored correctly. It is unsafe to try to manipulate unstructured and unformatted data into another format. Data can be stored and represented correctly by:

1) Designing our architecture and schema in a meaningful way to begin with (e.g. using multiple fields to store a name in the example above).

2) Forcing the data input into a specific format.This can be done by using data validation and transformation techniques (e.g. validating that users can only enter alphabetic characters, apostrophes, and hyphens as their name input).

Where and how can data be stored?
The main area to consider is the custom fields that you create and how you design them. Some specific examples include:

1) Date/time: These values should always be stored as a single Date/time value. From this you can extract the year, month, date, day as numbers or words, etc. Specifically for MySQL (and most other areas for easy sorting), the necessary format is "YYYY-MM-DD HH:MM:SS". By using the Date validation on a custom field you can enforce a uniform date formatting to be stored in the system regardless of how each individual user specifies the way they wish to view dates.

2) Numbers: Always store numbers exactly as they are. Consider how you would store currency data. When you use the Numeric validation and Currency formatting on a custom field, the system actually stores the number value to the full number of decimals and without a currency symbol (e.g. "12.948576"). However, when the system displays this field, it is rounded to two decimals and with a currency symbol (i.e. "$12.95").

3) In general: Always have a specific format for any standard data. For example, the Canadian postal code could be entered as "M5V 2H1" or "M5V2H1" (among other variants using lower/upper case letters). Best practice would be to enforce that the user always enters one specific format with upper case letters. This can be done using the validation configuration of a custom field.


Contact us at info@smartsimple.com or visit our main website to learn more about SmartSimple.