Create many-to-many relationship with role

[Jax.Smith]

Hi everyone,

I manage to duplicate the many-to-many relationship with role from Opportunities-Contacts module and called it Facility-Contacts.





Files I edited were same from Opportunities-Contacts example and simply recreate each files and rename opportunity to facility or your module name.

Here are the list:
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\metadata\tts_facility_conta ctsMetaData.php
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\TableDictionary.php
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\include\generic\SugarWidget s\SugarWidgetSubPanelTTS_FacilityEditRoleButton.ph p
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\Contacts\Contact.ph p
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\Contacts\SaveContac tTTS_FacilityRelationship.php
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\Contacts\ContactTTS _FacilityRelationship.php
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\Contacts\ContactTTS _FacilityRelationshipEdit.html
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\Contacts\ContactTTS _FacilityRelationshipEdit.php
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\Contacts\metadata\s ubpanels\ForTTS_Facility.php
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\Contacts\metadata\s ubpaneldefs.php
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\Contacts\vardefs.ph p
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\Contacts\field_arra ys.php
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\TTS_Facility\vardef s.php
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\TTS_Facility\TTS_Fa cility.php
C:\Program Files\sugarcrm-5.2.0f\htdocs\sugarcrm\modules\TTS_Facility\metada ta\subpaneldefs.php

And it works perfectly as shown above.

----------------------------------------------------------------------------------------------------------------------------------------------------

My problem arrive when trying to do duplicate it for Facility-Accounts Module.

First thing I notice is that in generated url for edit button it's missing record id number


Facility-Contacts edit button:
http://localhost/sugarcrm/index.php?...ationshipEdit&record=c9db3078-0aad-2659-b825-4a6f438a6d8a&return_module=TTS_Facility&return_act ion=DetailView&return_id=74145423-9488-604c-7605-4a6f4364d0b7

vs.

Facility-Accounts edit button
http://localhost/sugarcrm/index.php?...ationshipEdit&record=&return_module=TTS_Facility&return_action=DetailV iew&return_id=a261312b-27c5-0a19-4d92-4a6e27e90aec


and even after I manually edit the database to put a value in role



it still won't pull the value into role field



If anyone have done something similar to this before, please lend a hand. Thank You for your time and Thank You in advance.

[gunni]

This kind of relationship is really hard to handle. In my post ( http://www.sugarcrm.com/forums/showthread.php?t=50719 ) i described how to add that field, and its shown then.
I will work on editing in the next few weeks, and prefer to do the edit stuff from scratch. Seems easier to me than copying the files and changing anything, and after that something does not work because of a typo or something.
I will post it here when finished (2 weeks maybe).
I plan to implement an own SugarWidget for changing. In my case there will only be integer values, but maybe this can help you to solve your problem.

[Jax.Smith]

Thank You for your reply, gunni.

Do you have any idea where to check to see why the query failed? It shows nothing, which makes it difficult to debug.

Thanks,
Jax

[gunni]

Maybe you can see something in sugarcrm.log
If the info is to few in there you can change the debug level in config.php to "debug"
Maybe that helps, but dont be to optimistic. I hate those relations because a little little mistake makes them faulty, and they are hard to debug. Thats why i made that tutorial. Most for me, to know where i can find that info again.
My advise would be: Take my tutorial, and write the code for changing the value by yourself. I think this will be less time consuming than finding that error

[Jax.Smith]

The list of files above works flawlessly everytime I create any module with Contacts. Not sure what choked Account from working correctly. I will try the log to see if anything good shows up and keep this thread updated. I want to find the solution so anyone in the future want to do something similar can use it.

[Jax.Smith]

After diggign around in the log, I found this ERROR:

07/30/09 13:26:55 [1748][1][INFO] Query: SELECT accounts.id ref_id , jt0.assigned_user_id tts_loan_role_fields_owner, 'TTS_Loan' tts_loan_role_fields_mod, tts_loan_accounts.tts_loan_id tts_loan_id, tts_loan_accounts.id tts_loan_role_id, tts_loan_accounts.account_role tts_loan_role FROM accounts INNER JOIN tts_loan_accounts ON (accounts.id=tts_loan_accounts.account_id AND tts_loan_accounts.tts_loan_id= 'a261312b-27c5-0a19-4d92-4a6e27e90aec') where ( tts_loan_accounts.deleted=0 AND accounts.deleted=0) AND accounts.deleted=0

07/30/09 13:26:55 [1748][1][INFO] Query Execution Time:0.00029301643371582

07/30/09 13:26:55 [1748][1][ERROR] MySQL error 1054: Unknown column 'jt0.assigned_user_id' in 'field list'

I am not sure if jt0.assigned_user_id is generated or not but i am sure i never type the char "jt0" anywhere. What does this mean?

[Jax.Smith]

Ok Now It finally works!

So the error is the origin of the problem.

Here is what I did to fix it. Thanks to this thread https://www.sugarcrm.com/forums/showthread.php?t=41277

“I had to add "join_name" to make the query work.. This forces "jt0.asigned_user_id" to be replaced by "contact_assigned_user_id", and that's a proper existing field.

in my case it was accounts

'join_name' => "accounts",

it's in module - Accounts - vardefs.php

Thanks again gunni for getting my sugarcrm.log to work in debug mode or else i wouldn't have come across this MYSQL error message.

[SugarBeginnerUK]

Thank you very much Jax, you just saved my module! This works.

[Cullen24]

Jax, thank you so much for sharing this, its save my lot of time and i was stucking on this but it resolved the issue.

[danielvulrich]

Jax, you really saved me a lot of time!