Someone please help me.

[spectheintro]

I have been struggling for two days now to try and create a link between a custom module I have built, and the Accounts module. For the life of me, I cannot get the link to behave properly.

Essentially, I am seeking to have an "Account Name" field identical to what you find on the Contacts module, so that every new plan created in my "InsurancePlans" module is linked to an account. To this end I have built a relationship between the two modules, and inserted the following code into my custom module's vardefs:
'account_name' =>
array (
'name' => 'account_name',
'rname' => 'name',
'id_name' => 'account_id',
'vname' => 'LBL_ACCOUNTS',
'join_name'=>'accounts',
'type' => 'relate',
'link' => 'accounts',
'table' => 'accounts',
'isnull' => 'true',

'module' => 'Accounts',
'dbType' => 'varchar',
'len' => '255',
'source' => 'non-db',
'massupdate' => true,
'unified_search' => true,
),
'account_id' =>
array (
'name' => 'account_id',
'rname' => 'id',
'id_name' => 'account_id',
'vname' => 'LBL_ACCOUNTS',
'type' => 'relate',
'table' => 'accounts',
'isnull' => 'true',
'module' => 'Accounts',
'dbType' => 'varchar',
'len' => '255',
'reportable'=>false,
'source' => 'non-db',
'massupdate' => false,
'duplicate_merge'=> 'disabled',
),

Now, the crazy thing is, the link works in the subpanel. I can click on the "Select" button, select an account, and link them, and then the "Account Name" field in ListView will be populated with the correct account. I can Mass Update as well. Both the Accounts and the InsurancePlan modules will reference each other in this way with no issues. But neither the Accounts nor the InsurancePlan Edit and DetailViews will display this data correctly. First, instead of calling the field "Account Name," it only calls it "Account." Second, the data field it looks for is "ACCOUNT_NAME_NAME", as opposed to just "account_name." I have tried manually editing the DetailView and EditView html files, but nothing seems to work. What would cause the ListView to display this data properly, but the Detail and Edit views to incorrectly reference the data? I can't figure it out.

[anna]

First, instead of calling the field "Account Name," it only calls it "Account." Second, the data field it looks for is "ACCOUNT_NAME_NAME", as opposed to just "account_name."

Did you create the module from scratch or use ModuleBuilder? Just wandering what/who is it that is responsible for providing the names for the fields.

It sounds like the relationship itself is working fine, and it seems it's just a display issue that is the problem.
From what I can tell so far, the data for ListViews (metadata driven) is generated by looking in the vardefs.php file to create the fields (names are converted to upper case). So all fields that you properly create in the vardefs, should be available in the ListView (listviewdefs.php in upper case). That's probably why you see the account name in your ListView.

The DetailView is a bit different and you need to populate all fields, including the related ones, 'manually'. To do so, your custom module's sugar bean file needs to override the method fill_in_additional_detail_fields(). There you could use a SQL query to get the account name from the account_id that is stored in your custom module and assign it to the account_name of your custom module. In the DetailView.php and EditView.php you need to assign the value to the template variable, and hopefully that will do the trick!

[spectheintro]

Did you create the module from scratch or use ModuleBuilder? Just wandering what/who is it that is responsible for providing the names for the fields.

It sounds like the relationship itself is working fine, and it seems it's just a display issue that is the problem.
From what I can tell so far, the data for ListViews (metadata driven) is generated by looking in the vardefs.php file to create the fields (names are converted to upper case). So all fields that you properly create in the vardefs, should be available in the ListView (listviewdefs.php in upper case). That's probably why you see the account name in your ListView.

The DetailView is a bit different and you need to populate all fields, including the related ones, 'manually'. To do so, your custom module's sugar bean file needs to override the method fill_in_additional_detail_fields(). There you could use a SQL query to get the account name from the account_id that is stored in your custom module and assign it to the account_name of your custom module. In the DetailView.php and EditView.php you need to assign the value to the template variable, and hopefully that will do the trick!

Thank you so much for responding. I can't tell you how much I appreciate it.

I used ModuleBuilder to create the module, as prior to yesterday morning I had very little experience in PHP scripting. 16 hours of headache later, I still know very little about PHP, but at least I can string together a coherent statement. I did the coding for the field myself, so it's good to know that I did something correctly.

I'm having trouble following your instructions re: the bean file. How would I go about overriding that particular command? Is the bean file an actual separate file, or is it just an array within another file? Are there any tutorials or examples I could follow, just to see how the bean file operates? Hopefully I can figure out how to do an SQL query from PHP once I get there.

