Retrieve information from an external database

Hi,

I have two different problems but they both concern external database :

  1. Let’s say I have an external database with car models. It is dynamic : if a new car arrives on the market, it will be added to this database. I want a dropdown in Suite CRM which will show all the car models. How can I make it ?

  2. I have a field for flight ID in my opportunity module. I have an external database where the flights IDs are linked to all the information about the flight : date of departure, date of arrival, number of passengers, etc. What I want is to auto-populate fields in SuiteCRM (typically “Date of departure”, etc.) when I provide the flight ID.

In both case I need to link to an external database.

Does anybody have a solution ?

Thanks.

Anyone has an idea ?

Must I create a custom module ? Or can I do it directly ?

You can do it both ways. But what I found to be a better approach is to make a custom module. With custom module you can make relationships and have this field elsewhere in Suite. You can have search as well which is pretty helpful when you have a lot of items in this dropdown list.
But with custom module there is a question of data synchronization with external database. You don’t want to have different data in two databases. I deal with this using triggers on insert/update/delete in the external database to insert/update/delete data in the corresponding suitecrm database as well. Be careful with this since you might want to update certain value where ever it may get (eg. in some related module as well). Also make this custom module read only for all users since it is being updated externally.
However this is only better if you need this level of complexity. If the data in the external database is, let’s say static, with a few items and is not being regularly updated then you should go with a dropdown field and a query with connection string. Also if the application that uses this another database has some web service/api you definitely should use it instead of dealing directly with its database.

Thanks for your reply.

The problem is that I don’t manage the external database. It’s a database of all the flights in the world, which is obviously regularly updated. Maybe I can create a sync at a certain frequency but it can be a very heavy process.

In that case it is almost certain they do have an API and they expose it to partners, affiliates etc. However when you get the documentation for the api you will know what to do with it.