This was originally posted as a bug (#7086), but I’m not so sure that it is now. Unless it is a bug in migrating data. As follows:
Existing (or new) Project Templates do not show up in any lists expecting such.
Selecting Project Templates from the menu or from any association list, like creating a new Project and trying to select a Project Template to use, comes up empty.
Querying the tables directly shows that there are existing Project Templates.
The query error, which appears in the SuiteCRM.log is:
DECLARE @topCount INT
SET @topCount = 26
SELECT TOP (@topCount) * FROM
(
SELECT
am_projecttemplates.id ,
am_projecttemplates.assigned_user_id ,
am_projecttemplates.name ,
am_projecttemplates.status ,
am_projecttemplates.priority ,
LTRIM(RTRIM(ISNULL(jt0.first_name,’’)+N’ ‘+ISNULL(jt0.last_name,’’))) assigned_user_name ,
jt1.user_name assigned_user_name ,
jt1.created_by assigned_user_name_owner ,
N’Users’ assigned_user_name_mod,
am_projecttemplates.created_by ,
ROW_NUMBER()
OVER (ORDER BY am_projecttemplates.date_entered DESC) AS row_number
FROM
am_projecttemplates
LEFT JOIN users jt0 ON am_projecttemplates.assigned_user_id = jt0.id AND jt0.deleted=0
LEFT JOIN users jt1 ON am_projecttemplates.assigned_user_id=jt1.id AND jt1.deleted=0
AND jt1.deleted=0 where am_projecttemplates.deleted=0
) AS a
WHERE row_number > 0
The query failure is that assigned_user_name alias is used twice in the same query (Select lines 6 & 7 above).
Changing the second assigned_user_name alias to something like assigned_user_name1 in SSMS produces an expected result.
I could use a workaround in the meantime, if possible. I have no suggestions, I have no development environment setup to trace this in detail. But, I have a project to bring live soon which depends in part on this functionality (multiple pre-prepped Project Templates).
Environment is a WISP:
- (Azure) Server 2016
- PHP 7.2
- Azure SQL backend
- SCRM v7.11.2 (fresh install, not upgraded; just migrated Users, Accounts, Users, and the modifications to the Users and Accounts tabs using built in export/import functions from the test install)
Thanks for any help…