395
Hi ,
Is there any easiest way to find out the records created by a user in CRM? I want to check all the records created by a user in one of the CRM on premise instance. I know we can use the linked entities with outer join but i. have lot of entities and need to find out for around 70 or 80 users so want to write an SQl query and search across the DB.
You can query the metadata to find all entities that have the 'createdby' attribute, and use a SQL cursor to dynamically query each one. The following will give a list of all records and who created them, so you may want to filter it down before you run it
declare cur cursor fast_forward for select e.ReportViewName, e.Name as EntityName, aPrimary.Name as PrimaryName from AttributeAsIfPublishedLogicalView a join EntityAsIfPublishedLogicalView e on a.EntityId = e.EntityId join AttributeAsIfPublishedLogicalView aPrimary on aPrimary.EntityId = e.EntityId where a.Name = 'CreatedBy' and e.ReportViewName is not null and aPrimary.DisplayMask & 256 = 256 -- Get Primary Attribute order by e.Name declare @sql nvarchar(2000), @ViewName sysname, @EntityName sysname, @PrimaryName sysname open cur fetch next from cur into @ViewName, @EntityName, @PrimaryName while @@FETCH_STATUS = 0 begin set @sql = 'select ''' @EntityName ''', ' @PrimaryName ' , CreatedByName from ' @ViewName ' order by CreatedByName' exec (@sql) fetch next from cur into @ViewName, @EntityName, @PrimaryName end close cur deallocate cur
Hello,
You can use "Union" For that purpose. Here is an example:
Share
Report
All responses (
Answers (
Select
'account' recordType
,a.accountid recordId
,a.name recordName
From FilteredAccount a Where a.createdby = 'Put user guid here'
Union
Select
'contact' recordType
,c.contactid recordId
,c.fullname recordName
From FilteredContact c Where c.createdby = 'Put user guid here'
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156