Also, I don't understand what you mean by "you need to assign the value to the template variable." What is the template variable, and aren't those PHP files replaced by Studio every time I use it? I would like to use studio to do most of the formatting for the module, but if I have to finish the rest by hand, that's ok too. Mostly I just want this link to work.

Thanks again for all of your help--I'll start googling for bean files and whatnot.

[spectheintro]

Ok, I did a bit more research, and here's what I've got.

I found the bean file (InsurancePlans.php)

I also found fill_in_additional_detail_fields() within that, and added the following line:

$this->account_name = get_account_name($this->account_id);

However, I know that now I have to define the function get_account_name; unfortunately, I have no idea how to do that. Using get_assigned_user_name($this->assigned_user_id); as a template, I know that the function has to look something like this:

// Fill in the assigned_user_name
// $this->assigned_user_name = get_assigned_user_name($this->assigned_user_id);
$custom_join = $this->custom_fields->getJOIN();
$query = "SELECT ";
$query .= "
insuranceplans.*
,users.user_name as assigned_user_name";
/*
//BUILDER:START Pro only
$query .= ", teams.name AS team_name";
//BUILDER:END Pro only
*/
if($custom_join){
$query .= $custom_join['select'];
}
$query .= " FROM insuranceplans
LEFT JOIN users
ON insuranceplans.assigned_user_id=users.id";


So the question now is, what do I need to change to make this work? my join table is accounts_insuranceplan and the keys are account_id and insuranceplan_id, respectively. Would you mind pointing me in the right direction? I have no experience with mySQL and I don't know if I have the stamina to dive into that headfirst.

[anna]

Ok, I did a bit more research, and here's what I've got.

I found the bean file (InsurancePlans.php)

I also found fill_in_additional_detail_fields() within that, and added the following line:

$this->account_name = get_account_name($this->account_id);

However, I know that now I have to define the function get_account_name; unfortunately, I have no idea how to do that. Using get_assigned_user_name($this->assigned_user_id); as a template, I know that the function has to look something like this:

// Fill in the assigned_user_name
// $this->assigned_user_name = get_assigned_user_name($this->assigned_user_id);
$custom_join = $this->custom_fields->getJOIN();
$query = "SELECT ";
$query .= "
insuranceplans.*
,users.user_name as assigned_user_name";
/*
//BUILDER:START Pro only
$query .= ", teams.name AS team_name";
//BUILDER:END Pro only
*/
if($custom_join){
$query .= $custom_join['select'];
}
$query .= " FROM insuranceplans
LEFT JOIN users
ON insuranceplans.assigned_user_id=users.id";


So the question now is, what do I need to change to make this work? my join table is accounts_insuranceplan and the keys are account_id and insuranceplan_id, respectively. Would you mind pointing me in the right direction? I have no experience with mySQL and I don't know if I have the stamina to dive into that headfirst.

You seem to be on the right track, so that's good
I'm still assuming that the relationships were set up correctly and you are just having trouble displaying it.
Do I understand it correctly that your InsurancePlans record would have only one (or zero) account associated with it? If that is the case, you really don't even need the join table ... but if it works out, then it's fine too.

So what you would need to do:
1) In InsurancePlan.php create the function fill_in_additional_detail_fields(). Add the following code to this function:

 $query = "SELECT acc.id, acc.name FROM accounts AS acc, accounts_insuranceplan AS acc_in WHERE acc.id=acc_in.account_id AND acc_in.insuranceplan_id='$this->id' AND acc.deleted=0";
$result = $this->db->query($query, true, "Error filling in additional detail fields");
$row = $this->db->fetchByAssoc($result);
if ($row != null) {
    $this->account_name = $row['name'];
    $this->account_id = $row['id'];
} else {
    $this->account_name = '';
    $this->account_id = '';
} 
  

2) In DetailView.php of your custom module add this:

 $xtpl->assign('ACCOUNT_NAME', $focus->account_name);
$xtpl->assign('ACCOUNT_ID', $focus->account_id); 
  

3) In DetailView.html of your custom module add this:

<td width="15%" valign="top" class="tabDetailViewDL"><slot>{MOD.LBL_ACCOUNT}&nbsp;</slot></td>
<td width="35%" valign="top" class="tabDetailViewDF"><slot><a href='index.php?module=Accounts&action=DetailView&record={ACCOUNT_ID}'>{ACCOUNT_NAME}</a>&nbsp;</slot></td>

