TracCloud: Rosetta: Difference between revisions

Revision as of 17:24, 14 March 2025

TracCloud: Table and Field List

This article lists fields in TracCloud grouped by table. For each field, it will show the data type, description, how the field is accessible in Twig, the API, as well as how it displays in the SFTP data export.

Tips

Hover over the field name to see an example value.

Click the header to sort by that column, shift-click to sort by multiple.

Type Definitions

Invert All Visibility

Students

Toggle Visibility

Field	Type	Description	Twig	API	SFTP Export
Sequence	bigint	Static identifier for students, unrelated to ID.		Students.Sequence
UUID	varchar(36)	Unique identifier for this user.		Students.UUID
ID	bigint	The student's ID number. Should be unique.	{{Student.ID}}	Students.ID	StudentID
Other_ID	varchar(40)	Alternative identifier for the student.	{{Student.Other_ID}}	Students.Other_ID	OtherID
Other_ID2	varchar(40)	Another alternative identifier for the student.	{{Student.Other_ID2}}	Students.Other_ID2	OtherID2
Barcode	varchar(80)	The student's barcode.	{{Student.Barcode}}	Students.Barcode	Barcode
Status	varchar(80)	Whether the student is Active or Inactive.	{{Student.Status}}	Students.Status	Status
OtherStatus	varchar(80)	Alternative/custom status.		Students.OtherStatus	StudentOtherStatus
Legal_First	varchar(80)	The student's legal first name.	{{Student.Legal_First}}	Students.Legal_First	LegalFirst
First Name	varchar(80)	The student's preferred first name.	{{Student.First_Name}}	Students.First_Name	FirstName
Last Name	varchar(80)	The student's last name.	{{Student.Last_Name}}	Students.Last_Name	LastName
Middle	varchar(80)	The student's middle name.	{{Student.Middle}}	Students.Middle	Middle
Full_Name	varchar(255)	The student's full name, formatted as "Last, First M.".	{{Student.Full_Name}}	Students.Full_Name
LastFirst	varchar(255)	The student's full name, formatted as "Last, First M".	{{Student.LastFirst}}
Full_Name2	varchar(255)	The student's full name, formatted as "First M. Last".	{{Student.Full_Name2}}	Students.Full_Name2
FirstLast	varchar(255)	The student's full name, formatted as "First Last".	{{Student.FirstLast}}
Street	varchar(250)	The student's street address.		Students.Street	Street
Apt	varchar(80)	The student's apartment number.		Students.Apt
City	varchar(120)	The student's city.		Students.City	City
State	varchar(80)	The student's state.		Students.State	State
Zip	varchar(20)	The student's zip code.		Students.Zip	ZIP
Home_Phone	varchar(80)	The student's home phone number.	{{Student.Home_Phone}}	Students.Home_Phone	HomePhone
Work_Phone	varchar(80)	The student's work phone number.	{{Student.Work_Phone}}	Students.Work_Phone	WorkPhone
Cell_Phone	varchar(80)	The student's cell phone number.	{{Student.Cell_Phone}}	Students.Cell_Phone	CellPhone
Email	varchar(120)	The student's email address, typically their campus email.	{{Student.Email}}	Students.Email	Email
Preferred	varchar(80)	The student's preferred contact method. Possible values are listed below. 1 = Home 2 = Work 3 = Cell 4 = Email 5 = Verbal	{{Student.Preferred}}	Students.Preferred
LastDateIn	date	Date of the student's last visit.		Students.LastDateIn
UserName	varchar(120)	The student's username for authentication.	{{Student.UserName}}	Students.UserName	StudentUserName
Birthdate	date	The student's birthdate.		Students.Birthdate	Birthdate
Pronouns	varchar(80)	The student's preferred pronouns.	{{Student.Pronouns}}	Students.Pronouns	StudentPronouns
Gender	varchar(80)	The student's gender.	{{Student.Gender}}	Students.Gender	Gender
Ethnicity	varchar(150)	The student's ethnicity.	{{Student.Ethnicity}}	Students.Ethnicity	Ethnicity
Major	varchar(120)	The student's major.	{{Student.Major}}	Students.Major	Major
Class	varchar(80)	The student's class.	{{Student.Class}}	Students.Class	Class
DegreeGoal	varchar(80)	The student's degree goal.	{{Student.DegreeGoal}}	Students.DegreeGoal	DegreeGoal
Cohort	varchar(80)	The student's cohort.	{{Student.Cohort}}	Students.Cohort	Cohort
College	varchar(80)	The student's college.	{{Student.College}}	Students.College	College
Grad_Und	varchar(80)	The student's graduate status.	{{Student.Grad_Und}}	Students.Grad_Und	GradUndergrad
GPA	double	The student's GPA.	{{Student.GPA}}	Students.GPA	GPA
AccumHours	double	The student's accumulated hours.		Students.AccumHours	Hours
Fund	varchar(80)	The student's fund.		Students.Fund	StudentFund
PrimaryConsultantID	bigint	Relates to Staff.Sequence.		Students.PrimaryConsultantID
FlagText	varchar(80)	The contents of the student's "Flag Information" field.		Students.FlagText
CustomData	json	All custom fields are stored in this field. Specify a custom field with its sequence number after "cf_".	{{Student.CustomData.cf_123}}	Students.CustomData->>``$.cf_123``	[Field Name]
WatchLists	json	Returns a true or false value based on whether or not the student is on the specified watch list. Hover over the name of your watch list to find its sequence number (e.g., wl_9)	{{Student.WatchLists.wl_0}}
gen_BIOConfirmed	varchar(2)	Whether or not the student has confirmed their bio.		Students.gen_BIOConfirmed
gen_BIOConfirmedDT	varchar(20)	When the student last confirmed their bio.		Students.gen_BIOConfirmedDT
CreatedDT	timestamp	When this student record was initially created in TracCloud.		Students.CreatedDT
_LastImportedDT	datetime	The last time this student record was imported into TracCloud.		Students._LastImportedDT

