CollegiateLink offers the ability for your institution to import financial transaction data on a regular basis via a CSV file, rather than entering each transaction into CollegiateLink by hand. This feature is best used when:

  • You are entering some or all financial transactions into an institution-wide accounting system and simply want student treasurers to be able to view these transactions within CollegiateLink.
  • You do not need transactions within CollegiateLink to show as “attached” to specific purchase or funding requests. NOTE: Imported transactions are not shown as related to financial requests.

This article will walk you through all the relevant steps to prepare your system for importing transactions, performing the import, and errors you may encounter. To jump to a specific section, use the navigation below.

Setting up your accounts

In order for you to be able to import transactions, you will first need to create and identify the accounts for which transactions are to be imported within CollegiateLink. To identify these accounts, an External ID must be populated with each account that will also be included in each transaction record exported from your institution-wide accounting system. The External ID must be unique to each Account. NOTE: Once you’ve created an account with a designated “parent” account, you cannot change the “parent” account.

Example: You are importing transactions for the Sailing Team (a club sport). In CollegiateLink, you have created an account called Sailing Team Operating Budget but in your institution-wide accounting system this account is identified by account #123456. In this example, “123456” is the External ID that must be set for the Sailing Team Operating Budget account in CollegiateLink.

Creating a new account with an External ID

To begin, head to the Administration tab, select the Finance option on the left side of the page. and click the Account button in the middle of the page. This will give you a listing of existing accounts; to create a new one, click the + Create Account button.

Finance-CreateAcct.gif

Enter the name and a brief description of the account. Next, identify the unique External ID that will directly associate any imported transactions with this account. Then, choose which organization the account is for. You can choose this from the box listed below, searching by the first letter of the organization.

Indicate if the new account is a root account, meaning that other accounts will pull from this one. “No” is selected by default. Identify the Parent Account by typing characters in the name of the account and then select the appropriate one from the drop-down menu, if applicable. Finally, click Create to complete the account.

Updating an existing account with an External ID

Head to the Administration tab, select the Finance option on the left side of the page, then click the Account button in the middle of the page. This will give you the listing of existing accounts, click on any one to view its details, then click Edit Account in the upper right corner to begin making changes.

Finance-EditAcct.gif

Enter the External ID in the last field that will associate this account with the imported transactions, then hit Save when finished.

Creating your import file

The system accepts a Comma Delimited (.CSV) file of your financial transactions and imports them into CollegiateLink using CollegiateLink’s “deposit” transaction method. Expenses should be imported as negative transactions and income/distributions as positive transactions.

Each transaction must be listed as one row within the CSV file. The columns (fields) contained within the CSV file must be only the following, in the order described below. The maximum length for all string fields is 255 characters. A (*) denotes a required field.

Field Type Description
*externalTransactionID (string field) A unique identifier for the transaction to be imported. This is used by CollegiateLink to ensure no duplicate imports may run.
*externalAccountID (string field) A unique identifier for the account in which the transaction has occurred. This can be an account name or an ID# but must match the External ID set in CollegiateLink for the account in which the transaction will be recorded.
*transactionAmount (00.00 or -00.00 format) The amount of the payment or deposit to record in the account. A payment would generally be a negative number and a deposit would generally be a positive number.
*transactionCategory (string field) A category for the transaction, such as an expense or revenue code.
*transactionType (string field) A secondary category for the transaction most often used to specify the type or transaction such as Internal Transfer, Check, Credit Card Purchase, etc.
transactionMemo (string field) A description of the transaction.
*transactionPayee/Source (string field) The payee for a payment or the source of a deposit.

Here is an example of a correctly formatted CSV file of transactions:
NOTE: Your file should not contain a column header row but the columns are shown in the image.

Scheduling an import

Head to the Administration tab, select the Finance option on the left side of the page. and click Transaction Import button in the middle of the page. Browse for the CSV file created using the steps above by clicking Choose File, and then click Upload.

ImportTransactions.gif

The page will refresh listing all Valid Transactions in the top module and any Invalid Transactions in the bottom module. A transaction may be invalid if any of the fields do not match in the system, or if the transaction # is duplicated. These will not be imported. If you're fine with the listed transactions, click Import to complete the process.

You will see all of your pending and completed imports. The new import just scheduled will show in the list as Status Pending. If you have more than one import to run, you may schedule multiple imports to run at the same time. Return to this page to see the status of your imports and to display any errors that have occurred.

Common error messages and explanations:

  • No internal account has been mapped to this external account number – If this error occurred, the transaction was not imported because the External ID specified is not associated with any existing account in CollegiateLink. Correct the External ID in the CSV file or add it to an existing account and then try your import again.
  • A transaction with this ID has already been imported – This error means that the transaction in the CSV has an externalTransactionID that matches a transaction that was imported previously, so it was skipped because it was identified as a duplicate
  • {fieldname} cannot be null – A required field was missing a value for the specified transaction so the transaction was not imported. Add a value to the field specified in your CSV file and try the import again
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Need More Help?

Additional Support

Powered by Zendesk