How to create column formatting with custom hyperlinks for multi-value lookup column with an additional field

Question

I have two SharePoint lists:

List 1 - has item details
List 2 - has shipping details

As one item can be shipped to different locations, List 1 has one to many relationship with List 2.

List 2 has tracking number and courier columns. I linked those to List 1 as multi-value lookup column, tracking numbers as main value and courier as additional field.

Is there a way to create hyperlinks on the tracking numbers in List 1 to link to the respective courier's page for actually seeing the tracking info. So depending on the value of the courier, the tracking web address should change.

I don't even know if individual hyperlinks can be added to multi-value lookup columns even to a single web address. So I tried that below (which doesn't work and doesn't consider lookup additional field value) but I have no idea if that is possible.

If possible I need the address to be changing depending on lookup additional field value. If UPS, link to UPS page, if DHL, then DHL page, etc..

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "debugMode": true,
  "elmType": "div",
  "children": [
    {
      "elmType": "div",
      "style": {
        "display": "block",
        "width": "100%"
      },
      "txtContent": "=join(@currentField.lookupValue, '\n')",
      "attributes": {
        "target": "_blank",
        "href": "='http://wwwapps.ups.com/WebTracking/track?track=yes&trackNums=' + @currentField.lookupValue"
      }
    }
  ]
}

Answer 1

You cannot use the extended columns in lookup in JSON formatting. You can only use the lookupId & lookupValue (Main value - tracking number).

So you cannot achieve this as mentioned in your question:

So depending on the value of the courier, the tracking web address should change.

From documentation:

The lookup field object has the following properties (with example values):

{
   "lookupId": "100",
   "lookupValue": "North America",
}

Source: Special string values - Check "Lookup fields" section.

---

Updated from comments:

If you want to use the item ID of current list, use below JSON code:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "elmType": "a",
      "style": {
        "display": "block",
        "width": "100%"
      },
      "txtContent": "=join(@currentField.lookupValue, '\n')",
      "attributes": {
        "target": "_blank",
        "href": "='http://wwwapps.ups.com/WebTracking/track?track=yes&trackNums=' + [$ID]"
      }
    }
  ]
}

Answer 2

Yes I cant maybe conditionally use different hyperlink for each courier, I decided to use a hyperlink to the item in List 2 using a filtered view link. So after the user clicks on the hyperlink in List 1, it will open the respective shipping details item in List 2, and then they can click on the tracking number to go to the courier's page from List 2. So it works indirectly but below link is a good example if you want to have individual hyperlinks for each value in multi-value lookup column. I deleted some parts of the hyperlink to hide my company data so in order to get the correct URL, go to your second list, filter your view and copy the URL from the address bar and change the respective filter fields with your [$] variables referring to the columns in your List 1 as filter fields. [$region.lookupValue] is my tracking number in my example. And this formatting is applied to Multi-Value Lookup column with my tracking numbers in List 2.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "display": "block"
  },
  "children": [
    {
      "elmType": "a",
      "forEach": "region in @currentField",
      "txtContent": {
        "operator": "?",
        "operands": [
          "=loopIndex('region') == 0",
          "[$region.lookupValue]",
          {
            "operator": "?",
            "operands": [
              "=loopIndex('region') + 1 == length(@currentField)",
              "=', and ' + [$region.lookupValue]",
              "=', ' + [$region.lookupValue]"
            ]
          }
        ]
      },
      "attributes": {
        "target": "_blank",
        "href": "='https://aaa.sharepoint.com/sites/xxList/Lists/List2/AllItems.aspx?newTargetListUrl=%2Fsites%2FxxList%2FLists%2FList2&viewpath=%2Fsites%2FList%2FLists%2FList2%2FAllItems%2Easpx&FilterField1=ItemID&FilterValue1=' + [$ItemID] + '&FilterType1=Text&FilterField2=TrackingNumber&FilterValue2=' + [$region.lookupValue] +'&FilterType2=Text&viewid=xxx&id=%2Fsites%2FList%2FLists%2FList2'"
      }
    }
  ]
}