If you are running the SQL Server version of AboutFace behind your firewall, you
can use the following instructions to load employee data into AboutFace's SQL
database on a one-time basis or on an automated, scheduled basis.
If you are loading data into the AboutFace database on a
one-time basis, then you will run queries (described below) in SQL Server's
Query Analyzer. If you are setting up an automated, scheduled import, then
you will create a stored procedure based on the queries described below and set
SQL Server to run the stored procedure periodically (twice per day works well).
Overview of
Database Structure:
AboutFace's database has several tables, but only a few are
relevant to importing data:
employee: this table contains the core fields for
each employee record -- username, password, privilege level, etc.
field: this table contains a list of "virtual
fields" in the database and maps each field name to a field number (for example,
Title might translate to 12495.)
text: this table contains all of the rest of the
employee data that is not contained in the employee table (for example, Title,
Phone Number, etc.).
Note: Because AboutFace's database structure is highly
relational, we recommend that you start by opening the employee, field and text
tables and analyzing the sample data to better understand how data is stored.
A detailed explanation of each table follows:
Table Name: employee
Field Name
Explanation
id
Unique record
identification number. This field is populated
automatically when you create records in the employee
table.
directory_id
Set directory_id to
"default" when importing data.
username
AboutFace has its own
security system. Populate this field with unique
usernames.
password
The password field
stores an encrypted password. To ensure security,
it is recommended you populate this field with random
numbers or letters.
isadmin
Controls whether a user
is an AboutFace administrator. For most users, you
will set this to 0. For administrators, set it to
1.
user_group
Provides control over
each user's data editing privileges. For
administrators, set user_group to 4. For
non-administrators, set user_group to 2.
catagory
AboutFace can separate
groups of records into categories (for example, Active
Employees, Alumni, Contractors). Enter a catagory
number in this field by referencing the id field
in the catagories table. In most AboutFace
installations, the "Active Employees" catagory corresponds to
391, so you would enter 391 in this field. [Please
note the unusual spelling of "catagory" and "catagories".]
privacy
Enter 0 (the number 0,
not the letter o) in this
field when importing data.
created
This field is
populated automatically when importing data.
floorplan_id, x_pos
and y_pos
These fields are used
by AboutFace's floor plan manager. We do not
recommend that you populate these fields at the database
level. Instead, use the floor plan manager tool to
set employees' locations.
client_employee_id
Assuming the source database
you are importing from has its own unique record
identification numbers, enter the id number from the
source database in this field. For example, if Joe
Smith is record 38643 in the database you are importing
from, enter 38643 in this field.
Table Name: field
The field table contains a list of "virtual" fields in the AboutFace database.
Do not populate this table directly. Instead, use the Configure Database
Fields screen in AboutFace to create fields. You will only need to refer
to this table for data import purposes. There are several fields in the
field table, but you will only need to refer to the following two fields:
Field Name
Explanation
id
Unique record identification number.
This field is populated automatically when you create
records in the field table.
name
This is the field name
corresponding to the id number. For example, "Title"
might have
an id of 12495. For data import purposes you will
refer to fields by their number, not their name.
Table Name: text
Field Name
Explanation
id
Unique
record identification number. This field is populated automatically when you
create records in the text table.
employee_id
This is the
id number for the person you wish to enter data for. Refer to the id
field in the employee table for the value to enter here.
efield_id
This is the
id number for the "virtual" field you wish to enter data for. Refer to
the id field in the field table for the value to enter here.
subof
AboutFace allows "container"
fields. Container fields are displayed as one field through the web
interface but are actually stored as multiple, separate fields in the
database. The most common container field is the Name field, which is
made of up First Name, Last Name, and Middle Initial. The value to
enter here is the id number of the parent field in the text table. See
query examples below to accomplish this.
value
This is the
actual field data. For example, if this is the Title field, you would
enter "Associate" or "VP" here.
Sample Queries:
Here are sample queries that you would enter into SQL Server's Query
Analyzer:
Assumptions for the following examples:
The id for the Last Name field in the field table is 10446.
The id for the First Name field in the field table is
10444.
The id for the Name field in the field table is 10442.
This is the "parent" field to the First Name and Last Name fields.
The id for the Title field in the field table is 10516.
The name of the table you are importing from is
source_table in a database called master_employee_db.
/* insert rows into employee table -- one row per
employee*/
insert aboutface.dbo.employee (directory_id,username,password,isadmin,
catagory,user_group,client_employee_id)
SELECT 'default', [user id], 'randomvalue', '0', '391', '2', code from
master_employee_db.dbo.source_table
/* insert last name fields into text table*/
insert aboutface.dbo.text ( value, efield_id, employee_id)
SELECT LastName, '10446', aboutface.dbo.employee.id from
master_employee_db.dbo.source_table, aboutface.dbo.employee where
master_employee_db.dbo.source_table.code=aboutface.dbo.employee.client_employee_id
/* insert first names into text table*/
insert aboutface.dbo.text ( value, efield_id, employee_id)
SELECT FirstName, '10444', aboutface.dbo.employee.id from
master_employee_db.dbo.source_table, aboutface.dbo.employee where
master_employee_db.dbo.source_table.code=aboutface.dbo.employee.client_employee_id
/* insert name field placeholders into text table -- an important step to tie
together the individual components (first name, last name, etc.) of the Name
field*/
insert aboutface.dbo.text ( value, efield_id, employee_id)
SELECT 'null', '10442', aboutface.dbo.employee.id from
master_employee_db.dbo.source_table, aboutface.dbo.employee where
master_employee_db.dbo.source_table.code=aboutface.dbo.employee.client_employee_id
/* update first name values in text table to include the right subof values*/
update aboutface.dbo.text
set aboutface.dbo.text.subof=table2.id from aboutface.dbo.text left join
aboutface.dbo.text as table2
on aboutface.dbo.text.employee_id=table2.employee_id and table2.efield_id= 10442
where
aboutface.dbo.text.efield_id= 10444
/* update last name values in text table to include the right subof values*/
update aboutface.dbo.text
set aboutface.dbo.text.subof=table2.id from aboutface.dbo.text left join
aboutface.dbo.text as table2
on aboutface.dbo.text.employee_id=table2.employee_id and table2.efield_id= 10442
where
aboutface.dbo.text.efield_id= 10446
After you have run the above six queries, a skeletal database will exist, and
you will be able to view a list of names in AboutFace. To import additional
fields, execute queries similar to the one shown below:
/* insert Title field */
insert aboutface.dbo.text ( value, efield_id, employee_id)
SELECT Title, '10516', aboutface.dbo.employee.id from
master_employee_db.dbo.source_table, aboutface.dbo.employee where
master_employee_db.dbo.source_table.code=aboutface.dbo.employee.client_employee_id
and master_employee_db.dbo.source_table.Title is not null
If you have questions, please
email us at support@aboutface.com or call us at
888-423-4200.
