Many times I’ve been stuck making a report and wished there was an easy way to use two fields on the same record as a sorting mechanism.
For example, my company had an issue with our Cases. Our Cases relate to both Accounts and Contacts.
We wanted the Contact listed on the Case to be associated with the Account listed on the Case – but this didn’t always happen as it should have.
This meant that, occasionally, the Case would be related to a Contact and an Account that weren’t related to each other… confusing for end users.
As a preventative measure, we put Lookup Filters in place – but how could we get a report on the existing Cases, where there was a mismatch, to correct them?
I creatively attempted – and failed – to drag the Account field onto the Report filter panel and set it to “Not Equal To” Contact.AccountId but no dice!
This, though, led to wondering how to do that – and as the title suggests – a formula is the answer!
I made an extremely simple text formula on our Case object named “Contact Match” using the following: IF(AccountId = Contact.AccountId, “1”, “0”)
Magic! This formula doesn’t need to be on a layout and will display a “1” whenever the Account and Contact match and it will display a “0” when they don’t.
Then I ran a report on all Cases where “Contact Match” = “0” and BOOM! All the mismatches materialized before me 🙂
I’ve learned on several other occasions that if I’m hitting a roadblock with a report the answer might just lie in the creative use of a formula.
Another example, that I certainly didn’t invent, is The Power of One: The Greatest Formula Ever Written, which has proven useful many times.
This simple formula allows you to put a “1” on every record of a specific object. You can use this in tandem with report summaries to generate specific counts of records in your reports.
This is fantastic for reports using multiple objects such as “Accounts with Contacts” that show you the Account record multiple times for each Contact associated with it. Now you can count exactly how many Accounts and Contacts there are!
The key takeaway: Formulas + Reports = Magic!