Database Tables

Database Tables are where all of your system's data (CRM records, user settings, etc.) are securely stored. A table is the fundamental building block of your system's database structure, and can be thought of as a huge spreadsheet containing a bunch of configurable fields (aka columns), each of which holds different types of data depending on its assigned type. Tables can be associated with one another by defining a relationship, for example a relationship between Companies and Contacts allows a company record to connected to many contact records (aka its employees).


All tables are shows in a list. This list can be searched or filtered by the table name by using the space provided at the top of the list. To create a new table, click New table from below the list.


A dialog box will open. Fill out all fields.

  • Table name: The table name must consist of only letters and numbers, and each word should be capitalized. (i.e.. ExampleTableName) 
  • Plural label: Label text used to identify multiple records in the table (i.e.. Contacts)
  • Singular label: Label text used to identify a single record in the table (i.e.. Contact)
  • Table URL: The relative URL to the location where the table's records can be viewed. In most cases this should be left blank.
  • Record URL: The relative URL to the location where a single record in the table can be viewed. In most cases this should be left blank.
  • Icon: The image used to represent the table in various parts of the system interface and navigation.
  • Status: Setting a table to inactive will cause it to not be visible or accessible anywhere in the system.
  • Display in CRM: Allows the table and its records to be accessed from the CRM navigation menu, list and record views.
  • Display in reports: Allows the table to be selected as the primary record type in the Report Wizard.
  • Include in global search: Includes the table in search queries initiated from the system's global search box.
  • Locked: Locked tables can only be modified by a Cool Life CRM administrator.


When fields are completed, click the Save button at the bottom of the box. The newly created table will open.

To update an existing table, chose the table from the list.


All fields will appear in a list form. This list can be searched or filtered by the field name or field type by using the space provided at the top of the list.


To create a new field, click New field, located at the bottom of the fields list. To update an existing field, click on that field from the list. The field information will open to the right.


 

  • Field label: The text displayed for the field in the CRM list and record views and throughout the rest of the system.
  • Database field name: The name used to identify the field internally. Must consist of only letters and numbers, and each word following the first should be capitalized (i.e.. Persons_exampleFieldName)
  • Field type: Determines the type of data the field will accept, as well as how it is displayed and filtered on.
  • Associated LinkedIn field: Associating the field with one provided by the LinkedIn API will cause it to be updated during a LinkedIn import operation.
  • Description: Options help text that will be displayed inside a tooltip for the field.
  • Status: Setting a field to inactive will cause it to not be visible or accessible anywhere in the system. 
  • Required: Required fields must be filled in when creating or updating the record.
  • Indexed: Indexing a field will increase the speed of filter and sort operations in most cases.
  • Unique identifier: If a field is a unique identifier, it will not allow duplicate values to be entered.
  • Display in filters: Allows the field to be filtered on from CRM list views and reports.
  • Display in list view: Displays the field as a column in CRM list views.
  • Display in related list views: Displays the field as a column in CRM list views of other record types which the table is connected to.
  • Display in quick create forms: Displays the field in dialog-based record creation views.
  • Display in client portal: Displays the field and allows editing of it from the client portal's Update Profile Page.
  • Hideable: Allows individual users to hide the field's column in CRM list views.
  • Mass updateable: Allows the field to be mass updated from the CRM list view.
  • Include in global search: Includes the field in search queries initiates from the system's global search box.
  • Read only: Prevents users from entering or modifying the value for the field, and only allows the value to be viewed and searched on.
  • Control field: A table may optionally have a single control field (which must be a drop down), that affects the visibility of dependent fields based on its current value.
  • Dependent Field: Dependent fields are conditionally displayed in the CRM record view, depending on the current value of the table's control field, and their configured dependent responses.
  • Locked: Locked fields can only be modified by a Cool Life CRM administrator.

For certain field types (radio, dropdown, checkbox) responses need to be created. When the Field type is chosen, an area to add these will open to the right.


Add one response at a time in the available area, or add multiple responses at once by clicking on Multiple...


When fields are completed, click the Save field button at the bottom of the page.

Tables can be associated with one another by defining a relationship. For example a relationship between Companies and Contacts allows a company record to connected to many contact records (aka its employees).” I would also note that only Cool Life CRM administrators can create and edit table relationships. Its not something that we ever want the client to do, they can only view the existing relationships
 
Relationships can be viewed by clicking the Relationships tab at the top right hand side of the screen when in any Table.
 
 
The relationships for that table will appear in list form. These relationships are not editable by a system user, and must be created and updated by Cool Life Systems' staff. The current relationships can be viewed by clicking on the name from the list. 
 
 
  • Key field: The field that contains the identifying value of the related table records.
  • Relationship type: Indicated the cardinality of the table relationship.
  • LinkedIn table: The table containing the related records.
  • Foreign key field: The primary identifier field in the related table (Usually rge table's '_id' field.)
  • Join clause SQL: The SQL statement that is used to join the tables in queries (i.e.. 'ON Table_keyField  = LinkedTable_foreignKetField')
  • Data module: A CRM data module may optionally be used to display and allow modification of the record relationships.
  • Dependent link: If a relationship is dependent, when the parent record is deleted, all connected records in the linked table will also be deleted.

 

Have an additional question about any of the tools that you answered? Submit it here, and we will be in contact to help you!

Important: You must check the box above to continue.