Mastering SharePoint Internal Schemas: A Comprehensive Guide to Filtering Person or Group and Lookup Columns

Introduction

Navigating SharePoint's complexities can often be a daunting task, especially when dealing with intricate internal schemas like Person or Group and Lookup columns. These elements are pivotal in creating dynamic and functional SharePoint lists and libraries but can present challenges in understanding and applying their internal naming conventions, particularly when integrating with tools like Power Automate. This guide aims to demystify these complexities, providing a practical overview of SharePoint's internal schemas with a focus on filtering Person or Group and Lookup columns. We'll use a real-world example to illustrate these concepts:

json
{
  "ID": 12,
  "Date": "2022-11-16",
  "Content": "Sample text here...",
  "Name": {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
    "Claims": "i:0#.f|membership|forrest@hellosmart.ca",
    "DisplayName": "Forrest",
    "Email": "Forrest@hellosmart.ca",
    "Picture": "https://1s400n.sharepoint.com/sites/Forrest/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Forrest@hellosmart.ca",
    "Department": null,
    "JobTitle": null
  },
  ...
}

Through this example and detailed explanations, we'll explore how to correctly identify and use internal field names, understand the nuances in SharePoint's data representation, and effectively apply these insights in real-world scenarios.

Based on above sample data, using the internal schema knowledge, the query:

Name/EMail eq 'Forrest@hellosmart.ca'

can be effectively utilized in the "Get Items" action to filter the list items where the Name field (a Person or Group column) matches the specified email address.

Further Guidance:

Frequently Used SharePoint Internal Schemas

1. Title: The default field for the primary name or title of the item.
2. ID (or GUID in some contexts): Unique identifier for each list or library item.
3. Created and Modified: Timestamps for when an item was created and last modified.
4. Author and Editor: Represent the "Created By" and "Modified By" users.
5. FileLeafRef: The file name in document libraries.
6. FileRef: The URL path to the item.
7. ContentTypeId: Identifies the content type of an item.
8. LinkTitle: A linked version of the "Title".
9. EncodedAbsUrl: The absolute URL of the item.
10. Attachments: Indicates if the item has attachments.

For Person or Group Columns:

• PersonOrGroupName/EMail: To access the email of a user.
• PersonOrGroupName/Title: To access the display name of a user.
• PersonOrGroupName/Id: To access the unique ID of a user.

For Lookup Columns:

• LookupFieldName/Id: To access the ID of the looked-up item.
• LookupFieldName/Title: To access the Title of the looked-up item.

Finding PersonOrGroupName and LookupFieldName

• PersonOrGroupName: This is the internal name of your Person or Group field. Find it by navigating to your list settings, clicking on the Person or Group column, and checking the URL.
• LookupFieldName: Similar process for Lookup fields. Find the internal name in the list settings.

Understanding the Discrepancy in Naming Conventions

A common point of confusion is the difference between SharePoint's displayed and internal field names. For instance, "Display Name" in the user interface corresponds to Title in internal schemas. This arises from SharePoint's data model, which treats user entities like list items, where Title is a primary text representation.

Conclusion

Understanding SharePoint's internal schemas is key to harnessing its full potential, especially when integrating with tools like Power Automate. This guide provides a foundational understanding of these schemas, focusing on filtering Person or Group and Lookup columns. Armed with this knowledge, users can navigate SharePoint's intricacies with greater confidence, leading to more efficient and error-free workflows. Whether you're filtering list items in Power Automate or customizing SharePoint forms, this knowledge is essential for any SharePoint user.