Client Data Import Functional Spec

Owned by Lukasz Chudy (Unlicensed)
Last updated: Apr 20, 2012Version comment
14 min read

Client Data Import

Release

2.2

Status:

To be implemented

Introduction

A lot of users need functionality which allows them to create a large number of clients automatically by importing them from Excel file. This option should save a lot of time and effort in comparison to the manual creation of each client in UI. This requires the following:

  1. Ability to import new Clients in any states with all necessary information. It should be possible to import Client inside Group and without Group. Configuration properties should be taken into account. Mandatory fields defined in Mifos should also have impact on importing information.
  2. Import functionality should accept Excel files in .xls format. There should be no difference between clients created manually and by import.

User Stories

Priority

User Story

Section in FR

1

As a user, I want to be able to import a file with all Clients, so that they will be properly created in Mifos. I expect that there are no differences between Clients created manually and by import.

 

1

As a user, I want to have ability to define the Customer Global Number.

 

1

As a user, I want to be able to see errors in import and be able to correct them.

 

Goals

  • Ability to import file with Client data so that Mifos can create Clients in the specified state.
  • Properly displays rows read, ignored, errored, and imported during import.

Non-Goals

The following items will not be addressed in this version of the feature:

  • Ability to import file with different or switched columns
  • Ability to import Client data when Center hierarchy is turned off.
  • Ability to specify meeting information during import.
  • Ability to create default savings accounts for clients during import.

Basic flow in Mifos UI

1. Log in to Mifos.

2. Switch to 'Admin' tab.

3. Click on 'Import client data' link in 'Manage Imports and Exports' section as it is shown below:

4. Import Client data page will be loaded as it is shown below:

5. Choose previously prepared .xls file by clicking on 'Browse' button.
6. If the right .xls file is chosen, click on 'Review' button.
4. Mifos imports file and checks for any errors. If there are no errors, Mifos displays Review and Submit screen with “There are no errors found. Click Submit to continue with import.

Mifos Permissions and Mandatory Fields

Use Cases

Use Case – User without permissions to create Clients and to import Client data tries to import Client data

Actors

  • User without permissions to create Client

Preconditions

  • Only Adminstrator/Loan Officer has  permissions to import client data.
  • User without permissions to create Client should not also has  permission to import Client data.

Basic Flow

  1. User without permission to import Client data opens 'Admin' tab.
  2. User without permission to import Client data clicks on 'Import client data' link.
  3. User without permission to import Client data sees warning message instead of content of the page.

Post Condition

  • User without permission to import Client data is unable to import Client data.

Use Case – User with appropriate permissions tries to import Client data.

Actors

  • User with appropriate permissions

Preconditions

User has permissions to import client data.

Basic Flow

  1. User opens 'Admin' page.
  2. User clicks on 'Import client data' link.
  3. User selects file to import.
  4. User submits changes.
  5. User checks if clients are created properly.

Post Condition

  • User is able to create client via import functionality and to view/edit newly created clients.

Use Case – User with appropriate permissions imports file with client data without mandatory fields

Actor

  • User with appropriate permissions

Preconditions

  • Poverty status and Ethnicity fields have been set as mandatory fields under Define Mandatory/Hidden fields.

Basic flow

  1. User tries to import file with empty Poverty status and Ethnicity fields
  2. User clicks 'Review' button
  3. Mifos displays an error message that mandatory fields are not filled out.

Post condition

  • Mifos displays error message.

Mifos Permissions Functional Requirements

FR#

Pri

Description

Comments / Mockups

1.1

P1

A new permission called 'Can import client data' is added to the Client Management section.  This permission only applies to 'Client data import' page.


1.2

P1

It is checked by default for Administrators.

 

1.3

P1

If you have this permission, you can import Client data from Excel file.

 

1.4

P1

If a user does NOT have this permission checked, user has no access to Import Client Data page.

 

Import Client Data

Use Case - User import transaction file with clients data

Actors:

  • User (Loan Officer/Admin)

Preconditions

  • User has permission to import client data
  • User has file in xls to import

Basic Flow

  1. User logs into Mifos and navigates to the Admin section. User selects 'Import client data' link.
  2. Mifos displays new screen for import client data
  3. User selects previously prepared .xls file from his computer for import and clicks on 'Continue' button.
  4. Mifos imports file and checks for any errors. If there are no errors, Mifos displays Review and Submit screen with “There are no errors found. Click Submit to continue with import”
  5. Data Encoder clicks on SUBMIT. Mifos displays confirmation screen that import was successful.

