Reporting on data that doesn't exist

Blog Gerbrand Spaans | 04-05-2018 Salesforce

Working on a SaaS platform can have special difficulties as there are usually certain rules to the game that are strictly maintained by the platform. As awesome admins we try to work around these limitations as good as we can. Oftentimes this is very frustrating, but managing to work around it is what makes it fun, right?

Often a client asks for insight into the number of deleted records, essentially asking us to create a report on data that doesn’t exist. Impossible! So let’s go and do the impossible! Luckily Salesforce has a recycle bin where deleted records get dumped before purging them. So how does this work?

First of all, all records in Salesforce have, apart from the standard fields you see on a layout, a number of fields that are hidden and used by the platform to make certain decisions. The isDeleted field is an example. Setting the isDeleted field to true apparently deletes the record, filters it from any List View and in general from Salesforce, with the exception of the Recycle Bin. There it will be, for 15 days, after which it is purged.

So showing deleted files should be easy right? Nope.

After looking around I found a nice SOQL statement that allows some more advanced querying: adding the ALL ROWS statement to the end of a query should make all records, including the deleted records, available to the query engine so we can start filtering and showing deleted records.

Hmmm… so much for a quick demo…

The ALL ROWS statement seems to only be available when called from Apex, so to show the full demo we have to so some coding. As the main question that got me started was “Can we have a graph of the number of account records deleted each day?”, we are going to create exactly that.

First let’s start with the custom controller. The magic happens in the function getAggregateAccountData(), where we query all deleted accounts, and group by the date to get a daily count of deleted account records.

As Salesforce purges deleted records after 15 days, we will get returned a maximum of 15 rows. We then pass on these rows to a function to make these rows available in an object list which will be used by the VisualForce graph.

And the VisualForce page code:

So there we are, a Page that you can set on your homepage to look at on log in to see what happened!

If you’re interested in knowing more about Salesforce development, have questions or want to know how to get this on your production org, feel free to contact me through any of the social channels. Happy coding!

Find out what we can do for you!
Contact us