What are custom tables, and why should you care?
As a technology, custom tables have been supported in Act! since version 2008, Service Pack #3, however, despite their relative longevity, only a minority of Act! users are aware of them – and fewer still using them. So what are custom tables, and why should we care about them? To appreciate custom tables properly, we need to understand what Act! is without them.
Out of the box, Act! is comprised of tables for Contacts, Companies, Groups, and Opportunities, and for the most part that is where Act! users store their field data. For the majority, this type of data structure is sufficient, with people storing information like dates of birth, anniversaries, and general contact information quite adequately within these tables.
Transactional Data
For others, however, this type of rigid structure is quite limiting, as Contacts, Groups, and Companies do not support what I call “transactional data”. To illustrate the importance of this distinction, imagine having a contact field in your database for “Last Sale”. The data in this field would continuously be overwritten by the “latest” sales data for this contact, making any sort of history recording for this field difficult, if not impossible.
To illustrate a more complex example of the contrast between rigid and transactional data, I’m going to use the scenario of two mortgage brokers – one using Contact fields to store mortgage details in their database, and the other using custom tables. We could just as easily have used insurance brokers managing policies, or a car dealer tracking inventory, but the mortgage broker scenario is easy for most to relate to. Both brokers will have a Tab in their Act! database dedicated to mortgages, and both setups will support multiple mortgages per customer.
Scenario #1 – Mortgage Data in Contact Fields
The first broker organizes his data similar to the first illustration below, with contact fields for each mortgage’s “lender”, “Funding Date”, “Interest Rate”, “Loan Amount”, “Maturity Date”, and what not, organized in visually appealing rows and columns. At first glance, this setup seems both intuitive and organized, with support for up to four mortgage records per contact. It isn’t.
1. Illustration of storing mortgage data in the Contact Table:
The problem with this setup is Act! is a “contact centric” CRM, and as such it forces you to work with mortgage data like this on a contact by contact basis, and despite appearances, does not actually support more than one mortgage per contact and certainly not a fifth mortgage for this same contact.
For example, if the 1st broker looked up a particular contact and wanted to see their individual mortgage details, this setup would work fine, but if the same broker were to run a simple report on how many mortgages he had funded, or what their total value was, the results would be both incomplete and inaccurate. The problem is no matter which field he’s looking up or how complex his advanced query may be, the search results would invariably be limited to “contacts with mortgages”, as the total contacts and not the total mortgages would be responding to the query. In other words, his search results would never exceed the number of contacts in his database, even when the mortgages did.
So, after spending countless hours inputting all this mortgage data into his Act! database, this broker can’t perform an accurate lookup of any of the mortgage fields he’s using, because he was more concerned with how the data appealed to him visually, than how it worked functionally. The harsh truth is, to get accurate search results on mortgages with this setup, the 1st Broker would have to create a new contact for every new mortgage, even when this approach produced countless duplicate contacts in his database. Trading one mess with another is not a viable database solution.
Scenario #2 – Mortgage Data in Custom Tables
Now compare this fate to that of Broker #2, who recorded all his mortgage information within customs tables like the illustration below. In his database, each of the data lines represented different individual transactions, and each opened up to look like the following form (illustration #3) when drilled down into. All data points of each mortgage are contained within the form for each mortgage, and reporting is a snap.
2. Illustration of storing mortgage data in Custom Table:
3. Illustration of what each of the mortgages look like from the list above when drilled down into.
Custom tables would allow Broker #2 to click on the “mortgages” button on the left-side Navbar, to display all mortgage transactions from past or present, and allow him from the list view to easily filter this list based on whatever criterions he needed. As the illustration below indicates, he could also perform calculations, summaries, and export the results to Excel in a way contact fields couldn’t support.
Please click HERE for a 5-minute video demonstration of this exact scenario.
In fact, Contact fields were never meant to support transactional data, which is why custom tables are a much more efficient, flexible, and robust way to manage them.
So what exactly are custom tables?A custom table is a subentity within your database that nests under Contacts, Companies, Groups, and/or Opportunites. These subentities create a flexible one-to-many data relationship under one or more of these primary tables, creating a spreadsheet type functionality under each. Unlike spreadsheets, though, each row represents a self-contained collection of data that can be linked to one or more contacts, companies, groups, or opportunities, and can also be reassigned to others after creation as needed.
In list view, custom tables look like a spreadsheet within one tab of your database that you can store an infinite amount of transactional information. Each time you needed to add details for a new type of data (whether it be a mortgage, insurance policy, vehicle, or piece of equipment), click “Add” on that Tab’s toolbar, and up would pop a form where you could enter all the pertinent data for that custom entity. When you clicked save and close, it would neatly add another line to your Tab’s spreadsheet, and this process can be repeated as often as needed.
This custom entity can also simultaneously be linked to the contact’s Company, as well, allowing you to view them at the contact level, or roll up to see aggregate totals at the company level. For example, you could be tracking machines that you sold to different contacts within a company, but then pivot to the company level to see all the machines owned by the company as a whole. Powerful stuff.
More about custom tables:
- Custom tables work within all versions of Act! – Pro, Premium, and Web
- Create an unlimited number of functional, manageable “one-to-many” table-format tabs for Contacts, Companies and Groups in Act!
- Custom Tables are part of your database, and therefore fully synchronization enabled
- Convert a custom table look up to a Contact, Company or Group lookup
- Drill down on a custom table list view to access the detail view.
- Allows you to merge to Word or Email from a Custom Table lookup
- Search within tables and filter results for superior lookups
- Click to sort or filter in the list view.
- View related custom table fields side-by-side in the same view.
- Create robust queries and apply date ranges.
- Perform advanced calculations among fields and in the list view.
- Create activities or histories based on custom-table fields.
- Export list views to MS Excel.
- Merge any field, including custom table fields, into MS Word or Outlook
- Nest Tables within tables to further enhance & extend the capabilities of Act!
This has been part #1 on Custom Tables. Stay tuned for our next article and video on managing filters, calculations, and groups with custom tables.