Space TimelineSpace Timeline

Filling Out Each Data File (RIA)

Owned by Mercedes Rothwell
Last updated: Oct 16, 2023 by Inese Auzina
8 min read

Date

Change

Date

Change

October 2022

Inital Version

Warning: You must upload files in the following order:

  1. REP

  2. ACL

  3. CLT

Sample Files

These sample files contain test data. Please remove that data before importing the files into Docupace.

Product: RIA Productivity Suite

 

Audience: Back Office Users

Filter by label

There are no items with the selected labels at this time.

Rep File (.REP)

The .REP file contains all Rep codes for an advisor. The .ACL file references the Rep number to determine team members and assistants who should have access to work items created by a particular Rep. The information from this data file will populate three different domain tables in Docupace.

Advisor Table

The majority of the information resides here. 

The Advisor Table record example.

Rep Table

The Rep domain lists the Rep codes assigned users. The system can associate an advisor to more than one Rep code. The system can also associate multiple advisors with the same Rep code.

The Rep Code Table example

Email Recipient

The system will create an email recipient record for each Advisor. It will also link the Advisor's email with all associated Rep codes.

Required Fields/Columns:

  1. Rep Number (A)

  2. Rep First Name (C)

  3. Rep Last Name (D)

  4. BD Number (F)

  5. Phone Number (H)

  6. Email Address (I)

Column Definitions

Review the table below for help completing each column in the .Rep File. Asterisks (*) identify which columns must contain information.

Column 

Field/Header

Max (min) Char

Details

Column 

Field/Header

Max (min) Char

Details

A*

Rep_number

30

  • Unique Financial Advisor Number

  • Used for hierarchical access from the User File (.ACL)

B

clearing1 (nfs)

30

NFS Rep Code

C*

Rep_first_name

100

Advisor's First Name

D*

Rep_last_name

100

Advisor's Last Name

E

Rep_full_name

30

Advisor's full name

F*

Rep_bd_number

7

  • Set this field to 7 for each row

G

RESERVED1 (do not fill)

0

Leave blank.

H*

Rep_business_phone

15 (10)

Advisor’s Business phone number

I*

Rep_email_address

100

Advisor’s email address

J

Rep_fax_number 

15 (10)

Advisor’s Business fax number

K

RESERVED2 (do not fill)

0

Leave blank.

L

Rep_field_osj_first_name

30

OSJ First Name

M

Rep_field_osj_last_name

30

OSJ Last Name

N

RESERVED3 (do not fill)

0

Leave blank.

O

Rep_osj_office_branch_number

30

Rep’s OSJ Office Branch Number

P

Rep_ssn

9 (9)

  • Advisor’s SSN xxxxxxxxx

  • without dashes. Only numbers.

Q

region_code

30

Region Code

R

osj_code

30

OSJ Code

S

sales_office_code

30

Sales Office Code

T

address_line1

254

Advisor Address Line 1

U

address_line2

254

Advisor Address Line 2 

V

city

30

Advisor City

W

state

2 (2)

Advisor State

X

zip_code

10 (5)

Advisor Zip Code

Y

clearing2 (pershing)

30

  • Clearinghouse Rep Number (If applicable)

  • Not required.

  • often the same as the Rep Number

  • Not required

  • Labeled “Pershing” by default.

Z

RESERVED4 (do not fill)

0

Leave blank.

AA

split_Rep (T/F)

Y or N

Is the Rep Code shared with one or more advisors for commission purposes?

AB

Rep_dob

MM/DD/YYYY

The Date of Birth of the Registered Advisor

AC

pershing_Rep_number

3

Pershing IP Number

Note: used only for Pershing NAO API

AD

adv_order

1 2 or 3

It sets the Advisor as Advisor 1, Advisor 2, Advisor 3 for split Rep codes.

AE

create_adv_folders

Y

Determines if the system should create Advisor Office folders for this Rep code

