Using fields from multiple tables in uPlan Recipient Information Schema

[Jamison Chambers]

I am trying to use fields from two tables, both in the same
datasource, in the Recipient Information Schema. According to the
uPlan User Guide v5.2, I can do this. It says, "If data for the schema
fields is not available directly from a single table of the Data
Source, you can use a filter to create the appropriate fields using an
SQL SELECT query to retrieve the needed information from numerous
tables." I have a SQL query defined in the filter(testing it in MS SQL
Server Management Studio, after changing the syntax, returns exactly
what I want). I don't get any errors, however, I cannot generate a
proofset, and I get the error message "Recipients Data Source doesn’t
contain any Recipient Tables." I have read elsewhere on the forum,
that the Recipient Information Schema must match one table(the primary
table). I am confused that the documentation seems to conflict with
this. Has anyone used fields from two different tables in a Recipient
Information Schema? Thanks.

[George Marsh]

You need to create a user view for the additional table, the uPlan New
Plan wizard will do this for you if you have a relationship defined
between the tables, and you select to create ADORs from the other
table(s).

I've tended to do this in a new plan and then copy/paste the User View
query, then amend as needed, especially when it isn't working.

If you know your SQL works then you can paste in but may need to
change the syntax to match uPlan.

[Jamison Chambers]

Thanks for the quick reply. I have done that, and that part of the
Plan works, however, I need to be able to filter based on values from
the other table, not just create ADORs. That is what I can't figure
out how to do.

[George Marsh]

What do you need to filter? The table or the output? If you have an
ADOR in table A you can make it a variable and use it in the WHERE of
the User View on table B ... I've not worked out if there is a way to
filter output based on anything other than SQL.

If it helps the User View is a temporary table that is assembled by
the SQL to match the main table at run time. This can be 1:1 or
1:many, and you can use UNION ALL to join queries together - there are
some docs on the support site that might help.

[Jamison Chambers]

Thanks again. I need to filter the recipients. I need to use a value
from one table to filter whether the record in the other table(the
primary table) should be a recipient or not.

[George Marsh]

Sorry, just found this unsent in my drafts. Apologies, after reading
back through I think I missed the point of you initial query. I've not
been able to query 2 tables in one filter in uPlan (usually use Access
databases). If the datasource is static I tend to use long IN / NOT IN
lists (thanks Tim!) that I work out using Access or Excel. Presumably
you are using SQL Server? Have you tried building a query that
presents the tables you need to uPlan? Queries in Access appear as
tables to uPlan but haven't tried with SQL Server.

[Phil]

Hi George,
I've been trying to do the same sort of thing as Jamison, and I think
an external database for reference is the way forward.
I do have a query though, where do you store your other databases and
how do you reference them?
Thanks

[George Marsh]

> I do have a query though, where do you store your other databases and
> how do you reference them?

Phil, I tend to create multiple tables as needed in a fresh Access DB for each project (most of my work is assembling reports). Started using access as fairly easy and has fixed data typing. If you set the relationships correctly in access, the uPlan new plan wizard will create user views and adors for you, then easy to paste over and compare syntax.

I think Tim said at some point that one of the nice things about XMPie is the flexibility to easily add a flat file datasource late in the process with minimal effort.