TracCloud: API

From Redrock Wiki

(Redirected from TracCloudTechAPI)

TracCloud Technical Documentation

• Contents

• Mail Server

• Data Import

• Data Export

• Single Sign-on

• Custom URL

• SFTP SSH Keys

• API

• Migration from 4.0


TracCloud API

This section of our technical documentation covers TracCloud's v1 API support. For further assistance, reach out to us using the "Support" tab at the top of the page.

Sending Requests

Our API endpoint is: https://traccloud.go-redrock.com/CAMPUSCODE/app/webhook.php
Replace 'CAMPUSCODE' with your campus code, or replace traccloud.go-redrock.com/CAMPUSCODE with your custom URL if applicable.

For testing purposes, you can send the request from a browser's network tab like this.



That same request with curl looks like this. The rest of this article will use curl. Most of the examples will include line breaks for readability, it will work either way.

Updating Records (Action: update, store, create)



pass a parameter named "data" in the following format:

data={"action": "update", "APIKey": "YOUR_API_KEY", "KeyID": 1910,  "table": "Students", "Last_Name": "Halsteader"}

The response should be: 

{"status":"success","message":"data Students record updated #1910","data":{"Sequence":1910}}

Example curl command. 

curl -d 'data={
"action": "update",
"APIKey": "YOUR_API_KEY",
"KeyID": 1931,
"table": "Students",
"Last_Name": "Smith"
}' https://traccloud.go-redrock.com/demo/app/webhook.php

Available fields and their options:

  • action
update will update an existing record, if you try updating when the record does not exist, it will return an error.
store will update records, or create records if a record doesn't exist.
create will create the currently non-existent record. If you try creating when the KeyID already exists, it will return an error.

  • APIKey
Your API key.

  • KeyID
Provide the sequence number of the record being modified.

  • table
Provide the table of the record being modified (Students, Registrations, etc).

  • [field]
Provide the field you want to modify (e.g., Last_Name) as well as the value you want to set it to.

Retrieving Records (Action: query)



The next API action is “query”, which can be used to retrieve fields from records. Here's an example that finds 20 students who's ID is greater than 1. 

curl -d 'data={
    "action": "query",
    "from": "students",
    "page": 1,
    "page_recs": 20,
    "APIKey": "YOUR_API_KEY",
    "fields": ["Sequence", "First_Name", "Last_Name"],
    "order": "first_name ASC",
	"query": {
		"fields": [{
            "field": "Sequence",
		    "value": "1",
		    "op": ">"
        }]
    }
}' https://traccloud.go-redrock.com/demo/app/webhook.php

This query will return the following. 

{"status":"success","message":"data queried","data":
[{"Sequence":1901,"First_Name":"Jackson","Last_Name":"Gomez-Lopez"},
{"Sequence":1910,"First_Name":"Angi","Last_Name":"Halsteader"},
{"Sequence":1911,"First_Name":"Ou00edhare","Last_Name":"Wallace"},
{"Sequence":1912,"First_Name":"Joe","Last_Name":"BarraganIII"},
{"Sequence":1913,"First_Name":"Lori","Last_Name":"Train"},
{"Sequence":1914,"First_Name":"John","Last_Name":"Cunningham"},
{"Sequence":1915,"First_Name":"Jaime","Last_Name":"Tsosie"},
{"Sequence":1916,"First_Name":"David","Last_Name":"Perry"},
{"Sequence":1917,"First_Name":"Luis","Last_Name":"Frias"},
{"Sequence":1918,"First_Name":"Freida","Last_Name":"Miller"},
{"Sequence":1919,"First_Name":"Andre","Last_Name":"Davis"},
{"Sequence":1920,"First_Name":"Alyssa","Last_Name":"Gumeringer"},
{"Sequence":1921,"First_Name":"Nils","Last_Name":"Christianson"},
{"Sequence":1922,"First_Name":"Jeanne","Last_Name":"Patterson"},
{"Sequence":1923,"First_Name":"Rachelle","Last_Name":"Rodriguez"},
{"Sequence":1924,"First_Name":"Damian","Last_Name":"Garcia"},
{"Sequence":1926,"First_Name":"Brandi","Last_Name":"Clee"},
{"Sequence":1927,"First_Name":"Edward","Last_Name":"Ireland"},
{"Sequence":1928,"First_Name":"Nicole","Last_Name":"Love-Cleasby"},
{"Sequence":1929,"First_Name":"Liam","Last_Name":"Howlett"}]}

  • Action
