225x Filetype XLSX File size 1.12 MB Source: www.courts.ca.gov
Sheet 1: File Summary Page
Source of Data: |
eVision Access Database | Summary Description of Tables Contents: | Real Estate projects such as new construction, leases and other real estate projects for REO, planners and Real Estate Manager. Tracks work in progress as well as completed work, providing ability to produce reports and dashboard of work product complete with phases, milestones and additional lists. | ||||||||||
Database Size | 19,464 KB | Summary Description of Tables Purpose: | Tracking Projects: Data, Contacts, Activity, Status Updates, Lease associations, Time Assignments, Budget Management, Close out Processing, Contact Data Updates, Project Assignments, Agency Reports, and Change Requests. Providing history of ALL projects, during all phases, even cancelled. Provides WIP for Real Estate User, Planners and Real Estate Management, and retains history of contacts, activities and document management. eVision tracks and sends emails, auto populates documents and saves to network folders. Provides reports: Agency Reports - Status Report, Regional Reports Management Reports - Project Schedule, Over Budget, Status Staff Reports - Project Detail, Contacts, Activity Variance Reports - Budget Percentage, Complete or Cancelled Provides dashboard graphs: examples - Total Assignments and Weeks of Work by Unit, Project Allocation per REO, Project Allocation by Unit, Weeks of work by by FIscal Year |
||||||||||
Parent or Child or null: | See Table | Related File Name(s): | Projects Projects Status with dates Project Notes - Activity - projected, current and past Project Contact Agency/Agency Region Contacts/Contact Type/tbl_log_on_off Lease Contact Project Leases Leases Space Type Lease Status Lease Notes Building Word Document Templates - produced to be filed and/or sent via email. Some documents Archived to network folders. |
||||||||||
Other related smaller tables: | Milestone Phase Agency Region Contact Type (See Data Examples and Table) |
Comments or other Pertinent information: | eVision sends emails, auto populates document templates to send out. Document management - creates document, creates folders and saves document to network folders. |
Table/FileName: Project | ||||
Field Name | Type of Field | Related Table/Key | Description | |
ProjectID | Number/Key | Project Lease/Lease Project Status Project Notes |
Number sequential key for Projects File | |
ProjectName | Text | Name of Real Estate Project - could be a lease, acquisition, etc | ||
Category | Project Category | Lookup from Project Category File | ||
ProjectLeaseID | Key | Ties Project and a Lease record together via the Lease ID number | ||
Agency | Key | Building Table | Says Agency, but data is populated with Building ID | |
Unit/Division | Text | Building Name | ||
Address | Text | Building Address | ||
City | Text | Building City | ||
State | Text | Building State | ||
Zip | Number | Building Zip Code | ||
County | Text | Building County | ||
SqFt | Number | Project Sq Ft | ||
Rent | Number | Rent Amount | ||
PrintFlag | FALSE or TRUE - all records reviewed were FALSE | |||
Status | Text | Lookup from from Project Status table, however there are additonal statuses not in table - perhaps lookup or freetype | ||
Milestone | Text | Milestone | Lookup from Milestone File | |
Phase | Text | Phase | Project Phase Description or blanks | |
DateInactive | Text | Date project inactivated | ||
REOToDo | Text | Real Estate Analyst To Do, data surveyed was blank | ||
PlannerToDo | Text | Planner to Do, data surveyed was blank | ||
PriorityREO | Text | TRUE, FALSE - all data surveyed were FALSE | ||
PriorityPlanner | Text | TRUE, FALSE - all data surveyed were FALSE | ||
REOToDoDate | Date | To do date for Real Estate Analyst | ||
PlannerToDoDate | Date | TO do date for Planner | ||
BeginDate | Date | Project Begin Date | ||
ExpirationDate | Date | Project Expiratin DATe | ||
FirmExpiration | Date | Firm Expiration Date | ||
DaysToCancel | Number | Number of days to cancel | ||
Urgent | Text | TRUE, FALSE - all data surveyed were FALSE | ||
AgencyContact | Number | Key | Agency record number or blanks | |
iABMS | Text | TRUE, FALSE - all data surveyed were FALSE | ||
AgencyReport | Text | TRUE, FALSE | ||
Structure | Text | Data surveyed was blank | ||
DateRecieved | Date | Date project was received by Planner or Real Estate | ||
%Complete_L | Number | Percentage of Completion for Lease | ||
%Complete_P | Number | Percentage of Completion for Project | ||
Estimate Completion | Date | Date project estimated to be completed | ||
EstCloseOutDate | Date | Date project estimated to be closed | ||
AgencyRegion | Text | Populated by agency choice | ||
CruiseID | Text | Not interfacing with this system | ||
LeedRequired | Text | TRUE, FALSE - all data surveyed were FALSE, Table relationships indicte removal of Leed file | ||
CloseOutLog | Text | TRUE, FALSE - all data surveyed were FALSE | ||
LumpSum | TExt | Data surveyed - 1 record has 0 and the rest are blanks | ||
Section | Text | Lookup from REGION TABLE, but has 1, 4 and 5 in addition to valid regions | ||
Funding | Text | Surveyed Data appears to be free text | ||
Master_Project_Number | Key | Project (this file) | Lookup Project file for Project ID | |
Unit | Text | County number space County Name - example 23 Mendocino | ||
ExhibitDates | Date(s) | Data surveyed was blank | ||
Scope | Text | |||
ProjectFunding | Text | Data surveyed was blank | ||
Folder | Text | Folder Path for Project | ||
Other Party | Text | Organization File | Organization Name or Free Typed | |
CapitalProject | Text | TRUE, FALSE | ||
OptionType | Text | Data surveyed was blank | ||
EventDate | Date | Data surveyed was blank | ||
Table/FileName: Project Status with Dates | ||||
Field Name | Type of Field | Related Table/Key | Description | |
StatusID | Number | Key | Sequentially numbered key | |
ProjectID | Number | Key/Project | Project ID # from Project File | |
DateChanged | Date | Date of recorded status change | ||
Status | Text | Free Type Status | ||
Name | Text | user_action | First Name Last Name | |
Name_osu | Text | Log_on_off | User Logged in from Log_on_of table | |
Table/FileName: Project Contact | ||||
Field Name | Type of Field | Related Table/Key | Description | |
ProjectID | Number | Key/Project | Project ID # from Project File | |
Contact ID | Number | Key/Contact | Contact ID for linking to Contacts file | |
ContactTypeID | Number | Key/Contact Type | Lookup Contact Types and select | |
SendEmail | Text | TRUE, FALSE | ||
PersonID | Number | Person ID number, but most of data surveyed was blank | ||
Table/FileName: Project Lease | ||||
Field Name | Type of Field | Related Table/Key | Description | |
ProjectID | Number | Key/Project | Project ID # from Project File - links lease to a project | |
Lease ID | Number | Key/Leases | Lease ID# from Lease File - links lease to a project | |
Table/FileName: Project Notes | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Note ID | Number | Key | Sequential number key | |
ProjectID | Number | Key/Project | Project ID # from Project File - links lease to a project | |
Notes | Text | |||
DateChanged | Date | |||
Notes | Text | |||
Table/FileName: tblMilestone | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Milestone | Key | Milestones in text to use to lookup in Project File | ||
SortOrder | Number | Number to sort records, rather than alpha sort | ||
cboDisplay | Text displayed in lookups | |||
Table/FileName: tblPhase | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Phase | Key | Phases in text to useto lookup from Project File | ||
Sort Order | Text | Number to sort records, rather than alpha sort | ||
cboDisplay | Text | Text displayed in lookups | ||
Table/FileName: Leases | ||||
Field Name | Type of Field | Related Table/Key | Description | |
LeaseID | Text/Key | Project Lease/Project | Number sequence with L at end or text sequece such as DGS with L at end | |
ProjectID | Text | Project Lease/Project | Each Lease is a real Estate project | |
BuildingID | Key | Locations Table | Key to Building Table, Locations in CAFM 1.0 | |
LeaseName | Text | Name of lease | ||
ExpenseRevenue | Text | Expense, Revenue | ||
BuildingName | Text | Building Name | ||
Start Date | Date | Lease start date | ||
End Date | Date | Lease End date, expiration date | ||
Status | Number | 0, -1 | ||
PAA | Text | Real Estate Analyst assigned toLease | ||
Data pulled into Lease from other files - building, project, contact | ||||
Address | Building | |||
City | ||||
County | Text | |||
Zip | Text | |||
FirmExpiration | Text | |||
ManagingAgency | Number | |||
LeaseAgency | Text | |||
PortfolioRegion | Number | |||
PortfolioRegionCode | ||||
DaysToCancel | ||||
FY_Due | ||||
Contact# | Key | Contact | ||
Contact1 | ||||
Contact2 | ||||
ContactAddress1 | ||||
ContactAddress2 | ||||
ContactCity | ||||
ContactState | ||||
ContactZip | Date | |||
Phone | Number Formatted | |||
Fax | Number Formatted | |||
Terminated | Number/Date Calc | |||
Agency | ||||
SqFt_new | Number | |||
SqFt_old | Number | |||
Rent_new | Number | |||
Rent_old | Number | |||
DateRecieved | ||||
%Complete_L | Number | |||
%Complete_P | Number | |||
Estimate Completion | ||||
EstCloseOutDate | Date | |||
AgencyRegion | ||||
CruiseID | ||||
LeedRequired | ||||
CloseOutLog | ||||
LumpSum | ||||
Section | ||||
Funding | ||||
Table/FileName: Space Type | ||||
Field Name | Type of Field | Related Table/Key | Description | |
SpaceTypeID | Number/Key | Number sequential key | ||
LeaseID | Text/Key | Lease | Lease ID links Space type with Lease | |
ProjectID | Number | Key/Project | Project ID links Space type with Project | |
SpaceType | Text | Key in space types, could be classification | ||
Units | Number | |||
UnitDescription | Text | UOM of Units, example - square feet | ||
Rent | Number | Rent Amount | ||
DateAdded | Date | Date Speace type record added | ||
Table/FileName: Lease Contact | ||||
Field Name | Type of Field | Related Table/Key | Description | |
LeaseID | Text/Key | Lease | Lease ID links Contact file with Lease File | |
Contact ID | Key | Contact | links Contact File with with Lease Contact File | |
ContactTypeID | Key | ContactType | links Contact File with with Lease Contact Type | |
SendEmail | Text | TRUE, FALSE | ||
Table/FileName: LeaseLLContact | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Contract Status | Key | Status of Contract: Active, Inactive, blank | ||
Building ID | Key | Locations Table | Key to Building Table, Locations in CAFM 1.0 | |
Court Building Name | Text | Court Building Name | ||
Address | Text | Address | ||
City | Text | City | ||
PAA | Text | Real Estate Analyst assigned to Lease | ||
Lease ID | Text/Key | Lease | Lease ID links Lease LL Contact file with Lease File | |
Lease Name | Text/Key | Lease | Lease ID links Lease Land Lord Contacts with Lease | |
Revenue/Expense | Text | Expense, Revenue, blank | ||
Current Term Start | Date | Start of Lease Term | ||
Curent Term End | Date | End of Lease Term | ||
Landlord Name | Text | Landlord Name | ||
Landlord Legal Name | Text | Landlord Legal Name | ||
Attention To | Text | Attention To | ||
Landlord Address | Text | Landlord Address | ||
Landlord City | Text | Landlord City | ||
Landlord State/Province | Text | Landlord State/Province | ||
Landlord Zip/Postal Code | Number | Landlord Zip/Postal Code | ||
Landlord Phone | Number Formatted | Landlord Phone | ||
Landlord Fax | Number Formatted | Landlord Fax | ||
Table/FileName: Status_lm (aka Lease Status with Dates) | ||||
Field Name | Type of Field | Related Table/Key | Description | |
StatusID | Number | Key to Status, number | ||
Lease ID | Text/Key | Lease | Lease ID links Lease LL Contact file with Lease File | |
DateChanged | Date | Date status was modified | ||
Status | Text | Keyed in status of lease | ||
Name | Text | First Name Last Name of user updating status | ||
Name_osu | Text | Log_on_off | User Logged in from Log_on_of table | |
Table/FileName: Notes_lm ( aka Lease Notes) | ||||
Field Name | Type of Field | Related Table/Key | Description | |
LeaseID | Text/Key | Lease | Lease ID links Lease allows Lease Notes to be associated with Lease | |
Notes | Text | Notes text added, can have more than one note per lease | ||
Table/FileName: Contact | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Contact ID | Key | Log_on_off/Contact Type Lease Contact Project Contact Building Contact |
Contact ID to link contact to various related files | |
First Name | Text | Contact Last Name | ||
Last Name | Text | Contact First Name | ||
ContactTypeID | Key | Contact Type | Links Contact Type file and contact file | |
Organization | Text | Organization | Organization Name or Free Typed | |
WorkPhone | Number Formatted | Work Phone | ||
CellPhone | Number Formatted | Cell Phone | ||
Text /Link | Email address of contact | |||
StreetAddress | Text | Street Address | ||
City | Text | City | ||
State | Text | State | ||
Zip | Text | Zip Code | ||
Fax | Phone Number | Fax | ||
Notes | Text | Comments pertinent to contact | ||
ComputerNumber | Text | Some contacts are internal user, used for log_on_off user actions and pulling user name | ||
PrintFlag | Text | TRUE, FALSE - Data surveyed had all FALSE | ||
SendEmail | Text | TRUE, FALSE - Data surveyed had all FALSE | ||
osUserName | Text | System UserName signed in - internal user, used for log_on_off user actions and pulling user name | ||
Supervisor | Number | Supervisor ContactID number, to link for supervisor transaction reports and management of work product | ||
DGS | Text | TRUE, FALSE - Data surveyed had all FALSE | ||
Person_ID | Number | Key | File name needed | |
AdminRights | Text | TRUE, FALSE | ||
UserClass | Text | Admin User, Read Only, User | ||
IMS | Text | User ID for external interface system IMS | ||
Coorespondence | Text | TRUE, FALSE - Data surveyed had all FALSE | ||
FiscalServices | TRUE, FALSE - Data surveyed had all FALSE | |||
Section | Text | Data surveyed: RELPS, *, BANCRO, RPSS, SOLD, SRO | ||
Unit | Surveyed Data appears to be free text | |||
Task | Number | Number or blank | ||
ABMS_ID | Number | User ID for external interface system IMS | ||
StartDate | Date | Contact Start Date | ||
EndDate | Date | Contact End Date | ||
Title | Text | Contact Title | ||
Table/FileName: Contact Types | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Contact Type ID | Number/Key | Number sequential key | ||
ContactType | Text | Contact Type displayed for lookups | ||
cbo Display | Text | Text displayed in lookups | ||
Table/FileName: tbl_log_on_off | ||||
Field Name | Type of Field | Related Table/Key | Description | |
log_on_off_ID | Number/Key | Number sequential key | ||
ContactID | Key | Contact | Contact ID links with Lease Contact File | |
Timeon | Date/Time | Time logged in | ||
TimeOff | Date/Time | Time logged out | ||
osUserName | Text | From Contact file | ||
WorkPhone | Number Formatted | From Contact file | ||
Text | From Contact file | |||
Machine Number | Text | From Contact file | ||
Table/FileName: tblAgency | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Agency | Key/Text | Using Building ID number, plus BLD 99-XX | ||
Description | Text | Building Name, plus Unknown TBD | ||
cboDisplay | Text | Display Bldg ID, plus BLD 99-XX | ||
Agency Name | Text | County Nu,ber (space) County Name, example = 01 Alameda, also Unknown TBD | ||
CA | Data surveyed was blank | |||
BU | Data surveyed was blank | |||
Table/FileName: tblAgencyRegion | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Agency | Key | Regency ID number | ||
Region | Text | Region names | ||
SortOrder | Number | Number to sort records, rather than alpha sort | ||
osUserName | Text | Region names for display in lookup | ||
Table/FileName: Building ( data brought in from CAFM 1.0 Location/Acquisition) | ||||
Field Name | Type of Field | Related Table/Key | Description | |
BldgID | Key/Text | Key to Building in CAFM 1.0 Location for use in Projects and Lease | ||
County | Text | From County List CAFM 1.0 | ||
Region | Key | RegionTable & Region List in CAFM 1.0 | ||
Facility Ops Region | Text | JCC Region Name from CAFM 1.0 Location | ||
BldgName | Text | Building Name from CAFM 1.0 Locations | ||
Address | Text | Address from CAFM 1.0 Locations | ||
City | Text | City from CAFM 1.0 Locations | ||
Org | Text | Key | Oranizatin Type from CAFM 1.0 Locations | |
Owned/Leased | Text | Owned/Leased from CAFM 1.0 Locations | ||
Title Holder | Text | Title Holder from CAFM 1.0 Locations | ||
Managing Party | Text | Managing Party from CAFM 1.0 Locations | ||
JCC % Shared Cost | Number | JCC % Shared Cost from CAFM 1.0 Locations, number with 2 digit decimal place | ||
Exclus Court Space (SF) | Number | Exclusive Court Space square feet from CAFM 1.0 Locations | ||
Exclus Non-Court Space (SF) | Number | Exclusive Non- Court Space square feetAddress from CAFM 1.0 Locations | ||
Building Gross (SF) | Number | Gross square feet from CAFM 1.0 Locations | ||
Building Notes | Text | Building Notes from CAFM 1.0 Locations | ||
Bldg Type | Building Type from CAFM 1.0 Locations | |||
Orig Constrct Yr | Number | Original Construction Year from CAFM 1.0 Locations | ||
Ownership | Text | Ownership from CAFM 1.0 Locations | ||
Folder | Text | Building Folder Name on shared drive | ||
Unit | Text | County number (space) County Name, example 01 Alameda | ||
CFP | Number | Cost for Project | ||
AFA | Text | Real Estate Analyst in charge of Building, initials of analyst or blank | ||
Bond Funded | Text | TRUE, FALSE | ||
Bond Closing Date | Date | Date formatted MM/DD/YYYY or blank | ||
Bond Year/Series | Text | Year and Series Name, example 2012 Series G | ||
FMA | Text | JCC Facilities Mangement Administator | ||
Oracle ID | Text | Oracle ID from CAFM 1.0 Locations | ||
NumberOfCourtrooms | Number | Number of Court Rooms from CAFM 1.0 Locations | ||
Historic | Text | TRUE, FALSE from CAFM 1.0 Locations | ||
SeismicStatus | Text | Seismic Status DSA from CAFM 1.0 Locations | ||
FEMA Seismic P-154 Score | Text | FEMA Seismic P-154 Score from CAFM 1.0 Locations | ||
Seismic Risk Rating | Text | Seismic Risk Rating from CAFM 1.0 Locations | ||
DateTitleAcquired | Date | Date Title Acquired from CAFM 1.0 Locations/Acquisition | ||
SB17Transfer | Text | SB17 Transfer from CAFM 1.0 Locations/Acquisition | ||
AcquireType | Text | Date Title Acquired from CAFM 1.0 Locations/Acquisition = TRUE, FALSE | ||
FiscalLocationID | Text | Fi$cal Location ID from CAFM 1.0 Locations, used to interface with State of California Fi$cal System | ||
Status | Text | Status from CAFM 1.0 Locations, status of building record - only Active pulled into eVision | ||
BondIndebtedness | Text | TRUE, FALSE | ||
MaturityDate | Date | Bond Maturity Date | ||
Acquisition Date | Date | Acquisition Date from CAFM 1.0 Locations/Acquisition | ||
Table/FileName: Lease Transactions | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Region / JCC Region | Text | Region and JCC Region combined. JCC Region from CAFM 1.0 Region & JCC Region, example - BANCRO, 3 - West | ||
County | Text | |||
RE Lead | Text | |||
AFA | Text | |||
JCC LSE No | Text | Key | ||
CAFM Bldg Id | Text | Building ID | ||
Field7 | Text | Bldg Name | ||
Cate- gory | Text | |||
Transaction Type | Text | |||
Other Party | Text | |||
Street Address & City | Text | |||
Status | Text | |||
Comments | Text | |||
Fee / No Fee | Text | |||
Proj Lease Comm Date | Date | |||
Est Lease Execution | Date | |||
Field17 | Text | Data Surveyed | ||
CFR / IBA | Text | Data surveyed - N, Y, n/a, ?, Rev, blanks | ||
LS | Text | Legal Services assigned - first initial last name, first name, n/a, TBD, SW, blanks | ||
Field 20 - Field 255 | Blank fields | |||
Table/FileName: Region | ||||
Field Name | Type of Field | Related Table/Key | Description | |
SupervisorID | ||||
Region | ||||
Table/FileName: Project Date | ||||
Field Name | Type of Field | Related Table/Key | Description | |
ProjectID | Number | Key/Project | Project ID # from Project File | |
OwnerID | Key/Number | Owner ID number ID | ||
Followup | Date | Date of Follow Up MM/DD/YYYY | ||
osUserName | Text | Data surveyed all blank | ||
Table/FileName: Project Category | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Code | Text | Numbers, and 1 record with * to display header "All Categories" | ||
ProjectCategoryID | Number/Key | Number sequential key | ||
ProjectCategory | Text | For lookp in project file | ||
cboDisplay | Text | Project Category display name in lookup | ||
Table/FileName: Lease Folder | ||||
Field Name | Type of Field | Related Table/Key | Description | |
F1 | Number | Lease | Lease ID Number | |
F2 | Text | Folder Name | ||
F3 | Text | Unlabeled field no data | ||
F4 | Text | Unlabeled field no data | ||
Table/FileName: BuildingContact | ||||
Field Name | Type of Field | Related Table/Key | Description | |
BldgID | Text/Key | Building | Building ID number key to Building | |
ContactID | Number | Contact | Contact ID number key to Contact type | |
ContactTypeID | Number | Contact Type | Contact Type ID number key to Contact type | |
SendEmail | Text | TRUE, FALSE | ||
Workphone | Number Formatted | Contact Work Phone | ||
Cellpone | Number Formatted | Contact Cell Phone | ||
Text | Contact Email Address | |||
Table/FileName: BuildingNote | ||||
Field Name | Type of Field | Related Table/Key | Description | |
NoteID | Number/Key | Number sequential key | ||
BldgID | Text/Key | Building | Building ID links CAFM 1.0 Location | |
DateChanged | Date | Date of Building Note | ||
Note | Text/Key | Note or comment regarding building | ||
Name | Text | Name of user logged in making note | ||
Name_osu | Text/Key | Log_on_off | User Logged in from Log_on_of table | |
Table/FileName: BondFundedBuildings | ||||
Field Name | Type of Field | Related Table/Key | Description | |
Year / Series | Text | Bond Year and Series to populate to same field in Building File | ||
County | Text | Building | Building County | |
Facility | Text | Building | Facility is Building name from CAFM 1.0 Locations | |
Facility # | Text | Building | Facility is Building ID number from CAFM 1.0 Locations | |
Closing Date | Date | Date Bond closes | ||
Re-Fi | Text | Season Refi completed - Spring, Summer, Fall, Winter and Year, example Spring 2017 | ||
Table/FileName: User_Action | ||||
Field Name | Type of Field | Related Table/Key | Description | |
user_action_ID | Number/Key | Sequentially numbered key | ||
DateTime | Data | Date of User Action | ||
User | Text | Initial and Last Name from | ||
Control | Text | Related Table | Describes Action, exampele - Main Menu - Open the My Projects form |
no reviews yet
Please Login to review.