Filling Out Each Data File (RIA)
- Mercedes Rothwell
- Inese Auzina
- Reviewer
- Gints Vaskums
Warning: You must upload files in the following order:
REP
ACL
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.
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.
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:
Rep Number (A)
Rep First Name (C)
Rep Last Name (D)
BD Number (F)
Phone Number (H)
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 |
|---|---|---|---|
A* | Rep_number | 30 |
|
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 |
|
|
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) |
|
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 |
|
Z | RESERVED4 (do not fill) | 0 | Leave blank. |
AA | split_Rep (T/F) |
| Is the Rep Code shared with one or more advisors for commission purposes? |
AB | Rep_dob |
| The Date of Birth of the Registered Advisor |
AC | pershing_Rep_number |
| Pershing IP Number Note: used only for Pershing NAO API |
AD | adv_order |
| It sets the Advisor as Advisor 1, Advisor 2, Advisor 3 for split Rep codes. |
AE | create_adv_folders |
| 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.
Defines Rep
Creates and associates Assistant
Updates Assistant’s name and email
When adding row number two, the system uses the following logic to associate the Rep and assistant records.
Processor SA (assistant) must match on First and Last Name, SSN, or Email, AND Primary Flag or Split Rep =
YThe owned_Rep column (Col J) must contain the advisor’s Rep code.
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 |
| Firm number. You should enter
|
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 |
|
F | user_status | |
This will drive ongoing access – a required field for gaining or terminating access. |
G | record_type | 2 (1) |
This field determines which Advisor Records a user has access to. |
H | acl | 30 | Enter the corresponding Rep code from the |
I | primary_flag | | Sets the Rep code as the Advisor’s Primary Rep Code. |
J | assoc_user |
|
|
K | user_role | 50 | Docupace User Data Role Field Users:
Back Office Users:
|
L | bill_status |
| Always set this field to |
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 | |
|
P | trusted_sso | 50 | Examples:
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) |
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) |
S | user_type | 30 |
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 |
| Clears out the alias in the User Access domain:
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 |
|
F | ssn | 9 (9) | Format the ssn without dashes.
|
G | business_type | 30 | The available values can vary based on your site setup. A typical site includes |
H | client_status | 30 |
|
I | Rep# | 30 per value | Rep code. You can find this in Column G of the |
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 |
|
|
S | close_date |
|
|
-T | external_id | 50 | Subfolder External ID. 3rd party integrations often use this field. |
U | RESERVED | 0 | Leave blank |
V | account_status | 30 |
|
W | middle_name | 100 | Client’s middle name |
X | suffix | 30 | Client’s suffix |
Y | gender | 30 |
|
Z | dob |
|
|
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 | 100 | - | |
AK | home_phone | 15 (10) |
|
AL | mobile_phone | 15 (10) |
|
AM | work_phone | 15 (10) |
|
AN | empl_company_name | 100 | - |
AO | empl_company_addr | 254 | - |
AP | drivers_license# | 100 | - |
AQ | dl_issue_date |
|
|
-AR | dl_exp_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 | - | - |