Field ‘Not Equal’ to Field: Reporting solutions – Jenny’s Admin Tip #51

We all know the importance of reporting in Salesforce. The gained visibility that your Sales, and Service teams, Admins etc, receive from the visual representation of data in Salesforce via Reports and Dashboards is invaluable. Whether you are reporting to see the trends in your sales pipeline, closed won opportunities this month or the number of completed activities by sales person – reports and dashboards are not only valuable, but can be generally straightforward to create; especially with the user friendly report builder.

However, there may come a time when you as a user are unable to create a report without having to make amendments to the records you are wanting to report on. This is a perfect opportunity to call in your Admin.

I myself came across a great example of this when a colleague asked if I could create the following report which would show the :-

Total Amount of Invoices which are NOT EQUAL to the Total Amount of Order Products per record.

Now the first thing that popped into my head was to simply filter two fields with a ‘NOT EQUAL’ to operator in the report builder. HOWEVER, that would be too easy and I soon learned that the option to do field to field filtering is not actually possible. Yes, a pain in the you know what. So, before you continue the first thing you should do to save you having to the do the following in the future is to VOTE up the idea to resolve this issue here.

Here is how I resolved the issue:-

The second solution that came to mind was to create a Formula field on the Order object that would provide me with a field in which I can report on.  Now you may ask why I didn’t create a formula straight there in the report? Well that is simply because it is not possible to compare data in a report i.e. field <> field or field > field etc. Formulas in reports basically allow you to create custom summaries based on calculated values using report data.

Ok! Back to the solution:-

Field NOT EQUAL to Field.

1. First create a new formula text field on the Order object Setup>Create>Orders>Fields>

   1. Add a label, description etc.

   2. Your Formula Text Return Type will ‘Text’.

2. Create the formula that will populate the formula field. We have identified that we want the field to determine whether the Total Amount of All Invoice is Not Equal to the Total Amount of OP. Therefore you could use the following basic Syntax –

IF( Order.TotalAmount  <>  Order.InvoicedTotalAmount__c,  “NOT EQUAL”, “EQUAL”)

(p.s. NOT EQUAL, EQUAL, could be TRUE, FALSE, any text you desire, just like you can use a checkbox field type instead of a text field type!)

3. Continue to save the field, choosing the desired field level security.

4. In the report builder you can then continue to create a report which will provide the required data based the new field that you have created.

(Excuse the mega long field name, it’s just an example!)

There you have it. By creating a simple Formula field you can give your user the Report data they require.

See you next week!

Jenny