Post conditions

  • All clients listed in file will be created in Mifos in specified state.
  • All client details listed in xls file are properly imported to Mifos and are visible on Client details page.
  • Clients without Group membership will be created in Mifos also without Group membership.
  • There is an appropriate entry in change log for every created Client.
  • Meeting frequency is properly inherited from related Group/Center.

Validations

  • If mandatory fields in file are not defined, Mifos displays error message and row with incomplete data will not be imported (see below)
  • If status of Client is higher than Group status to which Client is imported, then Mifos displays error message and row with incomplete data will not be imported
  • See Import File Details and Validations for specific column validations.

Alternative Flows

Use Case - User Cancels import

  1. At step 4, User clicks on 'Cancel' button instead, Mifos returns user to admin section

Post Conditions

  • No data has been imported

Use Case - Import has errors and User chooses to continue

  1. At step 4, Mifos determines there are errors from Validations mentioned below. Mifos displays Review & Submit screen with error messages depending on types of error.
  2. User chooses to continue with import of the valid rows and clicks on 'Submit' button. Mifos displays confirmation screen that import was successful.

Post-conditions

  • Valid rows are imported and invalid rows are rejected.

Use Case - Import has errors and User chooses to Cancel

  1. At step 4, Mifos determines there are errors from Validations above. Mifos displays Review & Submit screen with error messages depending on types of error.
  2. User chooses to cancel and clicks on 'Cancel' button instead. Mifos returns User to the Admin screen.

Post-conditions

  • No data has been imported.

Use Case - Import has errors and User chooses to import a different file

  1. At step 4, Mifos determines there are errors from Validations above. Mifos displays Review & Submit screen with error messages depending on types of error.
  2. Accountant chooses to import a different file and clicks on 'Edit:Client information' button instead. Mifos returns User to the previous screen. Return to Step 2 of Basic Flow.

Post-conditions

  • No data has been imported.

Use Case - User checks that Clients have been created successfully

Actors

  • User (Loan Officer/Admin)

Preconditions

  • User has permissions to view clients loan data
  • Clients data has already been imported

Basic Flow

  1. User logs onto Mifos anduses search function to find Clients created via import.
  2. User selects any Client (created via import) from the list.
  3. Mifos displays Client details page with the same information as in the xls file (status, group membership, personal information, correct entry in change log).
  4. Meeting frequency is the same as in the related Group/Center.

Post-conditions

  • None

Import Client Data Functional Requirements

User can not correct errors during the import. If any error occurrs, user should stop importing data, make changes in the .xls (according to error message) and import corrected .xls file again.

FR#

Pri

Description

Comments / Mockups

1.1

P1

Client Data import file is an Excel format (97)

The file must be saved with .xls extension

1.2

P1

Client data import file's first few lines contain description of file. These are to be ignored. Only data after row of column headings will be imported.

 

1.3

P1

Import file will have columns in below

 

1.4

P1

First check if row should be ignored or accepted.


If only Group Global number is defined then Client will be created in that Group.
If only Branch short name is defined then Client will be created without Group mebership and will be attached directly to the Branch.
If Group Global number and Branch short name are defined then Client will be created in the related Group.
Else, accept the row, and continue doing error checks.


1.5

P1

If a row contains a cell that's missing a required field, Mifos displays an error message for each row this occurs.  Only check the rows that are not ignored.

Error in row <#>, Column <column name>: <value> does not exist

where the row # is the original row # of the import file.

 

1.6

P1

Date of Birth is in the format YYYY-MM-DD.  If any value under Date column does not start in format of YYYY-MM-DD, then Mifos displays an error message for each row this occurs.

Error in row <#>, Column Date of Birth: Invalid date: <value>

where the row # is the original row # of the import file.
If Date of Birth is a future date, then Mifos throws this error message:
Error in row <#>, Column Date of Birth: Date of birth cannot be a future date

 

1.7

P1

If Date of Birth is greater or lower than specified age range in configuration file then Mifos throws this error message:
Error in row <#>, Column Date of Birth: Given age is out of range. Age must be between <value> and <value>

 

1.8

P1

If Branch short name and Group global number are not defined in the file then Mifos throws an error message

Error in row <#>: You must specify either Branch short name or Group global number

where the row # is the original row # of the import file.

 

1.9

P1

If cell contains value which is not defined in Mifos (Define Lookup options), then Mifos throws this error message:

Error in row <#>, Column <column name>: Invalid cell value: <value>

 

1.10

P1

If there is no Status column in the file, create Client in Mifos in Pending Approval status.

 

1.11

P1

If there is Status column in the file, create Client in Mifos in specified state.
If status of Client is greater than status of the Group, then Mifos throws this error message:
Error in row <#>, Column <column name>: Group status should be higher than that of Client

 

