Emails table in Suite

Hi,

I’m trying to figure out where are my Emails I received? I imported Emails into the “emails” table in suite but I can’t see them in the “Emails”-module. Do you guys know what I have to do with them when I imported them into the table?

Thanks for helping me

  1. What is your version of SuiteCRM?

  2. Do you see the emails in the database? Just to check that they are there.

  3. Could you have permissions issues, or other filters in place that prevent you from seeing what you just imported?

  1. It’s the latest version.

  2. I see the Emails in the database and the text of them are in emails_text.

  3. No, I don’t think so.

When you say you “imported emails into the emails table”, are you talking about a database import? Or did you use the regular method of connecting an email account with SuiteCRM and then importing from the UI?

I just imported them manually from the Salesforce tables into the Suite tables.
I didn’t import them from the UI.

So it seems your Import isn’t complete for some reason.

I don’t know all the details of how Emails should be stored, but try to look in phpMyAdmin at how an Email created normally, from the UI, is stored in those tables, and perhaps in other related tables. You must be missing some ID, some relationship, to make sure that SuiteCRM sees the Emails where it should.

This database schema might help you navigate:

https://schema–suitedocs.netlify.com/schema/

@neon - "1. It’s the latest version. " it’s not an acceptable answer… go to About and provide full version info - for both platforms so we have something to work with. Also provide server side info: php, webserver, hosting…etc …

From what I understand:

  • you have salesforce and suitecrm
  • you want to migrate e-mails from saleforce to new suitecrm instance?
  • you imported a dump of the e-mails table in salesforce into the e-mails table of suitecrm?
  • did you just import only the e-mails content ??

reason why you won’t be able to see the e-mails is that both systems are based on relational databases. if you are going to take a look into the inbound e-mails setting, each one has an ID in the DB. when an e-mail is received in that email account the id if the account is used as a relation to the e-mail record so to make this work and to see the e-mails you imported make sure that the inbound e-mail ID = inbound E-mail ID of the record imported.

Had the same issue with data migration from old suitecrm to new install suitecrm. To test if this is the case:

  • login to mysql
  • select your CRM database
  • run:
select id from inbound_email;

this will provide you with the list of email accounts you have set as your inbound accounts.

  • select the id of the account you are using as your inbound account and run :
select * from emails where mailbox_id like "ID";

– replace ID with the actual email ID.

To fix this you could try a simple replacement of the ID in the inbound table with the ID of the inbound email you have on your salesforce install, or replace the mailbox_id of the e-mails in the emails table.

Let me know if this works. This solution worked on my migration when I couldn’t see the e-mail imported from a different instance of suitecrm not other CRM system so IDK if from salesforce is the same…shouldn’t be to far from this tho…

  • you have salesforce and suitecrm -> right

  • you want to migrate e-mails from saleforce to new suitecrm instance? -> correct

  • you imported a dump of the e-mails table in salesforce into the e-mails table of suitecrm? -> i imported my salesforce emails to the suite database

  • did you just import only the e-mails content ?? -> i didn’t imported the emails via UI. I want to import them from the salesforce database to the suite database manually

select id from inbound_email;

this showed just one sugar_id

select * from emails where mailbox_id like "ID";

The mailbox_id column is empty.

Do I have to import my emails from salesforce into “inbound_email”?

Ok. As you have the e-mails from another CRM system, things might get complicated, reason why some providers offer a migration solution. I DO NOT represent any vendor and HAVE NOT ever used any of their services so I can’t suggest any. Please look into a migration service as I don’t think you will be able to do this by yourself with the desired outcome. <<== This is my advice !

E-mails and e-mail information are stored under multiple database tables. For example:

MariaDB [(none)]> show columns from suitecrm4.emails_text;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| email_id         | char(36)     | NO   | PRI | NULL    |       |
| from_addr        | varchar(255) | YES  | MUL | NULL    |       |
| reply_to_addr    | varchar(255) | YES  |     | NULL    |       |
| to_addrs         | text         | YES  |     | NULL    |       |
| cc_addrs         | text         | YES  |     | NULL    |       |
| bcc_addrs        | text         | YES  |     | NULL    |       |
| description      | longtext     | YES  |     | NULL    |       |
| description_html | longtext     | YES  |     | NULL    |       |
| raw_source       | longtext     | YES  |     | NULL    |       |
| deleted          | tinyint(1)   | YES  |     | 0       |       |
+------------------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

MariaDB [(none)]> show columns from suitecrm4.emails;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | char(36)     | NO   | PRI | NULL    |       |
| date_entered       | datetime     | YES  |     | NULL    |       |
| date_modified      | datetime     | YES  |     | NULL    |       |
| assigned_user_id   | char(36)     | YES  | MUL | NULL    |       |
| modified_user_id   | char(36)     | YES  |     | NULL    |       |
| created_by         | char(36)     | YES  |     | NULL    |       |
| deleted            | tinyint(1)   | YES  |     | NULL    |       |
| date_sent          | datetime     | YES  |     | NULL    |       |
| message_id         | varchar(255) | YES  | MUL | NULL    |       |
| name               | varchar(255) | YES  | MUL | NULL    |       |
| type               | varchar(100) | YES  |     | NULL    |       |
| status             | varchar(100) | YES  |     | NULL    |       |
| flagged            | tinyint(1)   | YES  |     | NULL    |       |
| reply_to_status    | tinyint(1)   | YES  |     | NULL    |       |
| intent             | varchar(100) | YES  |     | pick    |       |
| mailbox_id         | char(36)     | YES  |     | NULL    |       |
| parent_type        | varchar(100) | YES  |     | NULL    |       |
| parent_id          | char(36)     | YES  | MUL | NULL    |       |
| orphaned           | tinyint(1)   | YES  |     | 0       |       |
| last_synced        | datetime     | YES  |     | NULL    |       |
| uid                | varchar(255) | YES  |     | NULL    |       |
| category_id        | varchar(100) | YES  | MUL | NULL    |       |
| date_sent_received | datetime     | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
23 rows in set (0.00 sec)

This is how SuiteCRM stores e-mail and E-mail information (not the only tables). If you can figure out the logic behind the e-mails module and the relationships between the tables/columns then you might have a chance. You would need to compare the SalesForce database schema with the SuiteCRM DB schema (as it’s based on sugar then see this https://apidocs.sugarcrm.com/schema/6.5.24/ce/index.html) and the manually import the info in the correct columns. Theoretically it’s not that complicated, but due to relationships it can be tricky to see all info in the correct fields.

Sorry if this doesn’t clear thinks up.

That Schema link is outdated, it’s for an old SugarCRM version and things have changed in our new Emails module.

I posted an updated schema link above, here it is again in case it helps

https://schema–suitedocs.netlify.com/schema/

https://schema–suitedocs.netlify.com/schema/

This link doesn’t work.

I have this schema too. My suite version is 7.11.2

Sorry about that, you’re right.

Please try this other link

https://schema–suitecrm-docs.netlify.com/schema/

After replacing the mailbox_id of my emails table I still can’t see anything in my inbound folder. Is there a column in emails who needs to be filled?

assigned_user_id, orphaned, message_id, type, flagged, reply_to_status, parent_type, uid, category_id and date_sent_received are empty colums.

I don’t know, we don’t have this level of technical documentation… what I would do, if I was in your place, is to do stuff normally from the UI, and compare what you find in the database with the other lines generated from your import. You should be able to spot the difference between the ones that don’t work, and the ones that do.

You might need to look also into related tables.

Of course, there are other alternatives: using a higher-level function to import (Beans), or debugging through the code reading the emails, etc.