Import Grants

Overview

The Import Data feature allows users to upload standard grant and scholarship grant records from outside sources into FCG from an excel spreadsheet. 

Importing Grant Records

1. Navigate to Settings > Data Tools > Import Data.
2. Click the Download Template button to download the formatted Excel template. The template contains of all the columns needed to import grant records. 
3. Complete the spreadsheet according to the outline below.
4. Use the Upload Completed Spreadsheet button to initiate the import process.
5. The system runs validations against the incoming data and displays a status column with Invalid or Valid.
6. If the status reads Invalid, click the Errors found:(x) View button to view validation errors.
   1. See Data Rules below for common errors and how to fix them.
7. When all rows have a status of Valid click Import.
8. Select the appropriate default values for the Grant Type, Grant Class and Contact Method fields.
   1. A list of grant numbers will generate as well as a list of new constituents (if any). 

Required Fields

1. The below list shows fields the user must set for every grant record that needs to be imported. These cannot be empty.
   • Fund Number
   • Received Date (MM/DD/YYY) 
   • Payment Amount
   • Check Designation
   • Report Designation (if unknown type: NULL) 
   • Pay Date (MM/DD/YYY)
   • Recipient Name
   • Rec Address Block
     
   • Rec City
   • Rec Zip
   • Rec Country (if unknown type: NULL) 
   • Rec Email (if unknown type: NULL) 
   • Payee Name
   • Payee Address Block
   • Payee City
   • Payee State
   • Payee Zip
   • Payee Country (if unknown type: NULL) 

Default Fields

1. The list below shows fields the Import Data Tool populates with a default value for every grant record import. These fields are NOT customizable. Even if columns with the same column names as the fields below are present in the import document, grants do not import with those values.
   1. Entered Date – Today's Date
   2. Grant Status – REQUEST
   3. Grant Manager - If left blank, defaults to the FCG user performing the import. 
   4. Grant Expense/Grant Payable Codes - If left blank, defaults to the Fund's default codes. 
   5. Anonymous Flag – False
   6. Require 1099 Flag – False
   7. Number of Payments – 1
   8. ParentId – empty
   9. Adjusted Amount – Matches Payment Amount
   10. Manager ID – Default Manager set in Business Rules
   11. FC Recipient ID – Searches FCG by Recipient Name and Recipient Zip
       1. If a recipient record is not found, a new recipient will be created in FCG.
   12. FC Payee ID – Searches Foundation Cloud by Payee Name and Payee Zip
       1. If a payee record is not found, a new payee will be created in Foundation Cloud

Optional Fields

Characteristics

1. The first two characteristic types configured for Grants (or Scholarships) will be generated in the downloadable template document. Users can add additional characteristics by adding column(s) to the template following the formatting of the generated characteristic column.
   1. Example: Navigate to the characteristic's configuration page. Settings > Characteristics. The first two characteristics in the list will populate in the template document as “Characteristic:Age Group” and “Characteristic:Availability.” If the user wants to add the “Region” characteristic to the imported grant records, they can add a new column to the document entitled “Characteristic:Region.”

Custom Fields

1. The first two Custom Fields configured for Grants (or Scholarships) are generated in the downable template document. Users can add additional characteristics by adding column(s) to the template following the formatting of the generated custom field columns.
   1. Example: Navigate to the Custom Field configuration page. Settings > Custom Fields. The first two fields for the first page will populate in the template document as “Profile:Assigned User” and “Profile:Requested Date.” If the user wants to add the “Grant Staffer” field to their imported grant records, they can add a new to the document entitled “Profile:Grant Staffer.”

Data Rules

1. The list below is a summary of validations performed on an uploaded spreadsheet for all grant records. If a grant record has a status of “Invalid,” the record did not pass all validations and will show information on why it failed.
2. To upload a spreadsheet of grant records, all required columns must be present and there cannot be more than 26 columns.
   1. Invalid Fund - “FundNumber” is not a fund in FCG
   2. Invalid Payment Amount - “PaymentAmount” value must be greater than 0
   3. Invalid Pay Date – “PayDate” value must be a valid date in the format: MM/DD/YYYY
   4. Check Designation not set - “Check Designation” value is blank
   5. Recipient [Column] is missing - Recipient Name and all related recipient fields must have a value.
   6. Payee [Column] is missing - Payee Name and all related payee fields must have a value.
   7. [Value] is not a valid characteristic code for [Name] - All columns labeled “Characteristic:[Name]” must be an active characteristic
      1. Value must be an active code for the characteristic
   8. Profile [Name] is not a valid Custom Field - All columns labeled “Profile:[Name]” must be an active custom field for either Standard Grant or Scholarship Grant
      1. Value must be formatted correctly for the custom field
      2. [Value] is not a valid characteristic code for Profile Detail [Name] - If the custom field of type characteristic, the value must be an active code for the characteristic

FAQ

What is the correct formatting for Date fields?

• Date fields must be formatted as MM/DD/YYYY