I want to display latest records from one account in leads listing page. I put group by leads.account_id in query but some times it displays oldest record of account not latest . The following query displays correct records when I run into database:
select l2.id ,l2.quote_no_c, LTRIM(RTRIM(CONCAT(IFNULL(l2.first_name,’’),’ ‘,IFNULL(l2.last_name,’’)))) as name, l2.first_name , l2.last_name , l2.salutation , l2.opportunity_amount , l2.assigned_user_id ,l2.account_name,l2.account_id,l2.date_entered from ( SELECT leads.id ,leads_cstm.quote_no_c, LTRIM(RTRIM(CONCAT(IFNULL(leads.first_name,’’),’ ‘,IFNULL(leads.last_name,’’)))) as name, leads.first_name , leads.last_name , leads.salutation , leads.opportunity_amount , leads.assigned_user_id ,leads.account_name,leads.account_id,leads.date_entered FROM leads LEFT JOIN leads_cstm ON (leads.id = leads_cstm.id_c ) where leads.deleted=0 order by leads.date_entered DESc ) l2 group by l2.account_id
How can I achieve this in suite crm. Please suggest.
I believe that the closest you can get to a “group by” on leads via the Search function in SuiteCRM is by going to the Leads Listview and clicking “Advanced Search”.
This menu will allow you to sort/search the records by many values, such as “Assigned User” or the “Account Name”.
You can also click the “Layout Options” dropdown to expand your search. This will allow you to show or hide certain columns from the search.
As well as changing what Column to Order the Search by.
Thanks for your reply. I need to show only one (latest) lead of one account by default in Leads module . When we search using advance search or basic search it will display all records of one account .
Hi,
This is not something that is possible out of the box with SuiteCRM.
You may be able to do this by creating Custom functionality such as a logic hook to ensure it fetches only one record.
If you wish to do it with a Logic Hook, you can find information on Logic Hooks here: SugarCRM Logic Hooks