Create a List Report Showing Associated Records from a Second Table
Using Views you can create a one-to-many Report that displays information from more than one table. This can be perfect when you want to view results especially if you use Advanced Reporting to group and run aggregation information.
Because views are based on joined information if a stage has no associated records that stage will not appear in the View. In some situations (such as a workflow or pipeline) the fact that no records are associated to a particular stage may be notable information. This article shows a method to answer this concern using a nested iframe.
Overview
In this example we are going to create viewer that shows interview candidates in successive stages of the hiring process.
The real power of this technique comes with passing search criteria and the ability to customize the “No Records Found” message in the localization. These techniques are explained in sections after how to create the viewer.
This viewer consists of two separate Report DataPages. The Report lists the stages of the workflow, and the second report is an embedded iframe that shows the records in each stage.
Steps to creating a List Report with Associated Records
Create a category lookup table
If you haven’t already you should create a lookup table that contains all of the stages, categories, or workflow steps. This can be a simple list of all possible stages.
Alternatively, you can create a more complex lookup table that includes “Next Steps” for each stage. This is especially useful if your workflow has branching or multiple possible courses of action at each stage. You can see an example of how to create this type of lookup table (as well as manage “Next Steps” using Record Level Security) at In Action: Creating a Workflow Using RLS and Filtered Drop Downs.
Create a Report to see records filtered by stage
The first Report you are going to create will be the iframe Report. This report will receive the stage parameter and display all records with that stage.
Create a new Report DataPage. On the Data Source screen be sure to Enable Advanced Options and Parameters. Click Next. On the Search Type screen select Filter data based on your pre-defined criteria. Click Allow parameters in search criteria. And select Bridge and external parameters.
On the Select Filtering Fields page move your Stage parameter to the right panel using the arrow buttons. Press Next. On the Configure Filtering Fields page go to the Advanced tab. Select Receive parameter. This will automatically generate a parameter name. Save it for future use.
Now you can complete the form as normal. Here is an example Tabular Report:
Deploy your Report using iframe
Now deploy this report using iframe deployment. Copy the iframe deploy code for later use.
Create a stage List Report
Create a List Report using your lookup table as the data source. Make sure advanced options and parameters are enabled. Press Next. On the Search Type screen select Filter data based on your pre-defined criteria. Click Next, and click Next again.
You should now be on the Select Results Page Fields screen. In this example we are not including any bound fields in the Stage Report. Press Next. An HTML block will be created in the DataPage Elements panel. In this block we can add the stage name using the parameter insert button at the bottom right of the HTML Block panel.
After the Stage name parameter has been entered, copy and paste the iframe deploy code from the previous step. Locate the source URL of the iframe. Now we are going to pass the Stage parameter directly to the iframe. After the URL add a query string.
This will consist of:
- the “&” symbol,
- the name of the parameter the iframe report receives (copied earlier, in this example “Stage”),
- an equal sign
- an inserted parameter of the current field’s Stage value (use the HTML Block’s insert button to insert the current record’s “Stage” parameter)
Here is a sample result:
Complete the Report as normal.
Viewing the results
When you open the list report you should now see small iframe windows located in each Stage record showing any associated records to that stage. The iframe can be styled to remove the border and change the default size if necessary.
In this example we passed only the Stage parameter, but you could easily pass additional parameters from the List Report’s search page by appending additional query strings to the iframe’s source URL.
Customize the “No Records Found” message
A corollary expansion to this technique is customizing the “No Records Found” message. By adding links to that message you can not only see at a glance which Stages are lacking records, but also submit new records to those areas from the same interface.
Go to the Localizations section of Caspio Bridge. Make a copy of your default Localization by pressing the Copy button. Rename the copy so you can identify it later.
Double click the new Localization and press Next. On this page, locate the original record listing Report you created (the nested iframe Report which receives the parameter and lists associated records). Change the Localization of this Report to the current Localization by clicking the checkbox in the far left column. Click Next.
Under the Messages submenu, Element ID # 351 contains the “No Records Found” message. Click on the Enable Custom Text checkbox. In this panel you can add any valid HTML including links, buttons, images, etc.
In our example we added a button in the lower right hand corner to create a new Applicant record:
