Relate field visible in studio not found in database

Hi all!

Where can I find relate fields in the database?

In studio we have a custom relate field on account named merged_into_c, which contains a link to another account.

I want select the accounts which were merged into another account, but the field merged_into_c does not exist in the database, not on the accounts and not on the accounts_cstm table. There is also no accounts_accounts or accounts_merged_into table. It is not clear to me where this relation is stored in the database...

SELECT Id, name FROM accounts LEFT JOIN accounts_cstm ON = accounts_cstm.id_c WHERE accounts_cstm.merged_into_c IS NOT NULL AND NOT accounts_cstm.merged_into_c='' LIMIT 10;

Anyone any idea how this information is stored?



  • Hello 

    When a relate type field is created Sugar will add an ID type field on the module cstm table.

    For your example, when the merged_into_c is created in Studio account_id_c is created on the accounts_cstm table to store the ID's of the related accounts. 

    You can double check the mappings of merged_into_c and the id field in the fields_meta_data table:

    SELECT name,ext3 FROM `fields_meta_data` where name='merged_into_c';

    If this is the only relate field that you have relating to the Accounts module your query should be something like this:

    SELECT id, name FROM accounts LEFT JOIN accounts_cstm 
    ON = accounts_cstm.id_c 
    WHERE accounts_cstm.account_id_c IS NOT NULL 
    AND NOT accounts_cstm.account_id_c='' LIMIT 10;

    I hope this helps