Filtering in List View based on related field's "ID" and not "Name"

đź’¬ General Discussion
1

While filtering Opportunities in the List View, based on Account, the Advanced Search popup lets you choose an Account “Name” and on applying the filter, you see all Opportunities where the name of the Account matches the chosen “Name”.

If more than one Account has the same name, then you will see all Opportunities linked to these multiple Accounts, whereas I would like to see the Opportunities linked to just the one chosen Account, based on that particular Account’s ID.

I notice that if you set a filter on the Assigned User, the ID of the Assigned User (and not the Assigned User’s Name) is always used while filtering records, which is exactly what should happen in case of other related fields also.

Any help on this will be greatly appreciated!

2

Is there a good reason to use different Accounts with duplicate names? I think this could get confusing in many places…

However, you are probably right that this is an inconsistency in the app. Are you capable of doing some PHP code changes, with a little help?

3

Thanks for your reply.

I was giving an example of Account records with duplicate names to illustrate my point.

In reality, I have an Enrollments module to record admissions in a college, and each Enrollment is linked to a Class, which in turn is linked to a Course. The Classes for a particular Course, tend to have duplicate names and I am not able to filter the Enrollments List View to show Enrollments for a particular Class. The List View shows all Enrollments where the Class “Name” matches the chosen Class (instead of using the Class ID to do the filtering).

Yes, I would be able to make PHP code changes, if you point me in the right direction.

Thanks for your help in advance!

4

I see.

I don’t know the exact instructions to give you. I just know that you need to dive into the listviewdefs which define the List view, and the metadata/SearchFields.php files that define the Quick filters. Maybe look for a core module that has a case similar to yours, and see how it is done in those files.

5

Thanks for your response. Will give it a shot and see how it goes.

Of course would welcome anyone who has implemented a solution for this and can contribute some specific code changes!

6

Today I came across something interesting related related fields under opportunities and I think it’s related to this topic.

For convenience I added relate field MyGroup(mygroup_c) (a relate field to security_groups) on Contacts to allow me to filter records based on my security groups. When filtering, the system created the proper join to the security_groups table based on the securitygroup_id_c field from table contacts_cstm (This is the field Studio created when I created my relate field MyGroup). All works fine when filtering.

I created field MyGroup(mygroup_c) on Opportunities as well. Studio created field securitygroup_id_c on table opportunities_cstm as espected. Then I added the field MyGroup to the Filter view. Now, when trying to filter based MyGroup, the query the system generates does not create the proper JOIN to the security_groups table.
This the query in log:

Tue Jul 16 11:29:45 2019 [2312][1][INFO] Query:SELECT  opportunities.id ,opportunities_cstm.our_op_type_c, opportunities.name  , accounts.name account_name, jtl0.account_id account_id, opportunities.sales_stage , opportunities.opportunity_type , opportunities.amount , opportunities.currency_id , opportunities.date_closed , opportunities.date_entered , opportunities.assigned_user_id  FROM opportunities  LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c   LEFT JOIN  accounts_opportunities jtl0 ON opportunities.id=jtl0.opportunity_id AND jtl0.deleted=0   LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0  AND accounts.deleted=0 where ((mygroup_c like '620%')) AND opportunities.deleted=0 ORDER BY opportunities.name ASC LIMIT 0,51
Tue Jul 16 11:29:45 2019 [2312][1][FATAL] Mysqli_query failed.

I tried that on version Version 7.10.13.

If you have more insides please let me know.

Thanks,

AlxGr

7

What comes next in the logs? Why did the query fail? MySQL tells you the reason.

8

I think I found the issue :blush:

The field needed to be added to the default list for listview layout in order for the filter to work.

Once the column is visible in listview, the query is created properly when filter by the relate field.

Thanks,

AlxGr

9

Hello! I’m not 100% sure we have the same case, but it seems to be so.

I was able to enable Relate fields to search using the ID by adding this to the field’s corresponding config in searchdefs.php

'displayParams' => array('useIdSearch'=>true)

In my case I have a locations relate field that I need to filter by the exact record, so this is how it ends up:

'location' => 
      array (
        'type' => 'relate',
        'studio' => 'visible',
        'label' => 'LBL_LOCATION',
        'link' => true,
        'width' => '10%',
        'default' => true,
        'name' => 'isre_listing_location',
        'displayParams' => array('useIdSearch'=>true),
      ),
10

Hello, I need to reach this target:
In my application that has an iframe with suitecrm.
I would like, generate url for the listview of my custom module that has a relation with accounts module, that filter the list by account id.

I try your solution:

'tsoft_installations_accounts_name' => 
      array (
        'type' => 'relate',
        'link' => true,
        'label' => 'LBL_TSOFT_INSTALLATIONS_ACCOUNTS_FROM_ACCOUNTS_TITLE',
        'width' => '10%',
        'default' => true,
        'id' => 'TSOFT_INSTALLATIONS_ACCOUNTSACCOUNTS_IDA',
        'name' => 'tsoft_installations_accounts_name',
		'displayParams' => array('useIdSearch'=>true),
      ),

quick repair but not working :confused:

how can I compose the url to filter by account id?
In my external application, I have only the account id, not the name

thanks for help me,
B