Should be "Query", otherwise review the previous section of this article.

  • from
The table you wish to retrieve data from (Students, Visits, etc).

  • page
The page number to get records from, similar to pages on listings. This attribute allows you to write a program that continues hitting the server 1 page at a time getting the number of records in page_recs at a time. The programmer would pass 1 as page the first time, get the results and process, then pass 2 as page, etc, until the number of records is less than the amount in page_recs.

  • page_recs (Optional, seems to default to 20)
The quantity of records to return.

  • APIKey
Your API key.

  • fields
The fields you want to display from the records you're retrieving. Twig tags can be used. Center.Name, etc. Custom fields would be formatted like "Students.CustomData->>``$.cf_123``" with 123 being the sequence number of the custom field.

  • order
How data is sorted. Choose a field and sort ascending (ASC) or descending (DESC).

  • Query
  • Query type
Detailed further into this article.
  • field
Choose the field you want to search by.
  • value
The value you want to search by.
  • op (use | for "or")
= - Equals.
!= - Not.
> - Greater than.
< - Less than.
<= - Less than or equal to.
>= - Greater than or equal to.
LIKE - Similar to, use % as a wildcard.
NOT LIKE - Similar to, use % as a wildcard.
Query Options



The examples below only include the "Query" portion of the data for brevity. These can be copied into the example curl command from earlier in this section.

fields | Search for specific values, using additional operators if needed. 

	"query": {
		"fields": [{
            "field": "ID",
		    "value": "1933",
		    "op": "="
        }]
    }

orfields | Search for records that match any of the listed criteria. This can include an unlimited number of fields. 

    "query": {
        "orfields": [
            {
                "field": "First_Name",
                "value": "%",
                "op": "LIKE"
            },
            {
                "field": "Last_Name",
                "value": "%e%|%i%|%son",
                "op": "LIKE"
            },
            {
                "field": "Major",
                "value": "Biology",
                "op": "!="
            }
        ]
    }


andfields | Search for records that match all of the listed criteria. This can include an unlimited number of fields. 

    "query": {
        "andfields": [
            {
                "field": "First_Name",
                "value": "Rob",
                "op": "="
            },
            {
                "field": "Last_Name",
                "value": "S%",
                "op": "LIKE"
            },
            {
                "field": "Major",
                "value": "Biology",
                "op": "="
            }
        ]
    }

or | Used to combine 2 (and only 2) instances of andfields/orfields. Records are returned if either criteria is met. 

        "or": [
            {
                "andfields": [
                    {
                        "field": "Sequence",
                        "value": "1",
                        "op": ">"
                    },
                    {
                        "field": "Sequence",
                        "value": "1000000",
                        "op": "<"
                    }
                ]
            },
            {
                "orfields": [
                    {
                        "field": "First_Name",
                        "value": "%",
                        "op": "LIKE"
                    },
                    {
                        "field": "Last_Name",
                        "value": "%e%|%i%|%son",
                        "op": "LIKE"
                    }
                ]
            }
        ]
    }


and | Used to combine 2 (and only 2) instances of andfields/orfields. Records are returned if both criteria are met. 

    "query": {
        "and": [
            {
                "andfields": [
                    {
                        "field": "Sequence",
                        "value": "1",
                        "op": ">"
                    },
                    {
                        "field": "Sequence",
                        "value": "1000000",
                        "op": "<"
                    }
                ]
            },
            {
                "orfields": [
                    {
                        "field": "TimeIn",
                        "value": "{{\"now\"|date_modify(\"-7 days\")|date(\"Y-m-d H:i:s\")}}",
                        "op": ">"
                    },
                    {
                        "field": "Last_Name",
                        "value": "%e%|%i%|%son",
                        "op": "LIKE"
                    }
                ]
            }
        ]
    }