Visits

Toggle Visibility

Field	Type	Description	Twig	API	SFTP Export
Sequence	bigint	A unique identifier for every visit record.		Visits.Sequence	VisitID
EnteredDT	datetime	The date and time the student entered the center. This will only differ from TimeIn if your center tracks wait time.	{{Visit.EnteredDT}}	Visits.EnteredDT
EnteredTime	time	The time the student entered, formatted as "09:45am."	{{Visit.EnteredTime}}
EnteredDate	time	The date the student entered.	{{Visit.EnteredDate}}
TimeIn	datetime	The date and time the student's visit began.	{{Visit.TimeIn}}	Visits.TimeIn	DateIn and TimeIn
StartTime	time	The time the student's visit began, formatted as "09:45am."	{{Visit.StartTime}}
StartDate	time	The datethe student's visit began.	{{Visit.StartDate}}
TimeOut	datetime	The date and time the student's visit ended.	{{Visit.TimeOut}}	Visits.TimeOut	TimeOut
EndTime	time	The time the student's visit ended, formatted as "09:45am."	{{Visit.EndTime}}
EndDate	time	The datethe student's visit ended.	{{Visit.EndDate}}
isWork	tinyint	Whether or not this visit is a work visit.	{{Visit.isWork}}	Visits.isWork
StudentID	bigint	Relates to Students.Sequence (not Students.ID).		Visits.StudentID
CenterID	bigint	Relates to Centers.Sequence		Visits.CenterID
ConsultantID	bigint	Relates to Staff.Sequence		Visits.ConsultantID
RegistrationID	bigint	Relates to Registrations.Sequence		Visits.RegistrationID
ReasonID	bigint	Relates to Reasons.Sequence		Visits.ReasonID
isWaiting	tinyint	Whether or not the student is currently waiting to be seen (i.e., TimeIn not yet started).		Visits.isWaiting
WaitTime	double	How long the student waited to start their visit in minutes (TimeIn - EnteredDT).	{{Visit.WaitTime}}	Visits.WaitTime	WaitTime
Duration	int	The length of the student's visit in minutes.	{{Visit.Duration}}	Visits.Duration	Duration
TotalTime	double	The total visit time (WaitTime + Duration).	{{Visit.TotalTime}}	Visits.TotalTime	TotalTime
CustomData	json	All custom fields are stored in this field. Specify a custom field with its sequence number after "cf_".	{{Visit.CustomData.cf_123}}	Visits.CustomData->>``$.cf_123``	[Field Name]
isTerminated	tinyint	Whether or not this visit was automatically terminated by TracCloud.		Visits.isTerminated
Fund	varchar(80)	The fund for this visit.	{{Visit.Fund}}	Visits.Fund	Fund
CtrNotes	text	The notes field filled in by staff/consultants in TracCloud.	{{Visit.CtrNotes}}	Visits.CtrNotes	VisitNotes
SurveyRespID	bigint	Relates to Responses.Sequence.		Visits.SurveyRespID
TermID	bigint	Relates to Terms.Sequence.		Visits.TermID	VisitTermCode (displays Terms.TermCode)
SectionID	bigint	Relates to Sections.Sequence.		Visits.SectionID
Location	varchar(512)	The location of the visit.	{{Visit.Location}}	Visits.Location	Location
Online	tinyint	Whether or not the visit was online.	{{Visit.Online}}	Visits.Online	Online
TimeZone	varchar(120)	The time zone of this visit's time.		Visits.TimeZone
VisitDay	varchar(20)	The day of week the visit took place.	{{Visit.Day}}	Visits.VisitDay	Weekday

