Fields, Record IDs, Relationships, and Joins

Fields and Field Types

There are over 3,000 fields in over 175 data sources within the InSight Reporting Management System (RMS). Not every field that exists in XenDirect is available within InSight. XenDirect fields that are not within InSight can be added. The process for adding new fields occurs about every 4-5 months. If there are XenDirect fields you want to be added that do not appear within InSight, submit a suggestion to add those in the next field insert process.

Most fields accurately reflect the contents based on the field name. Fields are labeled with icons to display the type of field including:

Text
Numbers
Dates
Money (Most financial fields are Numeric fields.Only a few are tagged as Money.)
Text Areas (Large text values such as a Client Case Note or Course Description)

Primary Keys

Many of the data sources also include hidden linking fields. The hidden fields are called Primary Keys or record IDs. They are used to link related records in various tables. Primary Keys are recorded as numeric integers, are unique to each record, and never duplicate within the same data source table. These are usually the fields you would use to link related tables to each other in the data sources section of the InSight report designer.

Here is a list of the Primary Keys used within InSight data sources.

Primary Key	Primary Table
accID	Accounts
appID	Client Application
assID	Client Assignment
braID	Branch
BranchID	Branch
calID	Calendar
CartID	Cart
claID	Classification
cliID	Client
codID	Attendance Code
conID	Contract
couID	Course (or Country)
cprID	Client Program
cscID	Section Coursework
cspID	Section Skills
depID	Department
disID	Discount
empID	Client Employment
evqID	Evaluation Question
evtID	Evaluation Template
genID	General Ledger (payments and expenses)
graID	Grade Code
groID	Fee Group
homID	Home School
invID	Invoices
jobID	Job Openings
masID	Checklists
merID	Merge templates
msbID	SMS Text Message
orgID	Organization
polID	Policies
prgID	Program Groups
queID	Questions
revID	Revenue Items
rooID	Location
schConfID	Workshop Link to Conference (schID)
schID	Sections
sesID	Sessions
souID	Funding Sources
spoID	Companies
staID	Staff (or State)
tesID	Tests
tesLisID	Test Types
texID	Textbooks
typID	Payment Types
useID	User
zipID	Postal Codes

Relationships and Joins

Users with experience creating queries in SQL, Microsoft Access, or other relational database applications are familiar with the process of table relationships and joining related tables. Adding data sources using InSight is no different.

Users can create the following join types between related tables:

• Inner: The most commonly used join when only the records between two tables are desired.
  • Example: All Clients with Assignments
• Left: The second most commonly used join when all the records from one table are desired and then only the records that match.
  • Example: All Clients even if they do not have Assignments but with all Assignments
• Right: The opposite of a Left join. Not a commonly used join within the XenDirect database.
• Cross: Produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table. Not a commonly used join within the XenDirect database.
• Full: Returns all matching records from both tables whether the other table matches or not. Not a commonly used join within the XenDirect database.

There are seven major data areas in XenDirect.

• Clients (cliID)
• Courses (couID)
• Sections (schID)
• Companies (spoID)
• Invoices (invID)
• Transactions (genID)
• Staff (staID)

Most all other tables are either subordinate to one of these tables or are feeder tables for the select list fields within these tables. Most subordinate tables are a 1:Many relationship to the parent table.

Examples: 

• Client table is 1 client record to many records in the Client Assignment table
• Course table is 1 course record to many records in the Sections table
• Client Invoice table is 1 invoice record to many records in the Invoice Items table
• Client Invoice table is 1 invoice record to many records in the Client Payment table

To best learn and understand the table relationships, it is highly recommended to study the design of any of the pre-built XenDirect InSight reports. InSight Pro users can copy any pre-built InSight report and dissect the design without causing any harm to the original report.