Search on relate field

[mikesolomon]

SugarCRM 5.0.0b

I have created a custom module and included a relate field account_name that relates to the accounts module

My module is a list of job numbers

My problem is that tho I can add the account to a job what I haven't managed to do is search for a job by account number

Adding the account number to the search is easy but when I search on it I get no results

I have been looking at this for 2 weeks now and am getting nowhere

How can I do this

[sidh211]

hii mikesolomon

i am also getting the same error i think its bcoz when you create a custom field a new _cstm table gets created which stores the id refrence and while searchinf a record in list view it searches in original table where it canot find that id clumn so any solution on this plss help

thanks in adv

sid

[jojiju]

i am also getting the same error i think its bcoz when you create a custom field a new _cstm table gets created which stores the id refrence and while searchinf a record in list view it searches in original table where it canot find that id clumn so any solution on this plss help

sid

hi sidh211,
I found the solution of the problem. according to mine requirements. I built new module called milestone through module builder. I added the custom fields like project name of type relate that will relate to the project module. now I added that project name in all the layouts eg. edit view, search view, listview and search view(basic).
now deploy the module or another option publish the custom module and install through the module loader.

my file in milestone's vardef look like as follows ---project_name is a relate field
step1.
'project_name' =>
array (
'required' => false,
'source' => 'non-db',
'rname'=>'name',
'link'=>'project_milestone',
'table'=>'project',
'name' => 'project_name',
'vname' => 'LBL_PROJECT_NAME',
'type' => 'relate',
'massupdate' => 0,
'comments' => '',
'help' => '',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => 0,
'audited' => 0,
'reportable' => 0,
'len' => '255',
'id_name' => 'project_id',
'ext2' => 'Project',
'module' => 'Project',
'studio' => 'visible',
),
I only added
'rname'=>'name',
'link'=>'project_milestone',
'table'=>'project',

in above relate field.....

step 2.
'project_milestone' =>
array (
'name' => 'project_milestone',
'type' => 'link',
'relationship' => 'project_financial_milestone',
'vname' => 'LBL_PARENT_NAME',
'link_type' => 'one',
'side'=>'right',
'module'=>'Project',
'bean_name'=>'Project',
'source'=>'non-db',
),
this is the link field (project _milestone) in step 1.

n parent module's vardef I added the following
'financial_milestones' =>
array (
'name' => 'financial_milestones',
'type' => 'link',
'relationship' => 'project_financial_milestone',
'side' => 'right',
'source'=>'non-db',
'vname'=>'LBL_NEW_FORM_TITLE',
),

note the relationship ie. project_financial_milestone should be same in step2


now in relationship array of project's vardefs
add the following

'project_financial_milestone' => array(
'lhs_module'=> 'Project', 'lhs_table'=> 'project', 'lhs_key' => 'id',
'rhs_module'=> 'pm_Milestones', 'rhs_table'=> 'pm_milestones', 'rhs_key' => 'project_id',
'relationship_type'=>'one-to-many'),

go to admin->repair n rebuild

now search the milestones by project(relate field) it works

cheers

[mikesolomon]

I created a relate field account in my custom module using studio

because i added it via studio the relate field is in the cstm table

it doesn't appear in vardefs - where would the relate definition live?

[jojiju]

I created a relate field account in my custom module using studio

because i added it via studio the relate field is in the cstm table

it doesn't appear in vardefs - where would the relate definition live?

deploy the module at the end only or publish the module and install through the module loader and you won't have to deal with the module_cstm table.
cheers

[mikesolomon]

deploy the module at the end only or publish the module and install through the module loader and you won't have to deal with the module_cstm table.
cheers

The problem with doing it that way is to build a one-many relationship the wiki says create the relate field in studio

I tried creating a quick test module with the relate field as part of the base module but then for some reason autocomplete isnt working

Does your autocomplete work?

[frankext]

hi sidh211,
I found the solution of the problem. according to mine requirements. I built new module called milestone through module builder. I added the custom fields like project name of type relate that will relate to the project module. now I added that project name in all the layouts eg. edit view, search view, listview and search view(basic).
now deploy the module or another option publish the custom module and install through the module loader.

my file in milestone's vardef look like as follows ---project_name is a relate field
step1.
'project_name' =>
array (
'required' => false,
'source' => 'non-db',
'rname'=>'name',
'link'=>'project_milestone',
'table'=>'project',
'name' => 'project_name',
'vname' => 'LBL_PROJECT_NAME',
'type' => 'relate',
'massupdate' => 0,
'comments' => '',
'help' => '',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => 0,
'audited' => 0,
'reportable' => 0,
'len' => '255',
'id_name' => 'project_id',
'ext2' => 'Project',
'module' => 'Project',
'studio' => 'visible',
),
I only added
'rname'=>'name',
'link'=>'project_milestone',
'table'=>'project',

in above relate field.....

step 2.
'project_milestone' =>
array (
'name' => 'project_milestone',
'type' => 'link',
'relationship' => 'project_financial_milestone',
'vname' => 'LBL_PARENT_NAME',
'link_type' => 'one',
'side'=>'right',
'module'=>'Project',
'bean_name'=>'Project',
'source'=>'non-db',
),
this is the link field (project _milestone) in step 1.

n parent module's vardef I added the following
'financial_milestones' =>
array (
'name' => 'financial_milestones',
'type' => 'link',
'relationship' => 'project_financial_milestone',
'side' => 'right',
'source'=>'non-db',
'vname'=>'LBL_NEW_FORM_TITLE',
),

note the relationship ie. project_financial_milestone should be same in step2


now in relationship array of project's vardefs
add the following

'project_financial_milestone' => array(
'lhs_module'=> 'Project', 'lhs_table'=> 'project', 'lhs_key' => 'id',
'rhs_module'=> 'pm_Milestones', 'rhs_table'=> 'pm_milestones', 'rhs_key' => 'project_id',
'relationship_type'=>'one-to-many'),

go to admin->repair n rebuild

now search the milestones by project(relate field) it works

cheers

Hi jojiju,

Very glad to see the search solution of a relate field.
just a small question: what value should be assigned to the 'bean_name' if it is a custom module? the custom module name?
Thank you in advance.

frankext

[mikesolomon]

The problem with doing it that way is to build a one-many relationship the wiki says create the relate field in studio

I tried creating a quick test module with the relate field as part of the base module but then for some reason autocomplete isnt working

Does your autocomplete work?

All working - thanks this is great

Interestingly it contradicts http://www.sugarcrm.com/wiki/index.p...s_in_Sugar_5.0 which explains setting up one-many-relationships and says:

In Studio for the CHILD module, create a custom Relate field named <FIELDNAME>. If your PARENT module is a standard OOB module then just select it from the Module drop-down list, save, and go to the next step.

NOTE: For Sugar 5.0a and b, If the child module is a custom module or module built with module builder, DO NOT create the relate field until after you have deployed the module. Otherwise you will not have the <module>_cstm table to work with. (ptemplin7 03/19/2008)

[mikesolomon]

Doing it this way I have noticed a strange problem

The search fails if only the relate field is searched on but works if another field is searched on as well as the relate field

I have put the details in this post http://www.sugarcrm.com/forums/showthread.php?t=33883

Is anyone else having the same issue?

[aigalli]

I also have a custom module that has two relate fields (organisation_name and contact_name). I can successfully search against the organisation name but not against the contact name. Unlike your situation I cannot obtain a result even when I include contact name as part of a multi-field search.

Is there any reason why I cannot have two relate fields in my custom module?

Thanks.