Control what your users can access and save time, money, and frustrations. Lock down sensitive data in SugarCRM or SuiteCRM to specific groups or teams. Supports unlimited assigned users, unlimited group assignments to records, custom layouts for each group, login/sudo capabilities and much more.
#4424 - Dashlet - Relationship additional assigned users - Query failed, id is ambiguous
Hi there,
We are using SuiteCRM 7.11.10 and the latest SecuritySuite install (3.1.21).
Since we've installed the module, no records are visible in the "My calls" dashlet. The following error is visible in the log file: Query Failed: SELECT DISTINCT calls.id ,calls_cstm.kans_score_c, calls.parent_id , calls_cstm.campaign_id_c , calls.assigned_user_id , calls.name , calls.parent_type , calls.date_start , jt0.name campagne_c , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, calls.created_by , calls.date_entered FROM calls LEFT JOIN calls_cstm ON calls.id = calls_cstm.id_c LEFT JOIN campaigns jt0 ON calls_cstm.campaign_id_c = jt0.id AND jt0.deleted=0 LEFT JOIN users jt1 ON calls.assigned_user_id=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 where ((calls.status IN ('Planned') ) AND (calls.date_start>='2020-09-20 22:00:00' AND calls.date_start<='2020-09-21 21:59:59' ) AND ( (calls.assigned_user_id IN ('301a62e7-0c96-035f-cb71-5f0856d23b5e') OR id in (select record_id from securitygroups_additionalusers where deleted = 0 and module = 'Calls' and user_id in ( '301a62e7-0c96-035f-cb71-5f0856d23b5e' )) ) )) AND calls.deleted=0 ORDER BY calls.date_start ASC LIMIT 0,11: MySQL error 1052: Column 'id' in IN/ALL/ANY subquery is ambiguous
It seems that the "... OR id in (select record_id ..." part of the query is wrong, it should be "... OR calls.id in (select record_id ...", because there is another link to the campaigns module that is also providing an 'id' field, which makes the field ambiguous when calling without the table prefix.
I tried to find where this is located so I can fix it myself, but didn't manage to get there.
Thanks a lot!
-
"Absolutely necessary for limiting user visibility."
Read More Reviews
- Most Recent Love from Users
kacsoristvan- damien8105
- anri
- Administrador
- Stefano1
- einkauf2
3 years ago
Usually it's a relationship that needs a little help getting squared away after install. Can you please run these 3 steps from the Admin->Repair options and see if that resolves the issue for you?
Let me know if anything changes for you after running all 3. If not, if you could list any other add-ons you have installed and/or code-level customizations made to the Calls module that will help pinpoint how this may be happening on your instance.
Thanks!
3 years ago
There is no custom coding apart from some logic hooks triggered on 'process_records' and 'before_save'. No other addons are uploaded apart from a Dutch language pack. The only thing that is custom here, is the custom relationship between calls and campaigns. This isn't a regular relationship, but it's a linked field.
3 years ago
Hi eggsurplus,
Thanks for your quick reply. Unfortunately, this didn't work. Still the same error is occurring.
Thanks!
3 years ago
Thanks for giving that a shot. This query is a dynamically created statement that has a lot of variables/moving parts involved so unfortunately it isn't just a missing bit of code. For some reason SuiteCRM is having trouble creating that statement in just your instance. I will try to recreate the issue locally and find some possible reasons for this to happen. Then we will be able to provide some alternative solutions.
Give me a couple of days to work through this one.
Thanks!