July 25, 2013

SugarCRM relate field vs 1-2-many relationship

I have been using studio to add custom relate fields to my modules, but until now I wasn’t really aware of its downsides. It seems that Studio is making really nasty shortcuts when it creates a relate field which impacts the ways you can access related fields from the API for example. And when I say impacts, it means it’s basically impossible. So let’s have a look what Studio does when you add a relate field.

Studio creates 2 fields: one called related_user_c and one called user_id6_c. Here are their definitions:

They are linked together via the id_name element of the related_user_c definition. After ‘Repair and Rebuild’ we gonna end up with an user_id6_c column in the table. It all seems to be working well, until you try to use the SugarCRM API. There is now way to fetch data from the related field in one API call. All you can do is to fetch the id from the user_id6_c column and then knowing that it actually links back to Users module fetch the details. It’s all because Studio did not create a proper relationship with the Users module. So, let’s have a look how we could do it with a 1-2-many relationship built in code. Instead of using the studio, let’s just build the required fields manually in the code. For a brand new module I recommend you do that in the modules directory –  modules/<module_name>/vardefs.php, for an existing one create a file in custom/modules/Accounts/Ext/Vardefs/. I prefer to have every field definition in a separate file. It’s up to you. You will need 3 fields – one storing an id in the database – related_user_id, one is gonna be a relate field used on the forms to present the field – releted_user_name and the final one is gonna be a link, that API can use to fetch related record details – requested_by_link. They will roughly look like this:

Please note the fields are linked to each other – releted_user_name links to the releted_user_id field using id_name element and links to the releted_user_link field using link element. releted_user_link field links to releted_user_id using id_name element and links to a relationship called accounts_related_user using relationship element. This relationship doesn’t exist yet, we still have to create it. It’s gonna roughly look like this:

Again, note that it links to the releted_user_id field via rhs_key element.


Now when you repair and rebuild the field will be added to the database and you can add it to your views – edit, details and list views. And most importantly you can access the related field details through the API, by using the link we have created – just pass it through using link_name_to_fields parameter in your request, something like this:

Leave a Reply

Your email address will not be published. Required fields are marked *