Many GLM users export their data out of GLM into FIMS regularly in order to cut checks and perform other accounting duties. Some field values differ between GLM and FIMS and therefore can not be imported without manually changing the value in Excel.
For example, in GLM an approved grant’s status can be “Approved, Follow Up Draft, Follow Up Submitted, Follow Up Complete or Closed” whereas in FIMS the status needs to be just “Approved”. Another example is that you may have GLM set up to capture Program Areas such as “Arts & Culture, Education, Health and Youth Development” but these areas are categorized as “A, B, C D” in FIMS.
By using the Formula tool in a generated Report, these values can be changed into the value needed to import the data into FIMS without having to do a “Find & Replace” or manually editing the data in Excel.
Setting up this formula may take a little time but in the long run will save you hours of editing your data. Here at Foundant we like to call this a “Nested IF Statement” which basically means you are telling the system to look at a specific column and if the value is “A” change it to “B” and if the value is "C" change it to "D" and so on.
Before you start, please note that it is very important to type the value in exactly as it shows up in the Foundant GLM system and how it appears in FIMS. That means if it is all caps, you type it in all caps and put spaces and punctuation exactly where they appear. The examples below refer to my examples above so please feel free to copy them and edit as needed. It is also very important that you have the exact values needed to be imported into FIMS before you start, to save yourself some work.
The following formula will change the values in the “Request Status” column into the “FIMS Status Field” values needed to import the data:
IIF([Request Status]="Approved", "Approved", IIF([Request Status]="FollowUpDraft", "Approved", IIF([Request Status]="FollowUpSubmitted", "Approved", IIF([Request Status]="FollowUpComplete", "Approved", IIF([Request Status]="Closed", "Approved", IIF([Request Status]="Denied", "Denied",0))))))
The following formula will change the values in the “Program Area” column into the “FIMS Program Area”:
IIF([Program Area]="Arts & Culture", "A", IIF([Program Area]="Education", "B", IIF([Program Area]="Health", "C", IIF([Program Area]="Youth Development", "D", 0))))
The fields below are commonly imported into FIMS:
- Process Name
- Program Name
- Organization Name
- Profile Number - many administrator have repurposed our “DUNS Number” field in the organization’s profile to be used for the FIMS Profile Number so it can be pulled into reports and exported.
- Tax ID
- Request Amount
- Program Area
- Contact Name
- Contact Email Address
- Amount Awarded