Report from history changes

[rslemer]

My user created a new filter and move many registry to another user

But this action affected more registry him wish

Is possible I generated a report from history changes to help them solved that ?

I wish now registry with changes assigned to ( xxx ) to another user ( yyy ) when date of changes greater than 12/5/2018 ...

[joebordes]

If you have ModTracker active on the module, try executing something like this directly in the database:

Code:

SELECT *
FROM `vtiger_modtracker_basic`
INNER JOIN `vtiger_modtracker_detail` on vtiger_modtracker_basic.id=vtiger_modtracker_detail.id
WHERE `changedon` > '2019-01-10 00:00:00' and fieldname='assigned_user_id'

[rslemer]

Works but only information displayed is CRMID, how I can associate a CRMID a another field allowed user find each registry?

The better if I can associate this CRMID ( at specific case a service contracts, field = Subject ), for example.

[joebordes]

You can join on any module table with the CRMID, for servicecontracts it would something like this:

SELECT *
FROM `vtiger_modtracker_basic`
INNER JOIN `vtiger_modtracker_detail` on vtiger_modtracker_basic.id=vtiger_modtracker_detail.id
INNER JOIN vtiger_servicecontracts on vtiger_servicecontracts.servicecontractid=vtiger_modtracker_basic.crmid
WHERE `changedon` > '2019-01-10 00:00:00' and fieldname='assigned_user_id';

Let me know how it goes.

[rslemer]

I can use a SQL report for this right? ( instead a use a directly in DB ) ...

[rslemer]

almost there ...

I wish translate a number of user to a name of user

[joebordes]

Join with the user table on whodid

SELECT *
FROM `vtiger_modtracker_basic`
INNER JOIN `vtiger_modtracker_detail` on vtiger_modtracker_basic.id=vtiger_modtracker_detail.id
INNER JOIN vtiger_servicecontracts on vtiger_servicecontracts.servicecontractid=vtiger_modtracker_basic.crmid
INNER JOIN vtiger_users on vtiger_users.id=vtiger_modtracker_basic.whodid
WHERE `changedon` > '2019-01-10 00:00:00' and fieldname='assigned_user_id';