1.12

P1

If Group is in wrong state, then Mifos thrwos the following errors:
For Closed Group:
Error in row <#>, Column <column name>: Group with global number <global number> is closed
For Cancelled Group:
Error in row <#>, Column <column name>: Group with global number <global number> is canceled

 

1.13

P1

If Group Global number does not matach with any Global number defined in Mifos, then the following error message is displayed:
Error in row <#>, Column <column name>: Invalid group global number <global number>

 

1.14

P1

Check if the same Client has been imported or already exists in Mifos. If so, then throw an error message:

Error in row <#>, Column <column name>: Client already exists

 

1.15

P1

Check if Client with the same global number has been imported or already exists in Mifos. If so, then throw an error message:

Error in row <#>, Column <column name>: Duplicate global number: <value>

 

1.16

P1

If file is not in .xls format then Mifos throws this error:
Error loading file <file name>

 

1.17

P1

If mandatory field is not defined in the file, then Mifos throwsthis error message:
Error in row <#>, Column <column name>: Empty mandatory field

 

1.18

P1

If there are no active Loan Officers in Branch to which Client will be imported then Mifos throws this error message:
Error in row <#>, Column <column name>: No active loan officers in office <value>

 

1.19

P1

If file doesn't contain enough number of cells or rows then Mifos throws the following errors:
Not enough input cells
or
Not enough input rows

 

1.20

P1

If specified Office short name does not match any Office short names in Mifos, then this error message is dispalyed:
Error in row <#>, Column <column name>: Office <value> does not exist

 

1.21

P1

After clicking on Continue, Mifos will display the Review & Submit screen with the following:

Review the information below.  Click Submit if you want to continue with import or click Edit to make changes. Click Cancel to return to Admin page without submitting information.

Import information

Import file name: <name of file>
Import Status:

<# read> rows were read
       <# imported> rows contained no errors and will be imported
       <# ignored> rows will be ignored  
       <# error> rows contained errors and were not imported

List all rows with error messages in red


1.22

P1

User can then either

  1. Click on 'Edit:Client information' button to go back to previous screen and upload new file.
  2. Continue with import and 'Submit' button.
  3. Cancel out of the workflow (returning to Admin screen).

 

1.23

P1

If User clicks on Submit, Mifos imports the file and displays confirmation screen that import was successful. 

 

1.24

P1

There is no option to revert a file upload once it has been submitted.

 

Client Data Import Columns and Description

Column Name

Required

Description

Validations

Action

Range

Example

Client Global Num

Yes

Client Global number is a System ID number. IMPORTANT: it is not ID number from the database! This number can be defined by the user.

Global number is unique in Mifos. Check if the same number doesn't exist in Mifos before import.

Import

Alphanumeric

BSA122789563-5

Branch Short Name

Yes

Short name of the Branch Office.

Check if this short name matches Branch Short Name definedin Mifos.

Evaluate

Alphanumeric (3 characters long)

AS1

Group Global Num

Yes

Group Global number is a System ID number. IMPORTANT: it is not ID number from the database!

Check if this number matches Group Global number definedin Mifos.

Evaluate

Numeric

0006-0000000012

Salutation

Yes

Lookup option defined on 'Define Lookup options' page in Mifos.

Value should match lookup option defined in Mifos.

Import

Options defined by HO

Mr

First Name

Yes

Client First Name  

Combination of First, Last name and Date of Birth is unique in Mifos.

Import

100 characters

Michael

Middle Name

No

Client Middle Name

N/a

Import

100 characters

John

Last Name

Yes

Client Last Name

Combination of First, Last name and Date of Birth is unique in Mifos.

Import

100 characters

Collins

Second Last Name

No

Client Second Last Name

N/a

Import

100 characters

Adams

Government ID

No

Government ID  

N/a 

Import

Alphanumeric 

123Ad45

Date of Birth

Yes

Date of birth

Date of Birth should not be a future date, should match specified age range and should be in proper format.

Import

YYYY-MM-DD

2011-02-20

Gender

Yes

Male or Female

Gender can be Male or Female

Import

Male, Female

Male

Marital Status

No

Marital status

Value should match lookup option defined in Mifos.

Import

Options defined by MFI

Married

Number of children

No

Number of children

Value shoud be numeric.

Import

Numeric (0-30)

2

Citizenship

No

Citizenship

Value should match lookup option defined in Mifos.

Import

Options defined by MFI

Non-Citizen

Ethnicity

No

Ethnicity

Value should match lookup option defined in Mifos.

Import

