Acessing 1:n data from DB in a Campaign Delivery

Question

Good morning,

I've been having a hard time since yesterday because I can't access the data that is in a one to many table in the database.

This is the case, I want to send a e-mail to an Employee, using data from one of the month and all the flights of that month.

So far I've not been able to find the data in a test preview as seen in the next images.

The code:

The result preview:

The data that I've found is the value of an external key (Unique Flight) in the employeeFlights Table.

Is it possible to access the full scope of data in the employeeFlights table to create a dynamic template that shows as many flights as there is entries in the table?

Note that the target mapping points to the Employees table not the Months table.

Bruno_Cotrim · Accepted Answer

I've found a way to do it the way I was expecting to do it.

Thank youWodnik for unlocking the thought process, my objective was a bit more dynamic than hard coding the number of rows but the input was critical to make me understand it was possible to do it as I had imagined it.

The problem was I was trying to access the DB directly instead of accessing the Temporary tables and when I tried to access the temporary tables I had the delivery as "Enable but do not Execute" so the targetData variable was empty...

Another problem was the change Dimension that does not keep the previous data without the steps bellow.

So future reference to any one this is was the solution:

The code:

The output:

Jonathon_wodnicki · Answer

Hi,For delivery personalization, select the many table into the temp table using query activity.It has a wizard that will help pivot the rows into row-number-suffixed columns, e.g. select top 5 rows and in the row they'll be named flightNumber1..5, departureDate1..5, etc. From there use a loop counter to append 1..5 to the col names to access them.Thanks,-Jon

Sign up

Login with SSO

Login to the community

Login with SSO