infojal

Data Extension Finder in Salesforce Marketing Cloud (SFMC)

Mahendra Bhalerao
Data Extension Finder in Salesforce Marketing Cloud (SFMC)

In the Salesforce Marketing Cloud (SFMC), Data Extensions (DEs) are used to store and manage data for email campaigns, journeys, and other automation processes. However, with a growing number of Data Extensions in your account, finding the correct one based on either its name can be a challenge.

A Data Extension Finder tool is a valuable functionality that helps users quickly locate the folder path of a Data Extension in SFMC, making the management of DEs more efficient. This article will walk you through the need for a Data Extension Finder, the necessary components to build it, how to create a CloudPage with an HTML form, where to place SSJS code, and the security concerns you need to keep in mind.

What is the Need for a Data Extension Finder in SFMC?

As marketers and administrators in the Salesforce Marketing Cloud, you often deal with multiple Data Extensions, each serving a different purpose (e.g., user profiles, email interactions, purchase data). These DEs might be housed in different folders within your SFMC instance. Over time, it can become difficult to track down a specific Data Extension by simply remembering its name or external key.

The Data Extension Finder solves this issue by allowing users to:

  1. Search for Data Extensions by Name or External Key.
  2. Retrieve Folder Path for the DE, helping users understand its location within the folder structure.
  3. Simplify Data Management: Quick access to DEs improves workflows, especially when dealing with hundreds or thousands of DEs in SFMC.

What Things Are Required to Create this Functionality?

To create a Data Extension Finder tool in SFMC, several components are required:

  1. SSJS Code (Server-Side JavaScript):
    The SSJS code is responsible for interacting with SFMC’s REST or SOAP API to retrieve Data Extension details. In our case, the DataExtension.Retrieve function is used to find the DE by either Name or External Key, and the Folder.Retrieve function is used to trace the folder path.
  2. Data Extension Details:
    • Name or External Key: The user will provide either the DE name or its external key  in the form.
    • Folder Path: This is dynamically determined by the SSJS code based on the location of the DE.
  3. CloudPage:
    A CloudPage is created in SFMC to host the HTML form and SSJS code. It serves as the user interface where the form is displayed and the functionality is executed.

How to Create a CloudPage (HTML Form to Get the Input as DE Name & DE External Key)

To begin, create a CloudPage where users can input the required information (either DE name or external key) to find a Data Extension’s folder path. The CloudPage will use a simple HTML form to collect user input.

  1. HTML Form for Input: The form asks the user to choose between searching for a Data Extension by Name or External Key and provides a field to enter the corresponding value.

Code:

<form action="%%=RequestParameter('PAGEURL')=%%" method="post">
    <label for="DE_Property">Find Data Extension By:</label>
    <select name="DE_Property" id="DE_Property">
        <option value="Name">Data Extension Name</option>
        <option value="CustomerKey">Data Extension External Key</option>
    </select>

    <label for="DE_Value">Enter Value:</label>
    <input type="text" name="DE_Value" id="DE_Value" value="" maxlength="128" placeholder="Enter DE Name or External Key" required="">

    <input type="submit" value="Submit">
</form>

2. Input Field Explanation

  • DE_Property: This is a dropdown where the user selects how they wish to identify the DE (by Name or Customer Key).
  • DE_Value: A text input where the user enters the value (either the Name or External Key) of the Data Extension they are searching for.

Where to Put SSJS Code for This Functionality

The SSJS code will be placed within a <script runat=”server”> tag on the same CloudPage. The purpose of the SSJS code is to process the form input, interact with the Salesforce Marketing Cloud APIs to find the DE, and then output the folder path for the specified DE.

SSJS Code Example:

<script runat="server">
    Platform.Load("core","1.1.5");

    // Get the form parameters for DE_Property and DE_Value
    var DE_Property = Request.GetQueryStringParameter("DE_Property");  // Either "Name" or "Customer Key"
    var DE_Value = Request.GetQueryStringParameter("DE_Value");    // The value to match

    if (DE_Property && DE_Value) {
        // Retrieve the Data Extension based on the provided property and value
        var FindDE = DataExtension.Retrieve({
            Property: DE_Property,
            SimpleOperator: "equals",
            Value: DE_Value
        });

        if (FindDE.length > 0) {
            // Extract the CategoryID (Folder ID) and Data Extension name
            var FolderID = FindDE[0].CategoryID;
            var DEname = FindDE[0].Name;
            var list = [DEname];  // Start the list with the Data Extension name

            // Recursive function to build the folder path
            var path = function(id) {
                if (id > 0) {
                    var results = Folder.Retrieve({ Property: "ID", SimpleOperator: "equals", Value: id });
                    list.unshift(results[0].Name);  // Add the folder name to the front of the list
                    return path(results[0].ParentFolder.ID);  // Continue to the parent folder
                } else {
                    return id;  // Return when we reach the root folder
                }
            };

            // Build the full folder path
            path(FolderID);

            // Output the folder path
            Write("<div class='result'>" + list.join(" > ") + "</div>");
        } else {
            Write("<div class='result'>No Data Extension found for the specified criteria.</div>");
        }
    } else {
        Write("<div class='result'>Please specify both a valid property and value.</div>");
    }
</script>

Explanation of SSJS Code

This Server-Side JavaScript (SSJS) code is used to retrieve a Data Extension (DE) based on either its Name or External Key (CustomerKey) and output the folder path where the DE resides in the Salesforce Marketing Cloud (SFMC). The code makes use of SSJS Data Extension function & Folder function: DataExtension.Retrieve and Folder.Retrieve. Below is an in-depth breakdown of how the code works and the usage of these functions.

Overview of the Code

  1. Getting Form Parameters:
    • The code retrieves two parameters (DE_Property and DE_Value) from the query string via Request.GetQueryStringParameter. These parameters are used to search for the Data Extension:
      • DE_Property: The property to search by (either “Name” or “CustomerKey”).
      • DE_Value: The value to search for (either the name or external key of the Data Extension).
  2. Validating Parameters:
    • The code first checks if both parameters are provided (i.e., DE_Property and DE_Value). If either is missing, an error message is displayed.
  3. Retrieving the Data Extension:
    • The DataExtension.Retrieve function is used to search for the Data Extension using the provided property (DE_Property) and value (DE_Value).
    • If the Data Extension is found, the code retrieves the CategoryID (which refers to the Folder ID) and the Name of the Data Extension.
  4. Building Folder Path:
    • A recursive function named path is used to build the path of folders leading to the Data Extension.
    • Folder.Retrieve is called to get the details of the folder (such as its name and parent folder).
    • The function continues traversing the parent folders recursively until it reaches the root folder.
    • The folder names are added to an array (list) and joined together to display the full folder path.
  5. Output:
    • The final folder path is displayed using the Write() function, joined with ” > ” between each folder name.
    • If the Data Extension is not found, a message is shown indicating no matches.
    • If the parameters are missing, a prompt to enter both values is displayed.

SSJS Functions Used

DataExtension.Retrieve
The DataExtension.Retrieve function is used to retrieve Data Extension(s) from Salesforce Marketing Cloud based on specific criteria.
Syntax:
code:

DataExtension.Retrieve(criteria)
  1. Parameters:
    • criteria: An object that defines the properties to search by, the operator to use, and the value to compare against.
      • Property: The property to match against (e.g., “Name” or “CustomerKey”).
      • SimpleOperator: The operator used to compare the value (e.g., “equals”).
      • Value: The value to match (e.g., Data Extension name or External Key).

Example:
code:

var results = DataExtension.Retrieve({
    Property: “CustomerKey”,
    SimpleOperator: “equals”,
    Value: “myDEKey”
});
  1. Explanation of Example:
    • This searches for a Data Extension where the CustomerKey is equal to “myDEKey”.
    • If a Data Extension with that CustomerKey exists, it will be returned in the results array.
  2. Important Notes:
    • This function returns an array of Data Extensions that match the search criteria.
    • The array can contain multiple Data Extensions if there are more than one that matches the criteria.

Folder.Retrieve
The Folder.Retrieve function is used to retrieve folder(s) in the Salesforce Marketing Cloud based on specific search criteria.
Syntax:
code:

Folder.Retrieve(criteria)
  1. Parameters:
    • criteria: Similar to DataExtension.Retrieve, this parameter is an object that defines the properties to search by and the value to match.
      • Property: The property of the folder (e.g., “ID” or “Name”).
      • SimpleOperator: The operator used to compare the value (e.g., “equals”).
      • Value: The value to match against (e.g., Folder ID or Folder Name).

Example:
code:

var results = Folder.Retrieve({
    Property: “ID”,
    SimpleOperator: “equals”,
    Value: “12345”
});
  1. Explanation of Example:
    • This searches for a folder with an ID of 12345.
    • If a folder with that ID exists, it will be returned in the results array.
  2. Important Notes:
    • The Folder.Retrieve function returns an array of folders matching the criteria.
    • The result can be a single folder or multiple folders depending on the search criteria.

Detailed Explanation of the Code

Retrieving Data Extension

code:

var FindDE = DataExtension.Retrieve({
    Property: DE_Property,
    SimpleOperator: “equals”,
    Value: DE_Value
});
  • This line uses the DataExtension.Retrieve function to retrieve a Data Extension based on the DE_Property (either “Name” or “CustomerKey”) and DE_Value (the value for the name or external key).
  • The SimpleOperator is set to “equals”, meaning the function will look for an exact match between the Property and Value.

Extracting Folder ID and DE Name

code:

var FolderID = FindDE[0].CategoryID;
var DEname = FindDE[0].Name;
  • If the Data Extension is found, its CategoryID (Folder ID) and Name are extracted from the first item in the FindDE array. The CategoryID represents the folder where the Data Extension is located, and the Name is the name of the Data Extension.

Recursive Function to Build Folder Path

code:

var path = function(id) {
    if (id > 0) {
        var results = Folder.Retrieve({
            Property: “ID”,
            SimpleOperator: “equals”,
            Value: id
        });
        list.unshift(results[0].Name);
        return path(results[0].ParentFolder.ID);
    } else {
        return id;
    }
};
  • The path function is a recursive function that starts with the FolderID (the folder ID of the Data Extension).
  • It calls Folder.Retrieve to get the details of the folder using its ID.
  • The folder’s name is added to the list, and the function continues to call itself to retrieve the parent folder’s ID (ParentFolder.ID).
  • This continues until the root folder is reached (i.e., id <= 0).

Displaying the Folder Path

code:

Write(“<div class=’result’>” + list.join(” > “) + “</div>”);
  • After the recursion completes, the list array contains the folder names in order from the root to the Data Extension’s folder.
  • The list.join(” > “) joins the folder names with ” > ” as the separator, and the result is displayed inside a div with the class “result”.

Security and Best Practices

  1. Limit Data Extension Keys:
    • Ensure the external keys of your Data Extensions are no longer than 36 characters. This helps avoid potential issues during data retrieval and processing.
  2. Error Handling:
    • Implement error handling for situations where no results are found or when invalid parameters are provided. This ensures that the user gets a meaningful error message.
  3. Use Secure Parameters:
    • Ensure that the parameters (DE_Property and DE_Value) are passed securely, especially if you’re using them in a public-facing form.

Security Concerns

When developing and deploying any functionality in Salesforce Marketing Cloud, especially when using Server-Side JavaScript (SSJS), there are several security concerns to consider:

  1. Input Validation:
    • Ensure that user input is validated properly to avoid SQL injection or other malicious activities. While SFMC’s APIs are generally safe, you should still validate that the DE_Property and DE_Value inputs are of the correct format.
    • Avoid Cross-Site Scripting (XSS): Ensure user inputs are sanitized before being displayed to prevent XSS attacks. Use appropriate encoding methods when displaying user input on the page.
  2. Access Permissions:
    • Ensure that the user accessing the CloudPage has the appropriate permissions to access the Data Extensions. Sensitive Data Extensions should not be publicly accessible without proper authorization.
    • Use role-based access control (RBAC) in SFMC to limit who can retrieve Data Extensions or view certain data.
  3. Error Handling:
    • The SSJS code should handle errors gracefully. For instance, if the DataExtension.Retrieve call returns an empty list, display a user-friendly message such as “No Data Extension found”.
    • Handle unexpected inputs (such as invalid characters) to avoid breaking the page or exposing internal data.
  4. Data Privacy:
    • Be cautious when displaying Data Extension paths and related metadata. Ensure you are not exposing sensitive information through the results.

Complete Code Example

Here is the complete code that combines the HTML form and SSJS code:

Code

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Data Extension Finder</title>
    <style>
        /* Styles for the page as provided above */
    </style>
</head>
<body>

    <div class="container">
        <h1>Data Extension Finder</h1>

        <div class="form-container">
            <form action="%%=RequestParameter('PAGEURL')=%%" method="post">
                <label for="DE_Property">Find Data Extension By:</label>
                <select name="DE_Property" id="DE_Property">
                    <option value="Name">Data Extension Name</option>
                    <option value="CustomerKey">Data Extension External Key</option>
                </select>

                <label for="DE_Value">Enter Value:</label>
                <input type="text" name="DE_Value" id="DE_Value" value="" maxlength="128" placeholder="Enter DE Name or External Key" required="">

                <input type="submit" value="Submit">
            </form>
        </div>

        <div class="result">
            <b>Folder Path:</b><br>
            <script runat="server">
                Platform.Load("core","1.1.5");

                var DE_Property = Request.GetQueryStringParameter("DE_Property");
                var DE_Value = Request.GetQueryStringParameter("DE_Value");

                if (DE_Property && DE_Value) {
                    var FindDE = DataExtension.Retrieve({
                        Property: DE_Property,
                        SimpleOperator: "equals",
                        Value: DE_Value
                    });

                    if (FindDE.length > 0) {
                        var FolderID = FindDE[0].CategoryID;
                        var DEname = FindDE[0].Name;
                        var list = [DEname];

                        var path = function(id) {
                            if (id > 0) {
                                var results = Folder.Retrieve({ Property: "ID", SimpleOperator: "equals", Value: id });
                                list.unshift(results[0].Name);
                                return path(results[0].ParentFolder.ID);
                            } else {
                                return id;
                            }
                        };

                        path(FolderID);
                        Write("<div class='result'>" + list.join(" > ") + "</div>");
                    } else {
                        Write("<div class='result'>No Data Extension found for the specified criteria.</div>");
                    }
                } else {
                    Write("<div class='result'>Please specify both a valid property and value.</div>");
                }
            </script>
        </div>
    </div>

</body>
</html>

This code will give you a fully functional Data Extension Finder in Salesforce Marketing Cloud. It allows users to search for Data Extensions either by name or by external key and displays the folder path where the DE is stored.

Clould Page Code with CSS

Add security to this cloud page so that anyone cannot access

To secure your CloudPage in the Salesforce Marketing Cloud (SFMC) and ensure that only authorized users can access it, you can implement several strategies. This will prevent unauthorized users from viewing or interacting with your page, safeguarding sensitive data such as Data Extension names and External Keys.

Here are some security measures you can apply to your CloudPage:

1. User Authentication via a Custom Login Form

You can create a custom authentication mechanism using a combination of AMPscript and SSJS (Server-Side JavaScript). This way, you can restrict access to the page by requiring users to authenticate using a login form before proceeding.

2. Restricting Access Using URL Parameters

You can pass a secure token or a shared secret as a URL parameter to verify that the request is legitimate. Only authorized users who know the token can access the page. You can also validate this token against a value stored in a database or hardcoded in the CloudPage.

3. IP Restrictions

If you only want specific users (e.g., users from a particular company or specific IP addresses) to access the page, you can restrict access based on the user’s IP address. This can be done using AMPscript or SSJS.

4. Role-Based Access Control (RBAC)

You can implement role-based access control within SFMC by ensuring that only users with appropriate roles have access to the CloudPage. This would require custom user management but is useful if you have a team of administrators and want to restrict access based on roles.

Below, I will walk you through implementing a simple token-based authentication and restricting access using AMPscript and SSJS.

Mahendra Bhalerao Avatar
Mahendra Bhalerao

Leave a Reply

Your email address will not be published. Required fields are marked *