Options defined by MFI

Indian

Education Level

No

Education Level

Value should match lookup option defined in Mifos.

Import

Options defined by MFI

Both Illiterate

Activities

No

Activities

Value should match lookup option defined in Mifos.

Import

Options defined by MFI

Trading

Poverty Status

No

Poverty Status

Value should match lookup option defined in Mifos.

Import

Options defined by MFI

Poor

Handicapped

No

Handicapped

Value should match lookup option defined in Mifos.

Import

Options defined by MFI

Yes

Spouse/Father Relationship

No

Spouse/Father Relationship

Father or Spouse

Import

Father, Spouse

Father

First Name

No

First Name

N/a

Import

100 characters

Jason

Middle Name

No

Middle Name

N/a

Import

100 characters

Aron

Last Name

No

Last Name

N/a

Import

100 characters

Collins

Second Last Name

No

Second Last Name

N/a

Import

100 characters

Adams

Address

No

Address

N/a

Import

100 characters

Main Street 1

City/District

No

City/District

N/a

Import

100 characters

Phoenix

State

No

State

N/a

Import

100 characters

Arizona

Country

No

Country

N/a

Import

100 characters

United States

Postal Code

No

Postal Code

N/a

Import

Numeric

67-2345

Telephone

No

Telephone

N/a

Import

Numeric

678543120

Recruited By

No

Assigned Loan Officer

Loan Officer specified in file should exist in Mifos.

Import

Alphanumeric

Loan Officer

Status

No

Status of the Client

Status of the Client shoud not be higher than Group status. If this field is empty, Client should be created in Pending Approval state.

Import

Partial Application, Pending Approval, Active

Active

QA Considerations

  • Client Details page - correct dates and values imported
  • Change log – initial Client status is logged

Standard Considerations

Security (Permissions, Roles, and Data Scope)

Does the user need to be in a particular user hierarchy to use this feature?

No

Does the office hierarchy affect use of this feature?

No

Are you using any existing permissions to control this feature?

No

Are you adding any new permissions or changing existing permission to control this feature?

Yes - new permission for importing Client data

Are you using any existing activities to control this feature?

No

Are you adding any new activities or changing existing activities to control this feature?

No

Are there any special considerations for upgrade scenarios? What will be the default value for new permissions?

No special considerations. Default value is checked in User role.

Impacts to System

Does this feature affect Bulk Loan Creation? How?

No

Does this feature affect Collection Sheet Entry? How?

No

Does this feature affect Redo Loans?

No

Does this feature affect Undo Loans?

No

Globalization/Localization

Will this feature support users localizing data that they enter?

No

Does this feature involve any date/time related data, and if so how should conversions be handled?

Yes. See date format on Column definitions.

Is there currency or other numeric data ? If so does it require any special handling or validation?

No

Logging

Change Log

Do changes to the data that is collected or stored by the new feature have to be fully logged by the system?

Yes

Does the administrator configuring the system need the ability to turn on or off logging for this feature?

No

Is the feature currently logged but the structure of the logged records changing?

No

Reporting

Provide any relevant information about reporting requirements for the new features and answer the questions below, providing detail to explain any particular area when necessary.

Does the feature affect any existing reports?

No

Does the feature require adding any new reports?

No

Performance

Will the feature be a high use-case scenario?

No

Will the feature have potential for high concurrency?

No

Does the feature include complex UI or data gathering logic that will be used by a significant portion of the user base?

No

Does the feature contain risks of database connection timeout?

Yes

Will the feature contain any bulk insert/update/delete transactions?

Yes

Could the feature result in a large amount of data being sent to the client or between the database and web server?

Yes

Would users on a low bandwidth connection likely face issues with a part of this feature?

Depends on size of file

Setup and Installation

New Installations

Will the feature include demo data?

No

Does the feature require any data to be gathered at setup runtime?

No

Backward Compatibility and Upgrades

Is there any data conversion that needs to be done as part of an upgrade?

 

Will customers lose data or will the way existing data is stored change significantly?

No

Will another feature, workflow or portion of the data model be deprecated as a result of this new feature?

No

Will existing role permissions be changed or impacted by this feature? If so provide details in the security section.

User role should be updated to have this permission checked.

Will existing customers need to learn a new UI process or change the way they use the system as a result of this new feature?

No

Hosting Support

If different user groups are using the same database, are there concerns over the sharing of data related to the feature?

No

Are there expected to be performance related issues with having many customers sharing the same hardware in support of this feature?

Yes. Depends on file size.

Configuration

Does this feature require changes to configuration files?

No

If so, is this feature enabled or disabled by default?

N/a