Field Relationships

Hi All,

Is there a way by which I can relate fields from two different modules?

Eg. I have an Account Phone Field in the Account & also the Target Modules.

Is there a way by which if a user Updates the Phone number in the Account module the same gets updated in the Target Module as well?

Please advise me as I am not a developer.

Thanks

Hi maverick129,

To my knowledge the Target module is in no way related to the Account module. How would you logically see a connection from an entry from the Target module to an entry from the Account module?

Can you describe your use case?

Hi Jan Siero,

Thank you for the reply. I have created a relationship between the two modules (Target & Account).

What I am trying to achieve -

I have a set of targets who are associated with the accounts.

I have a custom field Company Phone Number in the Account as well as the Target module. This is the phone number for the respective account.

If I change the Company Phone Number in the account, I want to get the Company Phone Number field in the Target module to get updated with the new details as well.

Is there a way to achieve this?

Thanks

Hi maverick,

There is a way to achieve this, however it would require some code.

Before describing a possible solution, I would like to know: why have you added an additional phone number field ā€œCompany Phone Numberā€ to the Accounts module? Why donā€™t you want to use one of the existing fields ā€œOffice Phoneā€ or ā€œAlternate Phoneā€ ?

Hi Jan Siero,

The other fields are already in use.

Hi, I give a solution that should work.

Make sure to backup your system before experimenting or trying this on a copy of your live system. The idea is that you create the account_phone fields by code instead of by studio, because you cannot display custom fields in studio in other modules easily.

  • Create the file
custom/Extension/modules/Accounts/Ext/Vardefs/account_phone.php

with the following contents:


<?php
$dictionary['Account']['fields']['account_phone'] = array (
    'name' => 'account_phone',
    'vname' => 'LBL_ACCOUNT_PHONE',
    'type' => 'phone',
    'dbType' => 'varchar',
    'len' => 100,
    'audited'=>true,
    'unified_search' => true,
    'merge_filter' => 'enabled',
    'reportable' => '1',
    'importable' => 'true',
  );
  • Create the file
custom/Extension/modules/Accounts/Ext/Language/en_us.account_phone.php

with the following contents:


<?php
$mod_strings['LBL_ACCOUNT_PHONE'] = 'Account Phone';
  • Create the file
custom/Extension/modules/Prospects/Ext/Vardefs/account_phone.php

with the following contents:


<?php
$dictionary['Prospect']['fields']['accounts_prospects_1_name']['join_link_name'] = 'accounts_prospects_link';

$dictionary['Prospect']['fields']['account_phone'] = array (
		'name' => 'account_phone',
		'rname' => 'account_phone',
		'id_name' => 'accounts_prospects_1accounts_ida',
		'vname' => 'LBL_ACCOUNT_PHONE',
		'join_name'=>'accounts',
		'join_link_name'=>'accounts_prospects_link',
		'type' => 'relate',
		'link' => 'accounts_prospects_1',
		'table' => 'accounts',
		'isnull' => 'true',
		'module' => 'Accounts',
		'dbType' => 'varchar',
		'len' => '255',
		'source' => 'non-db',
		'unified_search' => true,
		'massupdate' => false,
		'studio' => array('detailview' => 'true'),
	);
  • Create the file
custom/Extension/modules/Prospects/Ext/Language/en_us.account_phone.php

with the following contents:


<?php
$mod_strings['LBL_ACCOUNT_PHONE'] = 'Account Phone';
  • Go to admin / repair
  • do a ā€œQuick Repair and Rebuildā€. It will show a Query add the bottom, make sure Execute it.
  • If you want to copy the account_phone numbers from your previously created custom field into your new custom field, then run this query in MySQL:

update accounts as a, accounts_cstm as ac set a.account_phone = ac.account_phone_c where a.id = ac.id_c;
  • Go into studio, module Accounts and delete your accounts_phone field
  • Add the new accounts_phone field to your layouts
  • Go into studio, module Targets
  • You will see the field accounts_phone in the detailview, listview layouts as well as in the basic search and advanced search. You can add this field to the layouts.
1 Like

Hi Jan Siero,

Thank you very much for that.

I created the files as directed, did the quick repair & rebuild, deleted the custom filed & created the new one, however, the new filed is not coming up in either the detail or list view.

Did I miss anything?

Just like to mention, you informed me, there would be a query at the bottom when I do a Quick Repair & Rebuild, I didnā€™t get any such query, the queries were run automatically when I selected the Quick repair & rebuild

Please guide.

Thanks

Hi maverick,

can you verify that you saved the first file in exactly this directory?

custom/Extension/modules/Accounts/Ext/Vardefs

Can you make sure that your web server (apache probably) has read access to this file ?