User Access File (.ACL)

The .ACL file provides the mechanism to add/disable users into the system. The information in this file updates the User Access domain and the user field in the Rep domain. It also updates the corresponding settings in Security Realm. This allows you to create, update, and/or terminate users in bulk.

Associating Assistants with Advisors

Each association requires three rows. It's important that the rows get loaded in this order. The third row isn't required for the association, but you need to include it if you want to correct the name/email of the assistant after making the association.

  1. Defines Rep

  2. Creates and associates Assistant

  3. Updates Assistant’s name and email

When adding row number two, the system uses the following logic to associate the Rep and assistant records.

  1. Processor SA (assistant) must match on First and Last Name, SSN, or Email, AND Primary Flag or Split Rep = Y

  2. The owned_Rep column (Col J) must contain the advisor’s Rep code.

  3. The allowed_proc column (Col R) must contain the advisor’s user name.

What Does Each Column Mean?

The information below provides the fields/columns included in the .ACL File.

Column 

Field/Header

Max (min) Characters

Details

A

bd

7

Firm number. You should enter 7 for each row.

 

B

user_name

125 (1)

Enter the user’s email address

C

user_first_name

50

User’s First Name

D

user_last_name

50

User’s Last Name

E

user_email

50

xxxx@xxxx.com

F

user_status

 A or T

  • Active=A

  • Inactive=T

This will drive ongoing access – a required field for gaining or terminating access.

G

record_type

2 (1)

  • B – Back Office

  • RC – Rep code

This field determines which Advisor Records a user has access to.

H

acl

30

Enter the corresponding Rep code from the .REP file.

I

primary_flag

 Y or N

Sets the Rep code as the Advisor’s Primary Rep Code.

J

assoc_user

 

 

K

user_role

50

Docupace User Data Role

Field Users:

  • Processor FP

  • Processor SA

Back Office Users:

  • RIA BO BILLING

  • RIA BO COMPLIANCE

  • RIA BO MAINTENANCE

  • RIA BO PROCESSOR

  • RIA BO QA

L

bill_status

Y

Always set this field to Y.

M

entity

100

Enter the firm name. You can find this in the Firm domain. Most sites associate users to a single entity.

N

RESERVED

0

Leave blank

O

auto_assign_creator

 Y or N

  • Y for Field Users.

  • N for Back Office users

P

trusted_sso

50

Examples:

  • Salesforce

  • Redtail

  • Wealthbox

Enter the CRM your firm uses in addition to Connect.

Q

grant_access

125 per value

Enter one or more other user IDs. IDs you enter into this field will have access to the user’s unindexed documents.

(list separated by comma or semi-colon with no spaces)  dbuckley,jfriedman,

R

allowed_proc

125 per value

Enter one or more other user IDs to allow those individuals to process work items on behalf of the user.

(list separated by comma or semi-colon with no spaces)  dbuckley,jfriedman,

S

user_type

30

  • Advisor

  • Back Office User

Provides details for billing Reports. 

T

billable_user

125

Associates the user with a billable user ID. If an admin supports an Advisor, the Advisor's user ID would show here.

U

alias_user_name_clear

T or F

Clears out the alias in the User Access domain:

  • T clears out the current data in this field

  • F will leave the field as is

This column clears the alias_user_name field so that you can re-populate the alias field with the correct alias ID with one file load. 

V

alias_user_name

125

Populates the Alias field in the User Access domain. Typically, CRMs such as Redtail or Salesforce use columns U and V. Alternatively, you can use them as part of SSO.

Client File (.CLT)

You will only need to import the .CLT file during your initial site setup. It imports your client data into the Docupace system. Each row corresponds with an Account Folder or Subfolder for a client. If you plan to only add the Client information (and not account information), you only need to add the client-level detail.

 

Generating a Subfolder

