Changes

{{SeqReportPageHeaderBanner-UnderConstruction}}

In this =Overview=This article, will run you will create through the process of creating one of the most useful [[Reports|reporting ]] features – available in a [[SmartSimple]] [[instance]]: the ability to relate the information from two recordsets (tables ) into a single report. 

In this process specific exercise, you will explore “joining” multiple tablescreate a query that uses the company table and the contact table.

In this exercise, you will create =Connecting Multiple Recordsets for a query that uses Report=1. Click the company table and 9-square menu icon on the contact tabletop right of your page.

1:: {{Icon-Menu}}2. Create a Under the tab '''Tools, '''select '''new reportReports. ''' with the following values:

[[Image:Reps100.png]] 2. '''Save''' the reportThe list of available reports in your system will be displayed.

You will now select 3. Click the '''+ icon '''on the two tables required in top left, above the report[[List View Overview|list]] of reports, to create a '''New Report.'''

34. Scroll to the The '''TableNew Report ''' listpage is displayed, with fields for you to fill out.

4:: [[File:Reports multiple tables new. Set png|800px|border]]For this example exercise, fill out the primary table to following fields with the '''Companies/Accounts'''.corresponding information: 

[[Image:Reps99.png]] 5. Set the * '''ContactReport Name: ''' table as an List Companies and Contact* '''Inner JoinReport Description: '''This report joins the company and contacts table. 

=5. Click the '''Save '''button. You will now select the two tables that are required in the report. 6. Click on the new tab called '''[[Report Recordsets|Recordsets]] '''in the top row of tabs.  :: [[File:Recordsets tab.png|750px|border]] 7. Set the primary table to '''Primary Table '''to '''Organizations '''and the '''Inner Join '''as '''Contacts.''' To do so, simply set both the '''Organizations '''field (found under the '''Company and Account '''heading) and the '''Contacts '''field (under '''Users and Outer Joins=Contact) '''to '''Inner Join: '''

* An {| class="wikitable"|-!|Inner Join||The'''inner  inner join''' displays the intersection between the two selected 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 organization and the contact table with an inner join, the report will find the matching record(s) from the contact table related to each organization. If no match is found, the record from the company will not be included in the report results. If multiple results are found in the contact table, the companyinformation will be repeated.

If no match |-!|Outer Join||The '''outer join''' is foundvery different from an inner join. Instead of limiting results to those from both tables, the record from the company is not included it limits results to those in the results"left" organization table.

If multiple results This means that if there are found no records in the contact organization table, a row in the company information report will still be repeatedreturned for that company, but with NULL values for each column from contact.

* An '''Only use the outer join''' is very different from an inner join. Instead if you would still like all results (regardless of limiting results to those in both tables, it limits results to those in matching records with the "left" company other table) to show up.

This means that if there are no records in |}1. Open the contact table, a row in '''Report Builder '''by clicking on the four-square icon above the name of the report will still be returned for that company, but with NULL values for each column from contact.

1:: [[File:Report builder icon. Open png|border]]2. The report builder is opened in a modal window. Click into each field and select the '''Report Builder'''.following information: 

2:: [[File:Recordsets multiple table example. Add the '''following fields'''png|700px|border]]:: [[Image:Reps98.png]] The '''Field''' list now contains fields from both tablesSee [[Reports#Classic Report Builder - Column Settings|Classic Report Builder - Column Settings]] for more information about the report builder columns. 

:: [[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}}