Bulk Import Interface

Connecting

SFTP access must be set up for a lender to use this interface.

RLS will provide the host (domain) and login credentials to use.

How It Works

A scheduled job runs each hour looking for new files to process in a pickup directory. The status of the imports (how many succeeded, per-record failure messages) are written to an results directory. Files finished processing will be moved to a processed directory. The specifics are further explained below:

Home Directory Structure

Path: {environment}/import/{locations}

Example: sandbox/import/pickup

Environments

• production - Maps to production environment.

• sandbox - Maps to training environment.

Locations

• lookups - These are lookup files that contain available divisions, positions, and other lender-specific information. You may need to read these files to generate correct data for pickup.

• pickup - Write files to this location for them to be processed.

• processed - The are previously processed files from the pickup directory.

• results - These are outputs from the files processed in the pickup directory.

Explanations

Divisions

If you are a lender with a single division, you can ignore division-related fields. Those fields are only for lenders with multiple divisions.

File Formats

• CSV - Files in the CSV format are UTF-8, comma-delimited, and should follow RFC 4180 rules.

• JSON - Files in the JSON format are UTF-8, and should follow ECMA-404 rules. Comments, single quotes, undefined, JSON Schema and other extensions are not permitted.

Examples / Testing the Process

The example provided inputs should not be used as-is to test the import process. First, customize them with your own values, especially the email addresses (see Email Values for more of an explanation).

Data and Field Length Restrictions

1. Please only provide RLS with new or changed records in the provided files. Sending over the entire dataset each time, especially if it is large, may result in slow import times and degraded performance. RLS may be required to delay or temporarily disable import operations if they are adversely affecting the system. Note: It is fine to provide a large data set for the initial / first import, so all the existing data is loaded into the system.

2. Please trim extra whitespace before sending fields. If a field is String(10) and the value is "IniTech", supply that instead of "IniTech ".

3. Sending data that is too long may result in truncation or failure to import record(s). The exact behavior of overly long data per field is undefined (do it at your own risk, behavior may change in future).

Modes

Each record has a mode property that indicates what operation should be performed:

• "create" - Intending to make a new record. ThreadId should generally not be supplied for new records, as it is system generated on the RLS end. If a record already exists, it will typically be updated as a fallback. With that said, avoid using this mode unless you intent to actually create data in the RLS system.

• "update" - Intending to update a record. If the record is not found, the update operation will fail.

• "upsert" - Updates record if it exists; Otherwise, a new record is created. If you do not want to track what has already been sent to RLS, use this option when it is available.

• "add" - Intending to add a relationship between different records. If the relationship already exists, it may have metadata/properties updated.

• "delete" - Intending to remove a relationship. If it is not found, this typically will result in nothing happening (no error).

ThreadId Values

Unless you are using the API, you will not know what the RLS thread identifiers for records are. You should use the ExternalId/IdNumber/LoanNumber fields instead.

The exception to this is divisions, which have a lookup file to pull from.

ExternalId Values

These must be unique across broker accounts, borrowers, and employees. An employee and borrower / broker account cannot have the same External Id. If you need to, you can place a prefix before the value, if you would otherwise have conflicts from the source system sending the data.

Email Values

