Chapters: 
O365 Plumbing

A missing Contact link is preferable to a confidently wrong Contact link.

Contact lookup prototype

Below is the working path, the exact JSON snippets, and the known troubleshooting points. This is intended as a practical Monday reference, not a redesign.

Step 1: Select a play

Start here: 

/sites/ORG-6579-0004/Lists/702pbtoc1/AllItems.aspx?viewid=40eaccfa-51d2-46ab-922f-6c9bcb6cae5c

This is: 

702-pb-toc

View: Sorted

  1. Select a play.
  2. Click View Related Contacts.

The selected row already has a calculated ProdKey, extracted from NodeKey using: 

=RIGHT([NodeKey],LEN([NodeKey])-FIND("-",[NodeKey]))

Example: 

NodeKey = DNDMNGNET-CFXNET
ProdKey = CFXNET

Step 2: Open the filtered XRef list

View Related Contacts opens: 

702-pb-xref-prodkey

The list's display name is: 

702-pb-xref-prodkey

Its actual SharePoint URL name is: 

702pbtocprodkey

Example filtered URL: 

/sites/ORG-6579-0004/Lists/702pbtocprodkey/AllItems.aspx?FilterField1=ProdKeyText&FilterValue1=CFXNET

JSON used by View Related Contacts

This JSON belongs to the Contacts column in 702-pb-toc: 

{
  "$schema": "https://microsoft.com",
  "elmType": "a",
  "txtContent": "View Related Contacts",
  "attributes": {
    "href": {
      "operator": "+",
      "operands": [
        "/sites/ORG-6579-0004/Lists/702pbtocprodkey/AllItems.aspx?FilterField1=ProdKeyText&FilterValue1=",
        "[$ProdKey]"
      ]
    },
    "target": "_blank"
  },
  "style": {
    "cursor": "pointer",
    "text-decoration": "underline",
    "color": "#0078d4"
  }
}

The relationship is: 

702-pb-toc.ProdKey
↓
702-pb-xref-prodkey.ProdKeyText

Step 3: Search for the contact team

In the filtered 702-pb-xref-prodkey result, click:

Find Contact Team

The button opens the production Stakeholders_Contacts_Database and passes: 

q=<ProdKeyText>

For example: 

q=CFXNET

Working JSON for Find Contact Team

This JSON belongs to the Find Contact Team column in 702-pb-xref-prodkey: 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "Find Contact Team",
  "attributes": {
    "href": "=https://mysite/sites/000-001-702/SitePages/Forms/By%20Category.aspx?id=%2Fsites%2F000%2D001%2D702%2FStakeholders%5FContacts%5FDatabase&amp;viewid=1342045c%2Db774%2D4767%2Dd972a%2Daa29a2%2Df1c969b41036&amp;view=7&amp;q=' + [$ProdKeyText]",
    "target": "_blank"
  },
  "style": {
    "color": "#0078d4",
    "text-decoration": "underline",
    "cursor": "pointer"
  }
}

Note: The long URL above is the least photographically clear part of the pages. I have transcribed what is visible, but I would verify that particular production URL against SharePoint before pasting it back into JSON.

The critical working syntax is: 

"href": "=base URL ending in &q=' + [$ProdKeyText]"

Do not use: 

"href": "=concat(...)"

In this formatter, concat() was treated as literal URL text instead of being evaluated.

Step 4: Select a contact page

The Contacts Database search may return:

  • one matching .aspx contact page, or
  • multiple matching contact pages.

Click the appropriate .aspx page name.

The contact page then opens.

Complete user path 

702-pb-toc, Sorted view
↓
Select a play
↓
View Related Contacts
↓
702-pb-xref-prodkey, filtered by ProdKeyText
↓
Find Contact Team
↓
Stakeholders_Contacts_Database search results
↓
Select the appropriate .aspx contact page
↓
Contact page opens

Confirmed query behavior

The following search values returned useful contact results: 

CFXNET
DRENET
FRAK

Known exceptions or anomalies:

CCONET

Did not return the expected result. A known page is named something like: 

CCO_Net.aspx

The search key and page naming may not align.

O365

Returned many results. The term is too broad to produce a unique match.

These anomalies do not necessarily mean the links are broken. They may reflect the underlying free-text content, page names, titles, or inconsistent acronyms in the Contacts Database.

Tricky bits identified

1. SharePoint display name versus URL name

The XRef list is displayed as: 

702-pb-xref-prodkey

But its real URL remains: 

/Lists/702pbtocprodkey/

Always use the actual browser URL in formatting JSON.

2. Calculated columns and URL filtering

Filtering the calculated ProdKey field produced: 

Unknown render failure

The working solution was a normal text column: 

ProdKeyText

Therefore, the XRef URL filters on: 

FilterField1=ProdKeyText

not: 

FilterField1=ProdKey

3. Imported columns had incorrect data types

ContactName and Email were imported as Number columns, which resulted in values such as: 

0

The columns were changed to text. Existing zeros remained because changing the column type converted the existing zero values to text rather than clearing them.

4. concat() was not evaluated

This expression: 

"href": "=concat('URL', [$ProdKeyText])"

was treated as literal text and generated a broken path resembling: 

/Lists/702pbtocprodkey/=concat(...

The working expression uses the + operator: 

"href": "='URL' + [$ProdKeyText]"

5. Ampersands depend on context

In the working href expression, use ordinary ampersands inside the URL: 

&viewid=
&view=7
&q=

Do not replace them with &amp; inside the expression unless SharePoint itself encodes them after saving.

6. Search precision depends on production data

The query performs a search: 

q=CFXNET

It does not currently perform an exact metadata match. Results depend on how the acronym appears in page names, titles, or page content.

This explains why: 

CFXNET → useful result
CCONET → no expected result
O365   → many results

7. There is a follow-up selection click

After Find Contact Team, the user may need to choose among multiple contact pages.

That behavior is currently expected: 

Find Contact Team
↓
One or more search results
↓
User selects the appropriate contact page

Quick troubleshooting sequence

If View Related Contacts fails:

  1. Check that the source row has a populated ProdKey.
  2. Check that the XRef has the matching ProdKeyText.
  3. Confirm the link filters on ProdKeyText, not calculated ProdKey.
  4. Confirm the URL uses: 
/Lists/702pbtocprodkey/

If Find Contact Team fails:

  1. Copy the generated URL.
  2. Confirm it ends with the expected value: 
&q=CFXNET
  1. Hard-code the same value temporarily.
  2. If the hard-coded URL works, inspect the dynamic expression.
  3. Confirm the expression uses: 
"='URL' + [$ProdKeyText]"

If the search opens but returns no useful page:

  1. Test the same value directly in the Contacts Database.
  2. Inspect the returned page names and titles.
  3. Record the value as a data anomaly.
  4. Do not assume the navigation is broken until the production naming and content have been checked.

Short Monday reminder

Start here: 

/sites/ORG-6579-0004/Lists/702pbtoc1/AllItems.aspx?viewid=40eaccfa-51d2-46ab-922f-6c9bcb6cae5c

Select a play.

Click View Related Contacts.

Click Find Contact Team.

Select the appropriate .aspx contact page.

The key technical chain is: 

ProdKey
→ ProdKeyText
→ q=ProdKeyText
→ matching contact page(s)

There. Your four pieces of paper have escaped their cellulose prison. 📄→✏️