Appointments

Toggle Visibility

Field	Type	Description	Twig	API	SFTP Export
Sequence	bigint	A unique identifier for each appointment record.		Appointments.Sequence	ApptID
StartDT	datetime	The appointment start date and time.	{{Appointment.StartDT}}	Appointments.StartDT	DateIn and TimeIn
StartTime	time	The appointment start time, formatted as "02:00pm".	{{Appointment.StartTime}}
DisplayTime	time	The appointment start time, formatted as 200p.	{{Appointment.DisplayTime\|raw}}
StartDate	date	The appointment start date, formatted as "01/31/25".	{{Appointment.StartDate}}
DisplayDate	date	The appointment start date, formatted as "Fri, Jan 31".	{{Appointment.DisplayDate}}
Duration	double	The length of the appointment in minutes.	{{Appointment.Duration}}	Appointments.Duration	Duration
EndDT	datetime	The appointment end date and time.	{{Appointment.EndDT}}	Appointments.EndDT	TimeOut
EndTime	time	The appointment end time.	{{Appointment.EndTime}}
EndDate	date	The appointment end date.	{{Appointment.EndDate}}
Day	varchar	The appointment day of week.	{{Appointment.Day}}		Weekday
Link	varchar	A link to the appointment record in TracCloud.	{{Appointment.Link}}
StudentID	bigint	Relates to Students.Sequence (not Students.ID).		Appointments.StudentID
ConsultantID	bigint	Relates to Staff.Sequence.		Appointments.ConsultantID
VisitID	bigint	Relates to Visits.Sequence.		Appointments.VisitID
CenterID	bigint	Relates to Centers.Sequence.		Appointments.CenterID
ReasonID	bigint	Relates to Reasons.Sequence.		Appointments.ReasonID
SectionID	bigint	Relates to Sections.Sequence.		Appointments.SectionID
AvailRecID	bigint	Relates to AvailBlocks.Sequence.		Appointments.AvailRecID
RecurID	bigint	Recurring appointments that are part of the same series (e.g., weekly at 2pm) will have a matching RecurID.		Appointments.RecurID
Status	varchar(80)	The status of the appointment.	{{Appointment.Status}}	Appointments.Status	ApptStatus
Location	varchar(512)	The location of the appointment, can either be an in-person location or an online link.	{{Appointment.Location}}	Appointments.Location	Location
Fund	varchar(80)	The appointment fund.	{{Appointment.Fund}}	Appointments.Fund	Fund
Online	int	Whether or not the appointment is online.	{{Appointment.Online}}	Appointments.Online	Online (displays as "Yes" or blank)
OnlineURL	varchar(512)	The online meeting link for the appointment. This will be blank for in-person appointments. The Twig tag will contain a TracCloud link that tracks attendance when the student joins rather than a direct link to the meeting room.	{{Appointment.OnlineLink}}	Appointments.OnlineURL	ApptOnlineURL
OtherNotes	text	Student cancellation reason.	{{Appointment.OtherNotes}}	Appointments.OtherNotes	OtherNotes
Type	varchar	The type of appointment. 1-on-1, Group, etc.	{{Appointment.Type}}		ApptType
Icon		An icon that indicates the appointment type, such as .	{{Appointment.Icon\|raw}}
hasDocument	varchar	Whether or not the appointment has a document uploaded, displayed as "true" or "false".	{{Appointment.hasDocument}}
OnlineText	varchar	If the appointment is online, this tag will display as "Online", otherwise it will be blank.	{{Appointment.OnlineText}}
isRecurring	varchar	Whether or not the appointment is recurring, displayed as "true" or "false".	{{Appointment.isRecurring}}
RecurringDates	varchar	An array of recurring appointments in the series.	{{Appointment.RecurringDates}}
recurFirstDate	date	The first date of a recurring appointment series.	{{Appointment.recurFirstDate}}
recurLastDate	date	The last date of a recurring appointment series.	{{Appointment.recurLastDate}}
isCancelled	varchar	Whether or not the appointment is currently canceled, displayed as "true" or "false".	{{Appointment.isCanceled}}
isMissed	varchar	Whether or not the appointment is currently missed, displayed as "true" or "false".	{{Appointment.isMissed}}
autoCanceled	varchar	Whether or not the appointment was automatically canceled due to max cancel/missed in recurring series rules, displayed as "true" or "false".	{{Appointment.autoCanceled}}
SchedDT	datetime	The date and time this appointment was scheduled.	{{Appointment.SchedDT}}	Appointments.SchedDT	ApptScheduledDT
SchedModDT	datetime	The date and time this appointment was last modified	{{Appointment.SchedModDT}}	Appointments.SchedModDT	ApptModifiedDT
SchedUser	varchar(80)	The user who originally scheduled this appointment.	{{Appointment.SchedUser}}	Appointments.SchedUser	ScheduledBy
SchedNotes	text	Unused.			SchedNotes
SurveyRespID	bigint	Relates to Responses.Sequence.		Appointments.SurveyRespID
CustomData	json	All custom fields are stored in this field. Specify a custom field with its sequence number after "cf_".	{{Appointment.CustomData.cf_123}}	Appointments.CustomData->>``$.cf_123``	[Field Name]
SchedModBy	varchar(80)	The user who last edited the appointment.	{{Appointment.SchedModBy}}	Appointments.SchedModBy	ApptModifiedBy

