0

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"
      }
    }
  ]
}

2 Answers 2

0

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]"
      }
    }
  ]
}
7
  • Is there at least a way to accomplish what I tried in my code? Display all Lookup values but put one hyperlink to click. I can at least link to a filtered view of List 2 (filtered by ItemID forexample). Apr 9, 2021 at 11:35
  • Which itemID you will use in link? List1 item ID or List2 Item ID? Apr 9, 2021 at 11:39
  • I have identical column names in both Lists. So filtering List 2 with itemID in List 1. Yes I have multi-values in lookup as my question title suggest. However click can be to the entire cell, if not possible on individual multi-values (like in original view without formatting but microsoft hyperlinks to the item properties by default which isnt useful in my case. If individual hyperlinks can be put on multi-values then I would link to a filtered view of List 2 with itemID and TrackingNumber as filters. Apr 9, 2021 at 11:42
  • Great, check JSON in my updated answer. It should work for you. Try & let me know if it works. Apr 9, 2021 at 11:47
  • 1
    Hi Ganesh, Thanks, I have done it differently, see my answer. Thank you anyways. Apr 9, 2021 at 13:34
0

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'"
      }
    }
  ]
}

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.