Building Custom Forms To Link Sharepoint Lists Into A Hierarchical Dataset
Linking SharePoint Lists into Parent-Child Relationships
Connecting SharePoint lists hierarchically as parent-child relationships enables more intuitive retrieval and display of related data rows. By establishing lookup column connections, child list rows can be linked to parent list rows to show organizational hierarchies and drill-down capabilities.
Key benefits include:
- Retrieving and displaying hierarchical data more intuitively based on natural relationships
- Modeling real-world organizational structures and properties as connected parent-child data
- Enabling easy drilling down from summary parent data into greater child details
Prerequisites
To implement connected parent-child list relationships in SharePoint, you need:
- Two existing SharePoint lists that have data rows to connect – one for the parent data and one for corresponding child data
- Understanding of SharePoint lookup columns and experience adding them to SharePoint lists
- Permissions to customize SharePoint lists and forms
Connecting Lists with Lookup Columns
On a technical level, establishing connected parent-child relationships between SharePoint lists is achieved by adding a lookup column to the child list that references the parent list.
Steps include:
- Add a lookup column to the child list that will contain the linked parent references
- Configure the lookup column to point to the parent list and reference its unique identifier column like an ID number
- Set column validation settings to enforce referential integrity between child and parent rows
Setting column validation properly prevents orphaned child rows, keeping the hierarchical dataset intact. For example, deleting a parent row should delete or re-assign associated child rows.
Building Custom Forms for Hierarchical Data Entry
Custom SharePoint list forms provide the best way to manage data entry for connected parent-child list hierarchies. The forms can directly connect the parent and child data creation into a single user experience.
Steps include:
- Create parent and child list forms using PowerApps or other form designer tools
- Connect the parent and child forms seamlessly with a data source and form control
- Set up submissions from the child form to automatically populate the linking parent lookup column
Well-designed custom forms enable creating full parent-child row sets efficiently. Admins can also implement logic to propagate changes across form connections.
Displaying Hierarchical Data in Views
Optimized SharePoint list views are important for reporting on and displaying hierarchical parent-child data correctly.
Best practices include:
- Modifying default list views to show the lookup column pointing to the connected parent list
- Enabling the lookup column to drill-down into the parent row details
- Color coding child rows based on metadata inherited from the parent data
View formatting techniques help users browse the hierarchical data structure clearly. Interactive lookup columns make drilling down into details efficient directly within the child list.
Example Code for a Parent-Child Form Solution
Below find code samples for a custom forms implementation to create interconnected parent-child rows in SharePoint list data.
Parent List Form Code Sample
<SharePointForm> <p>Parent List Form</p> <FormControl mode="New" dataSource="ParentList" > <LabelControl label="Parent Name" > <TextInputControl dataField="ParentName" ></TextInputControl> </LabelControl> <LabelControl label="Parent Details" > <TextAreaControl dataField="ParentDetails" ></TextAreaControl> </LabelControl> <ButtonControl dataAction="SubmitForm" label="Create Parent Entry" ></ButtonControl> </FormControl> </SharePointForm>
Child List Form Code Sample
<SharePointForm> <p>Child List Form</p> <FormControl mode="New" dataSource="ChildList" > <LabelControl label="Child Name" > <TextInputControl dataField="ChildName" ></TextInputControl> </LabelControl> <LabelControl label="Parent Lookup" > <PeoplePickerControl dataField="ParentLookup" ></PeoplePickerControl> </LabelControl> <ButtonControl dataAction="SubmitForm" label="Create Child Entry" ></ButtonControl> </FormControl> </SharePointForm>
Connecting the Forms Code Walkthrough
The key setup needed to connect the parent and child forms into a hierarchical data entry solution is configuring the Child Form’s PeoplePickerControl for the ParentLookup field.
Within the form initialization scripts:
- Detect submission of the Parent Form
- Extract the unique parent list row ID value
- Pass that ID into the Child Form’s PeoplePickerControl to auto-populate the ParentLookup field
This allows cascading creation of connected parent-child rows with a seamless user experience for managing hierarchical data.
Considerations for Managing Hierarchical Data
Specialized logic and handling should be implemented when managing complex hierarchical datasets in SharePoint to address:
- Cascading deletes – Deleting parent rows should delete or re-assign associated child rows
- Parent ID changes – Changes to IDs in the parent lookup column can break connected child rows
- Scale – Very large, intricate hierarchies require optimized performance tuning
Proper validation settings, business rules, and performance testing helps govern hierarchical data appropriately across full solution lifecycles.
Next Steps
Additional opportunities when working with connected SharePoint lists include:
- Building more advanced metadata models across lists using lookup columns to multiple parents or upstream data sources like Excel
- Exposing reusable, dynamic parental hierarchy components through SharePoint Framework web parts
The methods explored in this article form a foundation for aggregating SharePoint list data into richer, more functional hierarchical datasets for organizations.