Query data extensions with SSJS and AMPScript

Marketing Automation, Salesforce Marketing Cloud
1 minute read

There’s another topic for which official documentation often lacks sufficient information, but it can be incredibly useful when needed.

It’s important to note that in AMPScript, we use the data extension name to reference the table for any query. On the other hand, in SSJS, we utilize the data extension’s external key to reference the table. Understanding this distinction can save you hours of debugging.

For many cases we will sufice with simple AMPScript Lookup or LookupRows. But when you need to set more complex queries we would need to use SSJS Rows.Retrieve method of DataExtension object.

var  dataExtension = DataExtension.Init("external_key"),
     data = dataExtension.Rows.Retrieve();


The Rows.Retrieve method accepts a single argument known as the “filter,” which allows us to construct our filtering conditions.

var dataExtension = DataExtension.Init("external_key"),
    filter = {Property:"Domain",SimpleOperator:"like",Value:"martechnotes.com"},
    data = dataExtension.Rows.Retrieve(filter);

You can select from multiple filter simple operators:

  • equals
  • notEquals
  • greaterThan
  • lessThan
  • isNull
  • isNotNull
  • greaterThanOrEqual
  • lessThanOrEqual
  • between
  • IN
  • like

Logical operators that can be used are:

  • AND
  • OR

You can also create complex filters wherever they are needed:

var 
filter = {
	leftOperand: {
      Property:"Domain",SimpleOperator:"like",Value:"https://martechnotes.com"
    }
  	LogicalOperator: "AND",//OR
  	rightOperand:{
  		leftOperand: {
      		Property:"Domain",SimpleOperator:"like",Value:"/path1"
		},
  		LogicalOperator: "OR",//AND
  		rightOperand:{
  			Property:"Domain",SimpleOperator:"like",Value:"/path2"
    	}
	}
},
data =  dataExtension.Rows.Retrieve(filter);
  

As you can see we can create as many nested filters as needed.

The results of each query are returned as an array of objects, consisting of the returned rows in JSON format.

[
  {Domain:"https://martechnotes.com/path1",...},
  {Domain:"https://martechnotes.com/path2",...},
]
Adobe Campaign Classic tips
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

How to copy query conditions to another split

1 minute read

Perhaps you’ve encountered an unusual occurrence while attempting to copy complex query conditions to your clipboard. In some instances, only a portion of the content was copied, while in others, nothing was copied at all. I’ve personally faced similar situations over the years but never delved deeper into the matter. However, one day, I reached […]

Continue reading
Salesforce Marketing Cloud Tips
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

Journey validation fails for no reason

less than a minute read

There is an error with starting the journey, specifically related to the email template, but it gives you no explanation whatsoever. I’m sure there can be many problems with the template that prevent the journey from being started. One of the problems I have experienced most recently is that the template has not been approved. […]

Continue reading
Salesforce Marketing Cloud Tips
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

Track links that are AMPScript variables

1 minute read

When you save links as part of an HTML code in an AMPScript variable, such as a paragraph containing a link to a page, you may face challenges in tracking these links. Salesforce offers a great feature that allows tracking of such links using the “httpgetwrap” inserted right before the URL protocol. When dealing with […]

Continue reading
how to publish cloud page immediatelly
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

How to publish cloud page immediately

2 minutes read

You know the struggle when developing a cloud page application and trying to debug some issues or make changes. You publish the cloud page and wait and wait. Sometimes it takes ages, and other times it is right away. Why does this happen? How can we make it work immediately? Why we have to endure […]

Continue reading
Adobe Campaign Classic tips
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

Troubleshooting web applications

1 minute read

When building any web application (webapp), you may have experienced a syntax error on, for example, line 200. This can be perplexing, especially when your custom JavaScript code does not even have 200 lines. Why is that? The reason for this discrepancy is that the web applications you create in the visual editor are compiled […]

Continue reading
Adobe Campaign Classic tips
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

Column must appear in GROUP BY clause

1 minute read

Creating aggregated data reports may not be a daily task, but there are times when you might be tasked with generating such reports. For instance, you could group and track log clicks to create the coveted ‘Hot Clicks Report’. You tell yourself, ‘It’s nothing major,’ and believe you have an idea of how to do […]

Continue reading
Adobe Campaign Classic tips
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

Fast select field in query builder

less than a minute read

Working with Adobe Campaign Classic often involves frequent mouse movements and clicks. However, after a while, you’ll discover certain tricks to work with this tool more efficiently and quickly. Today, I’ll show you a simple trick that will help you enter fields in the query builder faster and without having to move your mouse across […]

Continue reading
Adobe Campaign Classic enrichment activity
Adobe Campaign, Marketing Automation

How to use enrichment activity in workflow

6 minutes read

The Enrichment activity in Adobe Campaign Classic is a versatile tool that allows campaign managers to enrich their targeting data with additional information from various data sources. In technical terms, the Enrichment activity can be likened to performing a JOIN operation. A JOIN operation is a fundamental concept in database management systems (DBMS), where data […]

Continue reading
Salesforce Marketing Cloud Tips
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

Power of AMPScript Functions in SSJS

3 minutes read

Have you ever wondered, while working with SSJS, if there was a function in SSJS similar to the one you use in AMPscript? What if I told you that you can bring native AMPscript functions to SSJS? Today, I will show you how to incorporate the best features from both scripting worlds into SSJS automation, […]

Continue reading