Database Essentials - Development with the Force.com Platform: Building Business Applications in the Cloud, Third Edition (2014)

Development with the Force.com Platform: Building Business Applications in the Cloud, Third Edition (2014)

2. Database Essentials

In Force.com, the database provides the framework for the rest of your application. Decisions you make on how to represent data have significant consequences for flexibility and maintainability. Understanding the unique behaviors of the Force.com database is critical for successful applications. Force.com operates at a higher level of abstraction than a relational database, so although existing relational database skills are helpful, the Force.com database is a completely different animal.

This chapter covers topics in Force.com database design and development:

Image Overview of Force.com’s database—Get an overview of the Force.com database and how it’s different from standard relational databases.

Image Working with custom objects—Custom objects are components within the Force.com database that store your data. Learn how they are created and then test them by entering and browsing their data.

Image Additional database features—The Force.com database has a few features that are less frequently used but powerful. They include fields defined as aggregates of other fields, fields that limit the valid values of another field, the ability to display the same object differently in multiple user interfaces, and objects that store frequently accessed settings for optimal performance.

Image Sample application—Design a Force.com data model for the Services Manager, implement the objects using Schema Builder, and import sample data.


Note

The code listings in this chapter are available in a GitHub Gist at http://goo.gl/DOy91g.


Overview of Force.com’s Database

This section provides background on the database functionality within Force.com. It covers objects, fields, relationships, queries, and how data is integrated with your application logic. Each Force.com-specific database feature is described and contrasted with its equivalent in a standard relational database.

Objects

Strictly speaking, Force.com does not store objects in its database. Force.com’s objects are more closely related to database tables than they are to anything in object-oriented programming. Objects contain fields, which are equivalent to the columns of a database table. Data is stored in objects in the form of records, like rows in a database table.

Objects belong to one of two categories: standard and custom. Standard objects provide data for Salesforce applications like Sales Cloud or core platform functionality such as user identity. They are built in to Force.com and cannot be removed, although you can extend them by adding your own fields. Custom objects are defined by you, the developer, and you’ll be spending most of your time with them as you build your own applications. Custom objects include custom settings, a close relative of the custom object intended for small amounts of frequently accessed data, like user preferences.

Beyond the name, custom objects differ from their relational table counterparts in some significant ways.

Logical, Not Physical Objects

Unlike relational database tables, custom objects have no physical representation accessible to the Force.com developer. There are no physical storage parameters to tune, no tablespace files to create and manage. The Force.com platform decides how best to represent, index, back up, migrate, scale, and tune your database.

Delegated Operations

For the most part, operational concerns such as performance and reliability are managed entirely by the platform. This means you can design and build an application without worrying how to support it in production.

When you run your own database software and hardware, you inevitably face operational tasks such as backup, recovery, and replication for scalability. Although nothing prevents you from exporting the data from your Force.com instance and backing it up to your own servers, there is normally no reason to do so.


Note

Force.com applications that involve tens of thousands of users, tens of millions of records, or hundreds of gigabytes of total record storage belong to a category called Large Data Volume (LDV) deployments. Such deployments require special architectural considerations to maintain favorable performance. For more information, refer to the whitepaper titled “Best Practices for Deployments with Large Data Volumes” at http://wiki.developerforce.com/page/Best_Practices_for_Deployments_with_Large_Data_Volumes.


Undelete Support

Normally, when a row is deleted in a standard relational database and you need to recover it after a commit, you’re out of luck unless you have backups of the database or are using a database that provides some proprietary technology like Oracle’s Flashback. To avoid this situation, you could implement your own support for undeleting rows, like triggers to copy data to an audit table or a “deleted” column to accomplish a “soft” delete.

In contrast, Force.com provides undelete functionality on every object. When records are deleted, they go into the Recycle Bin, where they stay until they expire (15 days after deletion) and are gone for good or an administrator undeletes them. Deleted records can be queried and programmatically undeleted as well.

Accidentally dropping a table or another database object can also lead to a lot of unpleasant work for a system administrator. If your database vendor doesn’t offer specialized recovery features, you are stuck recovering data from backups. In Force.com, deleting objects sends them to the Recycle Bin. They stay there until they expire or are explicitly erased or undeleted by an administrator. If an object is undeleted, its definition and all its data are restored.

Fields

Fields are like columns in a database. They belong to an object and have a name, label for display purposes, and constraints such as data type and uniqueness.

In Force.com, there are two categories of fields: standard and custom. Standard fields are fields that are created by Force.com for its own internal use, but are also available to users. They can be hidden from view and unused, but not completely removed or redefined. They are a part of the Force.com data model that is static, relied on to exist by other layers of Force.com technology. Examples of standard fields are Id (unique identifier) and Created By (the user who created the record). Custom fields are created by you, the developer, to store data specific to your applications.

Some important differences between Force.com database fields and relational database columns are described in the subsections that follow.

Logical, Not Physical Fields

When you define a new field for your custom object, Force.com does not create a corresponding field in its physical database. Instead, it associates your new field with an existing “Flex” field, a VARCHAR column of its generic data table. This provides Force.com with the flexibility to redefine data types, add richer data types, and perform other processing on the data outside of a database’s typically rigid rules. Although this implementation detail of Force.com is not relevant to learning how to use Force.com’s database, it does help explain some of its underlying behavior.

Unique Identifiers

Typical database tables include one or more columns to contain the primary key, the unique identifier for each row. In Force.com, every object has a standard field called Id. This field is automatically populated with an 18-character, case-insensitive, alphanumeric string to uniquely identify your records. Unique identifiers can also be expressed as 15-character, case-sensitive strings, and this is how they appear in the Salesforce user interface. In most cases, the two styles of unique identifiers can be used interchangeably. So when you are designing your Force.com database, there is no need to add a field to contain a unique identifier.

Validation Rules

Validation rules place restrictions on the values of a new or updated record. They prevent users and programs from inserting data that your application defines as invalid. Rules are defined in an expression language similar to the function language found in the cells of a Microsoft Excel worksheet. The validation rule in Listing 2.1 prevents a record from containing a Start Date greater than its End Date.

Listing 2.1 Sample Validation Rule


AND(
NOT(
ISNULL(Start_Date__c)
),
NOT(
ISNULL(End_Date__c)
),
(Start_Date__c > End_Date__c)
)


When the expression evaluates to true, it is treated as a validation failure. For the rule to evaluate as true, the value in the fields Start_Date__c and End_Date__c must be non-null, and the value of Start_Date__c must be greater than End_Date__c.

Formula Fields

Formula fields contain values that are automatically calculated by Force.com, derived from other fields in the same object or in different objects. They use the same expression language as validation rules.

For example, Listing 2.2 shows a formula for a field called Billable_Revenue__c.

Listing 2.2 Sample Formula Field


Billable_Revenue__c = Week_Total_Hrs__c * Rate_Per_Hour__c


Week_Total_Hrs__c and Rate_Per_Hour__c are custom fields. When a new record is inserted or one of the two fields is updated, the two fields are multiplied, and the result is stored in the Billable_Revenue__c field.

Rich Data Types

Force.com supports a few flavors of the typical string, number, date/time, and Boolean data types. It also supports richer data types that lend themselves to direct usage in user interfaces with prebuilt validation, input masks, and output formatting. The rich types are phone, picklist, multi-select picklist, email, URL, geolocation, and rich text area.

Picklists are particularly valuable, as they address the clutter of “lookup tables” dangling off most relational data models. These lookup tables often contain only a key and description and can be readily replaced with picklist fields. Internally, picklists maintain their own identifiers for values, allowing their labels to be modified without updating the records that reference them.

History Tracking

Most databases do not provide developers a way to track every change made to records in a table. Typically, this is something that is implemented using another table and some code. In Force.com, any object can have History Tracking enabled on it. Every field with History Tracking enabled that is changed gets a new record inserted in a corresponding History object containing the old and new values.


Note

Field history data can be subject to automatic deletion. Organizations created on or after June 2, 2011, retain their history data for 18 months. You can log a case with Salesforce to request a longer retention period. Organizations created before this date retain field history data indefinitely.


Relationships

The capability to define and manage relationships between data entities is the basis for much of the value of relational databases. Relationships allow data from one entity to be logically separated from others. With this separation, data can be modified without integrity loss and combined with other entities for analysis.

Data relationships in Force.com resemble those found in standard relational databases. You can express one-to-one, one-to-many, and many-to-many relationships. But relationships in Force.com are closely controlled and managed by the platform and also integrated with many platform features. Some important points are listed in the subsections that follow.

Integrity Enforced

