Query using a many-to-many relationship querying-using-a-many-to-many-relationship
In this example, we want to recover recipients not contacted during the last 7 days. This query concerns all deliveries.
This example also shows how to configure a filter related to the choice of a collection element (or orange node). Collection elements are available in the Field to select window.
-
Which table needs to be selected?
The recipient table (nms:recipient)
-
Fields to be selected for the output column
Primary key, Last name, First name and Email
-
Based on which criteria is the information filtered
Based on the delivery logs of recipients going back 7 days before today
Apply the following steps:
-
Open the Generic query editor and select the Recipient table (nms:recipient).
-
In the Data to extract window, select Primary key, First name, Last name and Email.
-
In the sorting window, sort the names alphabetically.
-
In the Data filtering window, select Filtering conditions.
-
In the Target element window, the filtering condition for extracting profiles with no tracking log for the last 7 days involves two steps. The element you need to select is a many-to-many link.
-
Start by selecting the Recipient delivery logs (broadlog) collection element (orange node) for the first Value column.
Choose the do not exist as operator. There is no need to select a second value in this line.
-
The content of the second filtering condition depends on the first. Here, the Event date field is offered directly in the Recipient delivery logs table since there is a link to this table.
Select Event date with the greater than or equal to operator. Select the DaysAgo (7) value. To do this, click Edit expression in the Value field. In the Formula type window, select Process on dates and Current date minus n days, giving “7” as a value.
The filter condition is configured.
-
-
In the Data formatting window, switch the last names to upper-case. Click the Last name line in the Transformation column and select Switch to upper case in the drop-down menu.
-
Use the Add a calculated field function to insert a column into the data preview window.
In this example, add a calculated field with the first and last names of the recipients in a single column. Click the Add a calculated field function. In the Export calculated field definition window, enter a label and an internal name and choose the JavaScript Expression type. Then enter the following expression:
code language-none var rep = source._firstName+" - "+source._lastName return rep
Click OK. The Data formatting window is configured.
For more on adding calculated fields, refer to this section.
-
The result is shown in the Data preview window. Recipients which not have been contacted in the last 7 days are displayed in alphabetical order. Names are displayed in upper case and the column with first and last names has been created.