Per the current system design, all account-type records must have system-wide (RLS) unique email addresses. This affects Create and Update operations against Brokers, Borrowers, and Employees. Only borrowers are permitted to have a duplicate email addresses on Create operations (and that's strictly for borrowers, so if a broker is already using the email, the borrower operation will still fail).

A future system change will add more flexibility with how email addresses work, but this restriction is not removable for now.

Differences with the API

1. No access token is necessary, as the SFTP login credentials are used instead.

2. Inquiry mechanisms (get, search, etc.) do not currently exist. This interface exists for writing bulk data to RLS.

3. There is no support currently for uploading documents to loans, or to bulk rename existing uploads.

Lookup Files

Lookup files are updated at the same frequency that pickup files are processed.

These are provided as both CSV (.csv) and JSON (.json), unless otherwise stated.

• divisions - The list of available divisions for the lender. Data may change due to adjustments in Lender Management.

• positions - The list of available positions for the lender. Data may change due to adjustments in Lender Management.

• loan_occupancies - The list of universal (RLS) loan occupancies. Data changes very rarely.

• loan_purposes - The list of universal (RLS) loan purposes. Data changes very rarely.

• loan_statuses - The list of universal (RLS) loan statuses. Data changes very rarely.

• loan_types - The list of universal (RLS) loan types. Data changes rarely, when new types are added.

Lookup: Divisions

Reminder: Changes to division editable names will not be immediately reflected in this file.

Lookup: Positions

This list will only contain positions that are enabled in lender management.

Lookup: Loan Purposes

Lookup: Loan Occupancies

Lookup: Loan Statuses

Lookup: Loan Types

Pickup Files

All files start with a prefix indicating what type of data they contain. They are followed by a timestamp and file extension (e.g. borrowers.json).

These files are all JSON formatted, and contain arrays of records (enclosed in []). Example:

[
    { "Id": 1, "Name": "Example A" },
    { "Id": 2, "Name": "Example B" }
]

Filenames

• borrowers.json - Contains new or updated borrowers.

• brokers.json - Contains new or updated brokers.

• broker_accounts.json - Contains new or updated broker accounts.

• employees.json - Contains new or updated lender employees.

• loans.json - Contains new or updated loans.

• loan_positions.json - Contains new, updated, or removed loan positions. This is what ties an employee and position to a loan.

• loan_borrowers.json - Contains new, updated, or removed loan borrowers. This is what ties one or more borrowers to a loan.

• loan_brokers.json - Contains new, updated, or removed loan brokers. This is what ties a broker to a loan.

Order of Processing

Files are not processed in alphabetical order, but by order of dependency:

1. employees.json

2. borrowers.json

3. brokers.json

4. broker_accounts.json

5. loans.json

6. loan_positions.json

7. loan_borrowers.json

8. loan_brokers.json

Fields

These are the fields for the files mentioned above. Fields with special behavior are denoted below the tables. Some fields have a maximum length, which is included after the data type. Recommended fields are not strictly required, but should be provided when possible.

Null optional fields do not have to be supplied in the JSON, but can be.

Treat field names as case-sensitive.

Fields: Borrowers

For update operations, you can exclude fields that are not being updated.

Special Borrower Requirements

• Id - One of these must be present. If ThreadId is present, it take precedence over ExternalId.

Example: Borrower Record

[
  {
    "Mode": "create",
    "ExternalId": "Borrower20231219A",
    "FirstName": "Example",
    "MiddleInit": "",
    "LastName": "Borrower",
    "NameSuffix": "Jr.",
    "Email": "Sample20231219a@borrower.test",
    "Address1": "123 Elm",
    "Address2": "Suite C",
    "City": "Dodge City",
    "StateCode": "KS",
    "ZipCode": "67801",
    "PhonePrimary": "5555555123",
    "PhoneSecondary": null
  }
]

Fields: Brokers

For update operations, you can exclude fields that are not being updated.

Creating a new Broker automatically creates a Broker Account and associates it with the Broker as it's owner.

Special Broker Account Requirements

• Id - One of these must be present. If ThreadId is present, it take precedence over IdNumber.

• Division - Not required for creation. If not given, the default lender division will be used. Do not pass as an empty array, use null instead!

Sub-Field Type: BrokerAccountPositionPair

Example: Broker Record

[
  {
    "Mode": "create",
    "IdNumber": "23121901",
    "BrokerDivisionThreadIds": ["0123456789abcdef0123456789abcdeff","ffedcba9876543210fedcba9876543210"],
    "Email": "Example20231219A@broker.test",
    "NMLSNumber": "20231219",
    "CompanyName": "Acme Broker",
    "OwnerName": "Thomas Fakeman",
    "PhoneNumber": "5555554545",
    "WebsiteUrl": "https://www.acme-broker.test",
    "Address1": "234 Cedar",
    "Address2": "",
    "City": "Olathe",
    "StateCode": "KS",
    "ZipCode": "66061",
    "Contacts": [
      {
        "Mode": "add",
        "Account_ExternalId": "LoanOfficerBrokerAcct20231219A",
        "PositionName": "loan officer"
      }
    ]
  }
]

Fields: Broker Accounts

For update operations, you can exclude fields that are not being updated.

Special Broker Account Requirements

• Id - One of these must be present. If ThreadId is present, it take precedence over ExternalId.

Example: Broker Account Record

[
  {
    "Mode": "update",
    "ExternalId": "BrokerAcct20231219A",
    "FirstName": "Sally",
    "MiddleInit": "S",
    "LastName": "Brokerson",
    "NameSuffix": null,
    "Email": "Sample20231219a@broker.test",
    "Address1": "5544 E Market Ln",
    "Address2": "Suite C",
    "City": "Ponca City",
    "StateCode": "OK",
    "ZipCode": "74601",
    "PhonePrimary": "5555555009",
    "PhoneSecondary": "5555555008"
  }
]

Fields: Employees

For update operations, you can exclude fields that are not being updated.

Special Employee Requirements

• Id - One of these must be present. If ThreadId is present, it take precedence over ExternalId.

• Division - Not required for creation. If not given, the default lender division will be used. Do not pass as an empty array, use null instead!

Example: Employee Record

[
  {
    "Mode": "create",
    "DivisionThreadIds": ["11223344556677889900aabbccddeeff"],
    "ExternalId": "Employee20231219A",
    "FirstName": "Some",
    "LastName": "Employee",
    "Email": "20231219a@employee.test",
    "Address1": "456 E Vine St",
    "City": "Gering",
    "StateCode": "NE",
    "ZipCode": "69341",
    "PhonePrimary": "5555555544"
  }
]

Fields: Loans

Special Loan Requirements

• Loan - For new loans, only LoanNumber should be given. For updates, ThreadId takes precedence over LoanNumber if it is present (that can be used to change the loan number).

• Division - For new loans, if not given, the default division for the lender is assigned to the loan. If supplied, must point to a valid division for the lender. For lenders with a single division, this can always be null. Do not pass an empty string!

Example: Loan Record

    [
    {
    "Mode": "create",
    "LoanNumber": "LOAN20231219A",
    "DivisionThreadId": "a0b1c2d3e4f5061728394a5b6c7d8e9f",
    "Address1": "8876 SW Wood Ct",
    "City": "Dickinson",
    "StateCode": "ND",
    "ZipCode": "58601",
    "LoanTypeId": 1,
    "AgencyId": 1,
    "ClosingDate": "2023-12-30T00:00:00",
    "CompletionDate": null,
    "FHACaseNumber": "FHA20231219A",
    "BuildTerm": "0",
    "BranchNumber": "0",
    "ExternalLoanOfficer": "0",
    "FundedDate": "2023-12-20T00:00:00",
    "ClosedOutWithInvestor": null,
    "FinalTitleUpdateDate": null,
    "TotalLoanAmount": 0,
    "LoanPurposeId": 1,
    "LoanOccupancyId": 1,
    "StructureTypeId": 1,
    "LoanStatusId": 0,
    "WorkOrderDate": null,
    "SORDate": null,
    "EstCompletionDate": null
    }
    ]

Fields: Loan Positions

Special Loan Positions Requirements

• Loan - One, and only one, of these must be present.

• Account - One, and only one, of these must be present.

Example: Loan Position Record

[
  {
    "Mode": "add",
    "Loan_Number": "LOAN20231219A",
    "Account_ExternalId": "Employee20231219A",
    "PositionName": "AEA"
  }
]

Fields: Loan Borrowers

Special Loan Borrower Requirements

• Loan - One, and only one, of these must be present.

• Account - One, and only one, of these must be present.

Example: Loan Borrower Record

[
  {
    "Mode": "add",
    "Loan_Number": "LOAN20231219A",
    "Account_ExternalId": "Borrower20231219A"
  }
]

Fields: Loan Brokers

Special Loan Broker Requirements

• Loan - One, and only one, of these must be present.

• Broker - One, and only one, of these must be present.

• LoanOfficer - Not required, but only one of these two strings should be given and not both (LoanOfficer_AccountThreadId or LoanOfficer_AccountExternalId). LoanOfficer_AccountThreadId takes precedence over the other if both are present. Do not supply with "delete" mode.

Sub-Field Type: BrokerAccountPositionPair

Example 1: Loan Broker Record Add

[
  {
    "Mode": "add",
    "Loan_Number": "LOAN20231219A",
    "Broker_IdNumber": "23121901",
    "LoanOfficer_AccountExternalId": "BrokerAcct20231219A",
    "AdditionalContacts": [
      {
        "Mode": "add",
        "Account_ExternalId": "BrokerAcct20231219B",
        "PositionName": "Processor"
      }
    ]
  }
]

Example 2: Loan Broker Record Delete

[
  {
    "Mode": "delete",
    "Loan_Number": "LOAN20231219A",
    "Broker_IdNumber": "23121901"
  }
]

Processed Files

The most recently processed file will follow the convention supplied_name.last.ext.

Earlier / older files will follow supplied_name.timestamp.ext.

The timestamp format is "yyyyMMddhhmmssfff". It consists of a 4-digit year, 2-digit month, 2-digit day, 2-digit "military" hour (00-23), 2-digit minute, 2-digit second, and 3 digit millisecond. All these numbers are zero-padded.

Example processed filenames for "borrowers.json":

1. "borrowers.last.json" (most recent)

2. "borrowers.20231222070016329.json" (timestamped)

Empty pickup files (0 byte or whitespace only) will be discarded without creating a result file or being copied to the processed directory.

Result Files

The output result file format is uniform, regardless of what type of data was imported.

Sub-Output Type: Problem

Example: Output File Contents

{
  "Id": 123,
  "PickupName": "borrowers.json",
  "ProcessedName": "borrowers.202312201300.json",
  "Source": "SFTP",
  "StartedAt": "2023-12-20T02:30:00.161Z",
  "EndedAt": "2023-12-20T02:30:02.770Z",
  "ErrorMessage": null,
  "NumCreateSuccess": 20,
  "NumUpdateSuccess": 2,
  "NumAddSuccess": 0,
  "NumDeleteSuccess": 0,
  "NumCreateFailure": 0,
  "NumUpdateFailure": 1,
  "NumAddFailure": 0,
  "NumDeleteFailure": 0,
  "NumModeFailure": 0,
  "Problems": [
    {
      "Index": 1,
      "Message": "No borrower for supplied ExternalId exists."
    }
  ]
}

In the example above, the second entry (Index starts at 0) was an attempted update to a non-existent borrower.

Last Updated 2024-05-03, 3:00 PM CDT.