Supercharging SharePoint Forms with External Data Lookups

One of the frustrations with SharePoint list forms is that the out-of-the-box lookup column only gets you so far. You can point it to another list in the same site, and that’s about it. Useful in some cases, but far too limited if you want to build real-world business solutions.

That’s where we’ve always stepped in with Lightning Forms and Forms Studio. For years, you’ve been able to create cross-site lookups that reach across your tenant. But now, we’ve taken that capability much further.

With our latest release, you can lookup external data using Copilot Connectors (formerly Microsoft Graph Connectors). In other words, you’re no longer tied to SharePoint data alone—you can pull values from SQL, Oracle, JIRA, Salesforce, and a lot more.

What’s New?

The new lookup column lets you:

• Connect to SQL Server (on-premises and Azure), Oracle, JIRA, Salesforce, and other systems supported by Copilot Connectors.
• Store values from those sources directly in your SharePoint list items.
• Keep your forms dynamic by filtering lookups and showing only what’s relevant.

Example: Help Desk Issues with JIRA Integration

Let me show you how this works in practice.

I set up a new IT Helpdesk site in SharePoint using the template, which comes with a standard Issues list. Out-of-the-box, there’s a “Related Issue” column that just links to other issues in the same list.

That’s fine, but in many organizations issues are logged elsewhere—like JIRA. So instead, I:

1. Deleted the standard “Related Issue” column.
2. Created a new “Related Issue” column using Lightning Forms.
3. Pointed it at the JIRA Copilot Connector so it shows issues directly from JIRA.

Now when someone logs a new issue, they can relate it back to an issue already reported in JIRA—without leaving SharePoint.

Smarter Lookups

This isn’t just a static list. You can:

• Filter dynamically based on other form fields. For example, only show JIRA issues for the same product selected in the form.
• Show multiple columns in the dropdown to make it easier to choose the right value.
• Remove duplicates so your users only see clean results.

The End Result

The end result is a form that feels native to SharePoint but gives you access to external data that matters. In this case, our Helpdesk list can now reference JIRA issues directly, so reporting, dashboards, and processes stay in sync across systems.

Lightning Forms vs. Forms Studio – What’s the Difference?

We get asked this a lot, so here’s the quick version:

• Lightning Forms
  • Runs directly inside SharePoint as an SPFx app.
  • Perfect for site owners who want to make contextual design changes to their forms on the fly.
  • Quick, powerful, and right there in the site.
• Forms Studio
  • Centralized solution for architects and admins.
  • Lets you design forms, automation, and solutions in one place and then publish them to multiple sites.
  • Best if you need governance, reusability, and consistency across your tenant.

Think of it this way: Lightning Forms is about customizing here and now, while Forms Studio is about designing once and rolling out everywhere.

How Do You Create a Copilot Connector?

If you’re new to Copilot Connectors (formerly Microsoft Graph Connectors), the good news is that you don’t need to be a developer to get started. Here’s the high-level process:

1. Go to the Microsoft 365 Admin Center
   • You’ll find Copilot Connectors under the Search & Intelligence section.
2. Pick a Connector Type
   • Microsoft provides a bunch out-of-the-box (JIRA, ServiceNow, Salesforce, SQL, etc.).
   • If you don’t see what you need, you can also build a custom connector.
3. Configure the Data Source
   • For SQL or Oracle, you’ll provide the connection string and authentication details.
   • For cloud services like JIRA or Salesforce, you’ll authenticate using OAuth.
4. Set the Schema
   • Decide which fields from the source system you want to surface.
   • These are the fields you’ll later be able to query and display in your Lightning Forms lookup.
5. Define Filters & Access
   • You can apply filters to control which data is indexed.
   • Permissions can flow through so users only see what they’re entitled to.
6. Publish & Index
   • Once published, Microsoft 365 will index the data.
   • From there, it’s ready to be consumed by Lightning Forms or Forms Studio as part of your lookup configuration.

It’s a one-time setup, and once the connector is live, you can reuse it across multiple forms and sites. That’s the beauty of it—you centralize the connection and then surface it wherever it’s needed in SharePoint.

Arrange a Demo of Lightning Forms or Lightning Tools Forms Studio here