18 August 2010 7 Comments

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).

/* This is just the bare shell of the
contact / prospect - more detailed shit comes later */

USE [emdb]
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.

--remember when I pulled out the IDENTITY
--of the initial insert earlier? Yeeeesss.
INSERT INTO emdbuser.Opportunity
(ContactId, LoanAmount, CreditRating,
PropertyAddress, PropertyCity, PropertyState, PropertyZip)
(@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.

--again original IDENTITY required
INSERT INTO emdbuser.borrowerhistory
(contactid, timeofhistory, EventType, ContactSource)
(@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.

Questions, Problems, Fixes? Let me know.

  • http://none Guy

    Hello Ryan,

    This is the first tutorial on Encompass360 I’ve ever seen on the internet (in regards to programing). Im learning C-Sharp and I want to build applications that work with encompass360.

    *Can you tell me how to start? Or even better where I can find more encompass360 tutorials (with c#).

    I dont want you to do any work for me, I just want help. I’d be willing to pay for your time. Thanks

    • Ry

      Hey Guy, its funny you say that – because I found literally NOTHING even semi-useful online regarding E360 development, not even easy stuff. Their message boards are nearly useless and even the Encompass360 SDK docs are terrible. After fighting with a few apps in a few different languages using the SDK I wasn’t able to do exactly what I wanted and started experimenting with directly manipulating the database, thankfully they have some built-in stored procedures for doing just that. Add in some extra digging and I was able to do everything I needed (and then some)

      What are you trying to do (within Encompass) with your app? Maybe I can point you in the right direction.

  • http://aol.com Vera Mcintrye

    I’m impressed, I have to admit. Really rarely does someone encounter a blog that’s both educative and entertaining, and let me tell you, you’ve got hit the nail on your head. Your idea is outstanding; the catch is some thing which inadequate numbers rrndividuals are speaking intelligently about. I am just very happy which i found this within my seek something on this.

  • Taylor

    We use MortgageExchange to get mortgage data and contacts synchronizing between Encompass and our other systems. This worked for the cloud version of Microsoft’s CRM too. It automatically syncs all the mortgage data. Check it out at http://www.myabt.com/mortgage-exchange

  • cgashlin

    Hey Ryan,
    I am currently working with Encompass 360 the current version that we self host and looking to build into it. We are looking to have me code some c# with the SQL to have the clients input everything into a form and scan their documents in and it automatically create a loan. I did not find this stored procedure that you shown above but was really seeing if you or knew anyone that had some type of DB map.

    • cgashlin

      Looks like there is a GUI field that is required int he DB for borrower. Perhaps if you had a copy of the stored procedure that generates this I could utilize it to fullfill my companies needs.

      • jimv1983

        What field and what table?