Salesforce Filter Logic 1 or 2 or 3

Understanding how to create data sets in Salesforce is cardinal to creating accurate reports. The data you and your users desire to report on is not always stored in records from a single object. Many times yous will need to join data together from diverse objects to create meaningful reports. But with then many ways to join information together, it's crucial to know when to employ each method.

We will exist utilizing custom report types and cross filters to create the following data sets:

Records from one object
Parent records with child records
Parent records with or without child records
Parent records without child records

Here is the trailhead module on reports if you are brand new to Salesforce or demand a refresher.

And here is the sample data nosotros will be using:

Parent Records (Accounts)
Parent Records (Accounts)

Child Records (Opportunities)
Child Records (Opportunities)

The business relationship table is the parent object and the opportunities table is the child object. This is accomplished past a lookup field on the opportunity object that can optionally specify an account record.

Records from ane object

This is every bit unproblematic as it gets. There are no joins when creating this kind of report. Analogous to a list view in Salesforce, when yous only specify 1 object in your report type you lot will simply have admission to the data stored in the fields divers on that object for your columns*. Every row in this dataset represents a tape. If there is no record, there will be no row in the information set. This is the concept of the "primary object", which applies to all the report types we'll exist covering. If at that place is no record from the chief object, nosotros will non see a row in our data set. In the sample data higher up, each tabular array is already showing what the resulting data set would wait like. Hither is the account and opportunity data set:

Many standard objects already have a written report type but titled the plural name of that object – "Accounts", "Opportunities", "Campaigns", etc. For custom objects, this report type volition exist if there are no master-particular relationships defined and you've set the "let reports" choice to true in the object definition. Otherwise, you will have to create this written report type yourself. When creating the custom report blazon, select the desired object as the principal object in stride ane and don't specify any other objects in step 2.

* You do accept the choice to fix a filter based on fields defined on a child object by way of a Cross Filter. Even though those fields can't be displayed as a column in your report, there are many cases where this Cross Filter solution volition piece of work wonderfully for showing a de-duped list of parent records using filters set exclusively on the child object. Here'south an example showing just the accounts that have opportunities with amounts greater than $1,000,000.

Parent records with child records

This is an "inner join" in SQL terms, which ways the resulting data gear up will display a row for every unique combination of matching records between the two tables. More than on inner joins hither. The "match" occurs when the ID of a parent record matches the ID specified in the lookup field on a child record.

Using the sample data in a higher place, nosotros should expect to encounter a row for every lucifer of an business relationship to an opportunity record. A movie is worth a thousand words here – the resulting dataset looks like this:

Notice that the "Dream Big Inc" account and the "Patty's Deal" opportunity are non represented in this data prepare. This is because the "Dream Big Inc" account does non have whatever kid opportunity records and the "Patty'south Deal" opportunity record does not specify an business relationship record. Since our primary object is "Accounts" in this report type, a record will not be represented unless it is related to an business relationship record.

Setting up the report type is simple: First, choose the parent object as the primary object.

So, choose the child object as the related object.

Be sure to specify the selection for "Each "A" record must have at least ane related "B" tape."

Parent records with or without child records

This is a "left outer join" in SQL terms, which means the resulting data set will display a row for every unique combination of matching records betwixt the two tables, and so will testify a row for every parent record that does non have a child record.More on left outer joins here.

Using the sample data above, the resulting dataset would be the following:

The "Dream Big Inc" account appears in this report only does not have any values showing for the opportunity fields that are included every bit columns. The "Patty's Deal" opportunity record is not accessible here since our primary object is "Accounts" in this report type. A record volition not be represented unless it is related to an business relationship record.

Creating this report type is very similar to scenario #two. But exist sure to specify the option for " "A" records may or may not accept related "B" records."

Parent records without child records

At that place are ii reports types you tin use to achieve this one. One option is to start with the "Accounts with or without Opportunities" report blazon we created in scenario three and and then apply a cross filter within a report to ensure nosotros simply include Accounts that do non have child opportunities.

The resulting information set looks similar this for our sample information:

While that will certainly work, you may not need to meet all the empty columns for the child object. This can as well be satisfied using the same cantankerous filter on the bones "Accounts" report type from scenario 1.

You won't have admission to the fields on the child object – but none of those columns will be populated anyway.

Stay Tuned for Part 2

That's all for now! Nosotros've covered the basic building blocks of written report types. In the next post of this two-part serial, nosotros'll be covering three more written report types that you tin can add together to your toolbox.