What is missing in my custom field vardef?

Nagy Zoltan

It is not clear from your post so maybe this step has been done but: you cannot create entries in the *_cstm tables using the Extension Vardefs framework. You have to have an entry in the "fields_meta_data" table in the database as well. It is this table that specifies that a field belongs in the *_cstm table. Studio automatically creates this entry when you create a field there. If you just use the Extension Vardefs framework then Sugar will try to add the field to the core table. However, your vardefs explicitly tell it not to do this as you have included the  entry:

$dictionary['Currency']['fields']['c_primary_key_c']['source'] = 'custom_fields';

which tells Sugar the field definition is to be found in "fields_meta_data".

In your snippet, you don't need most of the entries. They should almost all be defined within the entry in the "fields_meta_data" table.

You are able to override existing fields in the *_cstm tables by including entries in a matching Vardefs extension file (if you use Studio you will probably find it also creates one of these as well for a couple of the values that do not appear in the database table definitions).

If you are working On-Cloud and therefore are not able to explicitly use a database INSERT query to create the entry(ies) in "fields_meta_data" then you should use the manifest.php file to create the custom fields entries. Fields defined in the custom fields section of a manifest are created as items in the *_cstm table. Then you can provide a corresponding Vardefs extension file to override any items needed.

I suggest getting a local (maybe from a backup if you are On-Cloud) instance and inspecting the "fields_meta_data" table to see how it is constructed. Check it after adding a field using Studio if you are looking at a clean instance. There are some items in your snippet that are not quite correct for an entry in "fields_meta_data" - specifically the "id" value needs to be unique (globally) so is usually prefixed by the module name in case a field with the same name appears in another module.

Thanks,

JH.

You should try to avoid creating your own custom fields using the vardefs as there are several caveats:

If your installation does not already contain custom fields, you must manually create the custom table. Otherwise, the system will not recognize your field's custom vardef. This situation is outlined in the following section.
You must run a Quick Repair and Rebuild and then execute the generated SQL after the vardef is installed.
You must correctly define the properties of a vardef. If you miss any, the field may not work properly.
Your field name must end with "_c" and have the property 'source' set to 'custom_fields'. This is required as you should not modify core tables in Sugar and it is not permitted on Sugar's cloud service.
Your vardef must specify the exact indexes of the properties you want to set. For example, use: $dictionary['<module singular>']['fields']['example_c']['name'] = 'myfield_c'; instead of $dictionary['<module singular>']['fields']['example_c'] = array(['name' => 'myfield_c');. This will help prevent the system from losing any properties when loading from the extension framework.
The initial challenge when creating your own custom vardef is getting the system to recognize the vardef and generate the database field. This issue is illustrated with the example below:

This is a copy pate from sugar docs. So you CAN create it in cstm. I will have to look into the fields_meta_data and about that ID thing. Thanks

Nagy Zoltan

You can indeed create it in cstm but the issue you have is illustrated with the line:

If your installation does not already contain custom fields, you must manually create the custom table. Otherwise, the system will not recognize your field's custom vardef.

which specifies that if you are using the extension vardefs to create custom fields (i.e. in the *_cstm table) then the custom table itself MUST exist first otherwise the field will not be built. You would need to ensure you had already built the table, with at least the id_c field, before running the QR&R. As you have not yet created any custom fields for your Currencies module, the cstm table will not exist and this is why your vardefs are not building the field for you.

The article you reference, in the developer guide, clearly gives the two ways of programmatically creating the entries in the *_cstm table. One of which is as I outlined above (use the "install_custom_fields()" method in the manifest to create the fields and the table first) and the other is the way you have tried BUT with the caveat that you need to manually create the *_cstm table yourself if this is your first field being added as the QR&R step will not CREATE *_cstm tables, only add fields to tables that exist.

Providing entries in the "fields_meta_data" table using SQL is the equivalent of using the "install_custom_fields()" method - you can use SQL to create the entries if you are On-Premise and you need to use the manifest method if you are On-Cloud. I believe that if you use the method in the manifest then you will find that Sugar actually creates the entries in "fields_meta_data" as part of the loader process.

You are nearly there with getting what you want, there is nothing wrong with your vardefs extension stuff - you just need to do the additional step to make sure the table you need is there as this is your first custom field on that module. You need to choose one of the ways to accomplish that and that depends on whether you want to (or indeed can) use SQL or a Module Loader package 

Thanks,

JH.

[EDIT]

Just for completion, and to help anyone else with this issue finding this at a later date, I should have made clearer:

If the QR&R process finds new entries in the "fields_meta_data" table then it WILL create the *_cstm table if it does not yet exist. It will create this table according to the column values supplied in the table record(s) plus any further data supplied in any extension vardefs. If the table already exists then it will of course simply add the new column(s) to the existing table

If the process finds entries in the vardefs Extensions framework then it WILL NOT create the *_cstm table but it will add or modify columns according to the supplied vardef data. This is because, by definition, the "Extension" vardefs are supposed to "Extend" existing vardefs not "Create" new ones.

John Hawkins

I read the post at

https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_11.0/Data_Framework/Vardefs/Manually_Creating_Custom_Fields/

and it was very helpful in understaning the options - and caveats - for adding fields to the _cstm table.

However, there was one critical piece of information missing.

If I want to use the first option - add custom fields programatically (ie not via vardefs and not via manifest file):

• Where do I put the php file with the call to the install_custom_fields method
• How do I call the php file

OK, two pieces of information ...

Richard Cantin

Where you put the file and how you call it entirely depends on how you are planning to do the work. The above info (creating custom fields programmatically) is there to guide you on using PHP code to do the work of adding the custom fields. That means you are in control of where and how you want this code to run. So you may want to do this on a specific logic hook and thus you include the code in the logic hook, or you may want to add it to a custom API end point that you can call. Another alternative might be to have a module (or admin) action that triggers the code from a button on a module view page. All of these are standard code points and adding code here is explained in the relevant guides. With this approach though you have to bear in mind that the code would be triggered for every fire of the action that invokes it. So each logic hook fire, API call or button click will run the code used to create the fields.

It is not possible (or really desirable) to write PHP code that runs in the Sugar environment on an ad-hoc basis.

If (as I suspect) you actually want to be able to add custom fields as a one-off action then you need to be creating an installable package and using the manifest.php way of adding the fields.

Thanks,

JH.

John Hawkins

Thank you for the quick reply

And yes, you are correct, I want to find a way to add new fields to the custom table for (a variety of) modules, including core modules (Contacts, Events, ...)

I also want to do it in a way that can be shared in a version control system (git).

When I create a filed using Studio, it goes into the _cstm table but there is nothing in the vardefs that lets the new field be captured and re-created with a pull from teh central repository, so I am looking for a way to do it via code or vardefs.

I tried the vardefs but soon realize it required a manual addition to the fields_meta_data table

I have it working by creating the script per the guidance above and setting an entry point for that script, then accessing the script using my browser.  Works like a charm.

But that too cannot be captured in a git pull without special instructions. 

Is there a way that a script can be called when a Quick Repair and Rebuild is done?

If so, I could put this file into the custom directory, have the QR&R cal it after the git pull, and include a script to delete the php script after it has been used.

What do you think?

Is there a way to have a scriopt called when a Quick Repair and Rebuild is run?

Richard Cantin

From your reply I can assume you are working on an On-Premise instance (i.e. in your own platform, not Sugar Cloud) as you manage the updates with a pull from Git. This is how we also manage On-Prem instances for customers.

Our way is to include a file in Git called "field_meta_data.sql" which either adds to or rebuilds the fields_meta_data table as a SQL script. As you say you use a Git pull rather than continuous integration tools, then you are similar to us there as well. We simply run the SQL script manually as part of the pull / update / QR&R process.

If you want to fully automate this without the use of CI tools like Jenkins etc. then you are going to be a bit limited. If you really want to try then I would suggest looking at the application logic hooks and try to create your process for a certain application action. You could also look at User or Module hooks and use some flag field (that can be reset by your SQL script) to identify when to pull in the new fields_meta_data - actually if this is your process then you would probably use the programmatic way of adding the fields rather than a SQL update.

You are able to run scripts as part of the Module Loader function but not as part of a Git pull by default so you need to create the action in Sugar that does that part at the correct time.

For your last sentence: you need to update the fields_meta_data before you run the QR&R, not after of course.

There are probably a few ways to achieve what you are trying to do but I would say that none would be as clean as using a proper tool like Jenkins / Hudson to do the work for you.

Thanks,

JH.

Richard Cantin

From your reply I can assume you are working on an On-Premise instance (i.e. in your own platform, not Sugar Cloud) as you manage the updates with a pull from Git. This is how we also manage On-Prem instances for customers.

Our way is to include a file in Git called "field_meta_data.sql" which either adds to or rebuilds the fields_meta_data table as a SQL script. As you say you use a Git pull rather than continuous integration tools, then you are similar to us there as well. We simply run the SQL script manually as part of the pull / update / QR&R process.

If you want to fully automate this without the use of CI tools like Jenkins etc. then you are going to be a bit limited. If you really want to try then I would suggest looking at the application logic hooks and try to create your process for a certain application action. You could also look at User or Module hooks and use some flag field (that can be reset by your SQL script) to identify when to pull in the new fields_meta_data - actually if this is your process then you would probably use the programmatic way of adding the fields rather than a SQL update.

You are able to run scripts as part of the Module Loader function but not as part of a Git pull by default so you need to create the action in Sugar that does that part at the correct time.

For your last sentence: you need to update the fields_meta_data before you run the QR&R, not after of course.

There are probably a few ways to achieve what you are trying to do but I would say that none would be as clean as using a proper tool like Jenkins / Hudson to do the work for you.

Thanks,

JH.