TracCloud Table List

This is a list of TracCloud data tables and fields that are relevant for API access.

Type Definitions



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).
Visits



Visits
Column Type Notes
Sequence bigint AI PK
EnteredDT datetime
TimeIn datetime
TimeOut datetime
Duration int
isWork tinyint
StudentID bigint Relates to Students.Sequence (not Students.ID).
CenterID bigint Relates to Centers.Sequence.
ConsultantID bigint Relates to Staff.Sequence.
RegistrationID bigint Relates to Registrations.Sequence.
ReasonID bigint Relates to Reasons.Sequence.
TotalTime double
isWaiting tinyint
WaitTime double
CustomData json
NotificationDT datetime
isTerminated tinyint
PostPaid tinyint
Posted1 tinyint
Posted2 tinyint
Posted3 tinyint
Fund varchar(80)
CtrNotes text
SchedNotes text
StudNotes text
tOptions text
SurveyRespID bigint Relates to SrvyRespAnswers.Sequence
TermID bigint Relates to Terms.Sequence.
SectionID bigint Relates to Sections.Sequence.
Location varchar(512)
Online tinyint
TimeZone varchar(120)
asynchVisitID bigint
VisitDay2 varchar(80)
gen2_date_EnteredDT date
gen2_date_TimeIn date
gen2_date_TimeOut date
VisitDay varchar(20)
Appointments



Appointments
Column Type Notes
Sequence bigint AI PK
StartDT datetime
gen_date_StartDT datetime
Duration double
EndDT datetime
Type varchar(20) Unused.
StudentID bigint Relates to Students.Sequence.
ConsultantID bigint Relates to Staff.Sequence.
VisitID bigint Relates to Visits.Sequence.
CenterID bigint Relates to Centers.Sequence.
ReasonID bigint Relates to Reasons.Sequence.
SectionID bigint Relates to Sections.Sequence.
AvailRecID bigint Relates to AvailBlocks.Sequence.
RecurID bigint
RecurTotal bigint Unused.
Status varchar(80)
Status2 varchar(80) Unused.
Location varchar(512)
Fund varchar(80)
Online int
OnlineURL varchar(512)
onLineDocType varchar(80) Unused.
onLineDoc text Unused.
RequestNotes text Unused.
OtherNotes text Cancellation reason.
SchedDT datetime
SchedModDT datetime
SchedUser varchar(80)
SchedNotes text Unused.
SurveyRespID bigint Unused.
PostPaid tinyint
Posted1 tinyint
ConfirmationSent tinyint
ReminderDT datetime
ReminderNeeded tinyint
CustomData json
SchedModBy varchar(80)
TimeZone varchar(120) Unused.
asynchConcluded tinyint
Students



Students
Column Type Notes
Sequence bigint AI PK Static identifier for students, unrelated to ID.
UUID varchar(36)
ID bigint
Other_ID varchar(40)
Other_ID2 varchar(40)
Barcode varchar(80)
Status varchar(80)
OtherStatus varchar(80)
Legal_First varchar(80)
First_Name varchar(80)
Last_Name varchar(80)
Middle varchar(80)
Street varchar(250)
Apt varchar(80)
City varchar(120)
State varchar(80)
Zip varchar(20)
Country varchar(80)
Home_Phone varchar(80)
Work_Phone varchar(80)
Cell_Phone varchar(80)
Email varchar(120)
Preferred varchar(80)
passhash varchar(128) Unused.
Attempts bigint
resetCode varchar(80)
lockedOut int
TextAddr varchar(120)
LastDateIn date
Notes text
UserName varchar(120)
Password varchar(128) Unused.
Prefs text
Birthdate date
Gender varchar(80)
Ethnicity varchar(150)
Major varchar(120)
Class varchar(80)
DegreeGoal varchar(80)
Cohort varchar(80)
College varchar(80)
Grad_Und varchar(80)
DateStarted date
DateWithdrawn date
ReasonWithdrawn varchar(128)
GradDate date
GradDegree varchar(128)
GPA double
AccumHours double
PrimaryConsultantID bigint Relates to Staff.Sequence.
FlagText varchar(80)
CustomData json
WatchLists json
CreatedDT timestamp
ModifiedDT timestamp
Fund varchar(80)
gen_BIOConfirmed varchar(2)
gen_BIOConfirmedDT varchar(20)
Pronouns varchar(80)
Full_Name varchar(255)
Full_Name2 varchar(255)
_LastImportedDT datetime
PassLastDateChanged date
PassFailedAttempts int
AccountLocked tinyint
AccountLockDateTime datetime
Staff



Staff
Column Type Notes
Sequence bigint AI PK
UUID varchar(36)
First_Name varchar(80)
Last_Name varchar(80)
Alias varchar(120)
Email varchar(120)
UserName varchar(80) Typically matches Students.UserName if the user has both account types.
FileAccess text Unused.
UserLevel int 0 = Staff, 1 = SysAdmin, 2 = Profile Admin
LastAccess datetime Date and time of last login.
NumAccesses bigint Unused.
GroupID bigint Relates to Groups.Sequence.
Comments text
ChangePass int Unused.
Prefs text
isKiosk tinyint
isConsultant tinyint
StudentID bigint
Inactive tinyint
Phone varchar(30)
CellPhone varchar(80)
WorkPhone varchar(80)
Address varchar(80)
City varchar(120)
State varchar(80)
Zip varchar(20)
CanEditSched tinyint
CanEditAppt tinyint
CanAccessStudents tinyint
CanGiveAppt tinyint
CourseListID bigint Relates to CourseList.Sequence.
Location varchar(512)
OnlineLink varchar(512)
Fund varchar(80)
PayCodeID bigint Relates to PayCodes.Sequence.
EligibilityExp date
Hired date
Terminated date
ReasonQuit varchar(220)
Mailbox varchar(80)
AlternateName varchar(80)
AlternateCenters text
ScheduleSort tinyint
SchedulingNotAvailable tinyint
AutoBlockRule varchar(80)
isRoom tinyint
CenterID bigint
noShowOnSched tinyint
Notes text
CustomData json
OtherID varchar(80)
LastFirst varchar(255)
FirstLast varchar(255)
StaffBIO text
Pronouns varchar(80)
CreatedDT datetime
_LastImportedDT datetime
ComputedAlias varchar(256)
PassLastDateChanged date
PassFailedAttempts int
AccountLocked tinyint
AccountLockDateTime datetime
Country varchar(80)
KioskSort varchar(10)
SupervisorID int Relates to Staff.Sequence.
TitleOrPosition varchar(80)
Subject Related (Courses, Sections, Terms, Registrations, Faculty, SectionSchedule)



Courses
Column Type Notes
Sequence bigint AI PK Unique identifier for each course.
Subject varchar(40)
Course varchar(20)
Title varchar(120)
ExcludeFromCharts tinyint
Dept varchar(40)
SubjectCourse varchar(200)
_LastImportedDT datetime
CustomData json
Sections
Column Type Notes
Sequence bigint AI PK Unique identifier for each section.
Inactive tinyint
Code varchar(80)
CourseID bigint Relates to Courses.Sequence
GradeGroupID bigint
CRN varchar(80)
TermID bigint Relates to Terms.Sequence
FacultyID bigint Relates to Faculty.Sequence
nonEnrolled tinyint
CustomData json
CreatedDT timestamp
ignoreRegImportDeactivate tinyint
Credits double
_LastImportedDT datetime
Title varchar(120)
Terms
Column Type Notes
Sequence bigint AI PK Unique identifier for terms.
Active tinyint
TermCode varchar(80)
OrderIdx int
ActiveFrom date
ActiveTo date
CustomData json Unused.
Registrations
Column Type Notes
Sequence bigint AI PK Unique identifier for registrations.
Inactive tinyint
OverrideDate tinyint
SectionID bigint Relates to Sections.Sequence.
StudentID bigint Relates to Students.Sequence (not Students.ID).
Grade varchar(40)
ValidFromDT datetime
ValidToDT datetime
Notes text
CustomData json
_LastImportedDT datetime
RegStatus varchar(40)
EarnedCredits double
Faculty
Column Type Notes
Sequence bigint AI PK
UUID varchar(36)
FirstName varchar(80)
LastName varchar(80)
Password varchar(128) Unused.
UserID varchar(80)
UserName varchar(80)
Custom2 text
Custom1 text
Salutation varchar(40)
Department varchar(60)
Inactive tinyint
Phone varchar(80)
Email varchar(120)
CustomData json
OtherID varchar(80)
FullName varchar(255)
facultyBIO text
Pronouns varchar(80)
_LastImportedDT datetime
PassLastDateChanged date
PassFailedAttempts int
AccountLocked tinyint
AccountLockDateTime datetime
SectionSchedule
Column Type Notes
Sequence bigint AI PK
SectionID bigint Relates to Sections.Sequence
Days varchar(30)
Time time
Duration int
Description text
OtherData text
Campus varchar(80)
Bldg varchar(80)
Room varchar(80)
Profile Related (Profiles, Centers, Groups, Reasons)



Profiles
Column Type Notes
Sequence bigint AI PK
Type int
Name varchar(80)
Inactive tinyint
ContactID bigint
NameForConsultant varchar(80)
CustomData json
TimeZone varchar(120)
Centers
Column Type Notes
Sequence bigint AI PK
Name varchar(80)
Prefs text
CourseListID bigint
fromEmail varchar(80)
ProfileType varchar(80) Unused.
ProfileID bigint Relates to Profiles.Sequence.
Inactive tinyint
PrimaryGroup bigint Unused.
Settings json
OtherData json
UpdatedDT datetime
IsWritingCenter tinyint
Reasons
Column Type Notes
Sequence bigint AI PK
ProfileID bigint Relates to Profiles.Sequence.
Subcenters varchar(255)
Reason varchar(80)
Inactive tinyint
AutoLogTime int
AutoLogNow int
NotificationTime int
SnoozeTime int
isWork tinyint
isStaff tinyint
SortCode varchar(40)
NotForAppt tinyint
NotForLogin tinyint
NotForVisit tinyint
Category varchar(80)
CustomData json
Groups
Column Type Notes
Sequence bigint AI PK
GroupName varchar(80)
ProfileID bigint Relates to Profiles.Sequence.
Prefs json
SurveyTrac Related (Surveys, SrvyQuestions, Responses, SrvyRespAnswers)



Surveys
Column Type Notes
Sequence bigint AI PK Static identifier for each Survey.
Name varchar(80)
CreatedBy bigint Relates to Staff.Sequence.
Options json This field can be ignored for API purposes.
ProfileID bigint Relates to Profiles.Sequence.
CenterID bigint
AutoSend varchar(80) The chosen "Initiated Via" option.
Active tinyint
ActiveFrom date
ActiveTo date
Instructions text
useXML tinyint Unused.
Assessment tinyint Unused.
Function varchar(80) Unused.
Confirmation text
HeadText text
SrvyQuestions
Column Type Notes
Sequence bigint AI PK Static identifier for each Question, sequences are not reused even across multiple surveys.
Question text
SurveyID bigint Relates to Surveys.Sequence.
Points double
Answer varchar(80)
AnswerChoices text
Options json
LinkName varchar(80) Unused.
LinkURL varchar(80) Unused.
Type varchar(80)
Number int
CustomData json
Responses
Column Type Notes
Sequence bigint AI PK Static identifier for each response.
SurveyID bigint Relates to Surveys.Sequence.
linkedUID varchar(36)
SentByID varchar(36)
StudentID bigint Relates to Students.Sequence (not Students.ID).
SentBy varchar(80)
SentDate datetime
AnsweredDate datetime
StaffID bigint Relates to Staff.Sequence.
FacultyID bigint Relates to Faculty.Sequence.
SentManually tinyint
CustomData json
SrvyRespAnswers
Column Type Notes
Sequence bigint AI PK Static identifier for each question response.
QuestionID bigint Relates to SrvyQuestions.Sequence.
ResponseID bigint Relates to Responses.Sequence.
aAnswer varchar(80)
nAnswer double
tAnswer text
CustomData json
Retrieved from "https://wiki.go-redrock.com/index.php?title=TracCloud:_API&oldid=11969"