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.
No matter what version of XenDirect your are licensed for, all the tables exist even for features not available in Plus and Basic. Since those features are not available in Plus and Basic, the related tables will be empty when accessing via ODBC or InSight.
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.