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:

1. Add a lookup column to the child list that will contain the linked parent references
2. Configure the lookup column to point to the parent list and reference its unique identifier column like an ID number
3. 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:

1. Create parent and child list forms using PowerApps or other form designer tools
2. Connect the parent and child forms seamlessly with a data source and form control
3. 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:

1. Detect submission of the Parent Form
2. Extract the unique parent list row ID value
3. 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.