Example: creating lists of marketing objects

Scenario

You have a project with four subordinate marketing object types:

• Two different brochures
• One mailer
• One resource bundle

After you create the project, you periodically check to see which participating marketing objects have already been created.

This example describes the steps necessary to create this scenario in Unica Plan.

Assumptions

The following items exist in Unica Plan:

• A project template named "Event planning".
• Marketing object templates for Brochures, Mailers, and Resource bundles.

Tasks

To implement this scenario, you do the following tasks.

1. Using the Forms Editor, set up the following forms:

   • Create a form to hold a custom Text - Single-Line attribute, Originating Project.

     After users create a participating marketing object, they enter the value of the project code for the originating project in this field.

   • Create a form for a custom tab, Participating Marketing Objects.

     For the custom tab, you add a Single Select Object Reference attribute to a form. To configure this attribute, you must first create a custom view.

   See "Creating the custom tab and attribute" below for details about creating these forms.

2. Create a custom view. See "Creating the custom view" below.
3. Add the forms to the appropriate templates.
   • Add the form with the Originating Project attribute as a section on the Summary tab of the Brochures, Mailers, and Resource bundles templates.
   • Add the form with the list as a custom tab on the Event planning project template.

4. Create the object instances from the corresponding templates:

   • A project, EventStuff001.
   • A brochure, Brochure001, with the originating project set to the project code for EventStuff001.
   • A mailer, Mailer001, with the originating project set to the project code for EventStuff001.

   When users open the Participating Marketing Objects tab for EventStuff001, they see the details for the associated marketing objects.

Creating the custom tab and attribute

We need a tab that can hold the list. We add this tab to a project template in Unica Plan. We also need a custom form to hold the project code. The custom view depends on both these forms using the same database table, so we create them both in this section.

1. In the Forms Editor, set up two forms, both using the same database table.
2. Create the database table as follows:

   Field	Value
   Table Name	dyn_mo_table
   Display Name	dyn_mo_table
   Key Column Name	po_id
   Attribute Name	po_id

3. Create the two forms:

   Field	Form 1	Form 2
   Internal Name	OriginatingProj	linkedMOs
   Display Name	Originating Project	Associated MOs
   Description	Holds a single attribute that points to the originating project.	Form to display the linked Marketing Objects in a list.
   Attribute Name Database Table	dyn_mo_table	dyn_mo_table

4. For the list grid component, specify:

   Field	Value
   Reference Database Table	proj_mos_by_proj_code
   Reference Table Key Column	mo_id
   Filter By Parent ID	checked
   Parent Id Column Name	ProjID

5. Create a grid attribute of type Single Select Object Reference. In the Object Reference Properties section, specify:

   Field	Value
   Object Reference ID Column	mo_id
   Object Reference Type Column	comp_type_name

6. Create a form attribute to hold the project code as a text attribute. For this attribute, specify:

   Field	Value
   Attribute type	Text - Single-Line
   Internal Name	PID
   Form	Originating Project
   Display Name	Project Code
   Database Column	PID

7. Save the forms.
8. Run the SQL script to create the dyn_mo_table and its columns.

Creating the custom view

Typically, you create a custom view before you can add an object reference to a list view. In this example, we reference a marketing object on a project, where the marketing object contains a text field that holds a project code.

This example uses three tables to create the view: uap_projects, uap_mktgobject, and the custom table dyn_mo_table. The view is named proj_mos_by_proj_code.

Prerequisite custom table

Before you create the view, ensure that you created the custom table, dyn_mo_table, and that it contains the following columns:

• po_id: key column, specified in the DB Tables tab for the form
• PID: text column created in the Forms Attribute tab to hold the project code as text.

Custom view details

The view contains the following columns:

• proj_code and project_id from uap_projects
• name, comp_type_name, and mktg_object_id from uap_mktgobject

The actual SQL code to create the view follows:

create view proj_mos_by_proj_code (
   asscProj, MOName, ProjID, mo_id, comp_type_name) As
   select PROJ.name as asscProj, MO.name as MOName,
   PROJ.project_id as ProjID, MO.mktg_object_id as mo_id,
   MO.comp_type_name as comp_type_name
   from uap_projects PROJ, dyn_mo_table MOT, uap_mktgobject MO
   where PROJ.proj_code = MOT.PID and MOT.po_id = MO.mktg_object_id

The following table shows the column names and some sample rows from this database view: