Investment Vehicle Tax and Address Update Template
To update the Tax ID and Address for your Investment Vehicles, you will need to complete the Investment Vehicle Tax and Address Update Template and use the Update function in dataloader. The template and the mapping file are attached below.
Pulling the Investment Vehicle ID From FundPanel
Before using the dataloader, you need to pull the account IDs for the investment vehicles. To pull the Investment Vehicle Account ID:
Go to the Reports tab > FundPanel Reports and search for the Investment Vehicle Details report.
Click into the report and click customize.
Search for Account ID in the quick find field on the left.
Click on Account ID, and drag and drop the account ID field into the report.
Click Run Report.
Completing the Template
Now you can use the exported FundPanel Report to populate the Investment Vehicle Name and Investment Vehicle ID columns. Export the FundPanel report into excel. If you have not yet completed your template, you can delete columns A and B and paste these into columns C and D and complete your template with the Tax ID and Address info (keep the headers from Columns C and D). If you have already completed your template with the Investment Vehicle Tax ID and Addresses, you can use a vlookup to pull in the corresponding Account ID. The External ID Column (Column E)- will be the Investment Vehicle Name (the same data that is in Column C).
Complete the Investment Vehicle Tax and Address Update Template attached below.
Copy the Investment Vehicle Name into Column A and Account ID into Column B from the FundPanel report to the template.
We will pull in the Investment Vehicle's Record ID from column B to column D based on the Investment Vehicle name in column A and C (via formula field) and utilize this column D for the Data Update.
Copy the formula below and paste into Column D (ID).
=IF(ISNA(Match(C2,$A$2:$A$160,0)),"",Vlookup(C2,$A$2:$B$160,2,false))
Please note: the number '160' that was utilized twice in this formula represents the last row of the Investment Vehicle Name and ID columns that were pasted onto this Wire Instructions Upload template from the FundPanel Report export (Column A & B). Ex: if column A only has 40 records then we would replace the number 160 with the number 40.
Prepping the CSV File
Before saving the CSV file for loading, make sure to format all cells with numbers to be text or add an apostrophe in front of any cells with leading zeroes. Ex: ('00063989). Without reformatting to text or adding the apostrophe, the leading zeroes will not be retained when saved to a CSV file. Be sure that you have copied the Investment Vehicle names into the External ID column.
Save the Excel file into CSV format.
Now that the template is ready to be loaded:
Open up the DataLoader.
Click Update.
Under Select Salesforce object, select Account.
Click Browse and select the Investment Vehicle Tax and Address Update file.
Click Next.
In the Mapping step, click Choose an Existing Map.
Select the field mapping 'Investment Vehicle Tax ID and Address Update' file provided below.
If there are any additional fields that need mapping, feel free to drag the fields from the top to match the fields down below using the Create or Edit a Map button.
Click Next.
Click Finish.
You can view the success file to see all the Tax and Address updates to your Investment Vehicles!