Availabilities

Toggle Visibility

Field	Type	Description	Twig	API
Sequence	bigint	A unique identifier for every availability record.		AvailBlocks.Sequence
FromDT	datetime	The starting date and time of this availability block.		AvailBlocks.FromDT
ToDT	datetime	The ending date and time of this availability block.		AvailBlocks.ToDT
Increments	varchar(5)	The increment duration in minutes.		AvailBlocks.Increments
Breaks	varchar(5)	The delay between increments in minutes.		AvailBlocks.Breaks
DAYS	varchar(30)	Which days of the week this availability is available on.		AvailBlocks.Days
isAvail	int	0 = Reserve block, 1 = Regular availability, 2 = Stacked availability.	{{AvailBlock.isAvail}}	AvailBlocks.isAvail
BlockType	varchar(256)	Reserve block description.		AvailBlocks.BlockType
BlockStatus	varchar(80)	Reserve block appointment status.		AvailBlocks.BlockStatus
MaxStudents	int	The number of available slots at this time.	{{AvailBlock.MaxStudents}}	AvailBlocks.MaxStudents
ConsultantID	bigint	Relates to Staff.Sequence.		AvailBlocks.ConsultantID
CenterID	bigint	Relates to Centers.Sequence.		AvailBlocks.CenterID
ReasonID	bigint	Relates to Reasons.Sequence.		AvailBlocks.ReasonID
SectionID	bigint	Relates to Sections.Sequence.		AvailBlocks.SectionID
AvailSubj	varchar(256)	The availability subject, including wildcard entries.		AvailBlocks.AvailSubj
RecurType		The type of recurring appointment. The possible options are listed below. None Daily Weekly Bi-Weekly Optional Daily Optional Weekly Optional Bi-Weekly Optional Daily Chosen Optional Weekly Chosen Optional Bi-Weekly Chosen		AvailBlocks.RecurType
Location	varchar(512)	The in-person location for this availability.		AvailBlocks.Location
OnlineURL	varchar(512)	The online location for this availability.		AvailBlocks.OnlineURL
Fund	varchar(80)	The availability fund.		AvailBlocks.Fund
isInperson	tinyint	Whether or not the availability is in-person.		AvailBlocks.isInperson
isOnline	tinyint	Whether or not the availability is online.		AvailBlocks.isOnline
isAsynch	tinyint	Whether or not the availability is asynchronous.		AvailBlocks.isAsync
defLocChoice	int	The default location choice. 0 = in-person, 1 = online, 2 = asynchronous.		AvailBlocks.defLocChoice
isMeeting	tinyint	Whether or not a reserve block is designated as as meeting block.		AvailBlocks.isMeeting

