I have said it before but will say it again … a HUGE thank you to @p.konetskiy for helping figure this out. Many posts in this and the Sugar forum tried to make sense of the topic but did not uncover some key things the way p.konetskiy did.
So, if you want to add fields to the _cstm database table instead of the core database table for a module - and you want to know why this is a good idea for core modules - and you do not want to use Studio - and you want to know why using Studio could create problems - read on.
It is a long-winded explanation but certainly instructive.
You can stop reading after reading the “Adding via vardefs” section if you want. That provides a very viable (preferrable) way to do it. The other options work as well so they have been included for completeness.
If you want to duplicate/transfer your SuiteCRM instance to another location/hoster, you can tar the SuiteCRM directory, mysqldump the sql database, take the files to the new location, untar the directory, run the sql file to duplicate the database, update the .htaccess and config file(s) and you have a duplicate system. If this is how you plan to duplicate/restore your system, you do not need the methods shown in this section, but you may want to read it and adjust your technique.
If you use a version control system (git) to duplicate your system in another location you definitely want to read this. If you use Studio to create new fields in your SuiteCRM installation, and someone tries to pull from your central repository and duplicate your setup, any fields created in Studio will NOT be recreated in the local system since Studio does not store the vardef definition in the custom directory for the custom fields it creates. Studio puts the configuration for its custom fields directly into the cache/ directory so after a pull and Quick Repair and Rebuild, the custom fields are not recreated. No, it is not a good use of a cache/ directory but that is what Studio does and it is one of the reasons you cannot just delete all the contents of the cache directory.
- You could include the sql commands to recreate the custom fields in a dedicated directory to regenerate the custom fields on the local system, but that would require an understanding between all users of the repository of the requirement to do so. And the sql commands may be different, depending on the current and target branch of the local system.
- You could include the full database with each push to the central repository but again, that would require an understanding between all users of the repository of the way to recreate the remote system AND it means the local system has a full copy of the CONTENTS of the database - which you may not want.
- Ideally, you want the pull to bring down all the configuration setup required so that a simple Quick Repair and Rebuild makes the local system complete. The instructions below let you do that with custom fields in the _cstm database table.
SuiteCRM has two database tables for each module: the core table (eg contacts) and another table with a _cstm suffix (eg contacts_cstm). Fields that are part of the original SuiteCRM code are in the core database table. Fields added to the module with Studio have _c appended as a suffix to the field name and go into the _cstm database table. The _cstm database table for a module does not exist until a field is created that would be stored in the _cstm table.
Just to complicate things:
- Fields added in Module Builder go into the core database table for the module, but since Module Builder is used to create a custom module, this is not an issue.
- However, fields added via direct entry in the vardefs file (eg custom/Extension/modules/{modulename}/Ext/Vardefs/{filename}.php) go into the core database table, unless specific parameters in the field definition are included which direct the system to add the new fields to the _cstm table.
Best practice for adding fields to CORE modules in SuiteCRM is to create new fields in the _cstm database table for each core module, not the core database table.
- Installations on cloud SuiteCRM systems likely will not allow you to alter core database tables
- For CORE modules, if you add new fields to the core database table, if SuiteCRM updates a core module and it creates its own new fields in the core database table, you risk your new field having conflict with a future SuiteCRM update. Additionally, there are times when an update to a core module does not just add to a database table but replaces it with the new structure, copying over the old data - but only for fields that were in the original core database table definition.
- Having said this, another developer could add another field to the cstm module database table that has the same name as your new field and you would be back into the conflict issue. This is a risk you can control though (choosing which add-on modules to install). However, it is recommended to ALWAYS have a prefix (I use ayu , the first 3 letters of my company Ayuda) for any new field you create in any module. This minimizes the odds of a conflict in either the core or _cstm database tables.
- When you are creating your own CUSTOM module, you may be better off adding new fields to the core database table for the new module and that is fine since you would use some kind of installer (manifest file, …) to add fields for that new module so all would work fine, even on cloud SuiteCRM systems. In fact, there is no JOIN required in the sql lookups if all fields are in the core database table, so if your custom module has a number of fields, it is preferable, from a performance perspective, to keep all the fields in the core database table.
You can add fields to the _cstm database table by defining them in vardefs like you do for fields going into the core database table. There are special parameters required. The advantage of using this method becomes very obvious when using a version control system (git). When another developer does a pull on the central repository, the custom fields with their database entries are created in the developer’s system with a Repair and Rebuild, unlike the result with custom fields created using Studio, where the field definitions are put into the cache/ directory and NOT recreated with a Repair and Rebuild on the new system.
- The field name must end with the suffix _c so the system knows to look in the _cstm database table for the field
- Each field definition must include
-
'source' => 'custom_fields'
- Each module into which you are adding custom field(s) must have somewhere in the vardefs for that module
-
$dictionary['{ModuleName}']['custom_fields'] = true;
- The field definition - when NOT using Studio - must NOT include
-
'custom_module' => '{ModuleName}'
-
This is created when a field is created with Studio, but if it is included when a custom field is created with vardefs, it conflicts with Studio and causes issue
- The ID for the vardef is created as
-
{Module Name}{field_name with the _c suffix}
- Unlike the post referenced below from the SugarCRM site where it said the fields_meta_data table is still used, when custom fields are created using vardefs, the fields_meta_data table does not have any entries for fields created with vardefs
- Like when creating a field in the core database table using vardefs, when creating a custom field using vardefs, a separate language file is alse required.
- As always, after creating the vardef and language file to define the custom field, a Quick Repair and Rebuild is required
The following example shows how to create a custom text field called ayu_my_custom_field_c in the Events module (FP_events) when the FP_events module did not previously have any vardefs or language files defined in the custom directory. It also works if there were previously defined custom fields, but in that case, you would not have to create the directory structures using mkdir.
You will see that I use a short prefix (ayu_) for my field name. You can use any characters you want - these are the first three letters of my company name . This reduces the chance of a conflict with a field created by another developer using the same field name (without the prefix).
mkdir -p custom/Extension/modules/FP_events/Ext/Vardefs
nano custom/Extension/modules/FP_events/Ext/Vardefs/my_custom_field.php
<?php
$dictionary['FP_events']['fields']['ayu_my_custom_field_c'] = array(
'name' => 'ayu_my_custom_field_c',
'vname' => 'LBL_MY_CUSTOM_FIELD',
'id' => 'FP_eventsayu_my_custom_field_c',
'type' => 'varchar',
'len' => '255',
'size' => '20',
'source' => 'custom_fields',
'help' => '',
'comments' => '',
'default' => '',
'no_default' => true,
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => '0',
'required' => false,
'inline_edit' => '',
'massupdate' => '0',
'audited' => false,
'reportable' => true,
'importable' => 'true',
'unified_search' => false,
'merge_filter' => 'disabled',
'studio' => 'visible',
);
$dictionary['FP_events']['custom_fields'] = true;
mkdir -p custom/Extension/modules/FP_events/Ext/Language
nano custom/Extension/modules/FP_events/Ext/Language/en_us.my_custom_field.php
<?php
$mod_strings['LBL_MY_CUSTOM_FIELD'] = 'It works when done properly';
Admin -> Dev Tools -> Repair -> Quick Repair and Rebuild
Read on if you want to see alternative ways to add fields to the _cstm database table
*
*
You can add custom fields using the manifest file structure like what is created with Module Builder when it is Published
You can add custom fields using a SuiteCRM built-in function.
Details are available at Manually Creating Custom Fields - SugarCRM Support Site
Two steps involved here:
- Create code that will call the install_custom_fields() method in the ModuleInstaller class in the {SuiteCRM_Directory}/ModuleInstall/ModuleInstaller.php (around line 1272)
- Trigger the code BEFORE a Quick Repair and Rebuild is run
Re 1) The code for calling the install_custom_fields() method will look something like the following sample (text field shown):
- The name will have _c added as a suffix by the install_custom_fields() method. If you do add the _c in your name definition, the install_custom_fields() method will recognize that and NOT add another _c suffix.
- The ID in the fields_meta_data table will be created by the install_custom_fields() method and will be {Modulename}{fieldname}_c
<?php
if(!defined('sugarEntry') || !sugarEntry) {
die('Not A Valid Entry Point');
}
$fields = array ();
$fields[] = array(
// Text Field
'name' => 'ayu_program_text_field',
'label' => 'LBL_PROGRAM_TEXT_FIELD_7',// No _c suffix used in LBL…
'type' => 'varchar',
'module' => 'FP_events',
'help' => 'Text Field Help Text',
'comment' => 'Text Field Comment Text',
'default_value' => '',
'max_size' => 255,
'required' => false, // true or false
'reportable' => true, // true or false
'audited' => false, // true or false
'importable' => 'true', // 'true', 'false', 'required'
'duplicate_merge' => false, // true or false
);
require_once('ModuleInstall/ModuleInstaller.php');
$newInstallerBean = new ModuleInstaller();
$newInstallerBean->install_custom_fields($fields);
// Return the user to the Administration menu
SugarApplication::redirect('index.php?module=Administration&action=index');
Notes:
In additional to the field elements that are defined, there are some entries required that are put into fields_meta_data and used by the install_custom_fields() method to add the fields to the _cstm database table
The only field elements that are defined in a _cstm database table are shown below with their type shown after.
id varchar(255)
This is the equivalent of a foreign_key setup so is not restricted to the normal ID length of 36 characters
name varchar(255)
vname varchar(255)
comments varchar(255)
help varchar(255)
custom_module varchar(255)
type varchar(255)
len int
required bool
default_value varchar(255)
date_modified datetime
deleted bool
audited bool
massupdate bool
duplicate_merge bool
reportable bool
importable varchar(255)
ext1 varchar(255)
ext2 varchar(255)
ext3 varchar(255)
ext4
If you have any additional elements you want defined (eg inline_edit or Label value), you need to separately define them in the appropriate custom/… directory as follows. Remember, the field name has _c as the suffix in a _cstm table
For additional elements, add one $dictionary definition per element
cd {SuiteCRM_Directory}
mkdir -p custom/Extension/modules{ModuleName}/Ext/Vardefs/
nano custom/Extension/modules{ModuleName}/Ext/Vardefs/{filename}.php
$dictionary['{ModuleName}']['fields']['field_name_c']['{element}'] = '{element_value}';
- {ModuleName} may be singular; check the other entries in vardefs
- {element} is the element you wish to define (eg inline_edit)
- (element_value} matches the type definition (eg bool = true/false)
For Label defintions
cd {SuiteCRM_Directory}
mkdir -p custom/Extension/modules{ModuleName}/Ext/Language/
nano custom/Extension/modules{ModuleName}/Ext/Vardefs/en_us.{filename}.php
$mod_strings['LBL_...'] = 'The text string you want';
The method for triggering that code is up to you. Options include
Adding a logic hook that when triggered includes code like the above
- Using SuiteCRM APIs
- Manully calling the code from a browser after enabling the code as an EntryPoint
- Add a button/menu to a SuiteCRM GUI that triggers the code when clicked
Make sure that whatever method you use, you only run the code once for each field. If you run it again for the same field, you will be trying to add a field to a _cstm database table when the field already exists in the table and you will get an sql error.
Simplest method: add a file with the above code, define the file as an allowed EntryPoint, and access the file from your browser. You could put the file anywhere; for the purpose of this explanation, I will put it into custom/…
Create the code
cd (SuiteCRM_Directory}
nano custom/addCustomFields.php
//
… add the code shown above, editing to suit your situation …
… one array element defined for each field
//
Enable the page with the code to serve as an Entry Point
mkdir -p custom/Extension/application/Ext/EntryPointRegistry/
nano custom/Extension/application/Ext/EntryPointRegistry/addCustomFieldsEntryPoint.php
<?php
$entry_point_registry['addCustomFieldsEntryPoint'] = array(
// Pointer to the location of the file for which this EntryPoint is defined
'file' => 'custom/addCustomFields.php',
// auth true means person using the EntryPoint must be an authenticated User
// auth false means anyone could the EntryPoint - NOT a good idea usually
'auth' => true,
);
Call the code using your browser
http://{SuiteCRM _Site}/index.php?EntryPoint=addCustomFieldsEntryPoint
This is all that is needed and it will work. The admin has to remember to populate the php code with each field to be added, manually run the code (ONCE for each field to be added) by calling the EntryPoint from their browser, and then remove the field definition from the code once the field has been created.
However, if you are going to use this on an ongoing development basis, I would do the following:
Create the file and the EntryPoint as shown above
Add a submenu to the Repair option in Admin → Admin Tools. We add a new submenu “Create Custom Fields”
mkdir -p custom/modules/Administration
cp -a modules/Administration/Upgrade.php custom/modules/Administration/Upgrade.php
nano custom/modules/Administration/Upgrade.php
Change
<tr>
<td scope="row"><?php echo SugarThemeRegistry::current()->getImage('Repair', 'align="absmiddle" border="0"', null, null, '.gif', $mod_strings['LBL_QUICK_REPAIR_AND_REBUILD']); ?> <a href="./index.php?module=Administration&action=repair"><?php echo $mod_strings['LBL_QUICK_REPAIR_AND_REBUILD']; ?></a></td>
<td> <?php echo $mod_strings['LBL_QUICK_REPAIR_AND_REBUILD_DESC'] ; ?> </td>
</tr>
<tr>
<td scope="row"><?php echo SugarThemeRegistry::current()->getImage('Repair', 'align="absmiddle" border="0"', null, null, '.gif', $mod_strings['LBL_EXPAND_DATABASE_COLUMNS']); ?> <a href="./index.php?module=Administration&action=expandDatabase"><?php echo $mod_strings['LBL_EXPAND_DATABASE_COLUMNS']; ?></a></td>
<td> <?php echo $mod_strings['LBL_EXPAND_DATABASE_COLUMNS_DESC'] ; ?> </td>
</tr>
To (add a new item between them)
<tr>
<td scope="row"><?php echo SugarThemeRegistry::current()->getImage('Repair', 'align="absmiddle" border="0"', null, null, '.gif', $mod_strings['LBL_QUICK_REPAIR_AND_REBUILD']); ?> <a href="./index.php?module=Administration&action=repair"><?php echo $mod_strings['LBL_QUICK_REPAIR_AND_REBUILD']; ?></a></td>
<td> <?php echo $mod_strings['LBL_QUICK_REPAIR_AND_REBUILD_DESC'] ; ?> </td>
</tr>
<tr>
<td scope="row"><?php echo SugarThemeRegistry::current()->getImage('Repair', 'align="absmiddle" border="0"', null, null, '.gif', $mod_strings['LBL_CREATE_CUSTOM_FIELDS']); ?> <a href="./index.php?entryPoint=addCustomFieldsEntryPoint"><?php echo $mod_strings['LBL_CREATE_CUSTOM_FIELDS']; ?></a></td>
<td> <?php echo $mod_strings['LBL_CREATE_CUSTOM_FIELDS_DESC'] ; ?> </td>
</tr>
<tr>
<td scope="row"><?php echo SugarThemeRegistry::current()->getImage('Repair', 'align="absmiddle" border="0"', null, null, '.gif', $mod_strings['LBL_EXPAND_DATABASE_COLUMNS']); ?> <a href="./index.php?module=Administration&action=expandDatabase"><?php echo $mod_strings['LBL_EXPAND_DATABASE_COLUMNS']; ?></a></td>
<td> <?php echo $mod_strings['LBL_EXPAND_DATABASE_COLUMNS_DESC'] ; ?> </td>
</tr>
Add the language definitions
mkdir -p custom/Extension/modules/Administration/Ext/Language/
nano custom/Extension/modules/Administration/Ext/Language/en_us.repairSubMenu.php
<?php
$mod_strings['LBL_CREATE_CUSTOM_FIELDS'] = 'Create Custom Fields';
$mod_strings['LBL_CREATE_CUSTOM_FIELDS_DESC'] = 'Add fields specified in ./custom/AYU_CustomFields/... to _cstm database table(s)';
This provides a menu to trigger the code so users do not have to remember the URL to trigger the EntryPoint directly.
The user still needs to remove the field definition after each use to prevent attempts to create the same field again.
If I was going to use it a lot, I would create a module called AYU_Customfields with ModuleBuilder to store the code and be the target URL for the user access and add code in custom/AYU_Customfields to read files, programatically add the new field definitions to the code, run the program, and set a flag to show that the fields have been added.
- Create the AYU_Customfields module in Module Builder and in that new module’s controller, add an action called add_custom_fields
- Create a new Module AYU_Customfields using ModuleBuilder and Deploy
- Edit/Create a custom controller in that module and add a add_custom_fields action
nano modules/AYU_Customfields/controller.php
if (!defined('sugarEntry') || !sugarEntry) {
die('Not A Valid Entry Point');
}
class AYU_CustomfieldsController extends SugarController
{
public function action_add_custom_fields () {
// insert the above code, edited to suit the situation
// …
// (Optional) add code to run Quick Repair and Rebuild
// from within this script, after creating the new custom fields
// The repairAndClearAll method uses the following arguments
// Argument1 => $selected_actions
// Argument2 => $modules
// Argument3 => $autoexecute (bool = true or false)
// Argument4 => $show_output (bool = true or false)
// require_once('modules/Administration/QuickRepairAndRebuild.php');
// $newFields = new RepairAndClear();
// $newFields->repairAndClearAll(array('clearAll'),array(translate('LBL_ALL_MODULES')), true , true);
}
}
Change the URL in the new menu
From
./index.php?entryPoint=addCustomFieldsEntryPoint
To
./index.php?module=AYU_Customfields&action=add_custom_fields
And in the new module, add a list view to show what field(s) have been added and what field(s) remain to be added, and direct the return after running the code back to the list view (the normal default return).
Add code to store field definitions and flag ones which have already been created
One way to handle this would be to add one file per field to custom/Extension/application/Ext/Extensions/{filename1}.php
A Quick Repair and Rebuild consolidates all these files into one file at custom/application/Ext/Extensions/extensions.ext.php
You could run your check code on either location and set flags to prevent an attempt to add a custom field more than once.
