Microsoft Dynamics CRM (Archived)

How to delete all accounts except which contain Opportunity?

(0) Share

Report

Posted on by Community Member

I Need to delete all accounts, but i have some accounts with Opportunity and i want to keep it.

So, How can i delete all account except which contain Opportunity ?

I Have tried bulk delete to sort accounts which do not contain Opportunity, but I can't.

How can i help you?

Thanks

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

tpeschat 4,930 on at

Like (1)

Report

RE: How to delete all accounts except which contain Opportunity?

Hi,

with advanced find it is only possible to look for accounts assigned to an opportunity (Look for Account --> Opportunity --> Opportunity contains data). But not for accounts not assigned to an opportunity.

Do you have access to the database or to SQL Report builder?

Then you could fetch all accounts not assigned to an opportunity via SQL.

Then you could mark Thete accounts via an import with some Special criteria (e.g. additional custom field for that purpose, or some "unused" field) for bulk deletion. Then you could setup a bulk deletion run based on that criteria.

There might be easier approaches as well, e.g. via Scribe (if you would use that for your Interface).

hth Thomas

Was this reply helpful? Yes No
tpeschat 4,930 on at

Like (0)

Report

RE: How to delete all accounts except which contain Opportunity?

BTW, why do you want to delete These accounts. If you delete them, they are gone from the database and can't be restored. Wouldn't deactivating them suffice?

br Thomas

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (2)

Report

RE: How to delete all accounts except which contain Opportunity?

Hi Le,

Go to Advance Find Select Account -> then Go to related records and select Opportunities(Account) then in next column Select Account and in next column select "Does Not contain Data and run the query -> which give you all the account except which contain the opportunity.

Thanks,

Mohit

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to delete all accounts except which contain Opportunity?

Thanks for your help,

I will try to access to the Database. But, access to what field in database? Can you guide me more detail?

In addition, i need to remove all account to sort data before import to Dynamics CRM.

Thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to delete all accounts except which contain Opportunity?

Sorry, but Dynamics CRM is only possible to look for accounts assigned to an opportunity as Thomas said, therefore, your way can not display what accounts do not contain Opportunity.

Thanks

Was this reply helpful? Yes No
tpeschat 4,930 on at

Like (0)

Report

RE: How to delete all accounts except which contain Opportunity?

Hi,

something like:

select accountid, name from dbo.FilteredAccount

where accountid NOT IN (select customerid from dbo.FilteredOpportunity)

I used the filtered views, because if you would add that to a report in report builder, to import the report to CRM, you need to use the filtered views instead of the tables.

I hope customerid is correct, since in my implementation no accounts are in use, but only contacts. So in my case I would have selected contactid ...

hth Thomas

Was this reply helpful? Yes No
tpeschat 4,930 on at

Like (0)

Report

RE: How to delete all accounts except which contain Opportunity?

Just saw you are using CRM Online. Well there SQL reports might not work and also direct access to database isn't supported. So you need to work with fetch XML reports there ...

So maybe you Export all your opportunities to CSV and all your accounts and then compare with SVERWEIS in Excel or in Access db, which accounts are not part of an opportunity ... a.s.o.

Was this reply helpful? Yes No
Sukh Gill 967 on at

Like (1)

Report

RE: How to delete all accounts except which contain Opportunity?

I would recommend creating a Marketing List and follow these steps:

1. Create new Marketing List (Accounts to delete). Type = Accounts
2. Add all Accounts to Marketing List
3. Then remove Members from Marketing List using Advanced Find (Do an Advanced Find of Accounts that have a link to Opportunity). This will remove all Accounts that have an Opportunity
4. Then do an Advanced Find on Accounts linked to the above Marketing List (Accounts to delete) which will return all Accounts with no Opportunity. Then delete accounts returned.

Thanks
Sukh

Was this reply helpful? Yes No