Joining lists on lookups the way SharePoint cannot

Last updated 23 October 2026

A report needs data from three related lists joined on their lookup columns, and SharePoint has no way to do that without pulling everything into Power Automate or Excel by hand.

SharePoint lookups go one field deep. A Tasks list can show the title of the project each task belongs to, but it cannot show that project’s budget, or the department of the person the task is assigned to, because SharePoint has no server-side join across lists. A report that needs Projects, Tasks and People together ends up as a Power Automate flow that loops over records, a Power Query merge that breaks when a column changes, or a script that pulls all three lists through Graph and joins them in memory on every run.

Lookups become key columns

SPSync replicates each list into its own table and turns every lookup into a key column holding the ID of the item it points at, alongside its display value. Tasks to projects to people is then a plain SQL JOIN. After the first load each run reads only what changed, so the joined result stays current without fetching anything twice.

The details matter once you write the query. A single-value lookup is a column on the row. A multi-choice column is a child table with one row per value back to the parent, and multi-value lookups follow that shape once confirmed against a live tenant. Every person column, in every list, resolves against one shared sp_users table, so “assigned to” joins the same way in Tasks as in Projects. A report that needs more than SharePoint’s 12 lookups in one query works the same way; the post on the lookup column threshold shows where that limit stops views, flows, apps and Power BI.

Bring both ends of every lookup

A join needs a table on each side. Select every list involved, including the ones the others look up into, and send them to the same destination database on one schedule so the tables stay in step. Leave a list out and its lookup columns still replicate, with nothing on the other end to join to.

Deleted rows keep their place with sp_deleted_at set, so a join still works against history unless the query filters them out. For a head start, SPSync can generate a reporting view per list that adds person names and leaves out deleted rows, and the cross-list report becomes a short query over those views.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name cross_list_joins --prefix cross_list_joins --destination-kind postgres --connection-string-env SPSYNC_DB_CONNECTION --dataset-name cross_list_joins --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: cross_list_joins
prefix: cross_list_joins
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: postgres
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: cross_list_joins
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist