Report or New Database for Data Combination?

Panorama X
1

I have the following databases:

DB1
Email (common field to all databases)
Company Name

DB2
Email (common field to all databases)
Status (indicates if the email address is good)

DB3
Email (common field to all databases)
Opens (indicates number of times email message has been opened)

DB4
Email (common field to all databases)
Unsubscribed (indicates email recipient has unsubscribed)

I need to create either a report or a new database that combines the information for each unique email address.

Example: email address test@test.com is a good email address that has opened a message 5 times, test1@test1.com has unsubscribed, etc.

I would much appreciate if someone can help me determine the simplest way to achieve my objective.

2

DB1 is likely the place to run the procedure from. and to generate the report.

Using ArrayBuild to build a list of all addresses in four variables would be the way to start. One variable for DB1, one for DB2, one for DB3, and another for DB4. Each named to clearly identify the db they came from, therefore what they represent.

A second item in each array element can be added to identify the source DB.

At that point you have four lists in variables that can be displayed separately within the report.

They can be combined: Array1+cr()+Array2,etc. Then sorted with ArrayMultiSort.

Upping the ante just a bit, to show each email address once, followed by any other info about it, LoopArray can be applied to load the address of the first element into a variable, then move to the next element and blank the address if it’s the same as the variable’s value or replacing the variable’s value if it’s not the same, then moving down to the next in a rinse and repeat process.

3

I believe your task is tailor made for the relational join feature.

Start by picking one of the databases to hold the finished result. You could pick any one, but let’s arbitrarily pick DB1.

Add three new fields to this database - Status, Opens and Unsubscribed.

Using the Database Options > Relation panel, configure a relation between DB1 and the other three databases.

Once the relations are set up, you’ll need to use the Join dialog 3 times, once for each of the other databases. Make sure you check both Replace Existing Data and Append Non-matching Data in the Join Options dialog.

That’s it! The end result will be a single database with all of the information combined into one. It shouldn’t take more than a few minutes to set up, and the actual join will just take a second or two.

4

Jim,

The solution has worked flawlessly.

Question:
If I had two opens on two different dates that I wanted to track, would that be possible?

Thank you.

5

Sorry, this question is too ambiguous. What do you mean by “track”? How would these different dates be stored?

6

In the opens database, I would have first open on date1 and second open on date2.

I know I can select either first or last on the relationship screen, but is it possible to import both dates into DB1?

Thanks.

7

Sure, if you set up 2 date fields in the DB1 database, you could transfer both of them. As many as you like.

8

Of course!
Thank you.