Skip to main content

Expressionator Tip Sheet

Lauren Villegas
Updated

When creating a query, if you're not using the condition builder (available from version 6.9.0.0), this handy tip sheet can assist you in finding what you need to construct your query.

**Note: Our expressionator uses SQL Lite.  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.

Tip Sheet

 

Request Fields that can be typed in directly:

Disposition, CallType, CallTypeID, CallSubType, CallTypeHierarchy, Number, Username, Priority, Asset, CallSign, Time, ScheduledTime, ReasonForCancel, VendorID, Vendor, PatientMileage, TotalMileage, ReasonForClose, TimeZoneName, Jurisdiction, JurisdictionID, BillingDivision, BillingDivisionID, CloseTime, SecondRequestID, RequestorName, RequestorAddress, RequestorType, RequestorUnit, RequestorCallback, RequestorDoctor, RequestorDoctorPhone, RequestorRoom, RequestorCaller, ReceiverName, ReceiverAddress,

ReceiverType, ReceiverUnit, ReceiverCallback, ReceiverDoctor, ReceiverDoctorPhone, ReceiverRoom, ReceiverCaller, SceneName, SceneCity, SceneLatLon, SceneState, SceneZip, SceneDescription, TimeZoneName, TimeZoneID, BaseName, IsAirplane, IsGround, IsHelicopter, IsBoat, IsUAV, Notes, CompetitorName, CompetitorID, IsFlightFollowing, WillCall, ScheduledTimeDescription, IsTemplate, GroupID, DateOfService, CurrentWaypointID, MileageCalculationType, NodeValue, NineOneOneTime, NineOneOneNumber, VectorCareAccount, VectorCareAccountID

 

 

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 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 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')

 

 

 

Related articles

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk