Understanding Field Mapping

Contents

1 Overview

Loading data from numerous sources can sometimes be a long and difficult database task as there are MANY different rules and constraints to any data loading process. bxp software (bxp) attempts to completely work around these difficulties by letting the software manage all the rules. This leaves the business level user the task of setting up a mapping between the two sets of data.

2 So how does it work?

Firstly we consider the strategic view of the work we’re trying to do. We are trying to get data from outside BE into BE, so that we can use the tools that BE offers.

The data outside can come from any sort of source. Microsoft Excel, Comma Separated Value files, text documents, Access databases, Microsoft SQL server databases, anything really. We even, using our data teams, enter paper records directly into the systems for you if required.

There are three types of interface for this.

• For big server solutions, we develop an automated and linked solution in conjunction with an IT department.
• For most solutions the data is in an Excel file or Access database and for this, we use a web based interface.
• The last is data entry and we use the Inbound Contact module to speed entry all the records.

As we will be using Excel spreadsheets 90% of the time, this wixi specifically deals with using this method of adding data.

3 Mapping concept

Imagine two Excel spreadsheets

Work book 1 (Wb1)

Work book 2 (Wb2)

We are going to move the data from Wb1 to Wb2. If you were going to write the instructions to another person it would make sense to write it as

1. Move Wb1. First name into Wb2.Firstname
2. Move Wb1.Family Name into Wb2.Surname
3. …… and so on …

giving us the idea that….

We’re going to do exactly the same using bxp but the system does a lot of the transferring and validation as it goes along.

For each Form in bxp there is a data table called CDA. It is created dynamically and the names of each of the columns (fields) in the database are not really much use to human understanding

If we were to use our Excel example…. Our database ( our Wb2 ) looks like this….

So the system allows you to change the name of the column to not only something more understandable but also to the column name of the data you’re importing.

Ideally what we want to do is…

So what we do is

Edit 3 and change the name in the box to “First Name”

This lets the system know, that if it sees First Name in the excel spreadsheet, it is to be loaded into this field in the database.

You repeat the process for every field. You can update the mappings every time you have a new spreadsheet to load. The order of fields is also not important, just make sure the Wb1 field name is beside the right item in the field mapping.

Don’t forget to click “Update Mappings” down the bottom.

4 Figuring out used fields

Naming fields in field mapping can be a bit confusing and hard to work out, but here is the simple rule of thumb to follow:

Open the form as if you're adding a record.

Beside the question you want the field mapping for, click the little pencil icon to the right of the question.

At the top of the page you'll see the original field name that bxp is using for this field.