Salesforce Reports – To Right Join or Not to Right Join

 

Those that are a bit more used to will be aware of a Right Join which is basically a list of all the records on one table with any records from the left where they link.

When creating a new report the following dialog box is shown:

Salesforce has politely provided a report template of object x with object y. is this a classic right join or something more sinister (or could get you in a lot of hot water).

Some of previous posts have used a Travel table – dependent picklists, adding text to page layouts and new dialog pages.

The Travel tables simply holds a name of holiday, continent and country. Lets add a new table called ‘Arrival Info’ which can hold information on how arrival is made (plane, boat, car, other) and the cost.

In the travel object we will have a lookup to the Arrival table, called ‘Arrival Info’.

Three records in the Travel table have been set up and one has some arrival information.

So the challenge is to get a report with all the the travel information with some information where entered from the arrival information – lets say ‘Method of Arrival’.   The ‘all’ and ‘where entered’ are highlighted as this is important.

The create new report will have the report we need under ‘Other Reports’:

Select this and ‘Create’.

Oh dear.  In the Salesforce world a report of object x with object y actually means ‘show me a report of only the items of object x where there is an object y’.

How about start with just the ‘Travel’ table and add the details from the ‘arrival’ object?

So new report selecting ‘Travels’

The create report screen allows the ‘Arrival Information’ to be added.

Initially this looks better as we now have all three travel items and the arrival info for the one record that has the lookup.  But we have no way (that I know) of adding items from the associated arrival information.

We are going to have to cheat.  The dictionary definition of cheating in the context of Salesforce is ‘to provide an ugly/odd/weird workaround that works but is not particularly efficient or cool.’

The cheat way around this is to add a formula on the Travel table that ‘fetches’ the information from the Arrival Info table and presents ready for reporting (we need not display on any layouts – indeed this would expose the cheat). Think of this as ‘bubbling’ up data from another table.

So back into Setup and the Object Manager.  Select our Travel object and under ‘Fields & Relationships’ click ‘New’.

Choose ‘Formula’ and ‘Next’.  As we are interested in say the Arrival method, , enter ‘Arrival Method’ as the field label and then select ‘text’.  This bring the formula screen.

Select ‘Insert Field’ and the select ‘Arrival Info >’.  The > shows more fields, this is using the lookup relationship to dig into the other object. Select ‘Arrive By’

Click ‘Insert’ (not close). This will show

Arrival_Info__r.Arrive_By__c

The __r is hidden in the documentation (there is so much documentation most things are hidden under the sheer weight of other documentation – so no criticism implied) and refers to the relationship (I think of this as r for relationship).

Click Next and things will fail !!  As we have a picklist in for the Arrive_by record things are not so straight forward but add a simple text() around the formula and we are good to go.

Text(Arrival_Info__r.Arrive_By__c)

Click ‘Next’ to move to screen and then ‘Next’ to go to the last step.

It is important to remove this formula from any page layout (unless you really want it).

Back to Reports and pick ‘Travels’ and back to the create report screen:

At last we are there – a report with all the travel records plus information where possible from the arrival record.  Right Join in SQL land.