Is there a way to determine (on SQL level) which user is responsible for setting activity (task, appointment) as finished (status) Entities (Activity pointer, Task, Appointment) show only users who created/last modified the record.
(lastmodified user is the right answer only for a short time when somebody updates the record then the responsible user disappears..)
/I use an entity dump as a source for our DW, determine who finished the appointment/
update [dbo].[LOCAL_COPY_ActivityPointer]
SET
[DW_RefAccountId] =[RegardingObjectId_Id],
[DW_FinishedBy_Id] =[CreatedBy_Id], [ModifiedBy_ID], .. -- GUESS WHAT, WHERE :)
WHERE RegardingObjectId_LogicalName ='account' and ActivityTypeCode in ('appointment', 'task')
When an activity is completed/finished/closed, the record will become inactive (read only). That being said “last modified by” will be the right user who completed it. Unless you have some SSIS or ETL job which does API updates to the inactive records - this will be intact.
Even if someone reopen and reclose, the new user will be the one who touched it last.
Otherwise you have to capture in a separate dedicated field for storing “closed by” user by using some plugin.