Need help with 1-many relationships with documents

[Kalendrinn]

I have been testing every release for v5 and the functionality has been added to add a relate-to field for various modules. I wish to add relate-to fields to the documents module for accounts and projects (at minimum) and have documents show up under accounts and projects in a subpanel. The Studio allows me to add the fields to the documents module, but they do not show up under the Accounts or Projects modules as subpanels in the detail views.

Since the automatic addition of these subpanels has been left out again and again, can someone from the sugar team (or community) please help me understand how I am to add a subpanel to show the documents module in Accounts and/or Projects? So far I have not been able to find any real documentation on it. Is there something I'm missing, something I need to define in the Accounts and Projects modules to get it to connect with the Documents module?

Please help.

[andopes]

Hi, kalendrinn

You can learn how to define a one-to-many relationship in this topic

Download the installer at the first post of dtokeefe.

Cheers

--
André Lopes
Lâmpada Global Services
Rua Bela Cintra, 299 conjunto. 51
São Paulo, SP 01415-000
tel1. 55 11 3237-3110
cel. 55 11 7636-5859
e-mail: info@lampadacrm.com.br

[Kalendrinn]

I guess I have to go code hacking. I was under the impression from previous posts and replies that this would be an automatic thing. Whatever happened to that?? Jennifer? Andy?

I will post any questions I have after looking at the information. I am guessing this is an upgrade safe approach?

[Kalendrinn]

Unless I'm missing something, relate-to fields won't work currently as there is no join table created...which means the data will never get connected properly...despite adding vardefs and all the other crap.

Following dtokeefe's instructions:
1) Documents is the child, nothing to do here except add the relate-to fields which I've done. (this created a documents_cstm table with the relate-to fields and no join table such as documents_accounts).

2) Cannot create the relationship because there is no join table. Otherwise I could add a relationship with the left hand side being Accounts and right hand side being Documents, but there is no common match (hence need for join table) Note: is there a way to do this that is upgrade safe??? Wouldn't the vardefs file be overwritten on an upgrade? Am I supposed to place a new vardefs file in the /custom/modules/module directory?

3) I placed the file in /custom/modules/Accounts/metadata and edited it to such:

$layout_defs['Accounts'] = array(
// list of what Subpanels to show in the DetailView
'subpanel_setup' => array(
'documents' => array(
'order' => 40,
'module' => 'Documents',
'subpanel_name' => 'default',
'sort_order' => 'desc',
'sort_by' => 'name',
'get_subpanel_data' => 'Documents',
'title_key' => 'LBL_LIST_DOCUMENT_NAME'
),
),

Which causes an error. I don't know if the 'title_key' field is correct, but it's the best guess I could make based on other subpaneldefs.php files.

4) Blah, not there yet and not concerned about it at this point.

Am I missing something? Other than the join table have I put everything where it needs to go and edited it properly?

Am I correct Sugar Team that this cannot happen without the join table and the system won't create that?

[andopes]

Hi, kalendrinn.

What is the error?

Can you post your installer??

Perhaps I can fix it to you.

Cheers

--
André Lopes
Lâmpada Global Services
Rua Bela Cintra, 299 conjunto. 51
São Paulo, SP 01415-000
tel1. 55 11 3237-3110
cel. 55 11 7636-5859
e-mail: info@lampadacrm.com.br

[Kalendrinn]

I am working on fixing it. I built a quick module with nothing but a relate-to field off the basic module and gave it a relationship to accounts. It installed fine and kept the relationship. So I am using it as a baseline for how to do it. However, I will warn, it may not be upgrade safe and may require that someone help me make it upgrade safe.

I will post how I do it so others may do the same...I know this has been a LONG awaited change/fix for many. The documents module is useless unless you can attach documents to other modules. Hopefully I will get it working today.

[jyim]

I guess I have to go code hacking. I was under the impression from previous posts and replies that this would be an automatic thing. Whatever happened to that?? Jennifer? Andy?

I will post any questions I have after looking at the information. I am guessing this is an upgrade safe approach?

Currently, the Relate & Flex Relate fields in Studio do not create associations between records. But they do not currently create the same types of relationships that are represented through the use of subpanels.

