LoanPro Database - Retrieve Sent Email Information
General
Note: This article is only applicable to customers who have direct access to a read-only copy of their LoanPro database.
Sometimes it's useful to pull information from the database about email communications that have been sent to your customers. This information can be found in the system_note_entity table.
system_note_entity table structure:
Column | Data Type | Column Info | Value Notes | Other |
id | int(11) | The id of the entity | ||
entity_type | char(50) | Type of the associated entity | ||
entity_id | int(11) | ID of the associated entity | ||
reference_type | char(50) | reference type | ||
reference_id | int(11) | reference id | ||
operation_type | enum | operation type |
| |
operation_sub_type | char(100) | operation sub type | systemNote.operationSubtype.* | |
create_user | int(11) | ID of the user that created the entity | ||
create_user_name | varchar(255) | creating user name | ||
created | timestamp | Timestamp of when the entity was created | ||
lastUpdated | timestamp | Timestamp of when the entity was last updated | ||
remote_addr | char(20) | IP address of the client machine | ||
note_title | varchar(255) | note title | ||
note_data | text | note data | ||
deleted | tinyint(4) | Whether or not the entity was deleted | 1 - yes, 0 - no |
This table holds all the system notes, not just those for email communications. As such, if you want to pull email communications from this table, you will want records with the following properties:
entity_type = Entity.Customer
operation_type = service_call
operation_Sub_Type = systemNote.operationSubtype.email
The database can be queried using SQL. Your query may look something like this:
SELECT * FROM system_note_entity WHERE entity_type="Entity.Customer" AND operation_type="service_call" AND operation_Sub_Type="systemNote.operationSubtype.email";
The table above will shows the fields that this will return.