Linking Remedy Tables

[Butlertl]

I have two Remedy 7.1 tables/views....One table has customer survey data with the Ticket number....the second table is the master table with all ticket information including the ticekt number. I have a report with the survey data in the report....when I link the HELP DESK table, and then add a field from the 2nd table...all other data dissappear. I do not have a select statement at this time. Any ideas will be appriciated. Thanks
CR 10

 

[lbass]

This is unclear--can't tell whether there are two or three tables involved. Please identify the tables by name. You probably need to be using left joins so you can bring in records in one table where they don't exist in the right hand table(s).

-LB

 

[Butlertl]

#1: HPD_survey_join
#2: HPD_Help_desk

 

[Butlertl]

I know I am missing something in the execution.....I would like to select using the resolved date in the HPD_Help_desk and link it via Incidence Number to the survey that weas returned in the HPD_survey_join.

 

[lbass]

Please post the SQL query from database->show SQL query.

-LB

 

[Butlertl]

SELECT "844_HPD_Survey_join"."Last Name", "844_HPD_Survey_join"."Incident Number", "844_HPD_Survey_join"."Contact Sensitivity", "844_HPD_Survey_join"."Status", "844_HPD_Survey_join"."Score", "844_HPD_Survey_join"."Survey Pass/Fail", "844_HPD_Survey_join"."SR_Q8", "844_HPD_Survey_join"."SR_Q1", "844_HPD_Survey_join"."SR_Q2", "844_HPD_Survey_join"."SR_Q3", "844_HPD_Survey_join"."SR_Q4", "844_HPD_Survey_join"."SR_Q5", "844_HPD_Survey_join"."SR_Q6", "844_HPD_Survey_join"."SR_Q7", "844_HPD_Survey_join"."First Name"
FROM "844:HPD Survey join" "844_HPD_Survey_join"
ORDER BY "844_HPD_Survey_join"."Incident Number"

 

[IanWaterman]

YOu are not showing the helpdesk table. We need to see that and its relationship with the survey join table.

DO NOT use Crystal Auto linking its is next to useless, when you add in table break any auto joins and do them manually as required Auto linking can put in spurious links which are not required/valid.

As LB suggests you may need to use Left outer joins.

Ian

 

[Butlertl]

I have the Help Desk table linked using Incident Number with a Left Outer Join with Enforced From...when I add a field to the report from the Help Desk table all data from the HPD_survey_join is removed.

 

[Butlertl]

I reversed the LINK....going from HPD_help_Desk to Survey_join...my select statement is LAST RESOLVED DATE in DATE RANGE

SELECT "844_HPD_Survey_join"."Last Name", "844_HPD_Survey_join"."Incident Number", "844_HPD_Survey_join"."Contact Sensitivity", "844_HPD_Survey_join"."Status", "844_HPD_Survey_join"."Score", "844_HPD_Survey_join"."Survey Pass/Fail", "844_HPD_Survey_join"."SR_Q8", "844_HPD_Survey_join"."SR_Q1", "844_HPD_Survey_join"."SR_Q2", "844_HPD_Survey_join"."SR_Q3", "844_HPD_Survey_join"."SR_Q4", "844_HPD_Survey_join"."SR_Q5", "844_HPD_Survey_join"."SR_Q6", "844_HPD_Survey_join"."SR_Q7", "HPD_Help_Desk"."Reported Date", "HPD_Help_Desk"."Last Resolved Date"
FROM "HPD:Help Desk" "HPD_Help_Desk" LEFT OUTER JOIN "844:HPD Survey join" "844_HPD_Survey_join" ON "HPD_Help_Desk"."Incident Number"="844_HPD_Survey_join"."Incident Number"
WHERE ("HPD_Help_Desk"."Last Resolved Date">={ts '2010-06-06 00:00:00'} AND "HPD_Help_Desk"."Last Resolved Date"<{ts '2010-06-15 00:00:00'})
ORDER BY "844_HPD_Survey_join"."Incident Number

 

[IanWaterman]

Did that work?

Did you see any data?

Do you have access to SQL server management studio so that you can query tables independently and see if there is any related data in the two tables?

Or write two reports one querying each table and review contents.

Ian

 

[Butlertl]

It did not work......I think my process is corrupted.
I have reprots from each table that provides data. The INCIDENT NUMBER is a string (15).....I created a report with HPD_survey_join with field in the report returning data. I then, added the HPD_Help_Desk table aith the above link......and when I insert a field from the new table in the report the previous data is erased and the new data is blank.

 

[lbass]

Do the incident numbers from each table display exactly the same?

-LB

 

[Butlertl]

Negative....nothing displays......test data using an access database works fine...I am thinking it's the setup here with our network

 

[lbass]

I meant--if you run a report separately for each table so you can see how they display.

-LB

 

[Butlertl]

Oh...yes...individual reports run without a problem....any field I put in the report will print....

 

[lbass]

I asked whether the incident numbers display exactly the same from each table.

-LB

 

[Butlertl]

Yes......they do.

Any chance this could be an ODBC issue?