More coming soon!

@@ Line 425: / Line 425: @@
 |-
 | <span title="2025-01-31 02:00:00">_LastImportedDT</span> || datetime || The date and time this record was last imported. || || Courses._LastImportedDT ||
-CustomData json
+|-
-RelatedCoursesID varchar(20)
+| <span title="This can be anything.">CustomData</span> || json || All [[TracCloud:_Custom_Fields|custom fields]] are stored in this field. Specify a custom field with its sequence number after "cf_".|| <nowiki>{{Course.CustomData.cf_123}}</nowiki> || Courses.CustomData->>``$.cf_123`` || [Field Name]
+|-
+| <span title="A123B456C">RelatedCoursesID</span> || varchar(20) || The course family identifer. || || Courses.RelatedCoursesID ||
 |}
@@ Line 434: / Line 436: @@
 ! <b>Field</b> !! <b>Type</b> !! <b>Description</b> !! <b>Twig</b> !! <b>API</b>
 |-
+| <span title="1234">Sequence</span> || bigint || A unique identifier for each section. || || Sections.Sequence ||
+|-
+| <span title="0">Inactive</span> || tinyint || Whether or not this section is currently inactive. || || Sections.Inactive ||
+|-
+| <span title="A01">Code</span> || varchar(80) || The section code/number. || <nowiki>{{Section.Code}}</nowiki> || Sections.Code || Section
+|-
+| <span title="1234">CourseID</span> || bigint || Relates to Courses.Sequence. || || Sections.CourseID ||
+|-
+| <span title="1234">GradeGroupID</span> || bigint || Relates to GradeGroups.Sequence. || || Sections.GradeGroupID ||
+|-
+| <span title="12345">CRN</span> || varchar(80) || The CRN of this section record. || <nowiki>{{Section.CRN}}</nowiki> || Sections.CRN || CRN
+|-
+| <span title="1234">TermID</span> || bigint || Relates to Terms.Sequence (not Terms.TermCode). || || Sections.TermID ||
+|-
+| <span title="1234">FacultyID</span> || bigint || Relates to Sections.FacultyID (not Faculty.UserID). || || Sections.FacultyID ||
+|-
+| <span title="0">nonEnrolled</span> || tinyint || Whether or not this is a [[TracCloud:_Non-Enrolled_Courses|non-enrolled course]]. || || Sections.nonEnrolled ||
+|-
+| <span title="This can be anything.">CustomData</span> || json || All [[TracCloud:_Custom_Fields|custom fields]] are stored in this field. Specify a custom field with its sequence number after "cf_".|| <nowiki>{{Section.CustomData.cf_123}}</nowiki> || Sections.CustomData->>``$.cf_123`` || [Field Name]
+|-
+| <span title="2025-01-31 02:00:00">CreatedDT</span> || timestamp || When this section record was initially created. || || Sections.CreatedDT ||
+|-
+| <span title="0">ignoreRegImportDeactivate</span> || tinyint || Whether or not this section is configured to ignore deactivations. || || Sections.ignoreRegImportDeactivate ||
+|-
+| <span title="100">Credits</span> || double || The potential credits for this section. || || Sections.Credits ||
+|-
+| <span title="2025-01-31 02:00:00">_LastImportedDT</span> || datetime || The latest date and time this record was imported into TracCloud. || || Sections._LastImportedDT ||
+|-
+| <span title="Introduction to Biology">Title</span> || varchar(120) || This optional field overrides Courses.Title if present. || <nowiki>{{Section.Title}}</nowiki> || Sections.Title ||
 |}
