Hello,I have a problem with communication entries that have been synchronized with Outlook with the Classic plugin.When a new appointment has been set, all team members can see it in TeamCRM.After synchronization it disappears from teamCRM and nobody can see it anymore. As far as I know, when synchronizing an entry with Outlook the Comm_OutlookId is set in the table "Communication".What do I have to do to keep the entry visible to all team members?
Thank you in advance Guido
I know that this isn't the newest version but maybe someone has a tip for me.
Have you checked in the database to see whether the communication record and the associated comm_link record exists? If a record 'disappears' from Sage CRM it make be that the security profile have caused the record to be hidden from the user. For example can the System Administrator see the records in the interface?
thank you for your Reply.
I don't see the records As an Administrator either
What rights do you mean and what do I have to check.
Do you have access to the database and are you happy running SQL statements?
If you are then you can look at the communication table and the comm_link table check whether the record exists.
You can also see whether the comm_deleted flag is set (This would supress the display of the record).
A simple check is to see whether the record is having its status changed so that it no longer is show in the list screens. Within the communication list page make sure that the filter box is looking for all records.
These are my Statements
select Comm_Deleted, * from Communication
where Comm_Subject ='Testtermin nicht löschen!!!'
select CmLi_Deleted, * from Comm_Link
where CmLi_Comm_CommunicationId = 49746
and both are not deleted
The filterbox, you mentioned, is set to all.
Do you have further suggestions
Try this: Run Sql profiler while this is happening.
Once done; check what queries making change in which area related to communication, may be you will get a clue if it is related to database or at least you will be able to narrow down the issue.
I'll do it. But I wonder how far it helps.
In the end I will see a SQL statement in which a value is entered in the column. From then on I don't see the entry in the team calendar anymore.
If I write "NULL" again, the entry is visible again.
I'll let you know when I'm through with it.
as promised here come the results:
we found three statements that are related to the subject.
SELECT COMM_COMMUNICATIONID, COALESCE(comm_recurrenceid, -1) comm_recurrenceid FROM COMMUNICATION WHERE COMM_COMMUNICATIONID = 49851
SELECT L.cmli_comm_personid AS PersonID, L.cmli_comm_companyid AS CompanyID, L.cmli_comm_accountid AS AccountID FROM COMM_LINK L WHERE L.CMLI_COMM_COMMUNICATIONID=49851 AND COALESCE(L.CMLI_DELETED, 0)=0
UPDATE COMMUNICATION SET COMM_OUTLOOKID='3860718720', COMM_MEETINGID=NULL WHERE COMM_COMMUNICATIONID=49851
After the record disappeared I set the column "outlookID" to "NULL" again. As stated before, the record was visable in the teamcalender and it vanished after I set it back to '3860718720'.
I hope someone else has got a clue
is it a select statement that picks up the data to display the appointments in the temcalender or is it one of the views?
while openig the teamcalender I caught the SQL-Statement
In the where-clause is the filter "AND (comm_outlookid IS NOT NULL"
What do I do next? The where-Clause seems to be generated dynamically. Where can I change the where clause?
select * from vListCommunication WITH (NOLOCK)
((Comm_DateTime >= '20190318 00:00:00'
AND Comm_DateTime < '20190325 00:00:00')
OR ( Comm_Type = N'Appointment'
AND ((Comm_DateTime < '20190318 00:00:00'
AND Comm_ToDateTime >= '20190318 00:00:00')
OR (Comm_DateTime < '20190325 00:00:00'
AND Comm_ToDateTime >= '20190325 00:00:00'))))
AND (comm_outlookid IS NOT NULL
and CmLi_Comm_UserId IS NOT NULL )
AND comm_status = N'Pending'
AND comm_type <> 'RecuMaster'
AND ((COALESCE(Comm_Private, N'') = N'')
OR ((COALESCE(Comm_Private, N'') <> N'')
AND (CmLi_Comm_UserId = 1)))
AND Comm_Type = N'Appointment'
ORDER BY Comm_DateTime
Good work Guido,
This looks a clear bug to me. so far I remember by the query this was resolved around 2017 R3.
Technically the check for Comm_outlookid should be removed from the query running for team calendar.
Is there anything I can do? For the next one or two years we are bound to that CRM-Version.
Just a wild one if it works provided you will get a suitable where condition to apply:
Try if you can apply a Table Level Script or A trigger on update of Communication table to set Comm_outlookid = NULL.
If it don't create any other issue, you will be able to see the communication on Team calendar always.
Have fun :) !!
© The Sage Group plc 2017All Rights Reserved