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

  • CREATE
  • UPDATE
  • DELETE
  • SERVICE_CALL

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.


How did we do?


Powered by HelpDocs (opens in a new tab)