Suitecrm: Project-Templates do not show up in any lists expecting such

[WTNetAdmin]

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.

Behavior appears to be replicated in the Demo version online. However, I don't know for sure that there are sample Project Templates on the Demo.

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)

Bob

[WTNetAdmin]

Correction... Creating a Project Template in the demo online does show up in the list and as an option when creating a new project and selecting a template.

[WTNetAdmin]

I guess my real question now is...how does SCRM create those queries. I suspect something got broken/duplicated somewhere as the join also looks a bit convoluted. But, I can't figure out the source of building the query...and I've read all the developer guides, I think, to try and find the source. Help, please?

[pgorod]

This is probably due to a difference in SQL treatment between MySQL/MariaDB in our demo and your back-end which is what, MS SQL Server?

[WTNetAdmin]

Thanks pgorod.

Yes, MSSQL. I'm pretty SQL savvy, though heavily on MS, but not unfamiliar with MySQL, and I don't think MySQL would tolerate this either, but would have to test.

I created a fresh and raw IIS set w/7.11.2 and went straight in, after base setup, and just created a single Project Template with a single Task Template Task associated, and got the same result.

Here is the failed query from the log, with the dupe field alias...however, the join is cleaner:

DECLARE @topCount INT SET @topCount = 21 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

I could spend the time to create a MySQL test as well, but...in general, SQL is SQL, and if MySQL is ignoring the dupe...that is still a problem.

I think there is a bug in this...

Bob

[WTNetAdmin]

Errr, ugghhh...I take back the cleaner join comment after review...but don't care about that part.

[pgorod]

I agree there is a bug somewhere. It doesn't have to be the fact that MySQL accepts the query and MSSQL doesn't - it can be the fact that SuiteCRM might have an if somewhere in the query generation code that writes different things for those two DB engines.

Ideally you would hook up a debugger and step through the query generation code and see where it produces this...

[WTNetAdmin]

Yep! However, I'm more of a manager these days and high level without a developer crew...and miss my development days...it is like heroin for me.

I don't mind making a change, but don't have the time in a day to setup and do such tracing, and was hoping someone who is buried in it could offer where to make a temporary tweak until next release...

Bob

[pgorod]

I am also not the guy to work on this now, sorry. Let's hope someone else can pick it up.

[WTNetAdmin]

Thanks!

[WTNetAdmin]

Okay...looks like @pgorod was correct. I set up a MySQL configuration for testing, and the same broken query is being created, but MySQL doesn't throw an error on it. However, I didn't look to see what the return values were...I'm guessing it either renames the second alias, or overrides the first, since you can't, or shouldn't at least, have both.

Doing this the painful way, without a development setup, and learning a lot about SuiteCRM in the process, this looks to be the problematic relationship, on the surface (but it is never that easy!):

In .\modules\AM_ProjectTemplates\vardefs.php starting @ line 143 to 171

    'assigned_user_name' =>
        array(
            'name' => 'assigned_user_name',
            'link' => 'assigned_user_link',
            'vname' => 'LBL_ASSIGNED_TO_NAME',
            'rname' => 'name',
            'type' => 'relate',
            'reportable' => true,
            'source' => 'non-db',
            'table' => 'users',
            'id_name' => 'assigned_user_id',
            'module' => 'Users',
            'duplicate_merge' => 'disabled',
            'required' => false,
            'massupdate' => 0,
            'no_default' => false,
            'comments' => '',
            'help' => '',
            'importable' => 'true',
            'duplicate_merge_dom_value' => '0',
            'audited' => false,
            'unified_search' => false,
            'merge_filter' => 'disabled',
            'len' => '255',
            'size' => '20',
            'ext2' => '',
            'quicksearch' => 'enabled',
            'studio' => 'visible',
        )

If I rem that out and repair and rebuild, Project Templates and all the associated functions appear to work correctly all the way down the road to creating projects from Templates and using them.

However, admittedly, I'm a newbie using SuiteCRM, and I may be overlooking something as yet.

But, that's as far as I can go with this...but it allows me to keep moving now until a fix or patch comes out.

Bob

[pgorod]

Interesting find. And with that commented-out, can you still assign project templates to users, and their name appears correctly?

[WTNetAdmin]

Yes. Everything seems to work well. It does seem to be connecting all the right dots, and DB tables look fine as well. I'll be digging back in deeper today and tomorrow to get back on with the production design and testing again, so I still might find something.

And, the SQL query being sent is not only squeaky clean, without the dupes and contorted join, but is also missing that weird LTRIM(RTRIM(ISNULL(jt0.first_name,'')+N' '+ISNULL(jt0.last_name,''))) piece. I would have expected both lines to match in trying to arrive at a assigned_user_name alias even if duped.

Which is why my comment about "it's never that easy". My suspicion is that somehow there are 2 parallel channels trying to create that query, and so by rem-ing out that relation, it cut off a channel, yielding a functional result, but that it is neither the real problem nor did it fix the real problem.

I tried for the life of me to figure out where that LTRIM(RTRIM... piece was getting built, with no success. But, it does seem to be somewhere in the DBManager cores. But, that part needs a debugging step and trace.

But, I could be wrong...

Bob

[WTNetAdmin]

Just for fun...here is the new query it is sending out:

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 ,
jt0.user_name assigned_user_name ,
jt0.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
AND jt0.deleted=0
where am_projecttemplates.deleted=0
) AS a
WHERE row_number > 0

So, you can see that it is still pulling in the Users table and data.

I also looked for a matching/conflicting Relationship array to the one above, but could not find one. It seemed in my searching that the Users table relationship was kinda an automatic given as needed and so was built automatically in most cases...which may be the source of the conflict as well...but I'm pretty iffy on that...lotta code and processes to learn.

[pgorod]

If you check the database in a table called relationships it might give you some clue. For example, if you find that the relationship is duplicated there, you might conclude this entry is not needed (because it is defined elsewhere).