We are hoping to add more functionality and flexibility within Studio & Module Builder in future releases that would allow you to create 1-to-many relationships represented by subpanels and the Relate & Flex Relate fields.

[Kalendrinn]

Ok, so I have almost gotten documents related to accounts. The one thing I am not sure about so far is how to get the system to read the relationship metadata file. Here's what I did for that part:

1) I created a metadata file 'documents_accountsMetaData.php' in /metadata which has the following code:

$dictionary["Documents_accounts"] = array (
'table' => 'documents_accounts',
'fields' =>
array (
0 =>
array (
'name' => 'id',
'type' => 'varchar',
'len' => 36,
),
1 =>
array (
'name' => 'deleted',
'type' => 'bool',
'len' => '1',
'default' => '0',
'required' => true,
),
2 =>
array (
'name' => 'document_id',
'type' => 'varchar',
'len' => 36,
),
3 =>
array (
'name' => 'account_id',
'type' => 'varchar',
'len' => 36,
),
),
4 =>
array (
'name' => 'date_modified',
'type' => 'datetime',
),
'indices' =>
array (
0 =>
array (
'name' => 'PRIMARY',
'type' => 'primary',
'fields' =>
array (
0 => 'id',
),
),
1 =>
array (
'name' => 'idx_doc_acct_doc',
'type' => 'index',
'fields' =>
array (
0 => 'document_id',
),
),
2 =>
array (
'name' => 'idx_doc_acct_acct',
'type' => 'index',
'fields' =>
array (
0 => 'accounts_id',
),
),
3 =>
array (
'name' => 'documents_accounts_alt',
'type' => 'index',
'fields' =>
array (
0 => 'document_id',
1 => 'accounts_id',
),
),
),
'relationships' =>
array (
'Documents_accounts' =>
array (
'lhs_module' => 'Documents',
'lhs_table' => 'documents',
'lhs_key' => 'id',
'rhs_module' => 'Accounts',
'rhs_table' => 'accounts',
'rhs_key' => 'id',
'relationship_type' => 'many-to-many',
'join_table' => 'documents_accounts',
'join_key_lhs' => 'document_id',
'join_key_rhs' => 'account_id',
),
),
);


2) I created a file 'Documents.php' in /custom/Extension/application/Ext/TableDictionary which has the following code:

include_once('metadata/documents_accountsMetaData.php');

The problem I can see is the the Documents.php file is not being read when rebuilding the extensions and so the metadata file isn't being read. So what happens is that all documents show up under each account's Documents subpanel and vice versa. There was a line in the Documents.php file above that said it was an autogenerated file but I am still not sure where that gets autogenerated...or the tabledictionary.ext.php that it generates gets autogenerated. I was obviously wrong in my first guess above. Any help would be GREATLY appreciated.

Other than that I have gotten the subpanels to show up and everything. It ended up that I decided to do a many-to-many relationship and forego the relate-to field in the documents edit and detail views. That way a document could be associated with multiple accounts (ie there's a project that encompasses multiple acocunts but they all had to sign the same contract...).

[Kalendrinn]

Currently, the Relate & Flex Relate fields in Studio do not create associations between records. But they do not currently create the same types of relationships that are represented through the use of subpanels.

We are hoping to add more functionality and flexibility within Studio & Module Builder in future releases that would allow you to create 1-to-many relationships represented by subpanels and the Relate & Flex Relate fields.

Although at first glance that seems utterly pointless, I can see situations where relate-to fields are needed instead of relationships for subpanels. I have to admit I was a bit confused between the two, apparently. It would be nice when you guys are working on more improvements to make sure and add the ability to define new relationships between established modules (whether custom or not), and not just add a relate-to field. In my case, that is what I needed as the relate-to field doesn't work for a 1-many or many-many type of relationship...it only works for a 1-1 relationship. And now I know, and knowing is half the battle!

---edit

P.S. - what is a flex relate field?? That is a new one since the GA release and I don't remember hearing about it.

[Kalendrinn]

I have posted a continuation of this in the developer forum here:

http://www.sugarcrm.com/forums/showt...ed=1#post95220

I need any help I can get as I am stuck and need a Team member or a developer familiar with v5 to help me out. I have provided enough that for someone who knows Sugar's internals it should be a snap.