In one-to-many relationships it is sometimes necessary to link directly between a single record in a results page and all associated records in another results page. This article shows how you can use a hyperlink and an AutoNumber parameter to link to between a single record in one results page and another results page displaying all associated records from a second table.
- Link to all log entries associated with a specific record (support ticket action logs, customer contact records, etc.)
- View all user records associated with a specific event record (attendees, signups, etc.)
Table structure:Two tables are used in this example, where one is named M2M_Student and the other is named M2M_Clubs. The Table Designs of both are displayed
These tables are associated by the Club_ID field. The Club_ID field is the primary key of the Clubs table and is a foreign key in the Students table. It creates one-to-many relationship because one club may have many students. For simplicity, each student can only belong to one club.
Now that you have created two base tables, you can start to create the DataPages.
Steps to link a results page to a results page:
Create the Club Report DataPage
- In the Caspio homepage, select and open an app. In the App Overview page, click DataPages on the sidebar menu and click New DataPage.
- This Report can be created as normal. Choose the M2M_Clubs table as your data source.
- You can configure the search functionality, results display, and details fields to your preference.
- When you approach the Configure Results Page Fields screen, insert an HTML block using the Insert button at the lower right of the DataPage Element panel. This HTML block is going to hold the link to the Student Members Report. For now, just type “Members” in the HTML Block panel as a place holder for the future link.
- Finish creating the Report DataPage and preview it inside of Caspio.
Create the Student Report DataPageNext, create the Student Members Report. This Report is different from the previous Clubs Report because it will not have a search page.
- In the DataPages listing, click New DataPage to open the DataPage Wizard again.
- Select any Report DataPage type.
- Select the M2M_Student table as your data source.
- On the Search Types screen select the radio button marked Filter data based on your pre-defined criteria.
- These steps allow us to pass the club ID from the Clubs Report and use it as the search parameter here to filter the Students Report. In the Select Filtering Fields screen include the Club_ID field as the filtering field by moving it to the right-hand panel.
- On the Configure Filtering Fields screen, select the Club_ID field and click the Advanced tab. Select Receive value or parameter, select the External Parameter option from the dropdown and enter the parameter name. Be sure to note the parameter name (in this case @Club_ID). The parameter name in the Club Report must match this parameter name exactly. Check If empty, ignore criteria so if an end-user leaves one or several options blank, they will still be able to see results.
- You can configure the rest of the Report however you would like. Click Finish when complete.
Linking the two Report DataPagesNow that the DataPages are complete we can link them together by changing the placeholder members link text from earlier into a hyperlink to the Student Members Report. First we need the URL of the Students Member Report. To get a location we will deploy the DataPage. For this example we are going to use the URL method to deploy our DataPages. You can use this same technique with the embedded method. Just use the URLs of your host pages instead of the link from the URL method of deployment. Click on Deploy and, for the deployment method select URL. Click on the Link tab, copy the deploy code and click Close.
- Now open the DataPage wizard again by selecting the Clubs Report and clicking Edit. If you haven’t already, make sure that both advanced options and parameters are enabled in this DataPage.
- Proceed to the Configure Results Page Fields screen. Select the HTML block that contains your member’s link placeholder. Paste in the deploy code link from the Students Report. If you are using the Direct from Caspio method, the default link text will be the DataPage name. Update the hyperlink text to say “Members”.
- Finally, you pass the Club_ID field as a parameter. Because you are using the Direct from Caspio deployment method, use an and symbol (&) to create a query string instead of a question mark. With the Direct from Caspio deployment, the first query string is always the AppKey of your DataPage.
- After the AppKey (the long series of numbers and letters) add an and symbol (&). Enter Club_ID (make sure it is spelled the same as the receiving parameter in the student Report DataPage. Now add an equal sign. Then, using the Parameter Insert button at the lower right hand of the HTML panel, insert the Club_ID parameter in string format.
- If you are using the embedded method and the Club_ID is your first parameter, use a question mark (?) as your query string signifier.
- Click Finish when complete.
Now your two DataPages should be connected. You can test the link right inside of Caspio.