Changes

Jump to: navigation, search

Reports Based on Multiple Tables - Overview

24 bytes added, 17:17, 8 July 2019
no edit summary
=Overview=
 
This article will run you through the process of creating one of the most useful [[Reports|reporting]] features available in a [[SmartSimple]] [[instance]]: the ability to relate information from two recordsets (tables) into a single report. 
=Connecting Multiple Recordsets for a Report=
 
1. Click the 9-square menu icon on the top right of your page.
:: [[File:Company and contact inner join.png|250px|border]] 
: :* The '''Organizations '''recordset will report on company information that you have entered into the system, including all organization-specific [[Custom Fields]] that are related to organizations.:* The '''Contacts '''recordset will report on all contacts ''outside ''of your organization (although this does not automatically mean that they are [[External|}external]] [[User|users]]). This table will include all contact-specific and [[User Role|role]]-based [[Custom Fields]].  
8. Scroll down to the bottom of hte page and click '''Save.'''
There are two types of joins supported in [[SmartSimple]] – inner joins and outer joins.
:* An '''inner join''' displays the intersection between the two tables. ::* This is the most common type of join used, and is the default join type when you [[Adding a Table to a Report|add tables to a report]].::* When you join the company table and the contact table with an inner join, the report will find the matching record(s) from the contact table related to each company.::* If no match is found, the record from the company is not included in the results.::* If multiple results are found in the contact table, the company information will be repeated. * An '''outer join''' is very different from an inner join. Instead of limiting results to those in both tables, it limits results to those in the "left" company table.
: :* An '''outer join''' is very different from an inner join. Instead of limiting results to those in both tables, it limits results to those in the "left" company table.::* This means that if there are no records in the contact table, a row in the report will still be returned for that company, but with NULL values for each column from contact.
:
1. Open the '''Report Builder'''.
:: 2. Add the '''following fields'''::: [[Image:Reps98.png]]::* The '''Field''' list now contains fields from both tables.
:
3. '''Build''', '''save''', and '''preview''' the report.
:: [[Image:Reps97.png]]::* The company name, city, and state information is displayed from the company table for each matching record from the contact table.::* No company details will be displayed if there are no contacts.
:
{{PrevNextStart}} [[Creating a Public – Internet Enabled Report]]
{{PrevNextMid}} [[Using an Outer Join]] {{PrevNextEnd}}
[[Category:Reports]]
2,299
edits

Navigation menu