When you define a relationship in Force.com, a relationship field is created to contain the foreign key. Force.com prevents you from using a foreign key to a different object. It enforces that the foreign key points to an object of the correct type.

This is basic foreign key constraint checking, like in a relational database. The difference in Force.com is that you can never elect to turn it off. It is a mandatory, always-on feature, protecting your data from inconsistency.

There is one minor exception to this rule. Many standard objects contain special fields that can be related to multiple object types. For example, a support case can be assigned to an individual user or a group representing a collection of users. In the Case object, the OwnerId field can contain the ID of a record in the User object or the Group object. Both types of foreign keys are valid. Note that polymorphic foreign key fields are defined by Salesforce and cannot be created by developers.

Explicitly Defined

In Force.com, all relationships are predefined, established when objects and fields are created. With the exception of semi- and anti-joins, you do not specify join conditions when you write queries. Instead, you specify the fields you want, and Force.com takes care of traversing the necessary relationships to retrieve the data.

Query Language

Force.com has two query languages. One is called Salesforce Object Query Language (SOQL) and is used for structured queries. The other language, Salesforce Object Search Language (SOSL), is used for searching the full text of one or more objects.

SOQL

Don’t let the name confuse you. Despite some similarities in syntax, SOQL is very different from SQL. It has more in common with a reporting or object-traversal language than its more mathematically grounded ancestor.

Listing 2.3 shows a sample SOQL query on a custom object. It returns the names, statuses, and expected revenue amounts for the top-ten largest uninvoiced projects started in the last quarter, in descending order by pending revenue.

Listing 2.3 Sample SOQL Query


SELECT Name, Total_Billable_Revenue_Pending_Invoice__c, Project_Status__c
FROM Proj__c
WHERE Invoiced__c = FALSE and Start_Date__c = LAST_QUARTER
ORDER BY Total_Billable_Revenue_Pending_Invoice__c DESC LIMIT 10


The query specifies a list of columns to be returned (SELECT), the object to query (FROM), filter conditions (WHERE), sorting results (ORDER BY) in descending (DESC) order, and a hard limit on the maximum number of rows to return (LIMIT).

Selecting a single object is the simplest type of SOQL query. More advanced queries select fields from multiple related objects, nested resultsets from child objects using subqueries, and perform semi-joins and anti-joins using IN and NOT IN.

The following subsections describe the four most significant differences between SQL and SOQL.

Implicit Join

In SQL, you can join any table with any other table, typically with one or more Boolean expressions involving pairs of columns. Assuming that the data types of the columns in the join expression are comparable, the join query returns the corresponding rows of both tables as specified in your join expression.

In Force.com, data from multiple standard and custom objects can be combined, but only in ways predetermined by you when you designed your database. SOQL itself does not support any concept of joins, other than semi-join and anti-join. Using SOQL, you tell the Force.com platform which fields of which objects to retrieve, and the platform does the work of traversing the data, maintaining the integrity between objects in accordance with the relationships you defined.

This behavior has its pros and cons. You cannot perform truly ad hoc queries, in which data from multiple objects is combined in ways possibly unanticipated by the database designer. But it results in much simpler, more concise queries that can be optimized entirely by the platform.

Nested Resultsets

In SQL, querying two tables in a one-to-many relationship without aggregate functions and GROUP BY results in a cross product of the rows. For example, assume you have a table containing orders and another table with their line items, and issue the query in Listing 2.4.

Listing 2.4 Relationship Query in SQL


SELECT Orders.OrderId, OrderLineItems.LineItemId
FROM Orders, OrderLineItems
WHERE Orders.OrderId = OrderLineItems.OrderId


Assume that there are two orders (1 and 2), each with three line items (1–3 and 4–6). Table 2.1 shows the results of executing the query.

Image

Table 2.1 Results of SQL Join Query

To begin comparing this with Force.com, Listing 2.5 shows an equivalent query in SOQL.

Listing 2.5 Relationship Query in SOQL


SELECT OrderId, (SELECT LineItemId FROM OrderLineItems)
FROM Orders


Note the lack of a WHERE clause to perform the join and the use of a subquery to nest the line items. Force.com is aware of the parent-child relationship between Orders and OrderLineItems, so it performs the join automatically. The result can be visualized as arrays of nested records, as shown in Figure 2.1. The outer record is the order, and each order contains an array of line items.

Image

Figure 2.1 Nested results of SOQL query

No Functions in Column List

You might have included functions like LEFT, RIGHT, MID, LEN, and IFF along with your columns in a SQL SELECT statement. SOQL does not permit functions in the SELECT list. The only exceptions are built-in aggregate functions such as COUNT, which returns the number of records in the query. But aggregate functions can’t be used in a query containing any other fields in the SELECT list.

Governor Limits

Force.com prevents a single user from consuming more than its fair share of system resources. This ensures a consistent level of system performance for all tenants. Limitations placed on resource consumption are called governor limits. A few examples of governor limits are the number of records that can be queried at one time, the amount of memory used by your code, and the size of messages sent between Force.com and external hosts. Some governor limits vary based on the type of licensing agreement you have in place with Salesforce.

SOSL

SOSL provides full-text search capabilities across many objects and fields simultaneously. This is an always inefficient and often impossible task in SOQL. SOSL statements can perform a search over all records, or incorporate SOQL to narrow the search scope and achieve the best of both worlds: structured and unstructured search. The SOSL expression in Listing 2.6 returns the IDs of records in four custom objects that begin with the word java in any of their fields.

Listing 2.6 Query in SOSL


FIND 'java*' IN ALL FIELDS
RETURNING Project__c, Resource__c, Assignment__c, Skill__c


Data Integration

Integration refers to the incorporation of the database into the rest of your application, the business logic, and the user interface. If your application consists solely of stored procedures, there is no integration; your code runs inside the database process and hits database objects directly. More commonly, there are application servers that need to communicate with the database.

With Force.com, either you are coding “on the platform,” which is akin to writing stored procedures, or you are developing a “composite application,” which executes somewhere else but integrates with Force.com data and logic. The following subsections describe how integrating data in Force.com differs from traditional Web application development.

Object-Relational Mapping

In traditional Web application development, one of the most important integration technologies is Object-Relational Mapping (ORM). This layer of infrastructure maps data objects from the database to and from the data structures in your program. Any ORM technology must be well integrated into your development process, efficient at runtime, and flexible in order to accommodate all data access patterns required by your application and allow for future schema changes. Java developers might use Hibernate, Ruby has ActiveRecord, and so forth.

With Force.com, the ORM layer is built in to the platform. Data objects, metadata objects, and queries have direct representation in Apex code. When you create a new custom object, it’s immediately accessible by name in Apex code. If you accidentally mistype the name of a field in your new object, your code will not compile.

For example, the snippet of Apex code in Listing 2.7 selects a single record from the Resource object, updates the value of its Hourly Cost Rate field, and commits the updated record to the database.

Listing 2.7 Apex Code Snippet


public void grantRaise(String resourceName, Decimal newRate) {
Resource__c r = [ select Id, Hourly_Cost_Rate__c
from Resource__c
where Name = :resourceName limit 1 ];
if (r != null) {
r.Hourly_Cost_Rate__c = newRate;
update r;
}
}


Note the use of an in-line SOQL query (in square brackets), the custom object as a first-class object in code (Resource__c), and in-line data manipulation (update statement).

Metadata in XML

Metadata in Force.com is created using one of the platform’s Web-based user interfaces, the Force.com IDE, or the Metadata API. Unlike SQL databases, Force.com does not use Data Definition Language (DDL) but has its own XML schema for metadata. Listing 2.8 shows a simple example of Force.com’s XML metadata.

Listing 2.8 Metadata XML for a Custom Object


<?xml version="1.0" encoding="UTF-8"?>
<CustomObject xmlns="http://soap.sforce.com/2006/04/metadata">
<deploymentStatus>Deployed</deploymentStatus>
<fields>
<fullName>Start_Date__c</fullName>
<label>Start Date</label>
<type>Date</type>
</fields>
<label>Project</label>
<nameField>
<label>Project Name</label>
<type>Text</type>
</nameField>
<pluralLabel>Projects</pluralLabel>
<searchLayouts/>
<sharingModel>ReadWrite</sharingModel>
</CustomObject>


This XML describes an object with a human-readable name of Project. It contains a single custom field called Start Date, of type Date. The Sharing Model of ReadWrite means that all users in the organization can edit the records in the Project object. Force.com provides a Metadata API for importing metadata XML into the platform. This is how development tools such as the Force.com IDE operate.

Generated User Interfaces

