When building a query, if you aren’t using the condition builder (available starting in 6.9.0.0) this cheat sheet will help you find what you are looking for to build your query.
If you aren’t familiar with SQL then this may just confuse you more. Please do not hesitate to reach out to support for assistance. You can email us at Support@velloxgroup.com.
Cheat Sheet
Request Fields that can be typed in directly:
| Disposition | CallTypeID | CallTypeHierarchy |
| Username | Priority | Asset |
| ReasonForCancel | VendorID | Vendor |
| TimeZoneName | Jurisdiction | JurisdictionID |
| SecondRequestID | RequestorName | RequestorAddress |
| RequestorDoctor | RequestorDoctorPhone | RequestorRoom |
| CallType | CallSubType | Number |
| CallSign | Time | ScheduledTime |
| PatientMileage | TotalMileage | ReasonForClose |
| BillingDivision | BillingDivisionID | CloseTime |
| RequestorType | RequestorUnit | RequestorCallback |
| RequestorCaller | ReceiverName | ReceiverAddress |
| ReceiverType | ReceiverUnit | ReceiverCallback |
| ReceiverRoom | ReceiverCaller | SceneName |
| SceneState | SceneZip | SceneDescription |
| BaseName | IsAirplane | IsGround |
| IsUAV | Notes | CompetitorName |
| WillCall | ScheduledTimeDescription | IsTemplate |
| CurrentWaypointID | MileageCalculationType | NodeValue |
| VectorCareAccount | VectorCareAccountID | IsBoat |
| ReceiverDoctor | ReceiverDoctorPhone | IsFlightFollowing |
| SceneCity | SceneLatLon | DateOfService |
| TimeZoneName | TimeZoneID | NineOneOneNumber |
| IsHelicopter | GroupID | NineOneOneTime |
| CompetitorID |
Crew Fields that can be accessed by selecting from CrewAboard:
Example: SELECT WaypointOnIndex FROM CrewAboard:
CrewID, Name, Type, WaypointOnIndex, WaypointOffIndex, WaypointOnID, WaypointOffID, TeamName, Role
Waypoint Fields that can be accessed by selecting from RequestPlan:
Example: SELECT Latitude FROM RequestPlan:
ID, Name, Depart, Arrive, RouteIndex, Latitude, Longitude, Intent, LocationType, Country, State, County, City, Zip, Address
Times Fields that can be accessed by selecting from Times:
Example: SELECT TimeUTC FROM Times
ID, WaypointID, UserID, UserName, TypeID, Type, Time, TimeUTC, Notes, New
To use date/time field from the request
will pull UTC - getstring('date_time')strftime('%H:%M', (getstring('time_date')),'localtime')
Fields that can be accessed by selecting from TransferItem:
Example: SELECT ID FROM TransferItem:
ID
Patient/Escort/Non-Medical Fields that can be accessed using WithTransferItem() in the following format(s)
Example1: WithTransferItem((SELECT ID FROM TransferItem), 'Number'):
Example2: SELECT WithTransferItem(ID, ‘Number’) FROM TransferItem
Number, ID, Caller, Time, TimeWithSeconds, CloseReasonID, CloseReason, CopiedFrom, Type, Requestor, RequestorType, RequestorUnit, RequestorCallback, RequestorDoctor, RequestorDoctorPhone, RequestorRoom, RequestorCaller, Receiver, ReceiverType, ReceiverUnit, ReceiverCallback, ReceiverDoctor, ReceiverDoctorPhone, ReceiverRoom, ReceiverCaller, ChiefComplaint, Triage, Token, Closed, ReferrerName, ReferrerType, ReferrerCaller, ReferrerCallerTitle, ReferrerPhone, ReferrerDoctor, IsTemplate, FirstName, MiddleInitial, LastName, Name, GenderAbbreviation, Weight, WeightLbs, WeightMetric, DateOfBirth, AddressCountry, AddressState, AddressStateAbbr, AddressCounty, AddressCity, AddressZip, AddressStreet, AddressStreet2, SelectedLegID
Patient/Escort/Non-Medical Leg Fields that can be accessed using WithTransferItem(), and selecting from TcEntryPlan with the second argument:
Example: WithTransferItem((select id from transferitem), 'select receiver_name from tcentryplan'):
Id, type, request_number, loc, priority, scene_name, scene_latlon, pick_up_name, pick_up_type, pick_up_latlon, drop_off_name, drop_off_type, drop_off_latlon, requestor_name, requestor_type, requestor_contact, requestor_callback, requestor_doctor, requestor_unit, requestor_room, receiver_name, receiver_type, receiver_contact, receiver_callback, receiver_doctor, receiver_unit, receiver_room, pending, existing_request, plan_index, callsign, registration_number, requestor_address, requestor_city, requestor_county, requestor_state, requestor_state_abbr, requestor_country, receiver_address, receiver_city, receiver_county, receiver_state, receiver_state_abbr, receiver_country, pick_up_lat, pick_up_lon, drop_off_lat, drop_off_lon, caused_by_patient, requestor_radio, receiver_radio, pick_up_country, pick_up_state, pick_up_state_abbr, pick_up_county, pick_up_city, drop_off_country, drop_off_state, drop_off_state_abbr, drop_off_county, drop_off_city, pick_up_zip, pick_up_address, drop_off_zip, drop_off_address, request_leg_number, is_first, is_last
Patient/Escort/Non-Medical Times Fields that can be accessed using WithTransferItem(), and selecting from TcEntryTimes with the second argument:
Example: WithTransferItem((select id from transferitem), 'select datetime from tcentrytimes'):
id, datetime, time_name, initial_datetime, notes, leg_id
Patient’s Associated Escort Fields that can be accessed using WithTransferItem(), and selecting from RelatedEntry with the second argument:
Example: WithTransferItem((select id from transferitem), 'select name from RelatedEntry'):
associated_id, type, name, number
Functions that can be accessed directly
name in sql, number of arguments, quick description)
CurrentUserName, 0, retrieves user name of CAD user
Example: CurrentUserName()
GetWaypointStringByID, 2, retrieves value of a waypoint custom text field by the wp’s ID
Example: GetWaypointStringByID('save-as', (select id from requestplan))
GetWaypointStringByIndex, 2, retrieves value of a waypoint custom text field by the wp’s index in the flight plan
Example: GetWaypointStringByIndex('save-as', (select routeindex from requestplan))
GetWaypointDropdownByID", 2, retrieves value of a waypoint custom dropdown field by the wp’s ID
Example: GetWaypointDropdownByID('save-as', (select id from requestplan))
GetWaypointDropdownByIndex, 2, retrieves value of a waypoint custom dropdown field by the wp’s index in the flight plan
Example: GetWaypointDropdownByIndex('save-as', (select routeindex from requestplan))
GetWaypointCheckboxByID, 2, retrieves value (1 or 0) of a waypoint custom checkbox field by the wp’s ID
Example: GetWaypointCheckboxByID('save-as', (select id from requestplan))
GetWaypointCheckboxByIndex, 2, retrieves value (1 or 0) of a waypoint custom checkbox field by the wp’s index in the flight plan
Example: GetWaypointCheckboxByIndex('save-as', (select routeindex from requestplan))
GetWaypointCheckboxStrByID, 2, retrieves value (Yes or No) of a waypoint custom dropdown field by the wp’s ID
Example: GetWaypointCheckboxStrByID('save-as', (select id from requestplan))
GetWaypointCheckboxStrByIndex, 2, retrieves value (Yes or No) of a waypoint custom text field by the wp’s index in the flight plan
Example: GetWaypointCheckboxStrByIndex('save-as', (select routeindex from requestplan))
GetAddress, 2, retrieves value of part of a request custom address field. Valid parts to ask for in the second argument include: country, state, city, county, zip, street, and street2
Example: GetAddress('save-as', 'city')
Example: GetAddress('save-as', 'street')
GetString, 1, retrieves value of a request custom text field
Example: GetString('save-as')
GetInt, 1, retrieves value of a request custom int field
Example: GetInt('save-as')
GetDropdown, 1, retrieves value of a request custom dropdown field
Example: GetDropdown('save-as')
GetCheckbox, 1, retrieves value of a request custom checkbox field
Example: GetCheckbox('save-as')
GetChecklist, 1, retrieves value of a request custom checklist field
Example: GetChecklist('save-as')
GetAddedTimeID, 0, only valid for triggers with event of request time added. Retrieves ID of the timestamp added (compare to (select id from times))
Example: GetAddedTimeID()
WithTransferItem, 2, examples above, use to retrieve values of transfer item fields
GetUpdatedWaypointID, 0, only valid for triggers with event of update waypoint or delay reason expressions. Retrieves ID of the updated waypoint (compare to (select id from requestplan))
Example: GetUpdatedWaypointID()
Template, 1, retrieves value of a simple pager field
Example: Template('[[Pager Field]]')
IsChildVendorOf, 1, returns 1 if request’s vendor is child of passed in vendor name
Example: IsChildVendorOf('Some Vendor Name')
IsChildVendorOfID, 1, returns 1 if the request’s vendor is child of passed in vendor ID
Example: IsChildVendorOfID(VendorID)
MinutesBeforeService", 0, only really applicable for scheduled requests, returns the number of minutes between now and the scheduled date/time of service
Example: MinutesBeforeService()
Functions that can be accessed using WithTransferItem():
(name in sql, number of arguments, quick description)
dropdown, 1, retrieves the value of a patient/escort/non-medical custom dropdown field
Example: SELECT WithTransferItem(ID, 'dropdown("save-as")') FROM transferItem
dropdown, 2, retrieves value of a patient/escort/non-medical leg custom dropdown field
Example: SELECT WithTransferItem(ID, 'dropdown("save-as", (select id from tcEntryPlan))') FROM transferItem
text, 1, retrieves value of a patient/escort/non-medical custom text field
Example: SELECT WithTransferItem(ID, 'text("save-as")') FROM transferItem
text, 2 GetPatientLegString, retrieves value of a patient/escort/non-medical leg custom text field
Example: SELECT WithTransferItem(ID, 'text("save-as", (select id from tcEntryPlan))') FROM transferItem
checklist, 1, retrieves value of a patient/escort/non-medical custom checklist field
Example: SELECT WithTransferItem(ID, 'checklist("save-as")') FROM transferItem
checklist, 2, retrieves value of a patient/escort/non-medical leg custom checklist field
Example: SELECT WithTransferItem(ID, 'checklist("save-as", (select id from tcEntryPlan))') FROM transferItem
date, 1, retrieves value of a patient/escort/non-medical custom date field
Example: SELECT WithTransferItem(ID, date("save-as")') FROM transferItem
date, 2, retrieves value of a patient/escort/non-medical leg custom date field
Example: SELECT WithTransferItem(ID, 'date("save-as", (select id from tcEntryPlan))') FROM transferItem
datetime, 1, retrieves value of a patient/escort/non-medical custom datetime field
Example: SELECT WithTransferItem(ID, 'datetime("save-as")') FROM transferItem
datetime, 2, retrieves value of a patient/escort/non-medical leg custom datetime field
Example: SELECT WithTransferItem(ID, 'datetime("save-as", (select id from tcEntryPlan))') FROM transferItem
checkbox, 1, retrieves value (Yes or No) of a patient/escort/non-medical custom checkbox field
Example: SELECT WithTransferItem(ID, 'checkbox("save-as")') FROM transferItem
checkbox, 2, retrieves value (Yes or No) of a patient/escort/non-medical leg custom checkbox field
Example: SELECT WithTransferItem(ID, 'checkbox("save-as", (select id from tcEntryPlan))') FROM transferItem
checked, 1, retrieves value (1 or 0) of a patient/escort/non-medical custom checkbox field
Example: SELECT WithTransferItem(ID, 'checked("save-as")') FROM transferItem
checked, 2, retrieves value (1 or 0) of a patient/escort/non-medical leg custom checkbox field
Example: SELECT WithTransferItem(ID, 'checked("save-as", (select id from tcEntryPlan))') FROM transferItem
initialDropdown, 1, retrieves initial value of a patient/escort/non-medical custom dropdown field
Example: SELECT WithTransferItem(ID, 'initialDropdown("save-as")') FROM transferItem
initialText, 1, retrieves initial value of a patient/escort/non-medical custom text field
Example: SELECT WithTransferItem(ID, 'initialText("save-as")') FROM transferItem
initialChecklist, 1, retrieves initial value of a patient/escort/non-medical custom checklist field
Example: SELECT WithTransferItem(ID, 'initialChecklist("save-as")') FROM transferItem
initialDate, 1, retrieves initial value of a patient/escort/non-medical custom date field
Example: SELECT WithTransferItem(ID, 'initialDate("save-as")') FROM transferItem
initialDatetime, 1, retrieves initial value of a patient/escort/non-medical custom datetime field
Example: SELECT WithTransferItem(ID, 'initialDatetime("save-as")') FROM transferItem
initialCheckbox, 1, retrieves initial value (Yes or No) of a patient/escort/non-medical custom checkbox field
Example: SELECT WithTransferItem(ID, 'initialCheckbox("save-as")') FROM transferItem
initialChecked, 1, retrieves initial value (1 or 0) of a patient/escort/non-medical custom checkbox field
Example: SELECT WithTransferItem(ID, 'initialChecked("save-as")') FROM transferItem
hasChanged, 1, returns “Yes” if the patient/escort/non-medical custom field with that save-as value has changed, “No” if it has not.
Example: SELECT WithTransferItem(ID, 'hasChanged("save-as")') FROM transferItem
compute, 1, retrieves value of patient/escort/non-medical computed value whose name = save-as
Example: SELECT WithTransferItem(ID, 'compute("save-as")') FROM transferItem
triagePriority, 0, retrieves value of patient/escort/non-medical triage priority
Example: SELECT WithTransferItem(ID, 'triagePriority()') FROM transferItem
addedTimeId, 0, retrieves ID value of patient/escort/non-medical timestamp that was added (only valid for trigger where such a timestamp was added)
Example: SELECT WithTransferItem(ID, 'addedTimeId()') FROM transferItem
locationLat, 1, retrieves latitude of patient/escort/non-medical custom location field
Example: SELECT WithTransferItem(ID, 'locationLat(''save-as'')') FROM transferItem
locationLon, 1, retrieves longitude of patient/escort/non-medical custom location field
Example: SELECT WithTransferItem(ID, 'locationLon(''save-as'')') FROM transferItem
locationName, 1, retrieves name of patient/escort/non-medical custom location field
Example: SELECT WithTransferItem(ID, 'locationName(''save-as'')') FROM transferItem
getOtherEntryText, 2, retrieves weight or weight unit of associated entry of patient/escort/non-medical
Example: SELECT WithTransferItem((ID, 'getOtherEntryText((select associated_id from RelatedEntry), ''<weight>'')') FROM transferItem
Example: SELECT WithTransferItem((ID, 'getOtherEntryText((select associated_id from RelatedEntry), ''<weightUnit>'')') FROM transferItem
IsChildVendorOf, 1, returns 1 if patient/escort/non-medical’s vendor is child of vendor-name
Example: SELECT WithTransferItem((ID, 'IsChildVendorOf(''vendor-name'')') FROM transferItem
GetUser, 0, retrieves username of current user
Example: SELECT WithTransferItem((ID, 'GetUser()') FROM transferItem
Calculate the difference in two times in minutes
DURATION_MINUTES(firsttime, secondtime)
Printf
Printf is used to change a number into a text
AIP
Below will pull a text field from AIP
Example: (select GetAipString(id, 'save-as_textfield') from AipEvents)
Below will pull a date/time field from AIP
strftime('%H:%M', (select GetAipString(id, 'time_date_aip') from AipEvents),'localtime')
Below will pull a dropdown field from AIP
(select GetAipdropdown(id, 'save-as_dropdown') from AipEvents)
Below will pull of the time stamps associated with AIP
Example: (Select group_concat(type || ': ' || time, ', ') from aiptimes)
To Pull a specific Time stamp
Example: (Select time from aiptimes where type = 'AIP Start Time')
Comments
0 comments
Please sign in to leave a comment.