Each filled row in your spreadsheet will create a Client Subfolder.

  • To create a specific subfolder, you must enter a value in the Subfolder Type column.

  • To create multiple subfolders for the same client, create duplicate rows and edit the data in the Subfolder Type column.

  • If you do not populate the Subfolder Type column, the system will create a general folder under the Client folder.

Required Fields for Client File

  • SSN/TIN (F)

  • Rep ID (I)

  • Client Type

    • If person: Last_name (A) and first_name (B)

    • If entity: entity_name (D)

    • Business Type (consult with your Docupace team about this value)

    • Status

What Does Each Column Mean?

The following table describes the information you need for each column.

Column 

Field/Header

Max length (Min length)

Field Details

A

last_name

100

-

B

first_name

100

-

C

group_name

100

Group can Represent a Household unit.

D

entity_name

100

Used for Company or a Trust

E

client_type

30

  • Person

  • Trust

  • Business/Entity

  • Vendor

  • Beneficiary

  • Other

  • Prospect

F

ssn

9 (9)

Format the ssn without dashes.

123456789

G

business_type

30

The available values can vary based on your site setup. A typical site includes BD/RIA and Non-BD/RIA. Many sites use the BD’s name in place of BD/RIA.

H

client_status

30

Active or Inactive

I

Rep#

30 per value

Rep code. You can find this in Column G of the .REP file. You can also find this in the Rep record if the Rep already exists.

J

account#

30 per value

The account number

K

Subfolder_type

30

The system requires this column for each subfolder/account you want to create. The available values can vary based on your site setup.

L

registration

50

The available values can vary based on your site setup.

M

sponsor

254

The available values can vary based on your site setup.

N

product_type

50

The available values can vary based on your site setup.

O

other_desc

254

-

P

state

2

2 digit state code

Q

short_name

100

Enter the short name of the account

R

open_date

yyyy-mm-dd or
mm/dd/yyyy

MM/DD/YYYY format

S

close_date

yyyy-mm-dd or
mm/dd/yyyy

MM/DD/YYYY format

-T

external_id

50

Subfolder External ID. 3rd party integrations often use this field.

U

RESERVED

0

Leave blank

V

account_status

30

Active, Inactive

W

middle_name

100

Client’s middle name

X

suffix

30

Client’s suffix

Y

gender

30

Male, Female

Z

dob

yyyy-mm-dd or
mm/dd/yyyy

MM/DD/YYYY format

AA

citizenship

30 per value

-

AB

mailing_addr

254

-

AC

mailing_city

30

-

AD

mailing_state

2

-

AE

mailing_zip

10 (5)

-

AF

legal_addr

254

-

AG

legal_city

30

-

AH

legal_state

2

-

AI

legal_zip

10 (5)

-

AJ

email

100

-

AK

home_phone

15 (10)

999-999-9999 format

AL

mobile_phone

15 (10)

999-999-9999 format

AM

work_phone

15 (10)

999-999-9999 format

AN

empl_company_name

100

-

AO

empl_company_addr

254

-

AP

drivers_license#

100

-

AQ

dl_issue_date

yyyy-mm-dd or
mm/dd/yyyy

MM/DD/YYYY format

-AR

dl_exp_date

yyyy-mm-dd or
mm/dd/yyyy

  • MM/DD/YYYY format

  • Driver’s License expiration date

AS

RESERVED2

 

Leave blank

AT

bank_name

100

-

AU

bank_city

30

-

AV

bank_state

2

-

AW

bank_routing#

Any number value 

-

AX

bank_account#

30

-

AY

client_marital_status

30

-

AZ

client_occupation

100

-

BA

client_id

50

For 3rd party integrations

BB

RESERVED3

0

Leave blank

BC

RESERVED4

0

Leave blank

BD

RESERVED5

0

Leave blank

BE

subregistration

50

Account Sub-registration type

BF

 crm_source      

-

-

BG

 crm_external_id

-

-

 

© Docupace Technologies 2020 | Documentation Dashboard