Missing Relationship Table for Standard Relationships like Accounts to Meetings, Notes and Contracts in Sugar Database

Hi EveryOne,

I am using Sugar Enterprise 9.0.1 Instance. I had a query in sugar standard relationship tables in Database. We all know that Sugar has default one to many relationship between Accounts to Meetings,Calls,Notes and Contracts. In General if we have one to many relationship between any two modules it creates a table in DB. I am able to see relationship table for accounts to contacts in my Database but not for Meetings,Notes or Contracts. I know that Sugar dont come with relationship table for Accounts to Contracts by default, but my query was if we dont have relationship table to Accounts to Notes or Accounts to Meetings if I delete any account accidentally from Sugar UI, I can retrieve it from Database but how we can link to Notes and Meetings records to the Account? Is there any way to relate relationships between Accounts and Notes in this case. Could any one of you please direct me in right path. Thanks a ton in Advance.

  • We can add Tasks to that list too, though it has the relationship Out of the Box , Sugar makes it feel like a Flex Relate . The only way is to create a new custom relationship between the two modules. I'm equally keen to hear from the tech gurus. 

  • For Relate/Flex relate fields, once wiped, there is no way to retrieve it, because it's part of the the record itself.

    But - there is a solution - Audit.

    To keep track of relate changes, we can start auditing those fields, which will help debugging and restoring data if needed.

    Audit is not enabled in some modules by default, so you may have to enable it. Here is a quick way to enable auditing in Meetings, for example. 

    Create a field in custom/Extension/modules/Meetings/Ext/Vardefs/sugarfield_parent_id.php


    $dictionary['Meeting']['audited'] = true;
    $dictionary['Meeting']['fields']['parent_id']['audited'] = true;
    $dictionary['Meeting']['fields']['parent_type']['audited'] = true;

    Flex relate usually uses parent_type and parent_id as the fields, so we just have to start auditing them.

    Also note that we have started auditing meetings module as well, which will automatically create the meetings_audit table.

    You can do the same for other modules or relate fields. But a point for Notes module - it's really huge, so you may have to think about consequences of auditing the Notes table - since it's used for email attachments.

  • Hi Neeraja,

    Thanks for your suggestion, I will do this. But Now how to link the Contracts to Account. I am able to retrieve account back, but as we dont have relationship table in database for Accounts to Contracts, dont know how to link the related contracts to this account.

  • Contracts module has audit enabled by default, and uses "Relate" field for account_id, and you just have to audit that field. The logic is the same for any relate field. eg:


    $dictionary['Contract']['fields']['account_id']['audited'] = true;