How to create a custom CRM#

Grist is as easy to use as a spreadsheet, but gives you new powers when data doesn’t fit in a simple grid.

A good example is keeping track of contacts and our conversations with them. For a business, this could be customers, sales leads, or job candidates. For an individual, it could be companies they have applied to in a job search.

In this tutorial, we’ll explain the “Lightweight CRM” example, which you can use as a template for your own contacts, and then show how to build it from scratch. You’ll learn how to:

Exploring the example#

Open the document “Lightweight CRM”, found in Examples & Templates in your Grist home page.

How does this compare to a spreadsheet? These screenshots show the Lightweight CRM example on the left, and a regular spreadsheet with the same data on the right.

 

The difficulty is in the history of notes for a contact. In a two-dimensional grid, you have few options for where to include multiple notes. If you include them as multiple columns, it quickly makes the spreadsheet unwieldy and difficult to navigate.

Grist feels more like an application, but it’s still as versatile as a spreadsheet.

The “Lightweight CRM” example is read-only, but you can make your own editable copy, either copying in full (with the sample data), or as a template (just the structure without the data). Here are a few more points on using your copy as a CRM:

You can use this example as a template for your own contacts. With “Lightweight CRM” example open, click “Make your own copy” in the top bar, and select “Copy as template”. You’ll get an empty document with the same laoyut, and can start filling it in with your own data.

Creating your own#

The rest of this tutorial will show you how to create such a document on your own. It’s a great exercise that will teach you some of the key features of Grist.

To start, we’ll import a file with sample contacts from the Grist home page. First, save this file to your desktop: lightweight-crm-contacts.csv. Then click the “Add New” button on the top left of your Grist home page, click “Import document”, and select the file on your desktop.

import1

You’ll see a table of contacts with sample data. Note that in Grist columns have names.

first-table

Rename this table to “Contacts” by clicking its name in the top bar, and typing the new name.

table-rename

That’s all you need for a simple table of contacts. You can add rows here, or add new columns to associate more data with each contact.

Adding another table#

For our next step, we want to be able to select a contact, and see the list of conversations with that contact. These conversations should be a new table of data. The cue is that it has a different number of rows from the table of contacts.

Create the new table using the green “Add New” button on the top left of your screen, and click “Add Empty Table” in the menu.

add-empty-table

This table will represent interactions with our contacts, so let’s rename it “Interactions” by clicking its default name (“Table1”) on top of the screen, as before.

interactions-table

It’s a good idea to give meaningful names to columns. In this case, for each interaction, we need to know which Contact it refers to, the date, type, and conversation notes. To rename a column, click its header to select the column, and click the header again to edit its name. You can hit the Tab key to continue to renaming the next column.

col-rename

Finally, hit the “+” button to the right of the last column to create one more column, and name it “Notes”.

col-add

Linking data records#

Every record in this table will belong to a particular contact. You set it up by turning the “Contact” column into a reference to the table “Contacts”. Using the triangle in the header of the column “Contact”, open the menu and select “Column Options”.

col-options

In the right panel, use the “Column Type” dropdown to select “Reference”, then under “Data from table”, select “Contacts”.

set-reference1

Each cell in this column will hold a pointer to a row in the “Contacts” table1. While it refers to an entire row, it’s useful to see some particular identifier of that row, so under “Show column”, select “Company”. You’ll see this in action shortly.

set-reference2

Setting other types#

In Grist, every columns has a types. Often the default of Text or Numeric is correct. For our “Date” column, a better type is Date. Click any cell in the “Date” column, and in the right panel, click into the “Column Type” dropdown and select “Date”. If you’d like, you can also choose a different date format right below the type.

set-date

Now, if you click on a cell in the “Date” column and hit Enter, you have a convenient date picker.

Another useful column type for us is “Choice”. Our interactions will be either “Phone”, “Email”, or “In-person”, and it’s useful to list these options. Click into the “Type” column, and in the right panel, set “Column Type” to “Choice”.

set-choice

You’ll see “Choice Values” textbox below. Click it, and enter your choices there, one per line: “Phone”, “Email”, “In-person”.

set-choice-values

Now, if you click on a cell in the “Type” column and hit Enter, you can now choose from among the choices you set, or start typing and use auto-complete.

Linking tables visually#

The next step is to link the two tables visually. Open the “Contacts” page, click “Add Widget to Page”, select widget “Table” and data “Interactions”. In the “Select By” dropdown at the bottom of the dialog, select “CONTACTS”.

add-widget1

This means that choosing a contact will display only the interactions with that contact. Click “Add to Page” to finish.

two-tables

Next, let’s select a contact (let’s use “Douglas LLC” in the fourth row) and add some notes. Type in a date (hint: the shortcut + ; (semicolon) on Mac or Ctrl + ; (semicolon) on Windows inserts today’s date), select a type, and enter a note. As soon as that row is created, the “Contact” column is automatically filled with “Douglas LLC”, thanks to the sections being linked.

add-record1

The note we added is shown only when “Douglas LLC” is selected. We can add more notes for “Douglas LLC”, or add notes for any of the other contacts.

