Expert Crystal Reports -> mySQL advice needed

[daffies]

Hi,
We are currently evaluating SugarCRM OS and have been using Goldmine for the past 5 years.
I designed several Goldmine reports using Crystal Reports.
I installed the SugarCRM on Windows 2003 and installed PHP, Apache and mySQL without any problems, Being quite familiar with Crystal Reports, I downloaded the MySQL ODBC 3.51 Driver and installed it successfully.

My problem is as follows:
I am trying to create a Report where I can view in a single report, the Company Name, Industry (Accounts)/ Firstname , Lastname (Contacts) / Case Number, Date Entered, Created By (Cases) created in SugarCRM. At the moment I can only create a report based on one table.

With Goldmine the GUID for each record is the same and appears in each table, Using Crystal's Visual Linking Expert, I can then map the accounts.id to the contacts.id and cases.id because the GUID for each record is the same.

However, in mySQL when I view the tables via phpmyadmin and look at the Accounts,Contacts and Cases tables, the GUID that appears in the Contacts.Id as well as Cases.Id is not the same as the Accounts.Id???

How can I successfully insert the Accounts, Contacts and Cases fields in a single Crystal Report?

I will really appreciate any advice!

Thanks,
Dave Reid

[JeanetteRoss]

Hi
Straight forward

accounts_contacts is the table that links the accounts and contact tables
you have to join the account to the account_contacts table then the account_contacts to the contacts table
SELECT *
`contacts`.`id`
FROM
`accounts`
Inner Join `accounts_contacts` ON `accounts`.`id` = `accounts_contacts`.`account_id`
Inner Join `contacts` ON `accounts_contacts`.`contact_id` = `contacts`.`id`
Inner Join `cases` ON `accounts_contacts`.`account_id` = `cases`.`account_id`

Which version of Crystal and mysql are you using as you could create a 'view' in mysql and then use that in your crystal report.
Plus is crystal 10 I think you can customise your own field - so you could trim() or left () your field and make it the same size

Jeanette

[JeanetteRoss]

Also -
If your sugar is running on Windows and its mysql you might want to check out a product called navicat which allows easier interogation of your data including an wizardy type thing to create queries and views.
If you are learning code its a good place to start
Cheers
Jeanette

[daffies]

Hi Jeanette,
Thanks for your feedback.
I am using Crystal 8.5 and MySQL 5.0.24
When i tried to link Accounts.id to Acoounts_contacts.Account_id I get a file link error in Crystal, due to type mismatch, Acoount.id is char and Acoounts_contacts.Account_id is varchar.
Where in Crystal I type in the QUERY?
Once I get the table links correct then designing the report or any report for that matter will be really straitforward.

Thanks,
Dave

[JeanetteRoss]

Hi Dave
If memory allows -
once you have created the odbc link select tables - try and not link them. go to sql query - it may be on the data tab and type a query in manually - It may work.

Version 10 of crystal allows you to create your own field attached to a database so you could say field = char 10 (id) it will then allow you to link.

I think the quickest way would be to force sugar database to change the datatype in mysql to varchar - that would fix the crystal issue but when you do any upgrades or fixes to Sugar you may have to change it back -

I think that this is a Sugar issue - it it good practice to have linked fields having the same properties. Crystal is very particular about indexing too but hopefully Sugar developers indexed the right fields!

Let me know how you get on - I have a copy of crystal 8.5 somewhere so I will try and test stuff out before the weekend
cheers
Jeanette

[jmaxwell]

Since mysql 5 supports views, it might be easiest to create a view for this , that way you don't have to worry about changing data types in the actual tables (and what happens if mysql chanages them back on the next sugar update).

-Ed