Link Table

[#1]

Hi,

i have created a link table for master calender but it is not working fine. can any one help me

i have two table calldetails and indentdetails both are linked through callno. for one callno there may be 1 or more indents in indentdetails.

Calldetails:

callno

call_date

close_date

indentdetails:

indentNo

callNo

indentdate

now i want to create master calender for call_date, close_date and indentdate

for that im creating linktable but it is not working fine for indentdate.

LINK_TABLE:

LOAD callno as %CallLink,
CallDate as Call_Date,
TIME(MakeTime(Hour(CALLDATE),Minute(CALLDATE)),'HH:mm') as Call_Hour,
Left(TIME(MakeTime(Hour(CALLDATE),Minute(CALLDATE)),'HH:mm'),2) as Hrs,
'Open' as Link_Flag
Resident
Calldetails;

Concatenate

LOAD callno as %CallLink,
Resolved_date as Call_Date,
TIME(MakeTime(Hour(ResolvedDate),Minute(ResolvedDate)),'HH:mm') as Call_Hour,
Left(TIME(MakeTime(Hour(ResolvedDate),Minute(ResolvedDate)),'HH:mm'),2) as Hrs,
'Closed' as Link_Flag
Resident
Calldetails;

Concatenate

LOAD callno as %CallLink,
IndentDate as Call_Date,
TIME(MakeTime(Hour(INDENT_DATE),Minute(INDENT_DATE)),'HH:mm') as Call_Hour,
Left(TIME(MakeTime(Hour(INDENT_DATE),Minute(INDENT_DATE)),'HH:mm'),2) as Hrs,
'Indent' as Link_Flag
Resident
IndentDetails;

can any one help me please.

[rajeshvaswani77]

Hi,

Check the date time format of the datetime fields in CallDetails and IncidentDetails table. Are they exactly as expected?

They could be different in format, or missing the time part and not matching.

thanks,

Rajesh Vaswani

[#3]

no all are same used makedate for all three fields.

[Peter_Cammaert]

Make a Table Box with callno, the %CallLink and the three original dates. Check whether you are getting NULL values and whether they're OK.

I'm not sure what you're talking about when you say "it's not working fine". Could you give more details about what results you get?

BTW This is a weird construction, as you will be counting the same record more than once. Imagine for example that callno=12345 has a CallDate=1/1/2013, a single INDENT_DATE=1/3/2013 and a Resolved_date=1/4/2013. A single  CallDetails row will now be linked to two different dates at the same time and a third time through IndentDetails. Is this your intention?

Peter

[#5]

Hi,

As your tables are having one to many relationship , so u better not concatinate those two table "calldetails" and

"indentdetails" . Join Calldetail Into Indentdetail to form one table .Then take 3 times the resident of this table one time having call dete and other time having Close date and last as Indent date, concatinate all three and form a link table.

then link this common date field to your master calendar date.

I hope this helps.

@ Pratibha

[#6]

thanks to all ill try it.