single data source with multiple tables

[efrank]

Good afternoon,

I'm still pretty new to xmpie and therefore I have sometimes
difficulties visualizing what I need to do.
I'm currently working on a campaign that seeks to aid in selling
condos in a new building complex. The building company has built and
sold some other complexes that are near to the one the campaign is
for.
1.- The first step in the campaign is to take owners from the already
built and sold complexes and send them an e-mail urging them to visit
a page where they refer a friend.
This is a table in the database and the columns are uid,
name_referrer, lastname_referrer, email_referrer.
2.- Each time a person is referred and on page up of the thank-you-
page, an e-mail is sent to the referred person with a purl to a page
where they should sign up to make a date with a sales person (self
regsitration). Another e-mail goes to the referring person, to thank
for the referral.
Another table in the database records all referred persons and columns
are uid, name_referred, lastname_referred, email_referred,
referrer_id.
3.- The referred person visits the purl and registers for a date with
a sales person. Upon page up of the thank-you page three emails are
sent. One to the person signing up, one to sales person and one to the
referrer letting him know that his referred person has signed up for a
visit.
The information gathered here is stored in a third table. Columns
include uid, name, lastname, email, area code, phone number, cell
phone number, date, time, registration time.

When I try to set up a plan file, I'm not sure which table should be
the primary.
If I choose one of the three tables, the uPlan tells me when finishing
the setup that one or more column views don't have a relationship. I
have no prior experience establishing those relationships.

I'm located in Venezuela and support is scarce over here. Any insights
are highly appreciated.

[Bill]

The warning you are mentioning is only due to the fact that no
relationship is defined between your tables in your database engine
(whether it is SQL Server, MySQL, etc.); so, unfortunately, uPlan
cannot guess which relationship does exist between the master table
and the "satellite" tables (even though column names are the same in
separate tables). You ***must*** help uPlan, by double-clicking the
User View name, and coding the relationship, thanks to an SQL query,
such as

select [satellitetable].[columns],[satellitetable].[column2]
from [satellitetable]
where [satellitetable].[mysatellitekey] = |->[mymasterkey]

BUT

As far as I know (XMPie gurus will stop me if I am wrong), the current
version of uCreate XM and uProduce (4.6.2) only allows you add
recipients (thanks to the Insert button in the uCreate XM toolbar in
Dreamweaver) to the ***main*** database (the one described in the
Schema "branch" in uPlan).

So, I am afraid that you cannot add both referers (say, in the master
table) and refered persons (say, in a satellite table).

When I need to add both referers and refered persons in the same
campaign, I try to store everybody in the ***same*** "master" table,
and add a particular column, to specify who was the referer of a
refered person; original people - who were not refered to by anybody -
have this column value empty. If needed, uPlan user views can further
help retrieve several people, who would have been refered to by the
same referer:

select [mytable].[name],[mytable].[email]
from [mytable]
where [mytable].[myreferer] = |->[myid]

[efrank]

Thanks for your help Bill. I went with the strategy of moving all
recipients to the same table, the one described under 3 in my first
post.
Now I'm having difficulties with the filtering of the recipients. I
wish to filter out original people by checking on two fields that
should be null if the recipient is original person.

select @[table3].[name], @[table3].[lastname]. @[table3].[email]
from @[table3]
where (@[table].{field1] = 'NULL' and @[table].{field2] = 'NULL')

Even though there is an original person in the database, I get empty
proofsets from this expression.
An original person has values only for these fields: @[table3].[name],
@[table3].[lastname]. @[table3].[email].
Every other field is NULL, including field1 and field2. I'm certain of
at least one entry in the database the complies with the above
conditions.
Is my expression built correctly? It validates when validating the
plan.

Thanks again

[Justin]

'NULL' is a character value, you are searching for the text value of
'NULL' with your statement. You want to use NULL (no apostrophe)

[Justin]

Proper syntax:

select @[table3].[name], @[table3].[lastname]. @[table3].[email]
from @[table3]

where (@[table].{field1] IS NULL and @[table].{field2] IS NULL)