linking absences to cicmpy and cicntp in Crystal for eSynergy 1

[ultrav]

I am trying to write a crystal report and having trouble linking the absences table to cicmpy and cicntp. Can anyone tell me the proper links? Is there a reference I can use?

Thanks
Celeste

 

[stravis]

To link the absences table to cicmpy and cicntp use the GUID fields. A SQL statement linking the tables looks like this:

SELECT cmp.cmp_name AS cmp_name, cnt.FullName AS cnt_FullName, a.*
FROM Absences a 
LEFT OUTER JOIN cicmpy cmp ON 
	a.CustomerID = cmp.cmp_wwn 
LEFT OUTER JOIN cicntp cnt ON 
	a.CustomerID = cnt.cmp_wwn AND 
	a.CustomerContactID = cnt.cnt_id

If you don't know SQL, the linkages are between the absences.CustomerID and the cicmpy.cmp_wwn fields for the customer information. For the contact link link the absences.CustomerID field to the cintpy.cmp_wwn field and the absences.CustomerContactID field to the cicntp.cnt_id fields.

The cicmpy table links to other tables using either the cmp_wwn, debcode, or debnr fields. The cmp_wwn is used to link to eSynergy and "Globe" tables, the debnr field is used to linked to GL tables, and the debcode field is used to link to traditional Macola tables. These linking rules are generally the case but not always the case.

Scott Travis

http://www.infospring.net

 

[ultrav]

Thanks - I created the left outer join links but I am getting an error when trying to run my report "OBDC Error:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting from a character string to uniqueidentifier.

Any ideas what I should do?

Celeste

 

[stravis]

Are you trying to filter by a GUID field? GUID fields contain those long non-sensical codes like in the cicmpy.cmp_wwn. If you are currently using this field to filter try to filter instead by the cicmpy.debcode or cicmpy.cmp_code fields.

 

[ultrav]

Thanks - that was it - I chose Absences.ID instead of absences.type

Celeste