Provided below is an Excel sheet with the Wire Instructions upload template. Please download and use this excel sheet to fill out the columns appropriately (leave columns A, B and D blank).
Match column C with column E.
Once you have completed filling out the excel sheet with Wire instruction information, we will need to get the data for columns A & B from FundPanel.
Collect Investment Vehicle's Record ID via FundPanel Report
We will create a Salesforce report to collect all of the Investment Vehicle records in FundPanel as well as their record ID. We will then export this report and utilize these two fields for the columns A & B in the Wire Instructions upload template.
- Click Reports.
- Click New Report.
- Type Accounts.
- Click Accounts.
- Click Create.
In the report filter:
- Show: All accounts.
- Date Field: Created Date.
- Range: All Time.
- Filter: Account Record Type equals "Investment Vehicle".
- Add the Account Name in the first column column and the Account ID in the second column.
- Click Run Report.
- Click Export Details.
Match the Investment Vehicle's Record ID in the Wire Instruction Upload Template
- Open up the excel sheet (FundPanel report).
- Copy the Account Name and Account ID columns from the FundPanel report that you have just exported.
- Paste these two columns onto column A and B in the Wire Instructions Upload 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 Upsert.
In column D type out the formula '=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.
Copy the provided formula and paste it down for the rest of the Investment Vehicle rows in Column D.
Retaining Leading Zeroes
Make sure to add an apostrophe in front of any cells with leading zeroes. Ex: ('00063989). Without the apostrophe, the leading zeroes will not be retained when saved to a CSV file.
Save the Excel file into CSV format.
Now that the Upload sheet has been completed we are ready to upload the data into FundPanel.
Formatting Date Time
- Make sure to highlight all the cells within the 'Wire Instructions Last Submitted On' column and right click into Format Cells.
- Click into Custom Format.
- Copy and Paste: MM-DD-YYYYThh:mm:ss.sssZ
DataLoader
- Open up the DataLoader.
- Click Upsert.
- Select Account.
- Click Browse and select the Wire Instruction Upload File.
- Click Next.
- Select Id.
- Click Next. (Do not need to match the other two fields).
- Click Choose an Existing Map.
- Select the field mapping 'Wire Instruction Field Mapping' file provided below.
- Click Create or Edit a Map.
- If there are any additional fields that need mapping, feel free to drag the fields from the top to match the fields down below.
- Click Next.
- Click Finish.
Downloading DataLoader
- Navigate to Salesforce Setup.
- Type Data Loader on the search bar.
- Click into Data Loader.
- Download the appropriate Data Loader.