This will create a linked field to the Account.

4) Similar procedure for EditView.php and EditView.html - see Contacts module for example.

Hope this helps

[spectheintro]

Anna--

Thank you so much! I was actually looking here when I saw you'd responded; the things you and he both recommend are essentially the same, and it WORKED!!!

. . . with caveats.

The field now displays the linked account properly, which is fantastic. However, in EditView, clicking the "Select" button to insert an Account Name produces nothing. Here is the code I have:

 <td class="dataField"><span sugar='slot5b'><input class="sqsEnabled" tabindex="1" autocomplete="off" id="account_name" name='account_name' type="text" size='35' value="{ACCOUNT_NAME}">
     <input id='account_id' name='account_id' type="hidden" value='{ACCOUNT_ID}'>
     <input title="{APP.LBL_SELECT_BUTTON_TITLE}" accessKey="{APP.LBL_SELECT_BUTTON_KEY}" type="button" tabindex='1' class="button" value='{APP.LBL_SELECT_BUTTON_LABEL}' name=btn1
            onclick='open_popup("Accounts", 600, 400, "", true, false, {encoded_account_popup_request_data}, "single", true);' {DISABLED_ACCOUNT}/></span sugar='slot'></td> 
  

I'm going to keep chipping away at this and try to figure out why the button is unresponsive. But this is the best progress I've made thus far; I can't thank you enough.

Woo! Once I get this down, the biggest hurdle to adopting SugarCRM will have been passed!

[spectheintro]

Ok, I've made some more progress and it looks like I never set up pop-up parameters in EditView.php. I'm going to try to do that now.

[anna]

Anna--

Thank you so much! I was actually looking here when I saw you'd responded; the things you and he both recommend are essentially the same, and it WORKED!!!

. . . with caveats.

The field now displays the linked account properly, which is fantastic. However, in EditView, clicking the "Select" button to insert an Account Name produces nothing. Here is the code I have:

 <td class="dataField"><span sugar='slot5b'><input class="sqsEnabled" tabindex="1" autocomplete="off" id="account_name" name='account_name' type="text" size='35' value="{ACCOUNT_NAME}">
     <input id='account_id' name='account_id' type="hidden" value='{ACCOUNT_ID}'>
     <input title="{APP.LBL_SELECT_BUTTON_TITLE}" accessKey="{APP.LBL_SELECT_BUTTON_KEY}" type="button" tabindex='1' class="button" value='{APP.LBL_SELECT_BUTTON_LABEL}' name=btn1
            onclick='open_popup("Accounts", 600, 400, "", true, false, {encoded_account_popup_request_data}, "single", true);' {DISABLED_ACCOUNT}/></span sugar='slot'></td> 
  

I'm going to keep chipping away at this and try to figure out why the button is unresponsive. But this is the best progress I've made thus far; I can't thank you enough.

Woo! Once I get this down, the biggest hurdle to adopting SugarCRM will have been passed!

Great to hear that you figured some of it out

For the Select button to work, you need to encode the request data.
To do so, put this code in your custom EditView.php:

  
$json = new JSON(JSON_LOOSE_TYPE);
if (isset ($_REQUEST['record'])) { 
    $popup_request_data = array (
        'call_back_function' => 'set_return',
        'form_name' => 'EditView',
        'field_to_name_array' => array (
            'id' => 'account_id',
            'name' => 'name'            
        )        
    );
}
$xtpl->assign('encoded_account_popup_request_data', $json->encode($popup_request_data)); 
  

That should do the trick. If you are still having troubles with it, post back with more details.
Hope this helps

[spectheintro]

I got it! This thread includes a tutorial on how to make the "select" button work properly. I am THRILLED. Now I've got to do some massive cleanup, and do it all again on the Accounts side. But it can be done!!! I'm so psyched.

[spectheintro]

Great to hear that you figured some of it out

For the Select button to work, you need to encode the request data.
To do so, put this code in your custom EditView.php:

That should do the trick. If you are still having troubles with it, post back with more details.
Hope this helps

We're almost there--the code you provided (and the code from the other thread) gives me a SELECT button that functions properly, but once I select the Account and click "Save", it doesn't seem to write the entry to the database. The Account Name remains blank. I'm trying to think if maybe I'm not telling it to store the data properly?