In the process of defining a custom object, described in the next section, you will see a number of settings related to the visual appearance of your object. These settings help Force.com generate a user interface for manipulating the data in your object. From here on, this is referred to as the “native” user interface, native meaning that it is built in to Force.com.

Force.com’s native user interface is tightly integrated with your data model. The definitions of your objects, fields, and relationships are combined with additional configuration settings to create full-featured user interfaces that can perform create, read, update, delete (CRUD) operations on your data. Note that the concept of CRUD is also referred to as read, create, edit, delete (RCED) in the Salesforce world.

SOAP and REST APIs

Force.com provides SOAP and REST APIs for accessing data from outside of its platform. Using these APIs, you can run SOQL and SOSL queries, import millions of records at a time, modify records individually or in batches, and query metadata.

Working with Custom Objects

This section describes how to create and manage custom objects in Force.com. This is an introduction to the process, so you can experiment with your own objects and data. It starts with instructions for getting your own Force.com Developer Edition account and gives a brief introduction to the tools available for working with custom objects. The rest of the section covers object creation and customization, field creation, entering and viewing data using the native user interface, and additional database features.

Force.com Developer Edition

To get hands-on with Force.com development, you need a development environment. Environments are known as organizations, or “orgs” for short. Orgs come in different shapes and sizes based on the licensing agreement with Salesforce. Salesforce gives its Developer Edition (DE) away free. DE orgs are full featured but have hard limits on the amount of storage (5MB of data, 20MB of files) and number of users (two full users and three platform-only users). When you are ready to test your application with production data and user volumes, license a Force.com Sandbox or Force.com Enterprise Edition (EE).


Tip

Contact a Salesforce sales representative for more information about the different licensing options for Force.com.


Registration

Visit http://developer.force.com with your Web browser. From this page, there is a link or button to create a free DE account. Complete the sign-up form. Within a few minutes, two emails are sent to the address you provide. The first email is a login confirmation containing a temporary password and a link to log in. The second email is a welcome message to Force.com, with links to resources for developers.

Logging In

Click the login link in the first email. Your browser is directed to a page that forces you to change your password. If there is maintenance scheduled for your organization, you may need to acknowledge it prior to the password change page. Passwords must be at least eight characters long and alphanumeric. Here, you also choose a security question and answer, as shown in Figure 2.2. The security challenge is used in the event that you forget your password.

Image

Figure 2.2 Force.com password change page

At this point, you are logged in to your own Force.com organization.

Tools for Custom Objects