We can now hide the “Contact” column in the “Interactions” table: using the menu in the column’s header, select “Hide Column”. Because the tables are linked, we already see who the notes are for.

hide-column

For longer notes to be convenient, resize the “Notes” column by dragging the right edge of its header. To wrap long notes, open the Column Options, and click the line-wrapping icon.

line-wrap

Customizing layout#

Once you have multiple tables on one screen, the layout of the screen may become an issue. Having many columns in the Contacts table may no longer be convenient. It’s better to lay it out like a custom application: select a contact from a list on the left and see that contact’s details and interactions.

This can be done by using “Add Widget to Page” again. This time, we’ll select the widget “Card” for the table “Contacts”, and for “Select By” will again use “CONTACTS”.

add-widget-card

You can move the resulting sections around to create a convenient layout. Move your mouse to the top left of each section until you see a “drag handle” icon. Use that icon to drag each section into the desired spot relative to other sections.

layout-drag

You can also resize sections by moving the mouse between them to find a dotted line. Drag this line to resize.

Note how the same personal data is now shown in two places on the screen. These are not copies of data, but different presentations of the same data. Changing the data in one place will change it in the other.

Customizing fields#

At this point, we may do some cleanup: hide unneeded columns in the main “Contacts” table and rearrange fields in the Card widget.

A quick way to hide columns is using the right panel. Using the three-dot menu on the top right of the “Contacts” table, select “Widget options”. In the panel that opens, find a list of “Visible columns”. Move the mouse over each column to reveal the “eye” icon. Click it to hide all columns except “Company”.

hide-column-eye

To customize the Card widget, click it. The right panel will show the relevant options. You can select a different Theme, e.g. “Compact”.

theme-compact

The rearrange fields, click “Edit Layout” in the right panel. You can now drag-and-drop fields in the card, resize them, or remove them. Click “Save” once you are done.

edit-layout

In a few short steps, we have gone from a clunky, unwieldy spreadsheet to a concise, elegant record of your interactions in a simple, effective custom application.

To-Do Tasks for Contacts#

The “Lightweight CRM” example has another trick up its sleeve. The “Type” column in the interactions table has an extra choice, “To-Do”. After you talk to a contact, you can add an extra note about what you need to do for the next conversation, and the date when it’s due.

The “Contacts” table makes these To-Do items visible, and sortable by due date. This way you can see at a glance what’s coming up next.

If you are interested in the details of setting it up, expand the section below. For your first introduction to Grist, you are welcome to skip it.

#

To set up To-Do items as in the example, select Column Options for the “Type” column in the Interactions table, and add another choice (“To-Do”) to the list of choices:

add-todo-choice

Let’s pick our contact “Douglas LLC” and add a To-Do item:

add-todo-item

In the “Contacts” table, add two new columns:

col-add2

Rename them to “Due” and “To-Do Items”.

col-rename2

Both these columns are calculated using formulas. Grist has great support for formulas, allowing full Python syntax and many Excel functions. In Grist, a formula always applies to the entire column of data. To enter a formula, click on a cell in the “Due” column, and hit “=” key:

formula-start

In this formula, we want to look up all Interactions for the current Contact whose Type is “To-Do”, then select the one with the earliest Date. Using Python syntax, the formula is:

items = Interactions.lookupRecords(Contact=$id, Type="To-Do")
return min(items.Date) if items else None

Paste it in, or type in. When typing in multi-line formulas, use Shift+Enter to add new lines, and Enter to save.

It’s also a good time to change the type of this column to “Date”. Open Column Options, and select “Date” for the type. You can choose the Date Format right below the type.

date-format

For the “To-Do Items”, enter a formula similarly. In case of multiple To-Do items, this formula will concatenate them, separated by line breaks. Click into the “To-Do Items” column, hit “=” to start typing the formula, and enter

items = Interactions.lookupRecords(Contact=$id, Type="To-Do")
return "\n".join(items.Notes)

Now the To-Do item we entered earlier is visible in the main Contacts table.

Note that the values in these columns are read-only, since they are calculated. To change the due date, find the To-Do item in the Interactions table where you created it.

Sorting tables#

We’ll want contacts with To-Do items to show up first, in order of the Due date. Click the triangle in the header of the “Due” column, and select “Sort A-Z”.

sorting

By default, sorting settings are not saved. The highlighted green button in the top right of the “Contacts” table reminds us of that. To keep this sort order when you reopen the document, save it by clicking that green button and selecting “Save”.

section-menu

Other features#

Grist has more great features, some of which are used in the “Lightweight CRM” example document. To read more about those, follow the links to their documentation.

In an actual business, you’ll need more. Specialized CRM products have tons of features. Their problem is complexity: trying to satisfy the needs of many different clients makes for a complicated product to use. The beauty of Grist is that you can start simple and add only the level of complexity you need, and nothing more.

Other tutorials show how to model more complex data, analyze and chart data, and more.


  1. In the database world, this kind of reference or pointer is known as a “foreign key”.