Home » Sage CRM Hub » Tables
h

Table of Contents

$

What are Sage CRM Metadata Tables?

$

Use Reporting Tables

$

Use SQL Identity and Next Record ID Functions

$

Use CRMEmailPhoneData Table

$

Link External Databases to Sage CRM

$

Link External Tables to Sage CRM

$

Display External Data within Sage CRM

What are Sage CRM Metadata Tables?

As you look through your Sage CRM database you will notice that almost all the tables are named in a way that makes it very easy to understand what data they store. You will also notice there are a few other tables in your database with names that may not clearly identify their purpose.

All these tables begin with the name ‘custom_’ and are used to store the system metadata. Metadata simply means data about data.

These metadata tables control how your Sage CRM looks and feels to your users. It also saves information about how your data will be displayed and stored in Sage CRM.

In this section, we will briefly describe each one of the Sage CRM metadata tables not related to reporting, which will be covered in the next section.

As always with customization work, if you plan to make any changes to your database, do so only on a test database and only after backing your database up.

The metadata tables in Sage CRM all begin with “Custom_”.

This is done so that they are easy to distinguish from the tables that contain data about your customers. Here is a list of the metadata tables and a description of the data they contain:

  • Custom_Captions: This table contains the caption for your fields. This also contains all of your selection values. For example, if you add a new selection item in Sage CRM for the Communication field Actions, a new record will be added in this table. All of these records can be accessed in Sage CRM by going to Customizations then Translations.
  • Custom_ContainerItems: This table contains information about your Sage CRM’s blocks. Blocks are used in CRM to group screens, list or other blocks together. Your Company and Person summary screens are examples of blocks. They each contain several different screens and the block groups them together so it’s easily displayed on a screen and you can control the order the screens are in. You can access your blocks by going to Customization, to any entity, then to the Blocks tab.
  • Custom_Database: This table contains information about other databases that you have linked into your Sage CRM. If you have integration to another system that has a database backend you will be able to see the information about how Sage CRM is connecting to that database in this table. You can access them in CRM by going to Advanced Customizations then to Database and Tables.
  • Custom_Edits: This table contains information about all your Sage CRM fields. This database holds all the information you see when you create a new field or when you edit an existing field. The column Colp_EntryType contains what type of field it is. Each field type has its own number in Sage CRM. For example, a Colp_EntryType with a value of 10 is a Text field in Sage CRM.
  • Custom_EmailAddress: This table contains information about E-mail aliases setup in Sage CRM. To access this information in CRM, goto E-mail and Documents then E-mail Aliases.
  • Custom_List: This table contains the information about each list found in Sage CRM. To access this information in Sage CRM, go to Customization, select any entity, then click on the List tab.
  • Custom_Pages: This table contains information about a few specific HTLM only screens in Sage CRM. They are various screens that don’t use the normal Sage CRM formatting so they are added to this table as special pages. There is no way to access this information in Sage CRM.
  • Custom_Relationships: This table contains information to assist Sage CRM with linking IDs fields for different tables. Most of the records contain Primary Key-Foreign Key relationships between the tables. There is no way to access this information in Sage CRM.
  • Custom_ScreenObjects: This table contains a list of all the names and connected entities for each Screen, List, Block, Dashboard and Tab. You can access these in Sage CRM by going to Customization, select any entity and go to the corresponding tab.
  • Custom_Screens: This table contains the list of fields for each screen in Sage CRM. This also contains the order the fields are in any code the field uses on a specific screen. You can access this information in  CRM by going to Customization, select any entity, select the screens tab and select any screen.
  • Custom_Scripts: This table contains the data for all Table scripts in Sage CRM. Table Scripts contain code that is trigged when a record for that entity is inserted, deleted or updated. You can access this information in Sage CRM by going to Customization, select any entity and select the Table Scripts tab.
  • Custom_SysParams: This table holds the value for several different global administration settings for the system in Sage CRM. For example, it contains the root directory for reports and library files in Sage CRM and the setting that controls if a user can install the Outlook plugin. You can access these in several sections of the Sage CRM Administration area.
  • Custom_Tables: This table contains the names and IDs for all the tables in Sage CRM. It also saves the prefix for all the tables’ fields and the name of each tables ID field. This is a very useful table to know when working on CRM directly from SQL. This information can only be access directly from the database.
  • Custom_Tabs: This table contains the name, entity and action for each tab in Sage CRM. You can access this information in Sage CRM by going to Customization, select any entity and select the Tabs tab.
  • Custom_Views: This table contains the information about all the views in Sage CRM. If you edit a Sage CRM view directly in SQL with the “Alter View” command, and the metadata is refreshed in CRM, your view changes will be overwritten based on what is in this table. Always modify views in Sage CRM. You can access this information in  CRM by going to Customization, select any entity, and select the Views tab.

It can be a challenge when working in SQL to find information about all these metadata tables, so this outline of the tables should help you get started working directly with the Sage CRM database. Again, always remember to do any testing on a test system and always backup your database before you make any changes to the system.

h

Return to Top

