Building reports around the connection record using related lookup fields

A lot of the information you will want to get out of your data is about people who have volunteered. And that information will be found in the Connection Record.

You will want your primary object for certain kinds of reports to be the Connection object -- however when you go create the custom report type - you'll find the secondary objects aren't there!    You COULD choose to build the report around the Contact object - but there's a better way.

You can get to fields you might not otherwise access, through the lookup fields that are in an object.  And the Connections record is FILLED with lookups!

Let's say we wanted to get a report on all connections, that included the Volunteer Type, the Location name, and other fields from other objects.

Go to setup / app setup / Create / Report Types

Go to setup / app setup / Create / Report Types

Here you'll see all your existing report types - and be able to create a New Custom Report Type.  

Click on New

We are going to create a New Custom Report Type based on the Connections Record as the Primary Object

We are going to create a New Custom Report Type based on the Connections Record as the Primary Object

Name this report "Connections with Lookups".

Make other settings as shown above.

We run into a problem however when we look for a secondary object to use with connections

We run into a problem however when we look for a secondary object to use with connections

Oh oh -- there are only two related secondary objects available.  How are we going to get the location name?  The occurrence start date? The Age of the volunteer?

Let's click on the X and go back to just one primary object:   Connections.

Save the report type. We are not going to add any other objects.  We'll use lookups to get the other fields we need!

Save the report type. We are not going to add any other objects. We'll use lookups to get the other fields we need!

Begin by editing the "Fields Available for Reports" section of the custom report type

Begin by editing the "Fields Available for Reports" section of the custom report type

Click on Edit Layout

The 43 fields that are part of a connections record are already in the layout

The 43 fields that are part of a connections record are already in the layout

We can however also add fields that are accessible to the Connections record via lookups.  To do this we click on the link that says "Add fields related via lookup"

Here we get a list of all the fields that have a 'lookup' connection to the connections object

Here we get a list of all the fields that have a 'lookup' connection to the connections object

So.... let's say we want to add fields that are on the Contact record to this report type.

Click on the link for the  Contact object

Now select the fields in the contact record

Now select the fields in the contact record

You now can put check-marks next to any fields in the contact record that you want to bring into this report!  Let's add Age, Background check completed and birthdate.  

Click OK

The new fields appear in the available fields list and are marked with a looking glass symbol

The new fields appear in the available fields list and are marked with a looking glass symbol

The view list to the right now has views for "Connection Fields"  and "Connection Fields (via lookup)

Select lookup again - and this time lookup items available through the Volunteer Opportunity Record

Select lookup again - and this time lookup items available through the Volunteer Opportunity Record

We can repeat the process grabbing other fields from other lookup objects.

Let's grab the Volunteer Opportunity name from the Volunteer Opportunity record.

Let's grab one from the locations record as well.  But how can we get to that?

Let's grab one from the locations record as well. But how can we get to that?

To do this we first select Occurrence  (as that's what Locations are related to)

From Occurrence we select location (which is a lookup from Occurrence) by clicking on "view related fields"

I don't want the Location field (its just an ID), but I want the related fields in the Location record.  So we click on the hyperlink to "view related fields" in the Location record!

And now we can select "Location Name"

And now we can select "Location Name"

Continue until you have all the fields you'll need for the report.

Optionally - you might organize the field layout by creating a new section for all your 'lookup fields"

Optionally - you might organize the field layout by creating a new section for all your 'lookup fields"

Clicking on the new section button allows you to name a new section of the layout.

(This will determine where the fields appear in the 'field tray' when creating or customizing a report.

You can drag and drop fields and determine where they show up, and in which order.

You can drag and drop fields and determine where they show up, and in which order.

You can also optionally click Edit Properties and determine which fields you want to have automatically on the report layout to begin with.

You can also optionally click Edit Properties and determine which fields you want to have automatically on the report layout to begin with.

By checking this, the field STATUS will automatically appear on the layout when you create a new report.  

You can set as many as you want as 'checked by default".

Click Save after you have completed customizing your report layout.

Now, your new report type is available when you create a new report!

Now, your new report type is available when you create a new report!

Note:  Rather than creating a lot of different variations on the Connections with Lookups report type, you can always edit this one and add additional fields via lookup.

This Connections with Lookup report type that you can create, is potentially your most powerful and useful report type!

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.