How to Import Grants

Getting Started:

Sometimes it is necessary to upload records from outside of Grant Edge Classic (GE Classic). The system can handle an import from an excel spreadsheet for grants, constituents, and funds. For optimal results make sure your constituents are added to your CRM (RENXT or BBCRM) and are synced over to GE. Note: Grants that are imported will not appear in the Spectrum portal unless they are PAID.

Get Constituent Records from GE:

1. Go to Records > Constituents > All Constituents
2. Make sure you have these columns as the first four columns you see:
3. Export this grid to Excel.

Generate Grant Template:

1. Admin > Import Menu > Grants.
2. Use the drop-down list to select the Grant Type as either Standard Grant or Scholarship Grant. 
3. Click Generate Template. An excel template with pre-populated column headers will open as shown below. Leave the columns in order and save as an .xls file.
4. Use this template to fill in the information that is available. Note: Not all fields are required and fields are case sensitive. If no information is available leave the cell empty.
5. The dropdown called CRM Constituent lookup Criteria is what GE uses to look into CRM if the Recipient RE Key field is not filled out in the spread sheet. 

Map Fields to the Template:

The most important fields to fill in for every grant are:

Contact Method: Letter, Email, Web, Application, etc. You can get a list of the available values from a new grant screen’s dropdown list of Contact Methods. If this field is left blank then GE will default to “Letter”. 

Fund: You must either set a Fund ID or a Fund Number in the spreadsheet. These values can be found by going to Records > Funds > All Funds. The two middle columns shown here are Fund ID and Fund Number, and at least one of these must be set in the spreadsheet so that GE can find the correct Fund. The fund number is what the fund number is in RE/FE (should match) and the Fund ID is the GE system record ID for a fund.

GrantType: S for Scholarship. DA for Donor-Advised, etc. This is required. This should match a value in your Config > Business Rules > Grant Type (example: DA for Donor Advised).  

Received Date: The date the Grant request was received. If left blank, this will default to “Now”.

Entered Date: The date the Grant request was entered into GE. If left blank, this will default to “Now” (and leaving it blank is recommended).

Grant Status: REQUEST (all caps). This is required.

Anonymous: TRUE or FALSE. This is required.

Require1009FLAG: TRUE if it is an expense, FALSE if it is a grant. This is required.

Payment Amount: Total amount of the grant.

Number of Payments: 1 for lump sum etc.

Check Designation: This gets sent to the Finance system to be put onto the check. You can either enter the text of the designation, or you can choose one of GE’s predefined designation templates (which can be found in GE Classic, Business Rules, Records, Grants, in the Custom Variables section. Designations 1-5 are listed with their custom formats). To use Designation 3, for example, enter “3” into the Check Designation field in the spreadsheet. This is required and limited to 60 characters (examples: General Operating Support or a short title of the grant, historical grant).

Report Designation: Longer field for more information such as how the grant should be paid out. This field is optional.

Special Instructions: Extra field for more details. This field is optional.

PayDate: First date the user wants their first payment to go out. Also known as the Grant Date.

ParentID – no longer used – please leave blank.

Adjusted Amount: This is the total grant amount after voids, refunds, etc. Since the grant will be created without any adjustments, users should copy the grant amount.

Grant Class: Grant, Expense, or Loan. This field is required, and will almost always need to be “Grant”.

ManagerID: The Manager is the GE user who “owns” the grant record. You can enter 1 for the system ID, or if left blank, this will default to the current GE user. To set it to a specific user, locate the GE User’s “User ID” value by going to GE Classic, Admin, User Admin. The User ID is the first column in this grid.

Recipient Identifiers

The RecipientID, RecipientREKey, or RecipientREConstituentID fields in the spreadsheet are used when you’re creating new Grants for existing recipients in the GE database. In order for the import processing function to find the correct constituent record, the spreadsheet column for at least one of the three recipient identifiers must be set. If these are left empty, or if the import function cannot find recipients by any of the provided identifiers, GE will attempt to create new RE constituent records. 

The user can find the Constituent identifiers by going to Records > Constituents and clicking on All Constituents screen.

RecipientID = Constituent ID. This is the system record ID in GE (first column in screenshot above). It is best practice to fill out this field, and the following two fields will be brought in automatically.

RecipientREKey- REKey. This is the system RE record in GE. Do not fill out if you have RecipientID. (if the RecipientREKey is blank, then GE will try and locate the CRM recording using based on the drop down selection from step 4 above.

RecipientREConstituentID – This is the system ID in RE, not to be confused with the REKey in GE. Do not fill out if you have RecipientID.

If the Recipients don’t already have constituent records in GE or RE, leave the three Recipient Identifier fields blank, and then make sure that the next set of fields are populated like this: 

RecipientOrgName: Grantee organization name (should match what is in GE). Helpful Hint: If you have a large number of records to map to constituents. Insert the four columns shown above before the constituent ID and do a =vlookup in excel and match to the Organization Name. This will add all the values quickly and you will not have add the id one by one. 

RecipientLastName: Last Name 

RecipientMiddleName: initial or name

RecipientFirstName: First Name

RecEmail: email address (take off the hyperlinks)

SSN: Social Security number. NPact strongly recommends that you only import the last 4 digits of a person’s SSN, or that you replace the first numbers with asterisks (***-**-1234)

The columns below are not required and can be used for grants to individuals. These fields are not necessary if you use S. RecipientID. This will pull the address from the record in GE.

Date of Birth: Date of birth like this 02/03/04

RecAddressBlock: Street Address, including Apt/Suite lines

RecCity: City

RecState: State

RecZip: Postal code

Payee Information:

PayeeID: Constituent ID. This is the system record ID in GE (first column in screenshot above). It is best practice to fill out this field, and the following two fields will be brought in automatically. If the grant’s recipient is the same constituent as the payee, copy the exact value in the RecipientID column into the PayeeID column. If the payee is a different constituent and the constituent record already exists in GE, you’ll need to set the PayeeID field.

PayeeREKey: REKey. This is the system RE record in GE. Do not fill out if you have PayeeID. 

PayeeREConstituentID: This is the system ID in RE, not to be confused with the REKey in GE. Do not fill out if you have RecipientID.

PayeeOrgName: In most cases, use the exact value in RecipientOrgName column unless it is a different payee than the recipient organization.

If the Payees don’t already have constituent records in GE, leave the two Payee Identifier fields blank, and then make sure that the next set of fields are populated (Org Name, Payee Address, etc.)

Characteristics and Profile fields (shown above) are not required but are useful if you want to map specific characteristic values. The column in these fields should reflect the name of the characteristic or profile field exactly (Case Sensitive). Then, enter the value of the characteristic or profile in these columns (Case Sensitive).

Import Grants:

5. Click Open Grant Spreadsheet to upload the completed spreadsheet, an example is below. The first column indicates if there is an error or necessary fields missing. 

6. To load the grants click Import on the bottom right of the screen. 

7. In the new window, select OK to process the grants in the database.

8. In the new window click Yes to view the logs. All the new grant numbers will populate here.