Hi All
I am using the latest xTuple 5.0
I have a screen that displays the CRM Account Cluster for a custom role.
So I use the extraClause in my script
ins_typecombo.extraClause(" crmacct_id in (select crmacctrole_crmacct_id from public.crmacctrole where crmacctrole_crmrole_id = (select crmrole_id from public.crmrole where crmrole_name = ‘Insurance’))" );
This should return only our custom roles Insurance
But when I click the list menu of the cluster, all the crm accounts are returned.
So after reviewing the _listAndSearchQueryString in the crmacctCluser.cpp, I changed the extra clause to
ins_typecombo.extraClause(" id in (select crmacctrole_crmacct_id from public.crmacctrole where crmacctrole_crmrole_id = (select crmrole_id from public.crmrole where crmrole_name = ‘Insurance’))" );
to try to match the metasql query.
This did not work either.
SELECT *, getcontactphone(cntct.cntct_id, ‘Office’) AS contact_phone, formataddr(addr.addr_id) AS street
FROM
(
SELECT crmacct_id AS id, crmacct_number AS number,
crmacct_name AS name, cntct_id,
crmacct_active AS active, cntct_addr_id AS addr_id
FROM crmacct
LEFT OUTER JOIN crmacctcntctass ON (crmacct_id=crmacctcntctass_crmacct_id
AND crmacctcntctass_crmrole_id=getcrmroleid(‘Primary’))
LEFT OUTER JOIN cntct ON (crmacctcntctass_cntct_id=cntct_id)
) crminfo
LEFT OUTER JOIN cntct ON (crminfo.cntct_id=cntct.cntct_id)
LEFT OUTER JOIN addr ON (crminfo.addr_id=addr.addr_id)
where id in (select crmacctrole_crmacct_id from public.crmacctrole where crmacctrole_crmrole_id = (select crmrole_id from public.crmrole where crmrole_name = ‘Insurance’))
returns 505 rows in the Query Analyzer.
I just dont know if the extra clause is inside or outside the crminfo group.
So is my extra clause wrong or is it not sent to the List() function?
Lastly, the _listAndSearchQueryString should be in the metasql.
the FROM crmacct() function takes over 7 seconds to return the rows and only 145 ms when I remove the ()
I know this function uses security, but we would prefer the faster query
Thank you
Bob