Many tools are available that work with Force.com, created by Salesforce and independent software vendors. But if you’re new to Force.com, it’s best to start with the free tools supported by Salesforce. Unless noted otherwise, all tools are available from the DeveloperForce Web site (http://developer.force.com). After you’re comfortable with the standard tools, explore the range of solutions offered by the Force.com independent software vendor (ISV) community.

Tools for Metadata

Metadata is the description of a Force.com application, from the data model to the user interface and everything in between. In this chapter, the focus is on the data model, and there are three tools available from Salesforce for building it.

Force.com App Builder Tools

App Builder Tools are built in to the native Web user interface of Force.com. They are the easiest and most full-featured tools for working with objects and many other features. When new features are added to Force.com’s database, you’ll find them in the App Builder Tools first. To use App Builder Tools, log in to Force.com and click Setup. In the App Setup area, click Create, Objects.

Force.com Schema Builder

The Schema Builder is a drag-and-drop interface for building and maintaining database schemas. It renders objects and relationships in a standard entity-relationship diagram style. The database for the Services Manager sample application, found later in this chapter, is built with Schema Builder. To use Schema Builder, log in to Force.com and click Setup. In the App Setup area, click Schema Builder.

Force.com IDE

The Force.com IDE is a plug-in to the Eclipse development environment. Its strength is developing Apex code and Visualforce pages and managing the metadata for larger deployments involving multiple Force.com organizations. It provides some functionality for working with custom objects, but the objects are presented in raw metadata XML, not in a friendly user interface. For more information about the Force.com IDE and installation instructions, visit http://wiki.developerforce.com/page/Force.com_IDE.

Tools for Data

Data tools enable you to import and export data in bulk. They are usually used in a migration, in which data from an existing system is loaded into Force.com.

Force.com Data Loader

Data Loader has the richest data import features of any Salesforce-provided data tool. To get the Windows version login to Force.com, visit the Administration Setup area, and click Data Management, Data Loader. There is also a community-supported Mac OS X version athttp://www.pocketsoap.com/osx/lexiloader.

Import Wizard

The Import Wizard is a tool built in to the native user interface. It allows bulk data to be imported as new or updated records of custom objects. To use it, log in to Force.com and click Setup. In the Administration Setup area, click Data Management, Import Custom Objects. The Import Wizard walks you through a seven-step process for getting the data from a comma separated values (CSV) file into Force.com.

Force.com Excel Connector

Excel Connector is an add-in to Microsoft Excel that allows bidirectional data movement between a worksheet and a Force.com object. You can fill an Excel worksheet with records from a Force.com object. In the worksheet, you can change values by simply editing the corresponding cells. The modified values can then be written back to the Force.com object. If you’re an Excel power user, you will appreciate this tool. You can download it at http://wiki.developerforce.com/page/Force.com_Excel_Connector.

Object Creation

The easiest way to understand the object creation process is to try it. Log in to Force.com using your DE account and click Setup. In the App Setup area, click Create, Objects. Figure 2.3 shows the screen as it appears in a new Force.com organization, with no objects yet defined.

Image

Figure 2.3 Custom objects in Force.com App Builder Tools

To begin, click the New Custom Object button.

Object Definition

The first step of building the custom object is its definition screen. The inputs to this screen are as follows:

Image Label—This label is a human-readable name of your custom object.

Image Object Name—This is a very important name. It’s how you refer to your custom object when you write Apex code, formula fields, and validation rules. It’s automatically populated from the label, but it can be set manually. Although not shown on this screen, internally Force.com appends the Object Name with “__c” to designate it as a custom object rather than a standard object.


Tip

Avoid naming your custom object something overly terse or common, and avoid the names of standard objects. It can be difficult to differentiate multiple objects with the same name.


Image Description—It’s good practice to set a description for your object if you’re working with other developers.

Image Context-Sensitive Help Setting—This setting dictates how the Help for This Page link in the corner of every page behaves on your custom object. By default, it shows the standard Force.com help. You can configure it to display a custom Visualforce page instead. Visualforce pages are discussed in Chapter 6, “User Interfaces.”

Image Record Name Label and Format—Every object has a standard field called Name. It’s used in the native user interface as a label for your record. Name can have an Auto Number data type, which causes names to be automatically generated from a pattern, or a Text data type with a maximum length of 80 characters. Name values are not required to be unique.

Image Allow Reports—If checked, this custom object can participate in the reporting feature of Force.com.

Image Allow Activities—If this is checked, users can associate calendar events and tasks to records of your custom object. You can find the calendar and tasks features on the Home tab.

Image Track Field History—If this option is checked, Force.com creates an additional object to store changes to selected fields.

Image Deployment Status (in development, deployed)—If an object is in development status, it’s hidden from the users in your org, except those with the Customize Application permission. Deployed objects become visible to any user, as dictated by the security configuration of the object and org.

Image Object Creation Options—Unlike the other options, which can be changed later, these options are available only when a custom object is first created. Add Notes and Attachments Related List to Default Page Layout allows external documents to be attached to records of your custom object, like attachments on an email. Launch New Custom Tab Wizard is a shortcut for building a custom tab at the same time as you define your object.

After you’ve clicked the Save button on the definition page and clicked through pages concerning the object’s behavior in the user interface, the detail page of your new custom object is shown. It contains a series of bordered boxes with titles. Each box contains configuration options for a different aspect of the object. Most aspects are described in the following subsections.

Standard Fields

Standard fields are automatically part of every object. They are used for platform-wide functions. The Created By, Last Modified By, Name, and Owner fields help provide record-level access control of your data. Data security is discussed further in Chapter 3, “Database Security.”

Custom Fields and Relationships

Custom fields are created by you, the developer, to store data specific to your applications. Custom relationships express associations between the records in a pair of objects, such as a purchase order and its line items. Initially, your object does not contain any custom fields or relationships. After you’ve added some, they are listed here and can be edited and deleted.

Validation Rules

Validation rules define what constitutes a valid record, preventing records that do not conform from being stored in the database. When a validation rule is added, it applies to data coming from anywhere: a bulk import process, a user interface, a Web service call from another application. When validation rules are defined, they are shown in this list and can be edited and deleted.

Triggers

Triggers are much like triggers in relational databases, except written in Apex code. They fire before or after a data manipulation action such as insert, update, delete, and undelete. They can inhibit the action or extend it by acting on other database objects, modifying data, or even calling out to external Web services.

Page Layouts

A page layout brings together all the native user interface elements for a custom object. This includes the buttons along the top and bottom of the screen, the fields displayed, and related lists, which are records of child objects.

Page layouts are assigned to profiles. This allows different user interfaces to be shown to different types of users. For example, you need one user interface for entering a contact for a support case, but a different one for entering job applicant information. Both end up as records in the Contact object, but the user interfaces can appear very different.

Search Layouts

In this section, you can customize the display of your object in the native search user interfaces. Make a point of editing the Tab layout. It’s the most frequently used and worth customizing to save yourself time. The Tab layout displays recently viewed, created, or modified objects on your custom tab. By default, it contains only the Name field.

Standard Buttons and Links

When a custom object is created, a native user interface is also created for that object to enable CRUD operations without coding. The native user interface contains a collection of standard buttons, and this list allows you to override their behavior. With overrides, you can use Visualforce to develop a custom user interface to be shown for actions that require special treatment, such as the creation of a new record in your object.

Custom Buttons and Links

This section allows the definition of one or more custom buttons to appear in the native user interface for your object. For example, you might want to add a Verify button, which would pop up a new window and allow the user to view the results of some analysis performed on the record.

Field Creation

As in object creation, the easiest way to understand field creation is to try it. Return to your custom object detail page and click the New button in the Custom Fields & Relationships section. The first page of the New Custom Field Wizard prompts for field type. The data types can be thought of in terms of seven categories:

1. Text, Text Area, Text Area (Long), Text Area (Rich), Text (Encrypted)—Text fields are varying lengths of Unicode text. Force.com does not allow fields with other encodings. Text stores 1 to 255 characters, Text Area stores the same number of characters but allows line breaks, and Text Area (Long) and Text Area (Rich) store up to 32,000 characters. The Rich Text Area field allows images, links, and basic formatting information to be stored in-line with the text. One limitation of both the Long and Rich Text Areas is that Force.com’s full-text search feature looks at only the first 2,048 characters. The encrypted text field stores up to 175 characters using the Advanced Encryption Standard (AES) algorithm with a 128-bit master key.

2. Picklist, Picklist (Multi-Select)—A picklist is a list of suggested values that is presented to the user. Multi-select enables a user to select multiple values. Record Types can be used to create multiple lists of suggested values for the same field, to be shown to different types of users. Picklist values are not enforced at the database level without the addition of a trigger or validation rule.

3. Number, Percent, Currency, Geolocation—Number can store signed values from 1 to 18 digits long, decimal places included. Currency and Percent are also Numbers but add type-specific formatting, such as a dollar sign. Geolocation stores a latitude and longitude pair formatted as a decimal or in degrees, minutes, and seconds.

4. Checkbox—Checkbox is a Boolean field. It stores a true or false value, and is represented in the native user interface as a check box.

5. Date, Date/Time—In the native user interface, dates are rendered with a calendar picker component and times with a separate, time-masked field with AM/PM selector.

6. Email, Phone, URL—These types are provided to enhance the experience in the native user interface. For example, uniform resource locators (URLs) are clickable and open in a new Web browser window.

7. Relationship (Lookup, Master-Detail)—These define relationships between two objects. They are covered in more detail in the subsection, “Relationship Fields.”

After you’ve established the field type, the detail page is shown. The settings on this page are described here. Note that not all settings are relevant to every data type.

Image Label—The label is the human-readable name of your field.

Image Field Name—Like Object Name, this is an important name. It’s the name used to refer to your field in Apex code, formula fields, and validation rules. It’s automatically populated from the label, but it can be set manually. Field names cannot contain spaces. Although it’s not shown on this screen, internally Force.com appends the Field Name with “__c” to differentiate it from standard fields.

Image Description—Use this text area to document the purpose of your field to other developers.

Image Help Text—If you provide help text for your field, a small blue circle icon containing the letter i is shown beside it in the native user interface. If a user hovers the mouse over this icon, your help text is displayed.

Image Required—If this is checked, a record cannot be saved unless this field contains a value. This applies to records created anywhere, in the native user interface, imported from other systems, and programmatically.

Image Unique—Text and Number fields allow a uniqueness constraint to be applied. If this is checked, new records must contain a unique value for this field, one that does not occur in other records, or it cannot be saved. Like the Required attribute, this is enforced at the database level.

Image External ID—Text and Number fields can be designated as External IDs. By default, the only unique identifier on an object is the standard Id field. But if External ID is checked, your custom field can be used to uniquely identify records. External IDs are also searchable from the Search sidebar. Note that each object can have at most three External ID fields.

Image Default Value—If no value is provided for this field in a new record, this optional expression is evaluated and shown as a default value, but can be overwritten by the user. The expression is written in the same language as formula fields and validation rules. It can be as simple as a static value or a series of calculations performed on other fields.

Relationship Fields

Relationship fields can express one-to-one, one-to-many, and many-to-many relationships between objects. Creating relationships keeps data normalized, but also adds to the complexity of the data model, causing greater complexity in code and user interfaces that rely on it.

There are two types of relationship fields: Lookup and Master-Detail. Lookup relationships are the default choice. They are the most flexible and transparent in their operation. You can create up to 20 of them on a single object, they maintain their own record of ownership, and child records can be reassigned to a new parent. By default, deleting a related record clears the value of the field referencing it. Optionally, the Lookup relationship can be defined to prevent a related record from being deleted.

Master-Detail relationships are useful for enforcing mandatory relationships, in which a child record cannot exist without its parent record. All child records in a Master-Detail relationship must have a parent record specified. When the master record in a Master-Detail relationship is deleted, all associated detail records are also deleted. Up to four nested levels of Master-Detail relationships can be created, counting from the master object to the most deeply nested child object. Master-Detail relationships have some other special behaviors, such as allowing aggregation of child records through roll-up summary fields, discussed later in this chapter.


Tip

When moving to Force.com from a relational database, resist the urge to create an object for every table and expect to join them all together with relationships. Force.com has hard limits on the distance between objects that can be joined together for purposes of user interface, reporting, formulas, and triggers. Queries on a child object can reference a maximum of five levels of parent objects. In the reverse scenario, queries against a parent object can reference only a single level of child objects. There are workarounds, such as using formula fields to consolidate fields from distant objects, but keeping your object and relationship count low pays dividends later in the development process.


Table 2.2 summarizes the differences between Lookup and Master-Detail relationships.

Image

Table 2.2 Comparing Lookup and Master-Detail Relationships

Additional Field Types

Some field types have special behavior, different than simply storing a value. These are listed here:

Image Auto Number—Most databases have an identity or sequence field, a field that automatically increments itself when a new record is added. In Force.com, Auto Number fields are read-only text fields with a maximum length of 30 characters. You define the length, a display format used to generate values, and the starting number. For example, if you define an Auto Number field with a display format of Project-{0000} and a starting number of 100, the Auto Number field in your first record will contain a value of Project-0100.

Image Formula—Formula fields are read-only fields that are calculated by Force.com based on an expression you provide when defining the field. The output of a formula can be a currency, date, date/time, number, percent, or text value.

Image Roll-Up Summary—Roll-up summary fields allow child records in a Master-Detail relationship to be summarized and the result stored in the parent record.

Entering and Browsing Data

One of the happy consequences of building a database in Force.com is that you receive a full-featured data maintenance user interface with near-zero development cost. It is the “native” Force.com user interface. It allows users immediate access to your data with a consistent look and feel, and helps developers visualize and test decisions related to database design.

It’s good practice to use the native user interface to test your data model by creating records with dummy values. This helps identify missing fields, nonintuitive page layouts, and additional validation rules needed. After your object contains some records, browse them using Views and Search. Customize Views to show the optimal set of columns. Usable Views are helpful later in the development process for troubleshooting data problems.

Getting Started

Salesforce often adds new features that users must opt in to use. For example, users must opt in to features that involve significant changes to the user interface. Salesforce recently released a faster, more powerful user interface for working with lists of records and for editing records with fewer clicks. Before starting this section, check to make sure your org has these features enabled. Go to the Setup, App Setup area, click Customize, User Interface, and then check the Enable Enhanced Lists and Enable Inline Editing options; click the Save button.

Entering Data

Custom tabs are containers for developer-defined user interfaces. These tabs, such as the Home tab, are displayed at the top of the page. Tabs are the gateway to the native list view and CRUD user interfaces for an object and can also present entirely custom user interfaces built in Visualforce.

If you have not created a custom tab for your object, do so now by going to Setup and, in the App Setup area, clicking Create, Tabs. Click the New button in the Custom Object Tabs section. In the details page, select your custom object from the drop-down list, pick a tab style, and optionally enter a description. Skip through the remaining pages, accepting the default values.

To create a new record in your custom object, click the Create New drop-down on the left side of the screen and select your object from the list. An edit screen is shown, as in Figure 2.4, which shows editing a new record in the standard object named Contact. This screen is defined by the page layout. Make note of things you don’t like as you enter test data and return to the page layout to fix them. This process is identical for standard and custom objects.

Image

Figure 2.4 Creating a new record in the Contact object

When your new record is saved, the page changes to a view mode. This is also controlled by the page layout. If you’ve enabled Inline Editing, you can double-click the editable fields to change their values.

Browsing Data

Your first encounter with a list of records is usually on the home page of your custom object. Click your custom object’s tab, and you’ll see a section listing recently viewed records. It shows only the Name of your records. To customize this list of recently viewed records to show more fields, go to the custom object definition, Search Layouts section, and edit the tab layout to add more fields. Figure 2.5 shows an example of the Contacts Home tab layout with Name, Account Name, Title, Phone, and Email fields visible.

Image

Figure 2.5 Contacts Home tab

Another way to browse data is a View. A View is a native user interface that displays the records of a single object as a list. It includes such features as sorting, pagination, columns that can be dragged to reorder, and the capability to delete and edit data in-line without switching to another user interface. To define a View, you specify the list of fields to be displayed and, optionally, filter criteria to restrict the list to a manageable size.

To show a View on your own object’s data, click its tab and then click the Go button. This displays the selected View, which is All by default. Unless you’ve already customized your All View, it contains only the Name field. Customizing Views is another task, like building tabs and page layouts, that can increase developer productivity, even if you don’t plan to use the native user interface outside of administration. Figure 2.6 shows a custom object’s View.

Image

Figure 2.6 View of custom object

Additional Database Features

This section introduces a set of features of the Force.com database that are unique to the way the Force.com platform works. Their configuration and behavior build on the definition of objects and fields, extending them to support more complex native user interfaces, calculations performed on groups of records, and the storage of configuration data.

The following features are discussed in this section:

Image Roll-up summary fields—Roll-up summary fields are like formula fields that operate on a group of records, calculating their sum, minimum, maximum, or a record count.

Image Dependent fields—Dependent fields enable the standard “cascading picklist” user interface pattern, in which user selection in one picklist filters the available values in a second.

Image Record types—Record types allow records in a single object to take on multiple personalities in the native user interface. For example, the standard object Account is designed to store information on businesses, but with minor adjustments can support data on individuals as well. This can be accomplished with record types.

Image Custom settings—Custom settings store and manage user preferences, aspects of an application that can be configured by users rather than hard-coded by developers.

Roll-Up Summary Fields

Summarizing data in SQL databases is a routine matter of invoking GROUP BY and an aggregate function like SUM. Force.com’s ad hoc query functionality in SOQL provides data grouping and aggregation, but it is subject to limits regarding the number of records aggregated. For the flexibility to obtain aggregate data regardless of data volume, Force.com requires that it be calculated incrementally, either by the database itself or in Apex code. As a result, it’s best to plan for summary-level data as the database is designed.

Roll-up summary fields are the mechanism for instructing the database that you’d like summary data to be calculated without custom code. You specify the child object to summarize, the function to apply to the child records, and filter criteria on the child records. The database then takes care of keeping the roll-up summary values up to date as child records are created, modified, and deleted. For example, given an Invoice Header object and Invoice Line Item child object, you could use a roll-up summary field on the Invoice Header to maintain a running count of invoice line items.

Roll-up summary fields are added to objects using the same process as adding other types of custom fields. There are additional options to define the summary calculation, which consists of three parts:

Image Summarized object—A drop-down list contains the objects you are permitted to summarize. This is restricted to child objects in a Master-Detail relationship with the object you’re creating the roll-up summary field on. Lookup relationships are not supported.

Image Roll-up type—Select the calculation to be performed on the child records and the field of the child object to perform it on. The fields available in this list depend on the calculation. If your calculation is Sum, the list contains fields of type Number, Currency, and Percent. With Min or Max, you can also summarize Date and Date/Time fields. Note that you cannot roll up other roll-up summary fields or formula fields that contain references to other objects, merge fields, or functions returning dynamic values such as TODAY and NOW.

Image Filter criteria—By default, all records are included in the summary calculation. Alternatively, you can also specify one or more filter criteria to restrict the records involved in the calculation. Build filter criteria by selecting a field to filter, the operator to apply, and the value. If you add more than one criterion, the effect is additive. All filter criteria must be satisfied for the record to be included in the summary calculation.

After you have specified the summary calculation and saved the new field, Force.com begins calculating the summary values on existing records. This can take up to 30 minutes. An icon is displayed beside the field to indicate that the calculation is running.

You can define at most ten roll-up summary fields per object. Make a point of creating them toward the end of your database design process because they make it more difficult to change your objects. For example, you can’t convert a Master-Detail relationship to a Lookup relationship without first removing the roll-up summary fields.

Dependent Fields

Dependent fields are primarily used to define cascading picklists. Cascading picklists are a user interface pattern in which the values in one picklist depend on the selection in another picklist. For example, a picklist for state/province might depend on another picklist for country. When a user selects a country, the state/province picklist is populated with a set of values that make sense given the selected country. In Force.com, the first picklist is called the dependent field, and the second is the controlling field. The controlling field can be a standard or custom picklist (with at least 1 and fewer than 300 values) or a check box field, but cannot be a multi-select picklist. The dependent field can be a custom picklist or multi-select picklist.

A dependent field is an ordinary picklist field with an additional attribute to relate it to a controlling field. To visualize the relationship between the fields, modify your object’s page layout so that the controlling field appears above the dependent field. Then perform the following steps to define the relationship between their values:

1. Navigate to the Custom Field Definition Detail page for the dependent field.

2. In the Picklist Options subsection, click the New link next to the label for Controlling Field.

3. Select the controlling field and click the Continue button.

4. Use the grid to specify which values of the controlling field should be included in the dependent field. Picklist values of the controlling field are shown as columns. Values of the dependent field appear as rows. Double-click individual values to include or exclude them or hold down the Shift key while clicking multiple values and click the Include Values and Exclude Values buttons to make changes in bulk.

Record Types

Record types overload the native user interface behavior of a single object. This allows you to get more mileage out of your existing objects or limit the complexity of a new data model.

For example, Salesforce uses this feature in its CRM product. Person Accounts are a record type of the Account object. Accounts ordinarily store information about businesses, but the Person Account record type adapts Account to store information about individuals. Salesforce opted to overload Account with a record type rather than creating an entirely new object.

Before creating a separate object to represent every business entity, ask yourself if the entity is truly new or merely a slight variation of another entity. Where you find slight variations, consider using a single object to do the work of many. The single object contains a superset of the objects’ fields. The record type of each record determines which variation of the business entity is stored. Force.com consults the record type and the user’s profile to display the correct page layout.

Even if you don’t plan to use the native user interface, record types can expand the flexibility of your data model. By using record types, you gain an additional standard field called RecordTypeId. In custom user interfaces, you can use this to drive different functionality. Of course, you can always add your own custom field to accomplish the same thing, but record types force you to make your design explicit at the native Force.com level and provide tight integration with native Force.com security.

Creating a Record Type

Record types are defined at the object level after an object is created. To manage Record types for custom objects, go to the App Setup area and click Create, Objects; then find the section called Record Types. For standard objects, find the standard object in the App Setup, Customize menu, and within it, click Record Types.

Every object has a default record type called Master. It contains the master list of values for all picklist fields in the object. New record types are cloned from the Master record type if no other record types exist, and given a name, label, and description. Normally, record types are in an active state, which makes them available to users who are creating and editing records. Deactivating a record type is required before it can be deleted.

After a record type is saved, it enters an edit mode. Edit mode permits the maintenance of picklist values for the record type. The list of picklist type fields in the object is shown, with Edit links beside each. These Edit links take you to a screen that allows picklist values to be customized. Here, you can select all, or a subset of the picklist values, and provide a custom default value.

This is just one way to manipulate the picklist values of a record type. When adding new picklist values in an object with more than one record type defined, you are asked which record types they apply to. By default, new picklist values are added only to the Master record type, leaving other record types unchanged.

Custom Settings

Custom settings are a special data storage feature designed for relatively simple, frequently accessed data. The type of data stored in custom settings is ancillary, used to configure or control your application rather than the operational data itself, which belongs in standard and custom objects. For example, user preferences in a Java application might be stored in an XML or properties file. In Force.com, they would be stored in custom settings. Once data is stored in a custom setting, it’s readily accessible throughout the Force.com platform in Apex, Visualforce, formula fields, validation rules, and Web Services API. As an example, a custom setting named Expert might indicate whether a given user receives the default or advanced version of a user interface.

A custom setting is an object definition, much like a standard or custom database object. It consists of a name, a type, and one or more fields. There are two types of custom settings: List and Hierarchy:

Image List—The List is the simpler form, behaving like a database object except for the fact that records are accessed one at a time, by unique name. For example, you might define a custom setting with fields representing configurable options in your application, and each named record representing a collection of those options, such as Test and Production.

Image Hierarchy—The Hierarchy type expands upon the List type, adding the ability to relate data to organization, profile, and user. If a value is not provided for a given level, it defaults to the levels above it. With Hierarchy types, you can create applications that manage settings for individual users, but defer to a profile or organization-wide default when necessary without storing and maintaining redundant, overlapping information.

Using List Custom Settings

The following steps describe how to build a simple custom settings object and manage the values stored in it:

1. Go to the App Setup area and click Develop, Custom Settings. This is where custom settings are defined and their values maintained.

2. Click the New button to define a new custom settings object. Label is the display name for your object, Object Name is the name by which you’ll refer to it in programs. Enter Config Setting as the Label and ConfigSetting as the Object Name. For Setting Type, select List. Visibility controls how this setting behaves when packaged. Leave it as Protected. Use the Description field to explain the purpose of your custom setting to other developers in your organization.


Tip

It’s a good practice to follow a naming convention for your custom settings so that they can be easily differentiated from custom objects. For example, append the word Setting to the end of any custom setting name. The value of naming conventions will become more apparent when you write Apex code that interacts with the database.


3. Click the Save button. Your custom setting is now created and needs some fields and data. Each custom setting can have up to 300 fields.

4. In the Custom Fields section, click the New button to create a new field. Custom settings fields use a subset of the data types available to custom object fields. They are Checkbox, Currency, Date, Date/Time, Email, Number, Percent, Phone, Text, Text Area, and URL. Select Checkbox for your field and click the Next button. For the field label, enter Debug. The Field Name, used to refer to the field in code, is automatically populated. Click the Next button.

5. Click the Save button to finish your field definition.

You’re ready to store values in your custom settings object. Force.com provides a standard user interface for this purpose. Click the Manage button and then the New button. There is a field for the Name of the setting record, which serves as a human-readable identifier for the record. Following the name are the custom fields you’ve defined on the custom setting. In this case, you have a single check box field named Debug. Enter Default for the name, check the Debug box, and click the Save button.

Using Hierarchy Custom Settings

Hierarchy type custom settings provide additional options when storing values. To see them in action, create a new custom settings object called Hierarchy Setting with an object name of HierarchySetting. Again, add a check box field named Debug. The default value of Debug selected here is the organization-level setting, which applies if there are no values defined for a user or profile.

When you’ve finished creating the custom setting, add a new value to it. You are prompted to set the value of the Debug field as with the List custom setting example. But there is an additional system field called Location. Location determines at which level in the hierarchy the setting applies. There are two options: Profile and User. Try to create two custom setting records, one with Debug checked for the System Administrator profile and the other a user in that profile with Debug unchecked.


Caution

There are storage limits on custom settings data. For example, in a Developer Edition organization, you cannot store more than 2MB total in all of your custom settings. To view your current storage usage and the storage limit for your organization, go to the App Setup area and select Develop, Custom Settings.


Sample Application: Data Model

In this section, you’ll build the Force.com database for the Services Manager sample application and import records into it. It begins with a discussion of design goals and a specification of the Services Manager data model. The remainder of the section describes how to implement the data model specification on Force.com and load sample data.

Data Model Design Goals

At a high level, the purpose of the Services Manager sample application is to staff consultants on customer projects based on their skills, and bill the customers for the consultants’ time. This means the Force.com data model must store and manage information about the consultants, customers, projects, staffing assignments of consultants to projects, time spent on projects, and the skills of the consultants. This data model forms the foundation of the Services Manager sample application, implemented piecewise throughout this book, designed to illustrate features of the Force.com platform.

Two other, more tactical goals are described in the subsections to follow.

Optimized for Force.com Developer Edition

A guiding principle of this book is to focus on features available in the free, Developer Edition of the Force.com platform. Although it is possible to build a more realistic version of the Services Manager, one that could form the basis of a production application, it is likely to introduce dependencies on a premium version of the platform. The most notable example of a design decision that impacts licensing cost is user authentication, and it is worth discussing in depth.

In a real-world implementation, each consultant in the Services Manager would be its own user (a record in the standard object named User). This would enable that consultant to log in and view only the information he or she has access to. This granular user identity, authentication, and data access control (covered in Chapter 3) is one of the most valuable features of the Force.com platform, so naturally it is not free for unlimited use. Salesforce charges per user for its product.

Rather than using the standard User object and being subject to license restrictions, the Services Manager implementation is designed around the Contact object. There is no relevant limit on the number of free Contact records, and they are easy to create, with no passwords or activation codes required.

If you have a premium Force.com organization and would like to experiment with the User object, it is a simple migration path from the Contact object. Create a Lookup field on the User object, referring to the Contact object. That way, you can always restrict the Contact to the corresponding User who is currently logged in to Salesforce.

Leverage Standard Objects

There are many advantages to using standard objects wherever possible. They are shared by Salesforce’s CRM applications such as Service Cloud and Sales Cloud, so there are many special features built in to the platform that you can benefit from. Also, if you plan to build or install other applications in your Force.com environment, they likely also leverage these objects. It’s much simpler for applications to interoperate and coexist when they share the same core data objects.

The Services Manager tracks data about consultants and the companies that hire them. This is an excellent fit for the standard objects Contact and Account, respectively. They contain many standard fields for such things as name, addresses, phone numbers, and email address, which can be customized to meet the needs of any application. If the standard fields are not sufficient, you can also add custom fields, the same types of fields you add to custom objects.

Data Model Specification

This section provides the blueprint for building out the data model. As you learn to use the Schema Builder (described in the subsequent section) or an equivalent tool, refer back to this section for the details of the objects, fields, and relationships needed for Services Manager.

The first five subsections cover the objects and their fields. Although relationships are displayed alongside fields in Force.com’s user interface, they are kept intentionally separate from the fields here. Instead, they are covered in the final subsection. It is easier to create relationships when all of the objects being related to each other already exist.

Contact

In the Services Manager application, a Contact record represents a consultant, an employee of the fictional professional services company. Contacts can also store information about a client of the services company. Contacts contain basic information, such as first and last name, email address, phone number, and mailing address. This is already captured by the standard Contact object. Contacts also have information specific to services delivery, such as primary skill, number of years of experience, education, and the hourly cost rate. The full list of custom fields to add to the Contact object is shown in Table 2.3.

Image

Table 2.3 Contact Custom Fields

Project

A project is a unit of work that the customer has contracted. It has financial attributes, such as the number of hours allocated for its completion, the expected revenue, and how billing is to be handled. It also has attributes for tracking its lifecycle, such as start and end date, status, stage, and notes. Table 2.4 contains the list of fields in the Project custom object.

Image

Table 2.4 Project Fields

Assignment

Projects are staffed with resources by the creation of assignments. Assignments associate a resource with a project for a specified period. Assignments contain a status, the role the resource is performing on the project, information about the hours billed and remaining, and expected and actual revenue. All Assignment fields are listed in Table 2.5.

Image

Image

Table 2.5 Assignment Fields

Skill

To ensure that projects are staffed with qualified resources, the application must store information about the skills of each resource. A skill contains a name, type, and numeric rating of the competency level of the associated resource. Table 2.6 provides the list of fields in the Skill entity.

Image

Table 2.6 Skill Fields

Timecard

As resources work on projects, they keep track of their time. The hours spent each day are logged to a timecard. Each timecard represents a week of work on the project. Multiplying the number of hours worked by the internal cost of the consultant produces a cost. You can find the full list of fields in the Timecard custom object in Table 2.7.

Image

Image

Table 2.7 Timecard Fields

Summary of Data Relationships

Table 2.8 lists the data relationships in the Services Manager and the Force.com relationship types corresponding to them.

Image

Table 2.8 Relationships in Services Manager

Figure 2.7 shows the same relationships in a diagram format.

Image

Figure 2.7 Relationship diagram

The two Lookup relationships in the Services Manager are between Account and Project, and Timecard and Assignment. They are Lookup relationships because they are optional. An Account does not require a Project, and a Project does not require an Account. An Assignment does not require a Timecard.

The remainder of the relationships are Master-Detail. In all of them, the child record requires a parent record. For example, Timecard records cannot exist without a corresponding Contact and Project. For mandatory relationships like this, Master-Detail is a good starting point because referential integrity is enforced. If a Project record is deleted, all child Timecard records are also deleted.

You might wonder why Contact and Skill are not a many-to-many relationship. It would be the more normalized way to go. But with the simpler, single Master-Detail relationship, the only repeated field is Skill Type. You can use a picklist field to keep users working from the same list of valid skills and a validation rule to increase data integrity. If Skill had a larger set of its own attributes and they could not be expressed as picklists, it would be a good candidate for a many-to-many relationship.

You should be aware of the following limitations of Master-Detail relationships:

Image Force.com supports a maximum of four levels of cascading Master-Detail relationships. So a child object in a Master-Detail relationship can be the parent of another Master-Detail relationship, and so on. The four-level limit in genealogical terms means that a child can have a great-grandparent object but not a great-great-grandparent. The canonical example of cascading Master-Detail is the purchase order: A purchase order contains one or more line items, and each line item contains one or more line item details.

Image A single object cannot be the child in more than two Master-Detail relationships. When an object is the child of two Master-Detail relationships, that object is referred to as a junction object. It joins two parent objects in a many-to-many relationship. In the Services Manager data model, Assignment and Timecard are junction objects.

In Force.com as in any technology, there are many ways to do the same things, some better than others. Given this first cut of the Services Manager data model, these restrictions on Master-Detail do not seem to be a problem. Incidentally, all the reasons that Master-Detail relationships were chosen can be also satisfied using Lookup fields in conjunction with other Force.com features, to be discussed in later chapters.

Implementing the Data Model

This section walks through the creation of the Services Manager data model in Force.com using Force.com App Builder Tools and Schema Builder. This includes a custom application to contain the user interface components, four custom objects, and the fields and relationships on both the custom and standard objects.

To begin, log in to your DE account and click Setup.

Creating a Custom Application

It’s a good practice to define your custom application first so that you can add tabs to it as you build them. The following steps describe how to create a custom application, assign its tabs, and determine which users can see it:

1. In the App Setup section, click Create, Apps. A list of applications is displayed. Ignore the built-in applications. Most come with the DE account and cannot be removed. Click the New button.

2. Enter a label for the application, a name, and a description, and then click the Next button. The label is the human-readable label for the application, displayed to users. Name is an internal name, used by Force.com at the API level.

3. Optionally, select an image to be displayed as the logo for your application. This image is shown in the upper-left corner when your application is active. When you’re done, click the Next button.


Tip

To prepare an image for use as an application logo, first go to the Documents tab and click the New button. Check the Externally Available Image check box, enter a name to identify the image, and click the Browse button to locate a file on your computer. Click the Save button to upload the image.


4. This screen is for selecting the tabs to be included in the custom application. Home tab is a system-provided tab included in every application and cannot be removed. There are no tabs defined for the application yet, so do nothing here. Click the Next button.

5. You can restrict access to your application by profile, a grouping of user permissions discussed in Chapter 3. For now, grant access to System Administrator by clicking the last check box in the Visible column. Then click the Save button.

You are returned to the list of applications, but it now contains your new application. If you activate your application by selecting it from the list in the upper-right corner drop-down, you’ll see that it contains a single tab, the Home tab.

Creating a Custom Object

The following steps define the custom object for Project:

1. In the App Setup section, click Schema Builder. Initially, all objects, standard and custom, are shown on the canvas. System objects, a subset of standard objects, are not shown.

2. Click the Clear All link to hide all objects from the canvas. This makes it easier to focus on the task.

3. Click the Elements tab. Drag the Object item from the palette on the left onto the canvas. The dialog in Figure 2.8 is shown to capture the details of the new object.

Image

Figure 2.8 Create New Object dialog

4. Enter Project for the Label and Projects for the Plural Label. The Object Name defaults to Project. Enter a one-line description of the object in the Description field. Enter Project Name for the Record Name Label, and leave the data type Text. Check Allow Reports, Allow Activities, and Track Field History; then click the Save button.

5. Now that the object has been created, it’s time to create the fields. Start with the Type field. It is a picklist field, so drag a picklist from the palette on the left to the canvas, dropping it directly onto the Project object.

6. In the resulting dialog, enter Type for the label. When your cursor exits the label, the Field Name is automatically populated. For the list of values, enter Billable. Press Enter to start a new line, and then enter Non-Billable. Click to enable the Use First Value as Default Value option. Click the Save button. You should see the Type field added to the top of the Project object.

Repeat steps 5 and 6 until all the fields of Project, listed in Table 2.4, are created. There will be different options in step 6 depending on the type of the field.

At this point, you have finished defining the first custom object of the Services Manager sample application. To create the remainder of the objects, follow the same steps.


Note

A few of the objects require that the standard field Name be changed from its default type (Text of length 80) to an Auto Number type. This cannot be done within the Schema Builder. Instead, visit the App Builder Tools (Setup, Create, Objects), click the object, click the Edit link beside the standard Name field, and proceed to set the type to Auto Number.


Creating Relationship Fields

The following steps create the Lookup relationship between Project and Account:

1. In the Elements tab in Schema Builder, drag the Lookup relationship type from the palette. Drop it onto the child object. In this case, the child object is Project.

2. In the dialog, enter the Field Label and Field Name. This is typically the name of the parent object. For the Project-Account relationship, the name is Account.

3. In the Related To drop-down list, select the parent object and then click the Next button. The parent object is Account. The Child Relationship Name and Related List Label are automatically set. The dialog should look like Figure 2.9.

Image

Figure 2.9 Create Lookup Field dialog

4. Click the Save button to create the relationship field. A line will indicate the new relationship between the two objects. The fork symbol at one end of the line indicates the child object. In this case, the fork appears on the Project side.

Repeat these steps until all the Lookup relationships listed in Table 2.8 are created. The steps to create Master-Detail relationships are slightly different. The following steps create the Master-Detail relationship between Project and Timecard:

1. In the Elements tab in Schema Builder, drag the Master-Detail relationship type from the palette. Drop it onto the child object. In this case, the child object is Timecard.

2. In the dialog, enter the Field Label and Field Name. This usually refers to the parent object. In the Project-Timecard relationship, the name is Project.

3. In the Related To drop-down list, select the parent object and then click the Next button. The parent object is Project. The Child Relationship Name and Related List Label fields are automatically set, in this case to Timecards. The dialog should look like Figure 2.10.

Image

Figure 2.10 Create Master-Detail Field dialog

4. Click the Save button to create the relationship field. A line will indicate the new relationship between the two objects. The fork symbol at one end of the line indicates the child object. In this case, the fork appears on the Timecard side.

As you build the relationships, the visual representation in Schema Builder should resemble the diagram in Figure 2.7.


Tip

One of the most important parts of creating relationships is making sure that they are created on the correct object. In the one-to-many relationship, the “one” side is the parent, and the “many” side is the child. Always create the relationship field on the child, relating it to the parent. You can always delete the field and start over if you make a mistake.


Repeat these steps for each relationship in Table 2.8. When you’re done, visit the list of custom objects (Setup, Create, Objects). Figure 2.11 shows the list. Compare it with yours, paying particular attention to the values in the Master Object column. This column is showing the Master-Detail relationships. There should be a total of five master objects listed across all of the relationships.

Image

Figure 2.11 Services Manager custom objects list

Creating a Validation Rule

The Skill object requires a new validation rule to enforce that Skill Type field contains a nonempty value. Although this requirement could be configured at the user interface level via a page layout, placing it on the object itself ensures that it is applied consistently across all user interfaces and other channels for data input, such as importing tools. It doesn’t make sense to have a Skill record without a Skill Type. Follow these steps to create the validation rule:

1. Go to the Objects list in App Builder Tools (Setup, Create, Objects) and click the Skill object.

2. Find the Validation Rules heading and click the New button.

3. Set the name to Type.

4. The code in Listing 2.9 checks for an empty picklist value. Enter it in the Error Condition Formula text area.

Listing 2.9 Error Condition Formula for Skill Type Field


ISPICKVAL(Type__c, '')


5. In the Error Message text area, enter “Type must contain a value.”

6. Click the Save button to create the validation rule.

Creating a Custom Object Tab

Custom object tabs are the gateway to all the native user interface functionality for managing data in your custom object. The following steps create a custom object tab for the Project object:

1. Go to the Objects list in App Builder Tools (Setup, Create, Tabs) and click the New button in the Custom Object Tabs heading.

2. The New Custom Object Tab Wizard is now displayed. Select the Project object from the Object field. Click the Lookup icon (magnifying glass) to select a style for the tab and then click the Next button.

3. Visibility of this tab by profile is easy to change later, so leave this screen unchanged and click the Next button. This means the new tab is visible for all profiles.

4. In the Add to Custom Apps screen, click the Include Tab check box at the top to uncheck it for all applications, and then check it for Services Manager only. Click the Save button to complete the creation of the custom tab.

Repeat these steps to create custom object tabs for all four custom objects in the Services Manager.

Setting Field Visibility

New custom fields are hidden by default. They are not visible in user interfaces in Force.com, and they are also invisible to external tools such as Data Loader. To start using these fields, you must first make them visible.

Perform the following steps to make the custom fields in Contact visible:

1. In the Administration Setup area, click Manage Users, Profiles.

2. Click the System Administrator profile.

3. Scroll down to the heading Field-Level Security, and click the View link beside the Contact object.

4. Click the Edit button, and enable all of the check boxes in the Visible column.

5. Click the Save button to commit your changes to the object’s field visibility.

Repeat these steps for the other four objects.

Importing Data

In this section, you will import sample project and resource data into the Force.com database using the Data Loader tool. This process is divided into three stages: preparing the data, importing it, and then verifying it visually using the native user interface. This is certainly not the only way to import data into Force.com, and probably not the easiest. But it employs a free, widely used, fully supported tool from Salesforce that can scale up to support large numbers of records and complex objects.

Data Preparation

Data Loader operates on CSV files. The first line of the file contains a header listing the columns present in the data. The following lines are the body of the data, with each line a record, values separated by commas. You should have access to Microsoft Excel or an equivalent tool for working with CSV files.

To begin, export CSV files for the Project and Contact objects. Because there is no data yet in the database, these files will be empty except for the header line. This serves as a template for the import file, providing an example of the data layout expected by the Data Loader.

To export, perform the following steps:

1. Launch Data Loader. Click the Export button.

2. Enter your username and password and click the Log In button. Make sure your password includes a Security Token appended to it. If you have not yet obtained a Security Token, log in to Force.com using your Web browser; navigate to Setup, My Personal Information, Reset My Security Token; click the Reset Security Token button; and get the Security Token from the email sent to you by Force.com. Click the Next button when your login is completed.

3. Select the Project object to export. Click the Browse button to name the export file and specify its directory. Name the file the same as the object name, and save it where you’ll readily find it, such as the desktop. Then click the Next button.

4. Click the Select All Fields button. Then remove the system fields, which are Id, OwnerId, IsDeleted, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, and SystemModstamp. Click the Finish button.

5. Answer Yes to the confirmation dialog. The export is performed, and a summary dialog is shown. Click the OK button to dismiss it. You now have a CSV file on your desktop containing a single line with the names of the exported fields.

Repeat this process for the Contact object, but this time remove all the standard fields in step 4 except for Id.

You should have two files on your desktop. Create a new worksheet and import contact.csv into it. Repeat this for project.csv.

Listing 2.10 is a sample import file containing five Contact records. In the first column, use the actual Id values from your contact.csv instead of the values shown here. Listing 2.11 contains five sample Project records. Make sure you save the Project and Contact Excel worksheets as two separate CSV files when you’re done. (Note: Only a certain number of code characters will fit on one line on the page. The arrow symbol indicates where code that should be entered as one line is wrapped to the next line.)

Listing 2.10 CSV Import File for Contact


ID,ACTIVE__C,EDUCATION__C,HIGHEST_EDUCATION_LEVEL__C, HOURLY_COST_RATE__C,HOME_OFFICE__C,REGION__C,START_DATE__C, INDUSTRY_START_DATE__C,YEARS_OF_EXPERIENCE__C
003i0000008TTBqAAO,TRUE, University of Chicago,MS,100,Chicago,Central,2/3/2003,6/1/1983, 003i0000008TTBrAAO,TRUE,St. Edwards University,BS,50,Austin,Central,5/15/2006,5/15/2006,
003i0000008TTBsAAO,TRUE,Cascade College,BS,40,Portland,West, 7/1/2008,1/1/2005, 003i0000008TTBtAAO,TRUE,University of Arizona,PhD,120,Tucson,West,10/15/2004,3/1/1992, 003i0000008TTBuAAO,TRUE,Fordham University,MS,125,New York,East,6/28/2007,5/1/1979,


Listing 2.11 CSV Import File for Project


NAME,TYPE__C,START_DATE__C,END_DATE__C,BILLABLE_HOURS__C, CONSULTING_BUDGET__C,EXPENSE_BUDGET__C,INVOICED__C,LOCATION__C, PROJECT_ID__C,NOTES__C,STAGE__C,STATUS__C,STATUS_NOTES__C
GenePoint,Billable,1/12/2015,,800, 200000,20000,FALSE,"Mountain View, CA", ,Phase 2,In Progress,Green,
Grand Hotels & Resorts Ltd,Billable,2/16/2015,,100, 30000,0,FALSE,"Chicago, IL", ,,In Progress,Green,
United Oil & Gas Corp.,Billable,2/9/2015,,500, 75000,10000,FALSE,"New York, NY", ,,In Progress,Green,
Burlington Textiles Corp of America,Billable,2/2/2015,,200, 40000,5000,FALSE,"Burlington, NC", ,,In Progress,Green,
Express Logistics and Transport,Non-Billable,3/1/2015,,0, 0,0,FALSE,"Portland, OR", ,Presales,In Progress,Green,


Data Import

Now that the data is prepared, you’re ready to import it. Launch Data Loader again, log in, and then follow these steps:

1. From the File menu, select Update.

2. Select Contact from the list of Salesforce objects.

3. Click the Browse button and locate your contact.csv file, and then click the Next button.

4. The file structure is verified, and a small dialog is displayed showing the number of records contained in the file. Check to make sure that this matches the number of records you expected. Click the OK button to continue.

5. The mapping dialog takes columns from your file and matches them with fields in the Force.com object. Click the Create or Edit a Map button.

6. The easiest way to create the mapping is to click the Auto-Match Fields to Columns button. Because the import files were actually once export files, the columns should match perfectly. Figure 2.12 shows the result of this mapping. All the available Force.com fields except for OwnerId were mapped to columns of the CSV file. The YEARS_OF_EXPERIENCE__C column has no mapping because it is a Formula field and cannot be modified. Click the OK button to continue.

Image

Figure 2.12 Column-to-field mapping for contact.csv

7. The new mapping is copied to the current mapping screen. Click the Next button.

8. Click the Browse button to locate a directory to save the results of the import. Data Loader creates two files, one containing errors and another containing success messages. Click the Finish button to begin the import and click Yes to confirm.

9. A dialog is shown with the results of the import. If you received errors, click the View Errors button to examine them, fix your import file accordingly, and try the import again.

Repeat this process for project.csv.

Data Verification

Data Loader outputs a CSV file containing the records successfully imported. But a more friendly way to look at the successfully imported data is to log in to Force.com and browse the records using the native user interface.

After you log in, select the Services Manager application from the application drop-down list in the upper-right corner of the screen. It contains six tabs, one for each of the custom objects defined in this chapter plus the standard Accounts and Contacts tabs. Click the Contacts tab and then click the Go button to display the view named All Contacts, which contains all the records of the Contact object.

You should see a list of the contact records you just imported. By default, only the names are shown. You can modify this view to show more fields by clicking the Edit link to the left of the Create New View link and then adding fields in the Select Fields to Display section. Figure 2.13 shows a modified All Contacts View.

Image

Figure 2.13 Modified All Contacts View

Figure 2.14 shows the detail of an individual Contact record. Verify that the currency and dates imported correctly. Notice that the number of years of experience was calculated from the Industry Start Date field.

Image

Figure 2.14 Contact record detail

To complete your rounds, browse to the Projects tab. Manually associate each Project with a parent Account of your choice, and verify that all the field types were imported correctly.

Summary

This chapter engaged you with the Force.com database in areas essential for application development. The skills covered in this chapter should enable you to build various data-driven applications, all through configuration rather than coding. Here are some key points to take forward:

Image The Force.com database is not a standard relational database. It’s a logical database based on Objects and Fields, like Tables and Columns but tuned for business applications and integrated into every feature of the platform.

Image Custom objects are the backbone of development in Force.com. By defining them and their fields, you are also defining a user interface that is programmatically generated by Force.com. This interface allows data to be entered and browsed without coding, while preserving the data integrity called for in your object definition.

Image Services Manager consists of four custom objects and leverages two standard objects: Account and Contact.