Creating SugarCRM Fields through Code

sugarcrmdevelopers —  April 5, 2012

SugarCRM’s Studio tool is great for administrators to point-and-click their way into field creation, but there are alternative methods. Creating fields in code has the advantage of gaining 100% control of the field, which can be especially helpful if you desired, for instance, multiple Flex Relate fields. You also gain the ability to keep the field under version control.

In current versions of SugarCRM, information about custom fields on stock modules is scattered in several places. In the process of creating a new text-input field for Accounts called “Certifications,” we would find that the following would take place

  • a file created at /custom/Extension/modules/Accounts/Ext/Vardefs/sugarfield_certifications_c.php that held some, but not all, of the advanced vardef information (e.g. SugarLogic)
  • after the repair-and-rebuild of the system (which Studio will automatically call), the data from the above file is compiled (with other custom Vardefs, if they are present) into /custom/modules/Accounts/Ext/Vardefs/vardefs.ext.php.
  • the file at /custom/modules/Accounts/language/en_us.lang.php was created or modified with the language data for the Certification field
  • the database table accounts_cstm was created or modified to include a certifications_c field.
  • data was inserted into the the database table fields_meta_data containing the vardef data for the field.

However, suppose we created the vardef file ourselves? And the language file? We can actually author the entire vardef, specifying field names (ever wanted to drop the _c suffix? Maybe for some address fields?). By doing so, we get the entire thing under our preferred VCS. It’s as straight-forward as creating the full field definition array in the first file location. The dificulty for some will come in the database. When you attempt your repair-and-rebuild, SugarCRM will find a vardef that isn’t defined in metadata, and so will incorrectly assume that the field should be appended to the default table (i.e. accounts instead of accounts_cstm). While this works, it’s less upgrade-safe than using the _cstm table.

Let’s go start-to-finish for our Certifications field.

Create the file /custom/Extension/modules/Accounts/Ext/Vardefs/sugarfield_certifications_c.php with the following contents:

$dictionary['Account']['fields']['certification_c'] = array(
    'name' => 'certification_c',
    'vname' => 'LBL_CERTIFICATION_C',
    'type' => 'varchar',
    'len' => '255',
    'comment' => 'List Certification'

This definition is pretty bare-bones, but that’s because we’re using a text field. If you used an enum (dropdown) field type, then you’d be sure to specify the options parameter, and there are several more parameters involving reporting and searching. For a better understanding of the parameters for a particular field type or function, the trick is to find an existing field of the type or function in the system and track down it’s vardefs, usually somewhere like /modules/Accounts/vardefs.php.

That handles the vardefs, so let’s do the same for the relevant language strings. Studio places the language strings it creates in /custom/modules/Accounts/language/en_us.lang.php, but repair-rebuild will do something similar function. Let’s stick with our top-level Extension directory. Create the file /custom/Extension/modules/Accounts/Ext/Language/en_us.certifications_c.php (be sure you have your en_us or language-specific prefix) with the following line:

$mod_strings['LBL_CERTIFICATION_C'] = 'Certifications';

The field and it’s label are defined in the system, but before we run our repair-and-rebuild to activate it, we need to set up the database. We’ll a SQL statement to add the data to fields_meta_data.

INSERT INTO fields_meta_data (id,name,vname,comments,custom_module,type,len,required,deleted,audited,massupdate,duplicate_merge,reportable,importable) VALUES ('Accountscertification_c','certification_c','LBL_CERTIFICATION_C','List Certification','Accounts','varchar',255,0,0,0,0,0,1,0);

Like the vardef itself, this query varies depending on field type. The fields match pretty closely to the vardef itself so you should be able to decipher basic fields.

Now that we have everything defined in the system, similarly but more-so to what Studio would generate for us, we can run repair-and-rebuild. RaR will notice that the actual field hasn’t been set up on accounts_cstm yet, and so will prompt you to do so (providing the query).

/*Checking Custom Fields for module : Accounts */
CREATE TABLE accounts_cstm ( id_c char(36) NOT NULL, PRIMARY KEY ( id_c ) ) CHARACTER SET utf8 COLLATE utf8_general_ci;
/*MISSING IN DATABASE - certification_c -  ROW*/
alter table accounts_cstm add column certification_c varchar(255)  NULL ;

If you already have custom fields for Accounts, the CREATE statement won’t show up, only the ALTER statement. Once you execute the statement, you’ll be informed that Database Tables Are Synced With Vardefs

Your field will then be accessible for use, and you can add it to your views via Studio or via code as normal.

A Note About the Future

SugarCRM announced in a recent community webinar that an goal for the system will be to do away with fields_meta_data and ensure that more aspects of the system are being controlled through code, instead of the current hybrid setup. At that point, there will be one less step in this process.