Mortgage Software Hack: Importing Contacts automatically into Encompass WITHOUT the SDK (just with SQL)
Anyone who has ever dealt with the mortgage software package "Encompass" from Ellie Mae - from any kind of support / IT standpoint knows the pain. It just... well, doesn't behave as you would assume. Seemingly simple things (from an integration / workflow standpoint) just don't play nice, help in the web forums is laughable, its slow, ugly, and so on. Yet, Encompass (360 even) is still an indispensable tool for many mortgage shops.
From an outside integration standpoint I had 2 initial hurdles - importing fresh data and decent reporting. I'll tackle the first one today; In terms of getting new contacts and prospects imported into the system - there has to be built-in ways to do that, right? Sure, but there are (at the very least) 3 serious problems with them "out-of-the-box":
- Loan Officers don't want to type in prospect data every time
- The Outlook "address book sync" thing is a total joke
- and Importing them manually via CSV files?
Yeah, right. Fuck all that.
If all of your Loan Officers got their perfect contacts / prospects over the phone and typed them in by hand into your beautiful Encompass Software - AND they always did it properly and consistently (chuckle) - well than that's fine. However, we all know that isn't how things work. Let's say you get 90% of your incoming apps via your webpage... now unless you have the SDK and build some custom apps in-house to take care of these applications (or buy some RIDICLOUSLY expensive 3rd party ones to do it) its going to be quite an effort - most likely of manual (and seemingly endless) importing...
Thankfully, there IS another easier and quicker way. Encompass is built on a SQL Server database backend. We can safely insert all new apps DIRECTLY into the Encompass backend Database as contacts.
This is what *I* do - your mileage may vary - it works with the latest version of Encompass (as of this writing - Aug 2010). ALWAYS TAKE A SQL SERVER backup before fucking around with the database (READ: I am not responsible for your hosing your installation), however, I've never had a problem doing this - even DELETING contact records didn't cause any damage.
Disclaimer: This maybe look like a "dirty" solution, but you're only seeing a fraction of the whole "integrated solution" - this is just a teaser to solve a simple problem that I faced many months ago and found very little help on the web for.
Step 1 - The INITIAL insert into the Borrower table via a built-in Encompass stored procedure.
NOTE: For this example, I'm just using static data for the Application info - this SQL would have to be dynamically generated with each new app obviously (from PHP, Python, ASP, .NET, etc. etc. etc). Also, note that not ALL of these fields are required (obviously), but some WILL be - give it a few tests first. You might have to put in dummy NULLs or empty strings for fields that Encompass requires, but your web app does not (as I did).
contact / prospect - more detailed shit comes later */
USE [emdb]
DECLARE @NewContactId INT
EXEC [emdbuser].[InsertBorrower]
@FirstName = 'First',
@LastName = 'LastName',
@HomeAddress1 = '123 Encompass Way',
@HomeAddress2 = NULL,
@HomeCity = 'Mapleopolis',
@HomeState = 'IN',
@HomeZip = '34256',
@BizAddress1 = NULL,
@BizAddress2 = NULL,
@BizCity = NULL,
@BizState = NULL,
@BizZip = NULL,
@BizWebUrl = NULL,
@EmployerName = NULL,
@JobTitle = NULL,
@WorkPhone = '5555555555',
@HomePhone = '5555555555',
@MobilePhone = '5555555555',
@FaxNumber = NULL,
@PersonalEmail = 'testytesterson@gmail.com',
@BizEmail = NULL,
@Birthdate = NULL,
@Married = 0,
@SpouseContactID = NULL,
@SpouseName = NULL,
@Anniversary = NULL,
@CustField1 = 'Custom Field 1', --maybe originating state
@CustField2 = 'Custom Field 2', --maybe web id number?
@CustField3 = 'Custom Field 3', --etc
@CustField4 = 'Custom Field 4',
@PrimaryEmail = 'testytesterson@gmail.com',
@PrimaryPhone = '5555555555',
@NoSpam = 0
SET @NewContactId = @@IDENTITY
/* saving @NewContactId identity number somewhere
so I can reference it in the next step... */
Step 2 - Now that we've started the contact process, we've got a unique contact ID (saved in a whole SQL script as I did, or saved in an external application to be re-used througout the transaction) and its time to put a bit more info in. The 'Opportunity' table has important things like Loan Amount Requested, Credit Rating, etc. Pretty important stuff.
--of the initial insert earlier? Yeeeesss.
INSERT INTO emdbuser.Opportunity
(ContactId, LoanAmount, CreditRating,
PropertyAddress, PropertyCity, PropertyState, PropertyZip)
VALUES
(@NewContactId, NULL, 'CreditProfile',
'PropertyAddress1', 'PropertyCity', 'PropertyState', 'PropertyZip')
Step 3 - (optional) This next one I do just for audit-trail. It basically adds a contact "history" record telling the LOs where the record came from - this is especially important if you've got apps coming from many sources or multiple websites.
INSERT INTO emdbuser.borrowerhistory
(contactid, timeofhistory, EventType, ContactSource)
VALUES
(@NewContactId, getdate(), 'First Contact', 'From Web App #1')
Incorporating this type of automation in your mortgage workflow could save you TONS of time and help increase conversions and reduce wasted administrative time. Obviously this is just the SQL Server side of the import - it would FIRST have to come from its original source and THEN be inserted... that small journey is up to your IT staff, a data consultant, or web team - but for the value it delivers... its well worth it.
