Data Uniqueness Rules

When you import into simple data fields (Text, Numeric, Date/Time, and Binary/Trinary), SBM attempts to convert the incoming data into the applicable format. When you import data into a foreign key fields (Single Selection, Multi-Selection, State, User, Multi-User, Multi-Group, auxiliary Single Relational and auxiliary Multi-Relational fields) SBM must determine if text represents a unique selection, user, state, company, and so on. If it does, a record is added to the appropriate Relational table and a foreign key value to that record is established. When you import from a source SBM database, the Import Data Wizard first checks ID maps before resorting to text comparisons, which are less accurate.

Under most circumstances, the Relational table of interest uses one identifying Text field to establish uniqueness when foreign key values or records are created. However, there are a few exceptions, which are explained in the following table.

Receiving Field Type Uniqueness Rule
Single Selection, Multi-Selection Selections are unique to each field. Before establishing a new selection for a field, SBM searches for case-insensitive selection names (based upon TS_SELECTIONS.TS_NAME) available to that field in that workflow, regardless of whether the selection is enabled, disabled, or deleted.
Auxiliary Single Relational and Auxiliary Multi-Relational fields, except Contact fields Before establishing a new record in an auxiliary table, SBM searches for case-insensitive values in the system Title field.
State States can be inherited through parent workflows. Before establishing a new records in the system TS_STATES table, SBM searches for case-insensitive state names for the current workflow based on the current item's project, then searches all possible inherited states.
User, Multi-User Before establishing a new user record, SBM performs two searches. First, a case-insensitive search of the login name, which must be unique, is performed. If that doesn't provide a user record, then a case-insensitive search of the full user name is performed. If multiple users result from the second search, the first user found is used. When you import from a generic source, you should map and import generic fields that provide a unique login ID, rather than just a name that might not be unique.
Multi-Group Before establishing a new group record, SBM performs a case-insensitive search of group names.
Contact The system Contacts table has three identifying columns. The source given to the Import Data Wizard is one text string from the foreign database. To determine whether this text represents an existing Contact, SBM first looks through a list of contacts that have been created for the field (using human-readable selections). If the selection isn't found, SBM then queries directly for a concatenated TS_CONTACTFIRSTNAME, TS_CONTACTMIDDLENAME, and TS_CONTACTLASTNAME and does a case-sensitive comparison between the source text and the concatenated fields. The second search is beneficial only when you import from SBM databases, where the foreign source was queried in exactly the same manner by concatenating the fields.

When attempting to create a new Contact from the single input string, SBM attempts to parse the first, middle, and last names out of the single string. SBM first attempts parsing based upon the format "Last, First Middle" and if no comma is found, attempts to parse using the format "First Middle Last" by pulling off the first and last names based upon the spaces. If you want to use the second parsing method, you must remove commas from your source data. The first format is more suitable when your source data is a generic text field; the second is more suitable when you import from another SBM database.

The Contacts field and table is a good candidate for using numeric ID mapping instead of textual matching. This can be done, at least on an SBM-to-SBM basis, by importing into the Contacts table separately as a first step and have cross-references established.The cross-references are checked before attempting to use textual matching.

Related Topics

Preparing to Import Data

Data Import Order

Considerations for All Types of Data Imports

Importing Data From A Generic ODBC Database

Importing Data From Another SBM Database