custom/Extension/modules/Accounts/Ext/Vardefs/account_phone.php
1 Like

You Are Amazing Jan Siero. Dont have words to Thank You. I saw the mistake I made. The file ā€œen_us.account_phone.phpā€ got saved as a Texty instead of PHP. Ijust changed it to PHP & it works exceedingly well. Just too good mate.

Thanks a ton.

Cheers :slight_smile:

Hi Jan Siero,

Once again Thank you for helping sort this for me. How do I replicate the same principle for a Text Area & Checkbox fields?

Thanks

Hi Maverick,

for the parent module (Accounts in this example):

You can use the module builder, create a package, create a module and the create a field of the desired type.

The resulting vardef of this field you can check in

custom/modulebuilder/packages/<packagename>/modules/<modulename>/vardefs.php

Especially, type, dbType and len would be relevant.

For the child module (Targets in this example):

  • make sure that the variable rname corresponds with the variable name of your parent module
  • You might have to adapt dbType and len, but SuiteCRM might use the field definitions of the parent module automatically

Always make sure you do a ā€œRepair and Rebuildā€ after changing your files.

1 Like

thank you all, I solved ā€¦
now step 2 ā€¦
how can I change the color of the date?
Today red
Blue past event
Future black event

thanks !!

Hi Jansiero,

Iā€™m struggling to understand what is happening in the code:

$dictionary[ā€˜Prospectā€™][ā€˜fieldsā€™][ā€˜accounts_prospects_1_nameā€™][ā€˜join_link_nameā€™] = ā€˜accounts_prospects_linkā€™;

I need to do exactly the same but with a different module so I need to understand the logic rather than just cutting and pasting. Iā€™m not a coder but understand the fundementals of db relationships etc so I kind of get whatā€™s happening but not entirely.
Where does [ā€˜accounts_prospects_1_nameā€™] come from?
[ā€˜join_link_nameā€™] = ā€˜accounts_prospects_linkā€™ presumably is a copy and paste of ā€˜join_link_nameā€™=>ā€˜accounts_prospects_linkā€™, in affect?

just trying to understand ā€¦

thanks for your help

regards

Douglas

Hi Douglas,

The field accounts_prospects_1_name is the field that studio created in Prospects to reference to the name field of Account (in this case the name of the company).

If you created a relationship through studio, please check:
custom/Extension/modules/<modulename>/Ext/Vardefs
to see if you spot a new .php file which defines your new relationship. Here you should also see a vardef entry for the ā€œnameā€ field, like above.
If you want to modify an existing relationship in SuiteCRM, then check the file
modules/<modulename>/vardefs.php

The reason that you set the join_link_name for these two fields is to make sure that when the fields are queried, that the created SQL only references the other table once. Instead of ā€˜accounts_prospects_linkā€™. I could have chosen any name, the only important thing is that both fields have the same join_link_name.

If you leave this out and then, for example search for phone number AND name, you will receive all matches with phone number OR name.

It might help if you let us know which relation you are creating and which field you would like to use in the other module.

Hi Jansiero,

thank you so much for your prompt response and for taking the time as well! much appreciated.

so, I found the relationship file that you mentioned and the contents are below. There is a ā€˜nameā€™ field as you said. What I am trying to achieve is the following:

I created a new module called ClientVisits. This has a relationship back to Accounts (one Account to many ClientVisits), details of which are below. in the Accounts module a user can select a Market (this is a custom field I created in Accounts and Studio called it market_c). When a user navigates to the Client Visit module and selects a client visit, I would like this Market field to show on the detail view on the visit, pulling the info from the related Accounts module.

So the field already exists in Accounts as I created it a while ago so presumably I dont need to follow the first step of creating the field? It is a drop-down so not sure if that complicates anything or not?

Then it is how I structure the file with the details below to get Market to show on the ClientVisit detail viewā€¦?

I really appreciate you taking to time to help!
appologies for the formatting but hopefully you can decifer it!

<?php
// created: 2019-09-20 11:29:09
$dictionary["cv_ClientVisits"]["fields"]["cv_clientvisits_accounts"] = array (
  'name' => 'cv_clientvisits_accounts',
  'type' => 'link',
  'relationship' => 'cv_clientvisits_accounts',
  'source' => 'non-db',
  'module' => 'Accounts',
  'bean_name' => 'Account',
  'vname' => 'LBL_CV_CLIENTVISITS_ACCOUNTS_FROM_ACCOUNTS_TITLE',
  'id_name' => 'cv_clientvisits_accountsaccounts_ida',
);

