Field Relationships

maverick129 · 2 August 2015 16:08

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

jansiero · 4 August 2015 23:20

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?

maverick129 · 5 August 2015 08:48

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

jansiero · 5 August 2015 10:08

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” ?

maverick129 · 5 August 2015 13:59

Hi Jan Siero,

The other fields are already in use.

jansiero · 5 August 2015 18:45

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.

maverick129 · 6 August 2015 17:03

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

jansiero · 6 August 2015 22:16

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

maverick129 · 7 August 2015 15:30

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

maverick129 · 7 August 2015 16:31

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

jansiero · 10 August 2015 12:55

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.

prmarketing · 12 December 2019 23:08

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 !!

doogle1975 · 20 January 2020 16:30

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

jansiero · 20 January 2020 17:15

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.

doogle1975 · 21 January 2020 11:14

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',
);

jansiero · 28 January 2020 16:59

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]