# Columns ## Overview This guide will demonstrate how to work with Columns in your database tables, including add/dropping as well as column properties that affect the behavior of applications. ## Get Started To work with your database columns, you should first [connect to your database](https://docs.servoy.com/guides/develop/application-design/data-modeling/databases/..#connect-to-a-database) and [open the table](https://docs.servoy.com/guides/develop/application-design/data-modeling/databases/tables/..#open-edit-a-table) in the [Table Editor](https://docs.servoy.com/reference/servoy-developer/object-editors/table-editor), where you will manage you columns. ## Add a Column While developers are free to use any DB admin tool to manage their database columns, it's easy to add a column in Servoy Developer.

1. In the Table Editor, click the Add button to create a column. 2. Set the [**`Name`**](#user-content-fn-1)[^1] property with a valid column name. 3. Choose the Data [**`Type`**](#user-content-fn-2)[^2] property from the list. Select the type of column you want to create. 4. Certain data types require that you set a value for the [**`length`**](#user-content-fn-3)[^3] property 5. Save the editor. When you save, your new column is both written to the database and tracked in your [Database Information](https://docs.servoy.com/reference/servoy-developer/project-file-structure/workspace-folder/resources-directory/database-information-.dbi-files) file so changes are under revision control {% hint style="info" %} **Data Type**\ Servoy uses a powerful data abstraction layer which generalizes SQL data types into six simple types. This ensures that your columns are compatible with any database and can be changed in the back-end at any time. {% endhint %} ## Drop a Column While developers are free to use any DB admin tool to manage their database columns, it's easy to drop a column in Servoy Developer.

To drop a column from your database table, select your column in the Table Editor and click the delete icon. You will be asked to confirm the delete. {% hint style="warning" %} **No Undo**\ When you drop a column from a database table, there is no undo. While you can re-add the column at any time, any data in that column will still be lost. {% endhint %} {% hint style="warning" %} **Unresolved Data Bindings**\ When you drop a column, you may also create errors in your application if you have objects bound to that column, such as a field on a form. These errors can be resolved in Servoy Developer. {% endhint %} ## Manage Column Properties You may use the [Table Editor](https://docs.servoy.com/reference/servoy-developer/object-editors/table-editor) to manage additional properties of the column, such as the [**`Title`**](#user-content-fn-4)[^4] or the [**`Default Format`**](#user-content-fn-5)[^5].

To manage column properties, select your column in the Table Editor and click through the various tabs in the column properties. {% hint style="info" %} **Reference Documentation**\ See the reference docs for a [**complete list of properties**](https://docs.servoy.com/reference/servoycore/object-model/database-server/table/column#properties-summary) of a Column object and how to use them. {% endhint %} ## Auto Enter You can configure the default value for a column by setting a value for the **`Auto Enter`** property. Below are some common examples of Auto Enter settings. To enable an auto-enter setting for a column, select the column in the Table Editor and click the `Auto Enter` tab. Choose the auto-enter type for your column. Below are some examples of the various auto-enter types: #### System Value The System Value auto-enter type will automatically update the column value when the record is created or updated (depending on the type chosen). This setting is ideal for tracking when a record was created or updated and by which users.

For example, to auto-fill a column with creation/modification timestamps or user Ids, select the auto-enter type `System Value` and choose the desired input type, such as `creation server datetime`. #### Custom Value You can auto-fill a column with a declared literal value by choosing the `Custom Value` auto-enter type.

Once you have selected the type, enter the literal value of your choice. #### Lookup Values You can auto-fill a column with a value which is looked-up from elsewhere in your application, including your data model or your business logic by selecting the `Lookup Value` auto-enter type.

Once you have selected this type, click the `...` button to open the Data Provider Chooser. Select your Data Provider or Method and click OK. For example, when an order recorded is created, you can auto-fill the shipping address columns with a value which is looked up from the *related* customer address `orders_to_customers.address`. #### Using Sequences You can auto-fill a column with a value taken from a sequence defined in your database by choosing the auto-enter type `Sequence`. Once you select the sequence option, enter the name of the sequence as it is defined in this table's database. {% hint style="info" %} **Reference Documentation** See the full reference docs for a complete list of options for the [**Auto-Enter**](https://docs.servoy.com/reference/servoycore/object-model/database-server/table/column#auto-enter) feature. {% endhint %} ## Validation You can declare data validation rules at the column level. There are several [built-in validators](#built-in-validators) to choose from, but it is also quite common for developers to implement their own validators in code by writing a [method validator](#method-validator) in JavaScript. #### Built-In Validators To apply validation to your column using one of the built-in validators, select your column in the Table Editor, then click the `Validation` tab.

Column Validation - Numeric Range Validator

Choose the the validator of your choice. Some validators, such as a `Size Validator` or `RegEx Validator` will have an additional input value. For example, to enforce that your `order_details.discount` column is between 0 and 1, you could apply a `Number Range Validator` to your column. {% hint style="info" %} **Reference Documentation** For a complete list of [**column validator properties**](https://docs.servoy.com/reference/servoycore/object-model/database-server/table/column#validation), see the reference docs. {% endhint %} {% hint style="info" %} **When is validation enforced?** It is recommended that you leave the validator property enabled to `execute`*`only`*`on save/validate`. Alternatively, validators will run at any moment a column value changes. While this is more strict and difficult to manage in the user experience. {% endhint %} {% hint style="info" %} **What happens when validation fails?** Failed validation is will apply validation markers to the record. If a save was attempted it will fail. While the declared rule is always enforced, the user experience is not prescribed. The developer may show any validation errors as they see fit. {% endhint %} #### Method Validator You can apply custom validation to a column choosing a Global Method Validator. This essentially binds a column to a custom method that will handle the validation event.

The method will take the new column value as input and evaluate if the value is valid. The method may add validation markers to describe the problem. ```javascript /** * Validate Ship Date * * @param value The value to be validated. * @param {String} dataproviderid The dataprovider name that is being validated (to use for reporting and problem). * @param {JSRecordMarkers} recordMarkers The recordMarkers object to report problems on. * @param customObject The optional customObject given by the caller. * @properties={typeid:24,uuid:"A5B42DDA-9886-4251-992A-D71C199E2191"} */ function isShipDateValid(value, dataproviderid, recordMarkers, customObject) { if(value < recordMarkers.record.orderdate){ recordMarkers.report( 'Ship Date cannot be earlier than Order Date', 'shippeddate', LOGGINGLEVEL.ERROR); } } ``` In this example, the custom logic will check if the `shippeddate` column is earlier than the `orderdate` column and flag the record with an error message. {% hint style="info" %} **Scripted Validation Logic** This guide is concerned with the no-code aspects of setting up a data model. For more information on writing scripting, please see the [**Programming Guide**](https://docs.servoy.com/guides/develop/programming-guide) section on **validation**. {% endhint %} ## Conversion Some scenarios require that a value is stored in a database column in one form and written to and read from the database column in another form. Servoy supports this requirement with a feature called Column Conversion and it has three implementations: [Serialization](#serialization-converters) and [Custom Converters](#custom-converters). #### Serialization Converters Servoy supports object persistence using Serialization, which converts a runtime (JavaScript) object into a String or Binary format, which can then be persisted in a database column. When the column is read from the database, the persistent string will be **deserialized** back into a runtime object. Because Servoy uses JavaScript as its scripting language, runtime objects will be serialized into standard [JSON](https://docs.servoy.com/reference/servoycore/object-model/database-server/table/column) format.

To enable column conversion using serialization, select your column in the Table Editor and click the Conversion tab, then select `StringSerializer` or `BlobSerializer` depending on the column's data type. ```javascript // Construct an object to capture some info in a database column "custom_settings" var obj = new Object(); obj.name = 'foobar'; obj.message = 'Hello World'; // Here it is serialized when it is saved because serialization enabled on the column custom_settings = obj; databaseManager.saveData(); // ...read object properties at a later time... application.output(custom_settings.message + 'My name is: ' + custom_settings.name); ``` When the column is used in code, the data is automatically serialized as shown in this sample. {% hint style="info" %} String Serialization can only be used for column type `TEXT` {% endhint %} {% hint style="info" %} Blob Serialization can only be used for column type `MEDIA` {% endhint %} #### Custom Converters A common use case is to store data in a single standardized unit and allow clients to read and write in a unit of their choice. For example, suppose you store product dimensions in centimeters and allow clients to use centimeters or inches. You could write a custom converter to switch between units.

1. To create a Custom Converter, select you column in the Table Editor and click the Conversion tab. Then select the `GlobalMethodConverter` option. 2. Double-click the `DB-Value-to-Object` event and create a new method in a scope. This handler will convert values as they are read from the database 3. Double-click the `Object-to-DB-Value` event and create a new method in a scope. This handler will convert values as they are written to the database. 4. Set the converted object type. This is needed in situations where the database value is a different data type than the object value. When they are the same, you can leave it "as-is". 5. Implement the custom logic in your methods. **Example**\ Convert from Celsius to client units when reading from the database {% code title="DB-2-Object Converter" %} ```javascript /** * This method converts database values (Celsius) into the current client units for degrees * @parameter {Object} value The value stored in the column * @parameter {String} columnType The data type of the column * @returns {Object} The value that was converted into current client units * @properties={typeid:24,uuid:"63C4D552-531C-48DB-A6C6-ED02F4603C20"} */ function dbToObject(value, columnType) { // evaluate client unit settings switch(tempUnits){ // Already using C, just return it as is case C : return value; // Fahrenheit, use conversion formula case F : return (9/5) * value + 32; // Kelvin, use conversion formula case K : return value + 273; } } ``` {% endcode %} **Example**\ Convert from client units back to Celsius when writing to the DB ```javascript /** * This method auto-converts from client units to Celsius as the value is being written * @parameter {Object} value The value of the runtime object * @parameter {String} columnType The data type of the column * @returns {Object} The value converted into celsius * @properties={typeid:24,uuid:"303ACB93-3B0E-4B9C-9550-D78FF17343C2"} */ function objectToDB(value, columnType) { // evaluate client unit settings switch(tempUnits){ // Already in C, just return it as is case C : return value; // Fahrenheit,use conversion formula case F : return (5/9)*(value-32); // Kelvin,use conversion formula case K : return value - 273; } } ``` ## Additional Resources **Programming Guide** In Servoy, developers can write scripts and program logic using robust data APIs. For mor information see the Programming Guide's chapter, [**Working With Data**](https://docs.servoy.com/guides/develop/programming-guide/working-with-data)**.** **Reference Documentation** See our complete reference documentation on [**Column**](https://docs.servoy.com/reference/servoycore/object-model/database-server/table/column) objects. [^1]: #### Name This is the name or **dataProviderID** of the column derived from the SQL name in the original data source. The name may be prettified (lowercase and underscore) for database vendors having non-standard column naming. Type: [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string)\\ Required: `true` [^2]: #### Type This is the data type of the column. All data types are generalized to one of six [standard types](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jscolumn#constants-summary):\\ * [DATETIME](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jscolumn#datetime)\\ * [INTEGER](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jscolumn#integer) * [MEDIA](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jscolumn#media) * [NUMBER](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jscolumn#number) * [TEXT](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jscolumn#text)\\ * [UUID](https://docs.servoy.com/reference/servoycore/dev-api/application/uuid#uuid) Type: [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number)\\ Required: `true` [^3]: #### Length For certain data types, databases must enforce the amount of storage allocated to single column for a single record. Data types which accommodate variable length entries, such as text, decimal numbers and binary data will have a length property. Servoy will infer and display this property in the column definition. [^4]: #### Title The *Title* property of a column is simply the human-readable name for a column. When a field is placed on a form with the *Place with labels* option, the label's *text* property will be initialized to the *title* property for the column to reach the field is bound. For multilingual applications, it is ideal to populate a column's *title* property with an *i18n message key*, thus allowing field labels to default to message key, which is translated at runtime. [^5]: The *Default Format* property of a column will enforce the formatting that is used when the column is bound to a field element. The field element's format property will assume the *default format* of the column unless it is overridden in the element.