Relationship between tables with custom key values

👨‍💻 Development Help
1

Hello everybody,
I’m currently using SuiteCRM for migrating an existing database on a custom made CRM solution.

Right now I’ve exported each table of the old CRM in CSV and imported them in SuiteCRM. For each table I have created a custom module. Each table, in fact, refers to a separate SuiteCRM entity.

I adapted the SuiteCRM modules to the CSV columns. My problem is the connection between these tables. Each table has one or more key value to connect to another table. The key values are integers like 1-2-3-4… How could I connect the key values?

Thank you so much

1 Like
2

I will suggest to create a custom field to hold those IDs and then create a LogicHook to create a relationship when imported.

3

Do you have any link or code example that could be suitable to my problem? Consider that after importing these data, I just need to keep the consistency between tables using ID’s. One table can have multiple ID’s connected to other tables.

Thanks!

4

I would use a PHP importer script that uses SuiteCRM Beans to make everything work.

There is an example here: https://gist.github.com/pgorod/3ce7868a290df0ef4f8c9de74f12a6cd

I also recommend using the default modules, as much as possible. Instead of creating new custom modules for your entities, try to think “which SuiteCRM module looks more similar to this entity?”. This will give you access to a ton of features in the existing modules.

5

Hi Pgr,
thanks for your response. Right now the database I’m importing is almost totally different. It’s based on Area/Streets/Building, but it doesn’t contain any geographical data like PostGIS. By the way, each table contains many data that cannot be found in the default models.

For example: most of the tables have ID numbered in progressive order, Name of the street, ID of the building, and many other data like check box of 0/1 or names.

How could I import everything in SuiteCRM? Your option would be interesting, but I don’t understand the full potential.

6

This is complicated to explain generically, but it goes something like this:

  • understand the SuiteCRM data model the best you can, and read about manipulating data with Beans objects: https://docs.suitecrm.com/developer/working-with-beans/

  • add some custom fields to the default modules to include some extra data you have

  • understand that SuiteCRM IDs don’t have any other requirement, other than being unique. You can use your existing IDs.

  • write the script to load data into modules, and build the relationships as it goes along

  • include Security Groups (who can access what) in your design

Of course it’s ok to use entirely new modules where necessary. But all the work of tying them up with the rest of the product will have to be done by hand, so do it only if it’s really necessary, and you have the ability to do so.

7

Ok thank you so much.

Until now I’ve just tried to import data via CSV with import function, but I were not able to keep the relationship as in the previous database.

I will try in the new way you told me to do.