$dictionary["cv_ClientVisits"]["fields"]["cv_clientvisits_accounts_name"] = array (
  'name' => 'cv_clientvisits_accounts_name',
  'type' => 'relate',
  'source' => 'non-db',
  'vname' => 'LBL_CV_CLIENTVISITS_ACCOUNTS_FROM_ACCOUNTS_TITLE',
  'save' => true,
  'id_name' => 'cv_clientvisits_accountsaccounts_ida',
  'link' => 'cv_clientvisits_accounts',
  'table' => 'accounts',
  'module' => 'Accounts',
  'rname' => 'name',
);

$dictionary["cv_ClientVisits"]["fields"]["cv_clientvisits_accountsaccounts_ida"] = array (
  'name' => 'cv_clientvisits_accountsaccounts_ida',
  'type' => 'link',
  'relationship' => 'cv_clientvisits_accounts',
  'source' => 'non-db',
  'reportable' => false,
  'side' => 'right',
  'vname' => 'LBL_CV_CLIENTVISITS_ACCOUNTS_FROM_CV_CLIENTVISITS_TITLE',
);

Hi, you will need to re-create your custom field by vardefs by adding the file:
custom/Extension/modules/Accounts/Ext/Vardefs/field_market.php

with contents:

<?php
$dictionary['Account']['fields']['market'] = array (
    'name' => 'market',
    'vname' => 'LBL_MARKET',
    'type' => 'enum',
    'massupdate' => '1',
    'importable' => 'true',
    'duplicate_merge' => 'disabled',
    'inline_edit' => true,
    'reportable' => true,
    'unified_search' => false,
    'merge_filter' => 'disabled',
    'len' => 60,
    'options' => 'market_list',
);

and the file
custom/Extension/modules/Accounts/Ext/Language/en_us.field_market.php

with contents

<?php
$mod_strings['LBL_MARKET'] = 'Market';

To make this field available in ClientVisits you will need to create
custom/Extension/modules/cv_ClientVisits/Ext/Vardefs/field_account_market.php

with contents

<?php
$dictionary["cv_ClientVisits"]["fields"]["cv_clientvisits_accounts_name"]["join_link_name"] = "clientvisits_accounts_link"; 

$dictionary['cv_ClientVisits']['fields']['account_market'] = array (
    'name' => 'account_market',
    'rname' => 'market',
    'id_name' => 'cv_clientvisits_accountsaccounts_ida',
    'vname' => 'LBL_ACCOUNT_MARKET',
    'join_name'=>'accounts',
    'join_link_name'=>'clientvisits_accounts_link',
    'type' => 'relate',
    'link' => 'cv_clientvisits_accounts',
    'table' => 'accounts',
    'isnull' => 'true',
    'module' => 'Accounts',
    'dbType' => 'varchar',
    'source' => 'non-db',
    'unified_search' => true,
    'massupdate' => false,
    'studio' => array('detailview' => 'true'),
    'inline_edit' => false,
    'options' => 'market_list',
);

and the file
custom/Extension/modules/cv_ClientVisits/Ext/Language/en_us.field_account_market.php

with contents

<?php
$mod_strings['LBL_ACCOUNT_MARKET'] = 'Market';

Afterwards:

  • do a Admin / Repair / Quick Repair and Rebuild, donā€™t forget to scroll down and execute to query displayed at the bottom.

  • Make sure to create the dropdown list named ā€œmarket_listā€ (or use an already existing) with admin / dropdown editor

  • Migrate field values from your previous custom field to your newly created field, by an SQL query, see for an example some posts above. Afterwards you can delete your previously created custom field in studio

Because the detailviews, filters and lists are not aware that this is a dropdown field, the following changes are needed to make sure the dropdown field is displayed correctly:

  • After adding this field to the detailview of ClientVisits (studio), edit the file
    custom/modules/cv_ClientVisits/metadata/detailviewdefs.php
    in:
    array (
        'name' => 'account_market',
        'type' => 'relate',

change ā€œrelateā€ to ā€œenumā€

  • After adding this field to your ClientVisits filters (studio), edit the file:
    custom/modules/cv_ClientVisits/metadata/searchdefs.php
    in:
      'account_market' =>
      array (
          'type' => 'relate',

change ā€œrelateā€ to ā€œenumā€

  • After adding this field to your ClientVisits listview (studio), edit the file:
    custom/modules/cv_ClientVisits/metadata/listviewdefs.php
    in:
      'ACCOUNT_MARKET' =>
      array (
        'type' => 'relate',

change ā€œrelateā€ to ā€œenumā€

The listview does not yet support the display of dropdown fields from related modules, which will cause it to display the list names instead of the list labels.

PS: for future post, try out the preformatted text button when posting code, or start with [code] and end with [/code]

1 Like