@@ Line 441: / Line 472: @@
 ! <b>Field</b> !! <b>Type</b> !! <b>Description</b> !! <b>Twig</b> !! <b>API</b>
 |-
+| <span title="1234">Sequence</span> || bigint || A unique identifer for this term record. || || Terms.Sequence ||
+|-
+| <span title="1">Active</span> || tinyint || Whether or not this term is currently active. || || Terms.Active ||
+|-
+| <span title="2025SP">TermCode</span> || varchar(80) || The term/semester code. || <nowiki>{{Term.TermCode}}</nowiki> || Terms.TermCode || Term
+|-
+| <span title="2025-01-31">ActiveFrom</span> || date || The start date of this term. || || Terms.ActiveFrom ||
+|-
+| <span title="2025-03-31">ActiveTo</span> || date || The end date of this term. || || Terms.ActiveTo ||
 |}
@@ Line 448: / Line 488: @@
 ! <b>Field</b> !! <b>Type</b> !! <b>Description</b> !! <b>Twig</b> !! <b>API</b>
 |-
+| <span title="1234">Sequence</span>|| bigint || A unique identifier for this registration record. || || Registrations.Sequence ||
+|-
+| <span title="0">Inactive</span> || tinyint || Whether or not this registration is currently inactive. || || Registrations.Inactive ||
+|-
+OverrideDate tinyint
+SectionID bigint
+StudentID bigint
+Grade varchar(40)
+ValidFromDT datetime
+ValidToDT datetime
+Notes text
+CustomData json
+_LastImportedDT datetime
+RegStatus varchar(40)
+EarnedCredits double
 |}
@@ Line 475: / Line 530: @@
 Pronouns varchar(80)
 _LastImportedDT datetime
-PassLastDateChanged date
-PassFailedAttempts int
-AccountLocked tinyint
-AccountLockDateTime datetime
 |}

Type	Description
varchar(#)	Alphanumeric field with a specified maximum length.
int	Integer, maximum value of about 4 billion, or a 10-digit numeric value.
bigint	Big integer, maximum value of about 9 quintillion, or a 16-digit numeric value.
tinyint	Tiny integer, typically used to store a 1 or 0 as true or false.
date	Date, formatted as YYYY-MM-DD
time	A specific time using a 24-hour format (HH:MM:SS), e.g., 13:45:00
datetime	Date and time, YYYY-MM-DD HH:MM:SS
double	Decimal # with up to 12 digits precision
json	JSON-formatted field that contains multiple fields and values (e.g., custom fields).

TracCloud: Rosetta: Difference between revisions

From Redrock Wiki

Revision as of 17:24, 14 March 2025