SharePoint Column Formatting: Referencing External Columns in Mail To Body

Question

Hello Stack Exchange Community,

I have a question regarding referencing external columns. In my code below, external columns are referenced using square brackets as specified in Microsoft's online documentation.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "@currentField",
  "attributes": {
    "href": {
      "operator": "+",
      "operands": [
        "mailto:",
        "[$Email]",
        "?subject=Task status&body=Follow Up: Agreements.\r\n---\r\n",
        "Dear [$ContactName]",
        "\r\nJust writing to follow up on that agreement that has been sent on [$StartDate]. Let me know if you have any questions."
      ]
    }
  }
}

However, after doing so fields such as $ContactName and $StarDate do not appear to be properly displaying their associated values. Instead, what appears on my email body is the literal code used to reference the columns (e.g Just writing to follow up on that agreement that has been sent on [$StartDate]).

If anyone knows of the proper way to reference values in external columns within the email body using JSON column formatting please do let me know !

Much appreciated !

Answer 1

For Date/Time fields:

The value of Date/Time fields can be retrieved a few different ways, depending on the date format you'd like to display. The following methods for converting date values to specific formats are supported:

• toLocaleString() - Displays a date type fully expanded with date and time.
• toLocaleDateString() - Displays a date type with just the date.
• toLocaleTimeString() - Displays a date type with just the time.

Examples:

1. toLocaleString():

   =toLocaleString(@now): results vary based on user's locale, but en-us looks like 2/5/2019, 1:22:24 PM

2. toLocaleDateString():

   =toLocaleDateString(@now) results vary based on user's locale, but en-us looks like 2/5/2019

Reference: Column Formatting - Operators.

---

For People fields (Person or Group):

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

{
   "id": "122",
   "title": "Kalya Tucker",
   "email": "[email protected]",
   "sip": "[email protected]",
   "picture": "https://contoso.sharepoint.com/kaylat_contoso_com_MThumb.jpg?t=63576928822",
   "department":"Human Resources",
   "jobTitle":"HR Manager"
}

Note:

The @currentField.title returns a person's name by default. However, if the person field's Show Field has been adjusted, it may change the value of the title property.

For example, a person field with the Show Field configured as Department will have the person's department for the title property.

Reference: Column Formatting - Special string values.

---

Solution For you:

1. If ContactName field is Single Line of Text:

    {
        "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
        "elmType": "a",
        "txtContent": "@currentField",
        "attributes": {
            "href": {
                "operator": "+",
                "operands": [
                    "mailto:",
                    "[$Email]",
                    "?subject=Task status&body=Follow Up: Agreements.\r\n---\r\n",
                    "Dear [$ContactName]",
                    "\r\nJust writing to follow up on that agreement that has been sent on ",
                    "=toLocaleDateString([$StartDate])",
                    ". Let me know if you have any questions."
                ]
            }
        }
    }

2. If ContactName field is Person or Group:

    {
        "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
        "elmType": "a",
        "txtContent": "@currentField",
        "attributes": {
            "href": {
                "operator": "+",
                "operands": [
                    "mailto:",
                    "[$Email]",
                    "?subject=Task status&body=Follow Up: Agreements.\r\n---\r\n",
                    "Dear [$ContactName.title]",
                    "\r\nJust writing to follow up on that agreement that has been sent on ",
                    "=toLocaleDateString([$StartDate])",
                    ". Let me know if you have any questions."
                ]
            }
        }
    }

Note: You can also use =toLocaleString([$StartDate]) instead of =toLocaleDateString([$StartDate]) if you want to add timestamp also in your email body.

Answer 2

Modify the JSON formatting as below.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "@currentField",
  "attributes": {
    "href": {
      "operator": "+",
      "operands": [
        "mailto:",
        "[$Email]",
        "?subject=Task status&body=Follow Up: Agreements.\r\n---\r\n",
        "Dear ",
        "[$ContactName]",
        "\r\nJust writing to follow up on that agreement that has been sent on ",
        "=toLocaleDateString([$StartDate])",
        ". Let me know if you have any questions."
      ]
    }
  }
}