Acessing 1:n data from DB in a Campaign Delivery | Community
Skip to main content
B
Bruno_Cotrim
Level 2
February 27, 2019
Solved

Acessing 1:n data from DB in a Campaign Delivery

  • February 27, 2019
  • 2 replies
  • 2432 views

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.

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Bruno_Cotrim

    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:

    2 replies

    Jonathon_wodnicki
    Community Advisor
    Jonathon_wodnickiCommunity Advisor
    Community Advisor
    February 27, 2019

    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

      B
      Bruno_CotrimAuthorAccepted solution
      Level 2
      February 27, 2019

      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:

      Terms & ConditionsAccessibility statement

      Loading footer...