How to Use Sage CRM Tables to Control Reporting

In this section, we will briefly describe each of the tables related to Sage CRM Reporting and for Groups. 

  • Custom_ReportsTables: These tables contain different type of information about the Reports and Groups found in Sage CRM.
  • Custom_ReportBands: Contains the information about which view the report uses and other basic report options.
  • Custom_ReportChart: Contains information about the chart a report uses.
  • Custom_ReportFavorites: Keeps track of what each user’s favorite reports.
  • Custom_ReportFields: Has a list of all the fields used in each report.
  • Custom_ReportGroups: Hold the information about the reports Group By fields in Sage CRM.
  • Custom_Reports: Contains the name title and category of the reports along with some other basic information.
  • Custom_ReportSearches: Saves the information about the default and saved search criteria for different reports.

These tables store the data for both Reports and Marketing Groups. For the most part, our recommendation is that these tables are modified through the interface as the data and relationships can be complex and it’s too easy to inadvertently make a mistake.

h

Return to Top

How to Use the Sage CRM SQL_Identity Table

Each record in your Sage CRM database has a unique identifier called the Record ID. The SQL_identity table is a key part of making this work smoothly and it stores the next Record ID for every table in the CRM database, even the metadata tables.

The SQL_Identity table only contains two columns: the ID of a table and the ID of the next record for that table. The Table ID for each specific table can be found in the Custom_Tables table. The Next ID is a value that gets automatically incremented each time the system adds a record and then runs the stored procedure “CRM_Next_Id”.

If you add any directly to a table through any process that skips the approved Application Programming Interface (API), you MUST update the Next ID field for the corresponding table or you will run into errors in Sage CRM.

To run this stored procedure in SQL simply type:

EXEC CRM_Next_ID

in SQL Management Studios.

If you wish to view the value that is returned from this you can run the following instead:

Declare @id int EXEC @id = CRM_Next_ID
Select @id

You can use the @Id in this example to insert a record into your table with the correct ID.

Notice that each time you run this, it increments the returned number and the corresponding record in the SQL_Identity table is updated with a new Next ID.

h

Return to Top

How to Use the CRMEmailPhoneData table

While this table is not the primary source of phone and email data, it contains all the email and phone information for every company and person. This was done to help with views and reporting.

Every time you add, change, or delete a phone or email record in Sage CRM, this table will be updated.

You may notice two particular views, vCompanyPE and vPersonPE, are referenced in many system views now. These pull directly from the CRMEmailPhoneData table and are designed to make it easier to write queries to retrieve data, as it is designed to skip manually joining in the email/emaillink and phone/phonelink tables each time you need an email address or phone number.

These linking tables contain table IDs (SQL_Identity table) and phone and email types (‘Fax’, Buisness’, etc.) that can make the SQL joins a bit confusing, so the vCompanyPE and vPersonPE views can make life a lot easier.

The stored procedure populate_CRMEmailPhoneData is used to refresh the data in the CRMEMailPhoneData table.

When you do any importing that contains phone and email data directly in SQL, you will need to run this stored procedure once you are done. To run this stored procedure simply type:

EXEC populate_CRMEmailPhoneData

This will clear out and then repopulate the CRMEmailPhoneData based on your current phone/phonelink and email/emaillink records.

Hopefully this information will reduce the challenge of working in SQL to find information about all these metadata tables and stored procedures and help you work with your Sage CRM database.

Again, always remember to do any testing on a test system, and always backup your database before you make any changes to the system.

h

Return to Top

h

Return to Top

h

Return to Top

How to Display External Data with Sage CRM

In the last section, we discussed how to create Table links to external databases in Sage CRM.

In this section, we will cover a quick and easy method to display your external table data within Sage CRM (SageCRM). We will create a Screen for the Sage 300 (Accpac) accounting system customer information by using the Sage 300 ARCUS entity and an Active Server Page to display the Screen within the context of the Company entity.

Follow the steps below to create the Screen.

  1. Login to Sage CRM as the System Administrator or as a user with administrative rights.
  2. Select Administration from the left menu bar.
  3. Select Customization.
  4. Select your external table entity from the Secondary Entities select list at the bottom of the page.
  5. Click on the Screens tab.
  6. Click the New button.
  7. Key in your screen definition (Screen Name, Caption and Associated View).
  8. Add your fields to the screen.

Now we will create the Active Server Page. Follow the steps below.

  1. Create a blank ASP file and name it appropriately. I named mine CompanyARInfo.asp and I placed it in %CRM Install Path%WWWRootCustomPagesCompany folder.
  2. Enter your Javascript code to display your new screen. See my screenshot as an example.

Sage CRM Custom Tables

Finally, create the tab and point it to your new page.

  1. Select Administration from the left menu bar.
  2. Select Customization.
  3. Select the Company entity from the Primary Entities list.
  4. Click on the Tabs tab.
  5. Drill into the Company Tab Group.
  6. Add a new node and point it to your custom page.

Now you can view your new custom page in the system.

Create Sage CRM Custom Tables

h

Return to Top