Filling Out Each Data File (RIA)
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
Functionality: Data files allow you to make changes to records in bulk.
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.
Attention: Each line in this file is equivalent to a Rep code. For example, if Rep A has three Rep codes, you must add three separate rows.
The information from the REP data file will populate three different domain tables in Docupace.
Advisor Table
The majority of the information resides here.
Note: There should only be one record per Advisor.
Rep Table
The Rep domain lists the Rep codes assigned to Advisors. An Advisor may have multiple Rep codes associated with them. Each Rep code will appear in the Rep table. The Advisor will then be associated with that Rep. There are situations where multiple Advisors may be linked to the same Rep code, called a Split Rep code.
Email Recipients Table
For each Advisor, an Email Recipient record is created, and the Advisor's email is linked to 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)
What Does Each Column Mean?
To fill out the template correctly, see the description of each column below:
Column | Field/ Header | Max (min) Characters | Details |
---|---|---|---|
A | rep_number | 30 |
|
B | clearing1 (nfs) | 30 |
|
C | rep_first_name | 50 | Advisor's First Name |
D | rep_last_name | 50 | Advisor's Last Name |
E | rep_full_name | 100 | Advisor's full name |
F | rep_bd_number | Any number value |
|
G | RESERVED1 (do not fill) | 0 | Leave blank. Attention: Do NOT remove this column. |
H | rep_business_phone | 15 (10) | Rep’s Business phone number |
I | rep_email_address | 100 | Reps’ email address |
J | rep_fax_number | 15 (10) | Rep’s Business fax number |
K | RESERVED2 (do not fill) | 0 | Leave blank. Attention: Do NOT remove this column. |
L | rep_field_osj_first_name | 30 | OSJ First Name Important: OSJ First Name, OSJ Last Name, and OSJ Office Branch Number are ignored for split Rep codes (if the split_rep column (AA) is set to |
M | rep_field_osj_last_name | 30 | OSJ Last Name Important: OSJ First Name, OSJ Last Name, and OSJ Office Branch Number are ignored for split Rep codes (if the split_rep column (AA) is set to |
N | RESERVED3 (do not fill) | 0 | Leave blank. Attention: Do NOT remove this column. |
O | rep_osj_office_branch_number | 30 | Rep’s OSJ Office Branch Number Important: OSJ First Name, OSJ Last Name, and OSJ Office Branch Number are ignored for split Rep codes (if the split_rep column (AA) is set to |
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 | Rep Address Line 1 |
U | address_line2 | 254 | Rep Address Line 2 |
V | city | 30 | Rep City |
W | state | 2 (2) | Rep State |
X | zip_code | 10 (5) | Rep Zip Code |
Y | clearing2 (pershing) | 30 |
|
Z | RESERVED4 (do not fill) | 0 | Leave blank. Attention: Do NOT remove this column. |
AA | split_rep (T/F) |
| Is the Rep code shared with one or more Advisors for commission purposes? Important: If the split_rep column (AA) is set to |
AB | rep_dob |
| The Date of Birth of the Registered Rep |
AC | pershing_rep_number |
| Populates |
|
|
| attribute 100016 in the Rep domain (Pershing #) |
AD | adv_order |
| Sets whether the Rep is Advisor 1, Advisor 2, or Advisor 3 for a split Rep code |
---|---|---|---|
AE | create_adv_folders |
| Determines whether Advisors Office folders should be created for this Rep code |
User Access File (.ACL)
The ACL file provides the mechanism to add/disable user access to the Docupace site in bulk. In this file, each line item is equivalent to one Docupace user with a specific data role. 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.
Best Practice: We recommend that you ALWAYS use the ACL file when adding or making changes to Docupace users for consistency and ease of user management. There are two exceptions:
You should create Admin users manually.
Manually remove users whose access to the system must be terminated immediately.
Important features of the ACL File:
Controls which documents each user can access (via Client Folder and Subfolder access)
Provides automatic initial user setup
Allows to update the user’s data role if the user’s role has changed.
Associating Assistants with Advisors
Each association requires three rows. The rows must 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 =
Y
The 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 following table describes the columns in an ACL file. Identify what each row should be. It can be a unique user or the same user entered multiple times to add extra data roles or additional user aliases for integrated CRM systems.
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 value determines ongoing system access. To terminate the user's access, set this field to |
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 REP file. Attention: You must enter a value for each row with Tip: If the user has more than one Rep code, you must create a separate row for each code. |
I | primary_flag | | Sets the Rep code as the Advisor’s Primary Rep Code. Attention: You must enter a value for each row with Note: The system automatically marks this as |
J | assoc_user |
|
|
K | user_role | 50 | Docupace User Data Role examples: Field Users:
Back Office Users:
Important Note: Contact Support or your onboarding team for assistance filling out this field. |
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. Important Note: Contact Support or your onboarding team for assistance filling out this field. |
N | RESERVED | 0 | Leave blank Attention: Do NOT remove this column. |
O | auto_assign_creator | | This will determine whether a Work Item created by the user will be automatically assigned to that same user for processing or placed in a Pool of Work Items available to other users.
Important Note: Contact Support or your onboarding team for assistance filling out this field. |
P | trusted_sso | 50 | Other systems, such as CRMs that are integrated with Docupace and are authorized to send data through Single Sing-On (SSO). Examples:
Note: By default, this field is set to Important Note: Contact Support or your onboarding team for assistance filling out this field. Best Practice: If you use several integrations, add multiple lines per user - one for each connected system and its corresponding alias_user_name (V). |
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) Note: If left blank, it will default to the user ID from column B. |
R | allowed_proc | 125 per value | Grants other users access to the Work Items created by this user. Shows the Assign to Me button in the Work Item. Specific user IDs separated by a comma or a semicolon with no spaces:
Note: If left blank, it will default to the user ID from column B. |
S | user_type | 30 |
Provides details for billing Reports. Note: Set all Field users to |
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. Note: Only use this field when setting up Field users. |
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. Attention: You must set this column to |
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. Example: A user launches Docupace from their company intranet. Normally, the user accesses Docupace via SSO. A. If this field does not contain data, the user will have to go through the SSO process again. B: If this field contains the company login, then the user can seamlessly launch into Docupace from the company site. |
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. In this file, each line item is equivalent to an Account (Subfolder) for a Client. If you plan to add only the Client Folder information (and no account information), fill out only the columns with the client-level details.
Note: Client Type dictates whether you use First/Last names or Company Name.
Generating a Subfolder
Each filled row in the spreadsheet will create a new Client Subfolder.
To create a subfolder, 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
Subfolder under the Client Folder.
Adding Multiple Rep Codes to the Same Client
When two or more reps work with the same Client, you will have the same Client Type, same Company, and same TIN with two different Rep codes. Include the Rep code as the unique key field to handle this situation.
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 | Used for a Person |
B | first_name | 100 | Used for a Person |
C | group_name | 100 | A Group can represent a Household unit |
D | entity_name | 100 | Used for a Company or a Trust |
E | client_type | 30 |
Warning: The system requires different fields based on the Client Type field (Column E). Incomplete records will result in file load errors.
|
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 Important: Contact Docupace Support or your onboarding team for assistance filling out this field. |
H | client_status | 30 |
|
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. Important: Add a separate row if multiple Reps are associated with a Client/Account combination. Example: If an account is shared by two unique Rep codes (not a split Rep code):
|
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. Warning: The record will fail to load if you don’t select a value in this column Important: Contact Docupace Support or your onboarding team for assistance filling out this field. |
L | registration | 50 | The available values can vary based on your site setup. Important: Contact Docupace Support or your onboarding team for assistance filling out this field. |
M | sponsor | 254 | The available values can vary based on your site setup. Important: Contact Docupace Support or your onboarding team for assistance filling out this field. |
N | product_type | 50 | The available values can vary based on your site setup. Important: Contact Docupace Support or your onboarding team for assistance filling out this field. |
O | other_desc | 254 | Additional description |
P | state | 2 | A 2-digit state code |
Q | short_name | 100 | A short name for the account |
R | open_date |
| Open Date for the Account |
S | close_date |
| Close Date for the Account |
-T | external_id | 50 | Subfolder External ID. This field is frequently used for 3rd-party integrations. |
U | RESERVED | 0 | Leave blank Attention: Do NOT remove this column. |
V | account_status | 30 |
|
W | middle_name | 100 | Used for a Person |
X | suffix | 30 | Used for a Person |
Y | gender | 30 |
|
Z | dob |
| Date of Birth for a Person |
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 |
| Driver’s License issue date |
-AR | dl_exp_date |
| Driver’s License expiration date |
AS | RESERVED2 |
| Leave blank Attention: Do NOT remove this column. |
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 | Client ID used by 3rd-party integrations |
BB | RESERVED3 | 0 | Leave blank Attention: Do NOT remove this column. |
BC | RESERVED4 | 0 | Leave blank Attention: Do NOT remove this column. |
BD | RESERVED5 | 0 | Leave blank Attention: Do NOT remove this column. |
BE | subregistration | 50 | Account Sub-registration type Attention: Must match with Registration Type |
BF | crm_source | - | Used by 3rd-party integrations |
BG | crm_external_id | - | Used by 3rd-party integrations |