Linking to multiple tables.

[rkeyser]

With the Visual Linking Expert, I need to link 1 table to 2 other tables, so I created an alais table for the table with 2 links. I linked the first instance of the table one way and the alaised copy the other way. Now I get an error message: "Your current link configuration contains multiple starting points. Please be advised that this is generally not supported."

1. Is this just a warning that I'd better know what I'm doing (I'm new to CR)?

2. Is there a better way to express 2 equal joins off of one table?

Thanks.

 

[dgillz]

I would need to see your actual linking diagram to really judge this, but I believe this is just a warning. Also, use of an alias is a very good way to do what you are describing. Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[rkeyser]

dgillz~

Thanks for the quick reply. Here is some more info:

I am using CR 8.5 to report on a MS SQL 2000 database of Clarify data. Clarify's schema is non-traditional and highly normalized.

Using SQL Query Analyzer, I can display the fields I need like this:

select c.id_number, s.name, 1.close_date, l.x_hours, e.work_group
from table_site s, table_close_case l, table_case c, table_act_entry a, table_employee e
where l.close_case2act_entry = a.objid
and a.act_entry2case = c.objid
and c.case_reporter2site = s.objid
and closer2employee = e.objid
and l.close_date > '2003-02-26' and l.close_date < '2003-03-25'
and l.x_hours <> ''
and l.x_hours <> '0.00'
order by 1

If you are not familiar with Clarify, it names the primary key of every table &quot;objid&quot; for Object ID, an arbitrary row identifier. To show this structure in CR's Visual Linking Expert, I have (all equal join links) field close_case2act_entry in table_close_case linked to objid in table_act_entry, act_entry2case linked to objid in table_case, and case_reporter2site in table_case linked to objid of table_site. Then disconnected from those tables, I have closer2employee in the alias table_close_case_1 linked to objid of table_employee.

When I preview the report, the fields I need from the first group of tables display properly, but the field I need from table employee appears empty. Yet when I 'Browse Field Date...' that field in the design tab, I can see all the values.

I also notice that when I 'Show SQL Query' under menu Database, there is no mention of table_employee:

SELECT
table_site.name,
table_case.id_number,
table_close_case.close_date, table_close_case.x_hours
FROM
MPSDev.dbo.table_site table_site,
MPSDev.dbo.table_case table_case,
MPSDev.dbo.table_act_entry table_act_entry,
MPSDev.dbo.table_close_case table_close_case
WHERE
table_site.objid = table_case.case_reporter2site AND
table_case.objid = table_act_entry.act_entry2case AND
table_act_entry.objid = table_close_case.close_case2act_entry AND
table_close_case.close_date >= &quot;Mar 10 2003 00:00:00AM&quot; AND
table_close_case.close_date < &quot;Mar 25 2003 00:00:00AM&quot;
ORDER BY
table_site.name ASC

That is what made me think the linking is not working.

Sorry for the long post, but I wanted to be thorough. Appreciate your help.

~rkeyser

 

[rkeyser]

Solved! Now I see what I was doing wrong.

I didn't know that in order to use an alias table, one should link the primary key of the first table to that of its alias. Then the error message goes away and suddenly the field(s) from the dead tables come alive in the report.

Reports against traditionally named database schemas would never have this problem, because smart-linking would automatically link the primary keys. I had turned off smart-linking, because of Clarify's naming convention.

Thanks for the advice. At least it kept me digging.