How create custom field on accounts_contracts (relatiship module)

[samuka]

Hi,

I need to create customs fields on accounts_contracts table.

I found the following guide: (http://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_7.5/Data_Framework/Relationships/Custom_Relationships/)

But this example, creates a new relationshop and my expectative is:

1. New table: accounts_contracts_cstm
2. Into this tabele, new fields: (codigo integer, sequencia integer) integer.

Is a way to do this?

Thanks!

[andy]

So you want to add custom fields into the actual relationship table between accounts and contracts which is called accounts_contracts?

Yes this can be done but is not straightforward.

Here is blog post I made on adding custom fields to the relationship table between two modules:

http://sugarmods.co.uk/how-to-add-custom-fields-to-a-relationship-table-and-display-them-in-a-subpanel-sugarcrm-suitecrm/

[pgr]

Hi andy, that blog post you linked was my salvation when I had to do something similar a few months ago.

Even though you did an excellent job laying out everything in detail, I still found it difficult to apply to my case. Learning by example isn’t always enough, sometimes the logic being the example is needed.

Even though my code is working now, I still don’t fully understand it and would like to ask you if you could please explain this part a little better:

In the vardefs, we define three fields, e_date_cancelled, event_cancelled and cancelled_id. It’s the first one that is unclear to me:

$dictionary['Contact']['fields']['e_date_cancelled'] =
 array (
 'name' => 'e_date_cancelled',
 'rname' => 'id',
 'relationship_fields'=>array('id' => 'cancelled_id', 'date_cancelled' => 'event_cancelled'),
 'type' => 'relate',
 (....)

If I understand correctly, this is a sort of “virtual field”, not stored in the database, is that right? Why is this needed?

Then those fields being mapped in relationship_fields? They seem to be mapping the stuff in the metadata for the relationship (‘id’ and ‘date_cancelled’) to the stuff that comes next in these vardefs (a dictionary entry for ‘cancelled_id’ and ‘event_cancelled’). Again, why?

Finally, when we get to the subpanel definitions, it seems we need to include all three fields there (e_date_cancelled, event_cancelled and cancelled_id), but leave them as ‘query_only’ if we don’t intend to show them to the user, or else give them a label and a width and show them.

Can you help with some more insights? For me this is a basic need when data modelling, using fields in relationships… and it is still way too difficult to do in SuiteCRM…

[andy]

To be honest with you I dont have all the answers, You seem to have good grasp of it how it works.

[pgr]

But according to all my Googling, you are the world-expert on this! Darn…

And to be honest with you, I learned half of “my grasp” as I was putting together the post and looking at everything attentively : - )

Until now I’ve been a victim of trial-and-error on this issue…

Maybe one day I’ll write a new more complete tutorial somewhere on StackOverflow or some SuiteCRM developer wiki (I don’t have a blog).