This tutorial will guide you through the process of combining data from multiple subforms into a single calculated table in the Record Detail View.
Overview
In Activity info, parent forms often contain multiple subforms. For example, in an Electronic Medical Records (EMR) use case, a Patient form may have subforms for Consultation Visits, Triage and Laboratory. Each subform records information about the patient, but switching between subforms to get a full picture about the patient can be a challenge.
By using a Calculated Table in a Record Detail View, you can bring data from several subforms together into one unified table. This allows you to display all related events in one place, review them as a timeline, and analyze or compare information across subforms.
Before you start
- You will need to have permission to create a new database or at least a new form. If you do not have this permission, you can create a free trial account to practice.
- To begin, create a copy of the EMR database template using this link.
- Some familiarity with ActivityInfo’s formula language, or Excel and PowerBI-style formulas, will be helpful.
Steps
- Define the structure of the table
Start by deciding which columns should appear in your combined table. To make the resulting table readable, use a consistent structure across all subforms.
For example, in an Electronic Medical Records use case, a patient’s medical history can be represented in a table with three columns:
| Date | Event Type | Description |
|---|---|---|
- Open the parent form and switch to the Record Detail View
- Click on Views and then click on Record detail.
- In the View Editor click on Add Component and then click on Calculated table to open the formula editor.
- Extract data from each subform
Use the SELECTCOLUMNS() function to extract and align relevant columns from your subform with the table structure in mind. The right panel of the formula editor shows the forms, subforms and the fields to help you reference them correctly.
- Enter a suitable name for the calculated table such as ‘Patient Medical History’.
- Click on the parent form or subform name in the formula editor to retrieve the formID.
- Use the CONCAT() function to build descriptive texts from multiple fields making the table easy to read.
The formula below extracts the date and triage details from the Triage subform.
SELECTCOLUMNS(cgw2i4xmf52aab7ahp, "Date", triageDate,
"Event Type", "Triage", "Description", CONCAT("Blood Pressure:
", bloodPressure , " Oxygen Saturation ", oxygenSaturation, " and a BMI of ", bmi ))
The table below shows the result of the formula above.
The formula below extracts the date and consultation details from the Consultation Visits subform.
SELECTCOLUMNS(cr6h5ysmf52aab7ahw, "Date", visitDate, "Event Type", "Consultation", "Description",
CONCAT("Patient was diagnosed with ", diagnosis))
The table below shows the result of the formula above.
- Combine with multiple table expressions
Once you have extracted information from each subform and structured it in the desired format, you can proceed to merge them with the UNION() function.
UNION(
SELECTCOLUMNS(cgw2i4xmf52aab7ahp, "Date", triageDate,
"Event Type", "Triage", "Description", CONCAT("Blood Pressure:
", bloodPressure , " Oxygen Saturation ", oxygenSaturation, "% and a BMI of ", bmi )),
SELECTCOLUMNS(cr6h5ysmf52aab7ahw, "Date", visitDate, "Event Type", "Consultation", "Description",
CONCAT("Patient was diagnosed with ", diagnosis)))
This will combine the information from both the Triage and Consultation Visits subforms.
Optional Tip: When working with multiple subforms, you can start by writing a SELECTCOLUMNS() expression for one subform and confirm that it produces the expected result. Then, repeat the process for each additional subform.
Once each individual SELECTCOLUMNS() expression is working correctly, you can then use the UNION() function to merge them into a single calculated table.