0

I want to be able to show a report with groups that have more than one of an asset. In SQL Server I would do it with a query like this:

Select AccountID, Count(AssetID) From Assets Where AssetName Like 'My asset' And Count(AssetID) > 1 Group By AccountID

Which would only return the Accounts that have more than 1 of that specific asset.

I can do all of that in report builder in an an "Accounts with Assets" report, except for the Count(AssetID) > 1 part.
Is that possible?

I would love to avoid having to scroll through several thousand rows looking for the few that got doubled up in the data import.

1
1

You can't do this with a report, but you can certainly do this with a query. Click on the Gear ⚙ icon, and then Developer Console, click on the Query Editor tab, and enter the query as follows:

select count(id) assetCount, accountid Id
from asset
group by accountid
having count(id) > 1

This will give you just those accounts with more than one asset.

2
  • This kinda works. Not as convenient as doing it in a report, since I have to copy each account id to the address bar to bring up the record that needs to be fixed, but it kinda works. This is a seperate question, but does dev console allow for using IN? like "Where AccountID in (select AccountID from asset ...) – AndyD273 Feb 2 at 15:50
  • @AndyD273 You can use any query tool you want, such as Informatica, Apex Data Loader, etc. For mass updating/deleting/whatever records, an ETL (Extract, Transform, Load) tool is preferable. – sfdcfox Feb 2 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.