Access to the ServiceTrade Data Warehouse is available only to ServiceTrade Enterprise customers.  For more information about ServiceTrade Enterprise, contact sales@servicetrade.com.

The ServiceTrade Data Warehouse contains several data sets, each of which has a number of fields.  Those datasets can be used to build visualizations with Amazon QuickSight and other BI tools.  This reference describes those datasets and the individual fields within each.

Datasets Available:

All data sets available for Core ServiceTrade and PartsLedger are easily accessible by clicking the In this article section to the right.

Within these datasets, the following types of fields are supported:

• text - A text value
• comma-separated text - A list of text values, separated by commas
• number - An integer or decimal number
• datetime - A date and time
• boolean - A true/false flag; 1 indicates true/yes, 0 indicates false/no

All tables include the following fields:

• account_id - The internal ID of the ServiceTrade account [number]
• account_name - The name of the ServiceTrade account [text]

Core ServiceTrade

Jobs

The 'jobs' dataset contains information about the jobs in your ServiceTrade account.  Jobs in all statuses, including canceled jobs, are included in this dataset. A record in the Jobs table will refresh when any change is made to the corresponding service request.

This dataset includes information about appointments and services on each job.  Appointments whose status is 'Canceled' are not included.  Services whose status is 'Canceled' or 'Void', or which are associated with canceled appointments, are not included.  An appointment's duration is the difference between its scheduled start and end times; for instance, an appointment which is scheduled to start at 10:00am and end at 12:30pm has a duration of 2.5 hours.

This dataset also includes information about clock events on each job.  A clock event's duration is the time elapsed between a given clock in and clock out for a given technician, appointment and activity type.

A Job record will update whenever any change is made to the Job, its Job_Items, Appointments tied to the Job, or Invoices tied to the Job.

Fields:

id - The internal ID of the job [number]
job_name - The name describing the purpose of the job [text]
job_number - The job number [number]
job_type - The job type [text]
status - The job's current status [text]
description - The job description [text]
created - The date on which the job was created [datetime]
updated - The date on which the job was most recently updated [datetime]
completed_on - The date on which the job was marked complete [datetime]
customer - The name of the ServiceTrade company which is the customer for this job [text]
purchase_order_number - The customer purchase order number for this job [text]
vendor - For subcontracted jobs, the name of the ServiceTrade company which is the vendor performing service for this job [text]
location - The name of the job's location [text]
location_state - The abbreviation of the state or territory of the job's location [text]
location_postal_code - The postal code of the state or territory of the job's location [text]
location_offices - A list of the names of the offices assigned to the job's location [comma-separated text]
location_regions - A list of the names of the regions containing the job's location [comma-separated text]
location_latitude - The latitude of the job's location [number]
location_longitude - The longitude of the job's location [number]
location_id - The ID of the job's location (matches id field in the Locations dataset) [number]
customer_id - The ID of the job's customer [number]
assigned_to - The name of the ServiceTrade user who is assigned as the owner of the job [text]
assigned_to_office - The name of the office assigned to the user who is assigned as the owner of the job [text]
sales - The name of the ServiceTrade user who is assigned as the salesperson of the job [text]
contract_id - The ID of the contract assigned to the job [text]
contract_name - The name of the contract assigned to the job [text]
office - The name of the office assigned to the job [text]
job_estimated_price - The sum total of the estimated price of all services on the job [number]
job_estimated_duration - The sum total of the estimated duration of all services on the job [number]
job_cost - The sum total of the costs of all job items on the job [number]
job_invoice_amount - The sum total (not including sales tax) of all invoices associated with the job [number]
job_expected_margin - The difference between job_cost and job_estimated_price, as a percentage of job_estimated_price [number]
job_actual_margin - The difference between job_cost and job_invoice_amount, as a percentage of job_invoice_amount [number]
job_open_services_estimated_price - The sum total of the estimated price of all incomplete services on the job [number]
job_open_services_estimated_duration - The sum total of the estimated duration of all incomplete services on the job [number]
job_completed_services_estimated_price - The sum total of the estimated price of all completed services on the job [number]
job_completed_services_estimated_duration - The sum total of the estimated duration of all completed services on the job [number]
job_due_start - The beginning of the due window for the job [datetime]
job_due_end - The end of the due window for the job [datetime]
job_service_lines - A list of the names of the service lines associated with this job's services [comma-separated text]
appt_first_start - The date and time on which the first appointment on the job is scheduled to start [datetime]
appt_first_end - The date and time on which the first appointment on the job is scheduled to end [datetime]
appt_last_start - The date and time on which the last appointment on the job is scheduled to start [datetime]
appt_last_end - The date and time on which the last appointment on the job is scheduled to end [datetime]
completed_appt_first_start - The date and time on which the first completed appointment on the job was scheduled to start [datetime]
completed_appt_first_end - The date and time on which the first completed appointment on the job was scheduled to end [datetime]
completed_appt_last_start - The date and time on which the last completed appointment on the job was scheduled to start [datetime]
completed_appt_last_end - The date and time on which the last completed appointment on the job was scheduled to end [datetime]
appt_count - The number of appointments on the job [number]
completed_appt_count - The number of completed appointments on the job [number]
technician_count - The number of technicians on the job [number]
job_technicians - The technicians on the job [comma-separated text]
job_technician_offices - The offices to which the technicians on the job belong [comma-separated text]
is_invoiced - A flag indicating whether the job has at least one invoice [boolean]
is_completed - A flag indicating whether the job has been marked as complete [boolean]
all_appts_completed - A flag indicating whether all the appointments on the job are complete [boolean]
all_appts_completed_in_due_window - A flag indicating whether all the appointments on the job were completed within the job due window; that is, completed_appt_first_start is later than job_due_start, and completed_appt_last_end is earlier than job_due_end [boolean]
completed_in_due_window - A flag indicating whether the job was marked complete before the end of the job due window; that is, completed_on is earlier than job_due_end [boolean]
appt_total_duration - The sum total of the durations of all appointments on the job, in hours [number]
completed_appt_total_duration - The sum total of the durations of all completed appointments on the job, in hours [number]
job_first_invoice - The earliest transaction date of the invoices for the job [datetime]
job_last_invoice - The latest transaction date of the invoices for the job; if the job has only one invoice, this will be the same as job_first_invoice [datetime]
job_invoice_count - The number of invoices for the job [number]
job_deficiency_count - The number of deficiencies reported on this job [number]
job_deficiency_quote_count - The number of quotes created to repair deficiencies reported on this job [number]
hours_created_to_first_appt - The elapsed time between when the job was created and when its first appointment was scheduled to start, in hours [number]
hours_first_appt_to_completed - The elapsed time between when the job's first appointment was scheduled to start and when the job was marked complete, in hours [number]
hours_created_to_completed - The elapsed time between when the job was created and when it was marked complete, in hours [number]
hours_completed_to_invoiced - The elapsed time between when the job was completed and the earliest transaction date of the invoices on the job, in hours [number]
hours_created_to_invoiced - The elapsed time between when the job was created and the earliest transaction date of the invoices on the job, in hours [number]
hours_created_to_on_site_first_clock_in - The elapsed time between when the job was created and the earliest on site clock in, in hours [number]
hours_on_site_last_clock_out_to_first_invoice - The elapsed time between the last on site clock out and the transaction date of the first invoice, in hours [number]
hours_on_site_last_clock_out_to_last_invoice - The elapsed time between the last on site clock out and the transaction date of the last invoice, in hours [number]
hours_on_site_first_clock_in_to_on_site_last_clock_out - The elapsed time between the first on site clock in and last on site clock out, in hours [number]
en_route_total_minutes - The sum total of the durations of all en route clock events for the job [number]
on_site_total_minutes - The sum total of the durations of all on site clock events for the job [number]
job_prep_total_minutes - The sum total of the durations of all job preparation clock events for the job [number]
on_site_first_clock_in - The date and time of the first on site clock in for the job [datetime]
on_site_first_clock_out - The date and time of the first on site clock out for the job [datetime]
on_site_last_clock_in - The date and time of the last on site clock in for the job [datetime]
on_site_last_clock_out - The date and time of the last on site clock out for the job [datetime]
job_prep_first_clock_in - The date and time of the first job preparation clock in for the job [datetime]
job_prep_first_clock_out - The date and time of the first job preparation clock out for the job [datetime]
job_prep_last_clock_in - The date and time of the last job preparation clock in for the job [datetime]
job_prep_last_clock_out - The date and time of the last job preparation clock out for the job [datetime]
en_route_first_clock_in - The date and time of the first en route clock in for the job [datetime]
en_route_first_clock_out - The date and time of the first en route clock out for the job [datetime]
en_route_last_clock_in - The date and time of the last en route clock in for the job [datetime]
en_route_last_clock_out - The date and time of the last en route clock out for the job [datetime]
servicelink_first_sent - The date and time on which the job's service link was first sent to the customer [datetime]
servicelink_last_sent - The date and time on which the job's service link was last sent to the customer [datetime]
servicelink_first_viewed - The date and time on which the job's service link was first viewed the customer [datetime]
servicelink_last_viewed - The date and time on which the job's service link was last viewed the customer [datetime]
review_requested - A flag indicating whether a service review request was sent to the customer [boolean] is_deficiency - A flag indicating whether the job is for a deficiency [boolean]
is_review_requested - A flag indicating whether the job has a service review request [boolean]
is_servicelink_sent - A flag indicating whether a service link was sent to the customer [boolean]
is_workack_created - A flag indicating whether the job has a workack created [boolean]
is_attachment_added - A flag indicating whether the job has at least one attachment added [boolean]
is_public_comment_added - A flag indicating whether the job has at least one public comment added [boolean]
is_enroute_sent - A flag indicating whether the job has an appointment enroute sent [boolean]
is_servicelink_viewed - A flag indicating whether the job's service link has been viewed [boolean]
job_first_completed - The date and time on which the job was first completed [datetime]
first_completed_by - The name of the ServiceTrade user who first completed the job [text]
last_completed_by - The name of the ServiceTrade user who last completed the job [text]
job_last_completed - The date and time on which the job was last completed [datetime]
percent_complete - The level at which the associated job is complete, as a percentage [number]
project_id - The internal id of the project associated with the job [number]
project_start_date - The start date of the project associated with the job [datetime]
project_end_date - The end date of the project associated with the job [datetime]
created_by - The name of the ServiceTrade user who created the job [text] job_first_appt_completed - The date on which the appointment associated with the job was first completed [datetime]
job_last_appt_completed - The date on which the appointment associated with the job was last completed [datetime]
job_first_invoice - The date on which the job was first invoiced [datetime]
job_last_invoice - The date on which the job was last invoiced [datetime]
invoice_status - The current status of the invoice associated with the job [text]
office_id - The ID of the office associated with the job

Job Items

The 'job_items' dataset contains information about the individual job items associated with jobs in your ServiceTrade account.  Each row represents a single job item.

All Job_Item records are refreshed every time the data warehouse data updates.

Fields:

id - The internal ID of the job item [number]
job_number - The job number [number]
job_id - The internal ID of the job (matches id field in the Jobs dataset) [number]
item_name - The name of the job item [text]
item_code - The item code of the job item [text]
item_type - The item type of the job item [text]
item_id - The internal ID of the item that is associated with the job item [id]
service_line - The name of the job item's service line [text]
service_line_id - The internal ID of the Job's service line [number]
quantity - The quantity of the job item [number]
cost - The unit cost of the job item [number]
total_cost - The total cost of the job item (job item quantity multiplied by job item unit cost) [number]
used_on - The date on which this job item was used [datetime]
source - The name of the source for this job item (technician name, warehouse name, or parts vendor name) [text]
source_type - The type of source for this job item; one of: 'Parts Vendor', 'Warehouse', 'Technician', 'Reference Number' [text]
source_status - The source status of this job item; one of: 'Not Yet Ordered', 'Ordered', 'Received' (populated only for job items whose source type is 'Parts Vendor') [text]
source_delivery_date - The delivery date of this job item (populated only for job items whose source type is 'Parts Vendor') [datetime]
source_details - Additional details about the source of this job item; populated with the purchase order number for job items whose source type if 'Parts Vendor', and empty for all other job items [text]
service_id - The id of the service associated with this job item [number]
service_description - The name of the service associated with this job item [text]
service_asset - The name of the asset for the service associated with this job item [text]
job_type - The job type [text]
job_status - The job's current status [text]
created - The date on which the job item was created [datetime]
updated - The date on which the job item was most recently updated [datetime]

Appointments

The 'appointments' dataset contains information about the individual appointments independent of how those appointments show up on, and are related to, job services and technicians. Each row represents a single appointment.

All Appointment records are refreshed every time the data warehouse data updates.

Fields:

id - Primary key / user id of the Appointments table [number]
location_id - Location id of the location the appointment takes places at [number]
job_id - Job id of the job that the appointment is tied to [number]
invoice_status - The current status of the invoice associated with the appointment [text]
job_type - The job type of the associated appointment [text]
quote_type - The quote type of the associated appointment [text]
customer - The name of the ServiceTrade company which is the customer for this associated appointment [text]
window_start - The date/time that the appointment starts at [datetime]
window_end - The date/time that the appointment ends on [datetime]
duration - The duration of the appointment in hours [number]
status - The current status of the appointment [text]
released - A flag indicating whether the appointment has been released or not [boolean]
created - The date on which the appointment was created [datetime]
updated - The date on which the appointment was most recently updated [datetime]

Appointment/Job Services

The 'appointment_services' dataset contains information about the individual services associated with appointments (and, by extension, jobs) in your ServiceTrade account.  Each row represents a single service associated with a single appointment. 

To analyze services by job instead of by individual appointment, construct your analysis to group by job number or job ID instead of by appointment date or appointment ID.

IMPORTANT:  A single service can be associated with more than one appointment, so it is valid for the same service to appear multiple times across several different appointments on the same job.  Therefore, if you are analyzing services by job, you may see some services appear to be duplicated, and you may wish to construct your analysis so that it filters out those "duplicated" services that are associated with several appointments.

An Appointment_Services record will refresh any time a change is made to the service.

Fields:

id - The internal ID of the service [number]
appointment_id - The internal ID of the appointment [number]
recurring_service_id - The internal ID of the recurring service to which this service belongs [number] (empty if this is a one-time service)
job_id - The internal ID of the job (matches id field in the Jobs dataset) [number]
job_number - The job number [number]
service_line - The name of the service's service line [text]
service_line_id - The internal ID of the service's service line [number]
asset - The name of the service's asset [text]
location - The name of the service's location [text]
location_id - The internal id of the location associated with the service [number]
window_start - The beginning of the due window for this service [datetime]
window_end - The end of the due window for this service [datetime]
status - The current status of the service [text]
price - The estimated price of the service [number]
duration - The estimated duration of the service, in seconds [number]
duration_hours - The estimated duration of the service, in hours [number]
appointment_window_start - The beginning of the scheduled window for this appointment [datetime]
appointment_window_end - The end of the scheduled window for this appointment [datetime]
appointment_status - The current status of the appointment [text]
office_assigned_to_job - - The name of the office assigned to the job [text]
created - The date on which the service was created [datetime]
updated - The date on which the service was most recently updated [datetime] quote_type - The quote type of the associated service [text]
office_id - The ID of the office associated with the appointment service

Appointment/Technicians

The 'appointment_technicians' dataset contains information about the technicians associated with appointments (and, by extension, jobs) in your ServiceTrade account.  Each row represents a single service associated with a single technician. 

To analyze technician assignments by job instead of by individual appointment, construct your analysis to group by job number or job ID instead of by appointment date or appointment ID.

IMPORTANT:  A single technician can be associated with more than one appointment, so it is valid for the same technician to appear multiple times across several different appointments on the same job.  Therefore, if you are analyzing technician assignments by job, you may see some technicians appear to be duplicated, and you may wish to construct your analysis so that it filters out those "duplicated" technicians that are associated with several appointments.

An Appointment_Technicians record will refresh any time a change is made to the appointment.

Fields:

id - The internal ID of the service [number]
appointment_id - The internal ID of the appointment [number]
job_id - The internal ID of the job (matches id field in the Jobs dataset) [number]
job_number - The job number [number]
job_type - The job type [text]
technician_id - The internal ID of the technician [number]
technician_name - The name of the technician [text]
technician_office - The name of office assigned to the technician [text]
appointment_window_start - The beginning of the scheduled window for this appointment [datetime]
appointment_window_end - The end of the scheduled window for this appointment [datetime]
appointment_status - The current status of the appointment [text]
released - A flag indicating whether this appointment has been released [boolean]
duration_hours - The duration of this appointment, in hours [number]
appointment_created - The date on which the appointment was created [datetime]
appointment_updated - The date on which the appointment was most recently updated [datetime]
location - The name of the location for this job [text]
location_offices - A list of the offices associated with the location for this job [comma-separated text]
location_regions - A list of the regions containing the location for this job [comma-separated text]
location_state - The state/province of the location for this job [comma-separated text]
window_start - The beginning of the due window for this service [datetime]
window_end - The end of the due window for this service [datetime]
status - The current status of the service [text]
price - The estimated price of the service [number]
duration_hours - The estimated duration of the service, in hours [number]
office_id - The ID of the office associated with the appointment technician

Job Contacts

The 'job_contacts' dataset contains information about the contacts associated with the jobs in your ServiceTrade account.  Each row represents a single contact associated with either the job's location or the job's customer company.

All Job Contact records are refreshed every time the data warehouse data updates.

Fields:

id - The internal ID of the contact [number]
first_name - The first name of the contact [text]
last_name - The last name of the contact [text]
type - The contact type [text]
phone - The primary phone number of the contact [text]
mobile - The mobile phone number of the contact [text]
alternate_phone - The alternate phone number of the contact [text]
email - The email address of the contact [text]
job_id - The internal ID of the job (matches id field in the Jobs dataset) [number]
job_number - The job number [number]
job_type - The job type [text]
job_location - The name of the job's location [text]
job_customer - The name of the ServiceTrade company which is the customer for this job [text]
job_location_id - The ID of the job's location (matches id field in the Locations dataset) [number]
job_customer_id - The ID of the job's customer [number]
created - The date on which the contact was created [datetime]
updated - The date on which the contact was most recently updated [datetime]
created_by - The full name of the user who created the contact associated with the job [text]

Clock Events

The 'clock_events' data set contains information about clock-in/clock-out events performed in your ServiceTrade account.  Each row represents a clock-in event, and the associated clock-out event (if it exists).

A record in the Clock_Events table will refresh when any change is made to the corresponding clock event.

Fields:

clock_in_id - The internal ID of the clock-in event [number]
clock_in_event_time - The date of the clock-in event [datetime]
clock_in_source - The source of the clock-in event; one of: 'Manual' (manually created in the ServiceTrade web UI) or 'Mobile' (created via the ServiceTrade technician mobile application) [text]
clock_in_lat - The latitude of the clock-in event [number]
clock_in_lon - The longitude of the clock-in event [number]
clock_in_created - The date on which the clock-in event was created [datetime]
clock_in_updated - The date on which the clock-in event was updated [datetime]
clock_in_id - The internal ID of the clock-in event [number]
clock_out_event_time - The date of the clock-out event [datetime]
clock_out_source - The source of the clock-out event; one of: 'Manual' (manually created in the ServiceTrade web UI) or 'Mobile' (created via the ServiceTrade technician mobile application) [text]
clock_out_lat - The latitude of the clock-out event [number]
clock_out_lon - The longitude of the clock-out event [number]
clock_out_created - The date on which the clock-out event was created [datetime]
clock_out_updated - The date on which the clock-out event was updated [datetime]
clock_duration_minutes - The elapsed time between the clock-in and clock-out events, in minutes [number]
clock_event_key - A concatenated key of job_id-appointment_id-user_id for this clock-in/clock-out pair. This is not necessrily a unique key for this table, as appointments can be re-used by the same user for the same job [text]
clock_pair_id - A concatenated key of the clock_in_id and the clock_out_id. For clock events that are unpaired, the missing clock_id will display as "[unpaired]". This key serves as a unique primary key for the clock_events table [text]
activity - The activity represented by this clock-in/clock-out pair; one of: 'En Route', 'On Site', 'Job Prep' [text]
job_id - The ID of the job associated with this clock-in/clock-out pair [number]
job_number - The number of the job associated with this clock-in/clock-out pair [number]
job_status - The status of the job associated with this clock-in/clock-out pair [number]
appointment_id - The ID of the appointment associated with this clock-in/clock-out pair [number]
user_id - The ID of the technician user associated with this clock-in/clock-out pair [number]
technician_name - The name of the technician user associated with this clock-in/clock-out pair [text]
technician_office - The name of the office assigned to the technician user associated with this clock-in/clock-out pair [text]
location - The name of the location of the job associated with this clock-in/clock-out pair [text]
customer - The name of the customer of the job associated with this clock-in/clock-out pair [text]
location_offices - A list of the names of the offices assigned to the location of the job associated with this clock-in/clock-out pair [comma-separated text]
location_regions - A list of the names of the regions containing the location of the job associated with this clock-in/clock-out pair [comma-separated text]
job_office - The name of the office assigned to the job associated with the clock events. [text]

Deficiencies

The 'deficiencies' data set contains information about deficiencies discovered in your ServiceTrade account.  All deficiencies, including those with a status of 'Invalid', are represented in this dataset. 

This data set includes information about the quote that proposes a fix to the deficiency (if there is one).   Only quotes that have at least one service to repair the deficiency are included.  Quotes with statuses of 'Canceled' and 'Rejected', and quotes whose deficiency repair services have statuses of 'Canceled' or 'Void' are not included.  If there are no quotes to repair the deficiency, all the fields beginning with quote_ will be empty.

This dataset also includes information about the job to perform the repair work described in the quote (if there is one).   Only jobs which have at least one service to repair the deficiency are included.  Jobs with a status of 'Canceled', and jobs whose deficiency repair services have statuses of 'Canceled' or 'Void', are not included.   If there are no jobs to repair the deficiency, all the fields beginning with jobs_ will be empty.

A Deficiency record will update whenever any change is made to the Deficiency, its corresponding Job, Quotes tied to the Deficiency, or Quote_Items tied to the Deficiency.

Fields:

id - The internal ID of the deficiency [number]
status - The current status of the deficiency [text]
description - The description of the deficiency [text]
resolution - The deficiency's current resolution status [text]
proposed_fix - The proposed fix for the deficiency [text]
created - The date on which the deficiency was created [datetime]
updated - The date on which the deficiency was most recently updated [datetime]
service_line - The name of the deficiency's service line [text]
service_line_id - The internal ID of the deficiency's service line [number]
asset - The name of the asset on which the deficiency was identified [text]
location - The name of the deficiency's location [text]
location_state - The abbreviation of the state or territory of the deficiency's location [text]
location_postal_code - The postal code of the state or territory of the deficiency's location [text]
location_offices - A list of the names of the offices assigned to the deficiency's location [comma-separated text]
location_regions - A list of the names of the regions containing the deficiency's location [comma-separated text]
customer - The name of the ServiceTrade customer company that owns the deficiency's location [text]
location_id - The ID of the deficiency's location (matches id field in the Locations dataset) [number]
customer_id - The ID of the deficiency's customer [number]
asset_id - The ID of the deficiency's asset [number]
reporter_office - The name of the office assigned to the user who most recently reported the deficiency [text]
reporter_name - The name of the ServiceTrade user who most recently reported the deficiency [text]
creator_office - The name of the office assigned to the user who originally created the deficiency [text]
creator_name - The name of the ServiceTrade user who originally created the deficiency [text]
deficiency_sales - The name of the ServiceTrade user who is assigned as the salesperson on the deficiency [text]
deficiency_owner - The name of the ServiceTrade user who is assigned as the owner of the deficiency [text]
quote_id - The internal ID of the quote (matches id field in the Quotes dataset) [number]
quote_number - The quote number [number]
quote_owner - The name of the ServiceTrade user who is assigned as the owner of the quote [text]
quote_sales - The name of the ServiceTrade user who is assigned as the salesperson on the quote [text]
quote_status - The quote's current status [text]
quote_expires_on - The date on which the quote expires [datetime]
quote_created - The date on which the quote was created [datetime]
quote_updated - The date on which the quote was most recently updated [datetime]
quote_service_id - The internal ID of the quote's service to repair the deficiency [number]
quote_price - The total quoted amount of the quote [number]
quote_cost - The total cost of all the items of the quote [number]
quote_margin - The difference between quote_price and quote_cost, as a percentage of quote_price [number]
quote_first_sent - The date on which the quote was first sent to the customer for approval [datetime]
quote_last_sent - The date on which the quote was most recently sent to the customer for approval [datetime]
quote_first_viewed - The date on which the quote was first viewed by the customer [datetime]
quote_last_viewed - The date on which the quote was most recently viewed by the customer [datetime]
office_id - The ID of the office associated with the deficiency
job_id - The internal ID of the job the deficiency was discovered on (matches id field in the Jobs dataset) [number]
repair_job_id - The internal ID of the job to repair the deficiency (matches id field in the Jobs dataset) [number]
job_service_id - The internal ID of the job's service to repair the deficiency (matches id field in the Appointment/Job Services dataset) [number]
job_service_status - The current status of the service on the job that repairs the deficiency [text]
job_status - The current status of the repair job [text]
job_created - The date on which the repair job was created [datetime]
job_updated - The date on which the repair job was most recently updated [datetime]
job_sales - The name of the ServiceTrade user who is assigned as the salesperson on the repair job [text]
job_first_appt - The date and time on which the first appointment on the repair job is scheduled to start [datetime]
job_last_appt - The date and time on which the last appointment on the repair job is scheduled to start [datetime]  If there is only one appointment on the repair job, this will be the same as job_first_appt. [datetime]
job_completed - The date on which the repair job was marked as complete [datetime]
deficiency_discovered_to_quote_created_days - The number of days from when the deficiency was created, to when the quote to repair it was created [number]
quote_created_to_sent_days - The number of days from when the quote was created, to when it was first sent to the customer for approval [number]
quote_sent_to_viewed_days - The number of days from when the quote was first sent to the customer for approval, to when the quote was first viewed by the customer [number]
quote_viewed_to_approved_days - The number of days from when the quote was first viewed by the customer, to when the quote was approved by the customer [number]
quote_sent_to_approved_days - The number of days from when the quote was sent to the customer, to when the quote was approved by the customer [number]
quote_approved_to_job_created_days - The number of days from when the quote was approved, to when the repair job for that quote was created [number]
job_created_to_job_completed_days  - The number of days from when the repair job was created, to when the repair job was marked as complete [number]
deficiency_discovered_to_job_completed_days - The number of days from when the deficiency was discovered, to when the repair job was marked as complete.  This measures the length of the entire 'cradle-to-grave' lifecycle of a repaired deficiency. [number]
last_reported_on - The most recent date on which the deficiency was last reported [datetime]
invoice_status - The current status of the invoice associated with the deficiency [text]
job_type - The job type of the associated deficiency [text]
quote_type - The quote type of the associated deficiency [text]

Locations

The 'locations' data set contains information about locations in your ServiceTrade account.  All locations in all statuses (including inactive locations) are represented in this dataset.

A record in the Locations table will refresh when any change is made to the corresponding location.

Fields:

id - The internal ID of the location [number]
name - The location name [text]
address - The location street address [text]
city - The location city [text]
state - The abbreviation of the location state or territory [text]
postal_code - The location postal code [text]
email - The location email address [text]
phone - The location phone number [text]
latitude - The location latitude [number]
longitude - The location latitude [number]
status - The location status [text]
offices - A list of the names of the offices assigned to the location [comma-separated text]
location_is_office - A flag indicating whether the location is considered an office [boolean]
regions - A list of the names of the regions containing the location [comma-separated text]
customer - The name of the ServiceTrade company which owns this location [text]
customer_id - The ID of the ServiceTrade company which owns this location [number]
created - The date on which the location was created [datetime]
updated - The date on which the location was most recently updated [datetime]
created_by - The name of the ServiceTrade user who created the location [text]

Items

The 'items' dataset contains information about the individual items associated with your ServiceTrade account.  Each row represents a single item.

A record in the Items table will refresh when any change is made to the corresponding item.

Fields:

id - The internal ID of the item [number]
name - The name of the item [text]
cost - The unit cost of the item [number]
customer_id - The id of the customer that the item belongs to [number]
customer - The name of the customer that the item belongs to [text]
service_line_id - The internal ID of the item's service line [number]
service_line - The name of the item's service line [text]
type - The type of item [number]
taxable- Whether this item is taxable [number]
active - The current status of the item [number]
code - The internal code associated with this item [number]
created - The date on which the item was created [datetime]
updated - The date on which the item was most recently updated [datetime]

Quotes

The 'quotes' data set contains information about quotes created in your ServiceTrade account.  All quotes in all statuses (including canceled and rejected quotes) are represented in this dataset. 

This dataset also includes information about the job to perform the repair work described in the quote (if there is one). Jobs whose quoted services have statuses of 'Canceled' or 'Void' are not included.   If there are multiple jobs associated with the same quote, the information from the most recently updated job is used.  If there are no jobs to perform the work described in the quote, all the fields beginning with jobs_ will be empty.

This dataset also includes information about the invoices associated with the repair job.  Invoices whose status is 'Void' are not included.  If there are no invoices associated with the repair job, the 'job_invoice_amount', 'job_first_invoice', and 'job_last_invoice' fields will be empty.

A Quote record will update whenever any change is made to the Quote, its Quote_Items, its related Job, or its related Services.

Fields:

id - The internal ID of the quote [number]
quote_number - The quote number [number]
name - The quote name [text]
status - The quote's current status [text]
created - The date on which the quote was created [datetime]
updated - The date on which the quote was most recently updated [datetime]
customer - The name of the ServiceTrade company which is the customer for this quote [text]
location - The name of this quote's location [text]
owner - The name of the ServiceTrade user who owns the quote [text]
location_state - The abbreviation of the state or territory of the quote's location [text]
location_postal_code - The postal code of the state or territory of the quote's location [text]
location_offices - A list of the names of the offices assigned to the quote's location [comma-separated text]
location_regions - A list of the names of the regions containing the quote's location [comma-separated text]
location_latitude - The latitude of the quote's location [number]
location_longitude - The longitude of the quote's location [number]
location_id - The ID of the quote's location (matches id field in the Locations dataset) [number]
customer_id - The ID of the quote's customer [number]
quote_assigned_to - The name of the ServiceTrade user who is assigned to the quote [text]
quote_sales - The name of the ServiceTrade user who is assigned as the salesperson on the quote [text]
contract_id - The ID of the contract assigned to the quote [text]
contract_name - The name of the contract assigned to the quote [text]
expires_on - The date on which this quote expires [datetime]
quote_first_sent - The date on which the quote was first sent to the customer for approval [datetime]
quote_last_sent - The date on which the quote was most recently sent to the customer for approval [datetime]
quote_last_sent_by - The name of the ServiceTrade user who last sent the quote to customer [text]
quote_sent_count - The number of times the quote was sent to the customer for approval [number]
is_sent - A flag indicating whether the quote was sent to the customer for approval [boolean]
quote_first_viewed - The date on which the quote was first viewed by the customer [datetime]
quote_last_viewed - The date on which the quote was most recently viewed by the customer [datetime]
quote_viewed_count - The number of times the quote was viewed by the customer [number]
is_viewed - A flag indicating whether the quote was viewed by the customer [boolean]
quote_approved - The date on which the quote was approved [datetime]
quote_first_approved - The date on which the quote was first approved by the customer [datetime]
quote_last_approved - The date on which the quote was most recently approved by the customer [datetime]
quote_approved_count - The number of times the quote was approved by the customer [number]
is_approved - A flag indicating whether the quote was approved by the customer [boolean]
quote_first_changes_requested - The date on which the quote first had changes requested [datetime]
quote_last_changes_requested - The date on which the quote most recently had changes requested [datetime]
quote_changes_requested_count - The number of times the quote had changes requested [number]
is_changes_requested - A flag indicating whether the quote had changes requested [boolean]
quote_first_emailed - The date on which the quote was first emailed out [datetime]
quote_last_emailed - The date on which the quote was most recently emailed out [datetime]
quote_emailed_count - The number of times the quote has been emailed out [number]
is_emailed - A flag indicating whether the quote has been emailed out [boolean]
quote_first_attachment_added - The date on which the quote first had an attachment added [datetime]
quote_last_attachment_added - The date on which the quote most recently had an attachment added [datetime]
quote_attachment_added_count - The number of times the quote had an attachment added [number]
is_attachment_added - A flag indicating whether the quote had an attachment added [boolean]
quote_service_lines - A list of the names of the service lines associated with this quote's services [comma-separated text]
total - The total quoted amount of the quote [number]  Note that this value is BEFORE tax is applied.  This calculation may be changed in a future data warehouse update.
quote_cost - The total cost of the items on the quote [number]
quote_margin - The difference between total and quote_cost, as a percentage of total [number]
quote_type - The type of the job that is being quoted [text]
job_number - The job number for the repair job associated with this quote [number]
job_type - The job type [text]
job_status - The current status of the job [text]
job_created - The date on which the job was created [datetime]
job_updated - The date on which the job was most recently updated [datetime]
job_assigned_to - The name of the ServiceTrade user who is assigned as the owner of this job [text]
job_assigned_to_office - The name of the office assigned to the user who is assigned as the owner of this job [text]
job_sales - The name of the ServiceTrade user who is assigned as the salesperson on this job [text]
job_cost - The sum total of the costs of all job items on this job [number]
job_appt_first_start - The date and time on which the first appointment on the job is scheduled to start [datetime]
job_appt_first_end - The date and time on which the first appointment on the job is scheduled to end [datetime]
job_appt_last_start - The date and time on which the last appointment on the job is scheduled to start [datetime]
job_appt_last_end - The date and time on which the last appointment on the job is scheduled to end [datetime]
job_due_start - The date on which the due window for the job begins [datetime]
job_due_end - The date on which the due window for the job ends [datetime]
job_invoice_amount - The sum total (not including sales tax) of all invoices associated with the job [number]
job_first_invoice - The date and time on which the job's first invoice was created [datetime]
job_last_invoice - The date and time on which the job's last invoice was created.  If the job has only one invoice, this will be the same as job_first_invoice. [datetime]
job_expected_margin - The difference between job_cost and job_estimated_price, as a percentage of job_estimated_price (see the jobs dataset for job_estimated_price) [number]
job_actual_margin - The difference between job_cost and job_invoice_amount, as a percentage of job_invoice_amount [number]
job_is_invoiced - A flag indicating whether the job has at least one invoice [boolean]
job_is_completed - A flag indicating whether the job has been marked as complete [boolean]
job_all_appts_completed - A flag indicating whether all the job's appointments have been completed [boolean]
job_completed_in_due_window - A flag indicating whether the job was marked as complete before the end of its due window [boolean]
created_by - The name of the ServiceTrade user who created the quote [text]
po_number - The PO number associated with the quote [number]
invoice_status - The current status of the invoice associated with the quote [text]
job_id - The internal ID of the job associated with the quote (matches id field in the Jobs dataset) [number]

Quote Items

The 'quote_items' dataset contains information about the individual quote items associated with quotes in your ServiceTrade account.  Each row represents a single quote item.

All Quote Items records are refreshed every time the data warehouse data updates.

Fields:

id - The internal ID of the quote item [number]
quote_id - The internal ID of the quote (matches id field in the Quotes dataset) [number]
item_code - The item code of the quote item [text]
item_type - The item type of the quote item [text]
item_name - The name of the quote item [text]
service_line - The name of the quote item's service line [text]
service_line_id - The internal ID of the quote item's service line [number]
lib_item_id - The internal ID of the item that is associated with the invoice item [id]
quantity - The quantity of the quote item [number]
price - The unit price of the quote item [number]
cost - The unit cost of the quote item [number]
total_price - The total price of the quote item with tax (quote item quantity multiplied by quote item unit cost, plus tax) [number]
total_cost - The total cost of the quote item [number]
taxable - Whether this quote item is taxable [number]
tax_rate - The tax rate percentage for this quote item [number]
service_id - The id of the service associated with this quote item [number]
service_description - The description of the service associated with this quote item [text]
service_asset - The name of the asset for the service associated with this quote item [text]
created - The date on which the quote item was created [datetime]
updated - The date on which the quote item was most recently updated [datetime]
quote_created - The date on which the quote item's quote was created [datetime]
quote_updated - The date on which the quote item's quote was most recently updated [datetime]

Invoices

The 'invoices' dataset contains information about invoices created for jobs in your ServiceTrade account.  Invoices in all statuses except 'Void' are included in this dataset.

A record in the Invoices table will refresh when any change is made to the corresponding invoice.

Fields:

id - The internal ID of the invoice [number]
invoice_number - The invoice number [text]
status - The current status of the invoice [text]
total - The total amount of the invoice, including tax [number]
subtotal - The amount of the invoice, not including tax [number]
invoice_type - The invoice type [text]
created - The date on which the invoice was created [datetime]
updated - The date on which the invoice was most recently updated [datetime]
transaction_date - The invoice transaction date [datetime]
invoice_service_lines - A list of the service lines associated with the invoice's items [comma-separated text]
customer - The name of the ServiceTrade company which is the customer for this invoice [text]
location - The name of the invoice's location [text]
location_id - The ID of invoice's location (matches id field in the Locations dataset) [number]
customer_id - The ID of the invoice's customer [number]
location_state - The abbreviation of the state or territory of the invoiced job's location [text]
location_postal_code - The postal code of the state or territory of the invoiced job's location [text]
location_offices - A list of the names of the offices assigned to the invoiced job's location [comma-separated text]
location_regions - A list of the names of the regions containing the invoiced job's location [comma-separated text]
location_latitude - The latitude of the invoiced job's location [number]
location_longitude - The longitude of the invoiced job's location [number]
contract_id - The ID of the contract assigned to the invoice [text]
contract_name - The name of the contract assigned to the invoice [text]
office - The name of the office associated with the invoice [text]
job_id - The internal ID of the invoiced job (matches id field in the Jobs dataset) [number]
job_number - The job number for the invoiced job [number]
job_type - The invoiced job type [text]
job_status - The current status of the invoiced job [text]
job_cost - The sum total of the costs of all job items on the invoiced job; null if the invoice has no associated job [number]
assigned_to - The name of the ServiceTrade user who is assigned as the owner of the invoiced job [text]
assigned_to_office - The name of the office assigned to the user who is assigned as the owner of the invoiced job [text]
job_sales - The name of the ServiceTrade user who is assigned as the salesperson on the invoiced job [text]
creator - The name of the ServiceTrade user who created the invoice [text]
creator_office - The name of the office assigned to the user created the invoice [text]
is_recurring_invoice - A flag indicating whether the invoice was generated from a recurring invoice [boolean]
invoice_first_sent - The date that this invoice was first sent to the customer [datetime]
invoice_last_sent - The date that this invoice was last sent to the customer [datetime]
invoicelink_first_viewed - The date that the Invoice Link for this invoice was first viewed [datetime] invoicelink_last_viewed - The date that the Invoice Link for this invoice was last viewed [datetime]
creator_id - The user_id of the user that created the invoice [number]
display_notes - The invoice notes [text]
due_date - The invoice due date [date]
name - The invoice name as seen in several parts of the ST app. This will often include other data points in the name, such as Job Number and Company Name [text]
owner_id - The user_id of the user that currently owns the invoice [number]
paid - A flag that indicates if the invoice has been paid [boolean]
partial - A flag indicating whether this invoice is the final invoice or not. If the flag is true, the invoice IS partial and thus NOT final. If the flag is false, the invoice is NOT partial and thus IS final [boolean]
payment_terms_id - The Payment Terms id of the set of payment terms that apply to this invoice (eg. Net 30, Net 15) [number]
purchase_order_number - The PO Number of the invoice [text]
sent - A flag indicating whether this invoice has been sent to the customer or not [boolean]
terms_id - The Terms id of the specific Terms and Conditions that apply to this invoice [number]
total_paid_amount - The sum of all payment amounts made on the invoice thus far [number]
quote_subtotal - The sum of all quote totals for all quotes tied to the job of the invoice in question [number]
quote_cost - The sum of all quote costs for all quotes tied to the job of the invoice in question [number] invoice_last_submitted_user_id - The id of the user that submitted the invoice to an external accounting system [number]
invoice_last_submitted_by - The name of the user that submitted the invoice to an external accounting system [text]
invoice_last_submitted - The time at which the invoice was submitted to the accounting system [datetime]
invoice_submitted_accounting_system_id - The id of the accounting system that the invoice was submitted to [number]
invoice_submitted_accounting_system_name - The name of the accounting system that the invoice was submitted to [text]
quote_type - The quote type of the associated invoice [text]
office_id - The ID of the office associated with the invoice
quote_id - The internal ID of the quote associated with the invoice (matches id field in the Quotes dataset) [number]

Recurring Invoices

The 'recurring_invoices' dataset includes information about recurring invoices in your ServiceTrade account.  In this dataset, recurring invoices are projected through (at least) the end of the year following the current year.  Only recurring invoices are included in this dataset.  One-time (single instance) invoices are not included. 

Each row in this dataset represents a single instance of that recurring invoice.  For instance, an invoice that occurs quarterly (every 3 months) would have up to 8 rows representing it in this dataset: up to 4 rows for the current year, and 4 rows for the next year.

A record in the Recurring_Invoices table will refresh when any change is made to the corresponding recurring invoice or to the Location of the recurring invoice.

Fields:

id - The internal ID of the recurring invoice [number]
sequence_id - A generated ID for this instance of the recurring invoice [text]
description - The invoice description [text]
location - The name of the invoice's location [text]
location_id - The ID of invoice's location (matches id field in the Locations dataset) [number]
location_state - The abbreviation of the state or territory of the invoiced job's location [text]
location_offices - A list of the names of the offices assigned to the invoiced job's location [comma-separated text]
location_regions - A list of the names of the regions containing the invoiced job's location [comma-separated text]
location_city - The name of the city of the invoiced job's location [text]
location_status - The status of the location of the invoiced job's location [text]
window_start - The beginning of the due window for this invoice [datetime]
window_end - The end of the due window for this invoice [datetime]
interval - The interval on which the invoice recurs [number]
frequency - The frequency on which the invoice recurs [text]
cost - The unit cost of the invoice item associated with the recurring invoice [number]
total - The total cost of the invoice items associated with the recurring invoice [number]
office_id - The ID of the office associated with the recurring invoice

Invoice Items

The 'invoice_items' dataset contains information about the individual invoice items associated with invoices in your ServiceTrade account.  Each row represents a single invoice item.   Items for invoices in all statuses except 'Void' are included in this dataset.

A record in the Invoice_Items table will refresh when any change is made to the corresponding invoice.

Fields:

id - The internal ID of the invoice item [number]
invoice_number - The invoice number [number]
invoice_id - The internal ID of the invoice (matches id field in the Invoices dataset) [number]
item_name - The name of the invoice item [text]
item_code - The item code of the invoice item [text]
item_type - The item type of the invoice item [text]
item_id - The internal ID of the item that is associated with the invoice item [id]
service_line - The name of the invoice item's service line [text]
service_line_id - The internal ID of the invoice item's service line [number]
quantity - The quantity of the invoice item [number]
price - The unit price of the invoice item [number]
subtotal - The total price of the invoice item without tax (invoice item quantity multiplied by invoice item unit cost) [number]
total - The total price of the invoice item with tax (invoice item quantity multiplied by invoice item unit cost, plus tax) [number]
tax_rate - The tax rate percentage for this invoice item [number]
is_recurring_invoice_item - A flag indicating whether this invoice item was generated by a recurring invoice [boolean]
job_item_id - The ID of the invoice item's associated job item; null if the invoice item has no associated job item [number]
job_item_total_cost - The total cost of the invoice item's associated job item (job item quantity multiplied by job item unit cost); null if the invoice item has no associated job item [number]
job_id - The ID of the invoice item's associated job item's associated job; null if the invoice item has no associated job item [number]
job_number - The number of the invoice item's associated job item's associated job; null if the invoice item has no associated job item [number]
service_id - The id of the service associated with this invoice item's associated job item [number] service_description - The description of the service associated with this invoice item's associated job item [text]
service_asset - The name of the asset for the service associated with this invoice item's associated job item [text]
created - The date on which the invoice item was created [datetime]
updated - The date on which the invoice item was most recently updated [datetime]
tax_group_name - The local town/city for which the taxes will be calculated by [text]
tax_group_state - The state for which the taxes will be calculated by [text]
invoice_status - The current status of the invoice associated with the invoice item [text]
job_type - The job type of the associated invoice item [text]
quote_type - The quote type of the associated invoice item [text]
customer - The name of the ServiceTrade company which is the customer for this associated invoice item [text]

Payments

The 'payments' dataset contains information about individual payment records. Each row represents a single payment instance.

A record in the Payments table will refresh when any change is made to the corresponding payment.

Fields:

id - The internal ID of the payment [number]
creator_id - The user_id of the user that created the payment [number]
invoice_id - The internal ID of the invoice associated with the payment record [number]
customer_id - The ID of the customer associated with this payment [number]
transaction_date - The payment transaction date [datetime]
payment_method - The payment method [text]
source - The source of the payment [text]
amount - The amount of the payment [number]
reference_number - The payment's reference number [text]
status - The payment status [text]
invoice_status - The current status of the invoice associated with the payment [text]
job_type - The job type of the associated payment [text]
quote_type - The quote type of the associated payment [text]
customer - The name of the ServiceTrade company which is the customer for this associated payment [text]
job_id - The internal ID of the job associated with the payment (matches id field in the Jobs dataset) [number]
created - The date on which the payment record was created [datetime]
updated - The date on which the payment record was most recently updated [datetime]

Contracts

The 'contracts' dataset contains information about service contracts created in your ServiceTrade account.  Single-use contracts generated from quotes (called 'quote' contracts in the ServiceTrade API) are NOT included in this dataset.

A record in the Contracts table will refresh when any change is made to the corresponding contract.

Fields:

id - The internal ID of the contract [number]
name - The contract name [text]
contract_type - The type of contract [text]
customer_id - The ID of the customer associated with this contract [number]
customer_name - The name of the customer associated with this contract [text]
parent_contract_id - The ID of this contract's parent contract [number]
parent_contract_name - The name of this contract's parent contract [text]
payment_terms - The payment terms for this contract [text]
sales - The name of the ServiceTrade user who is assigned as the salesperson of the job [text]
starts_on - The start date for the contract [datetime]
ends_on - The end date for the contract [datetime]
review_on - The review date for the contract [datetime]
customer_po - The customer purchase order number for this contract [text]
expected_revenue - The expected revenue for this contract [number]
expected_margin - The expected margin for this contract, as a percentage (e.g. 80.0 = 80% expected margin) [number]
actual_revenue - The actual invoiced revenue (post-sales tax) for all invoices associated with this contract [number]
actual_pretax_revenue - The actual invoiced revenue (pre-sales tax) for all invoices associated with  this contract [number]
actual_cost - The actual cost for all job items for all completed jobs associated with this contract [number]
actual_margin - The actual margin for this contract, calculated by comparing actual_pretax_revenue to actual_cost, for this contract, as a percentage (e.g. 80.0 = 80% actual margin) [number]
status - The status of this contract [text]
created - The date on which the contract was created [datetime]
updated - The date on which the contract was most recently updated [datetime] location_id - The internal ID of the location that the contract applies to [number]

Contract Items

The 'contract_items' dataset contains information about individual price rules and other items on service contracts created in your ServiceTrade account. 

All Contract_Item records are refreshed every time the data warehouse data updates.

Fields:

id - The internal ID of the contract item [number]
item_id - The internal ID of the item associated with this contract item [text]
item_name - The name of the contract item (usually derived from the associated item) [text]
item_code - The item code of the item associated with this contract item [text]
rule_type - The type of the contract item [text]
amount - The amount of the contract rule; for the rule type 'Markup', this is a percentage markup (for instance, 30.0 means 30% markup); for all other rule types, this is a dollar amount [number]
contract_id - The internal ID of the contract [number]
contract_name - The name of the contract [text]
created - The date on which the contract item was created [datetime]
updated - The date on which the contract was most recently updated [datetime]

Assets

The ‘Assets’ dataset contains information about the individual assets. Each row represents a single asset.

All Asset records are refreshed any time a change is made to the asset.

Fields:

id - The internal ID of the asset [number]
parent_id - The internal id of the parent asset [number]
location_id - T​​he internal id of the location associated with the asset [number]
name - The name of the asset [text]
asset_type - The asset type [text]
status - The current status of the asset [text]
created - The date on which the asset was created [datetime]
updated - The date on which the asset was most recently updated [datetime]

Asset Details

The ‘Asset Details’ dataset contains information about the individual asset data relating to the asset in the ‘Assets’ dataset.

All Asset Detail records are refreshed every time the data warehouse data updates.

Fields:

id - The internal ID of the asset detail associated with the assets table [number]
property_name - The name of the asset detail’s property [text]
property_value - The value of the asset detail’s property [text]

Services

The 'services' dataset includes information about services, both recurring and non-recurring, in your ServiceTrade account. Each row in this dataset represents a single instance of a service.

A record in the 'Services' table will refresh when any change is made to the corresponding service request.

Fields:

id - The internal ID of the recurring service [number]
created - The date on which this service was created [datetime]
updated - The date on which this service was most recently updated [datetime]
closed_on - The date on which the service was closed [datetime]
preferred_start_time - The preferred start time for this service [datetime]
window_start - The beginning of the due window for this service [datetime]
window_end - The end of the due window for this service [datetime]
description - The service description [text]
status - The status of this service [text]
service_line_id - The internal ID of the service's service line [number]
service_line - The name of the service's service line [text]
price - The estimated price of the service [number]
duration - The estimated duration of the service, in seconds [number]
job_id - The internal id of the job associated with the service [number]
location_id - The internal id of the location associated with the service [number]
location - The name of the service's location [text]
asset_id - The internal id of the asset associated with the service [number]
asset - The name of the service's asset [text]
recurrence_id - The internal ID of the recurring service, if there is one [number]
contract_id - The ID of the contract assigned to the service [text]
deficiency_id - The internal id of the deficiency associated with the service [number]
preferred_vendor_id - The internal id of the preferred vendor for the service [number]
sequence_id - A generated ID for this instance of the service [text]
invoice_status - The current status of the invoice associated with the service [text]
job_type - The job type of the associated service [text]
quote_type - The quote type of the associated service [text]
customer - The name of the ServiceTrade company which is the customer for this associated service [text]

Recurring Services

The 'recurring_services' dataset includes information about recurring services in your ServiceTrade account.  In this dataset, recurring services are projected through (at least) the end of the year following the current year.  Only recurring services are included in this dataset.  One-time (single instance) services are not included. 

Each row in this dataset represents a single instance of that recurring service.  For instance, a service that occurs quarterly (every 3 months) would have up to 8 rows representing it in this dataset: up to 4 rows for the current year, and 4 rows for the next year.

A record in the Recurring_Services table will refresh when any change is made to the corresponding recurring service or to the Location of the recurring service.

Fields:

id - The internal ID of the recurring service [number]
sequence_id - A generated ID for this instance of the recurring service [text]
description - The service description [text]
contract_id - The ID of the contract assigned to the service [number]
location - The name of the service's location [text]
location_id - The ID of the service's location [number]
office_id - The ID of the office associated with the recurring service
location_state - The abbreviation of the state or territory of the service's location [text]
location_postal_code - The postal code of the state or territory of the service's location [text]
location_offices - A list of the names of the offices assigned to the service's location [comma-separated text]
location_regions - A list of the names of the regions containing the service's location [comma-separated text]
location_status - The status of the service's location [text]
asset - The name of the service's asset [text]
window_start - The beginning of the due window for this service [datetime]
window_end - The end of the due window for this service [datetime]
interval - The interval on which the service recurs [number]
frequency - The frequency on which the service recurs [text]
price - The estimated price of the service [number]
duration - The estimated duration of the service, in seconds [number]
duration_hours - The estimated duration of the service, in hours [number]
service_line - The name of the service's service line [text]
service_line_id - The internal ID of the service's service line [number]
description - The service description [text]
preferred_techs - A list of preferred technicians for the service [comma-separated text]
preferred_tech_offices - A list of offices assigned to the preferred technicians for the service [comma-separated text]

Service Lines

The 'service_lines' dataset includes information about the various service lines that are available in Service Trade. Please note that this table contains the superset of all possible service lines. Based on your particular needs, some of may be turned off in the Service Trade core app, as they are not applicable.

A record in the Service_Lines table will refresh when any change is made to the corresponding service line.

Fields:

id - Primary key / service line id [number]
name - Service line name [text]
trade - The high-level trade that the service line is tied to (Fire Protection, Exhaust Cleaning, Security, etc) [text]
abbr - The abbreviation of the service line [text]

Technician Productivity

The 'tech_productivity' dataset contains information about the expected and actual daily revenue for your technicians.  In this dataset, each row represents a single technician on a single appointment. 

Because each job can have multiple invoices, each job can have more than appointment, and each appointment can have more than one technician and/or service, both expected and actual revenue may be distributed among multiple technician/appointment combinations.

When calculating the distribution of actual revenue, the pre-tax totals of all non-void invoices on a given job are summed.  Then, that revenue is distributed evenly across each technician/appointment combination.  For example, in the following scenario:

• Appointment 1:  Technician A and Technician B
• Appointment 2:  Technician C
• Appointment 3:  Technician A and Technician C

Technician A and Technician C will each receive 40% of the job's actual revenue, and Technician B will receive the remaining 20%.

When calculation the distribution of expected revenue, the estimated revenue for each service on a given appointment is summed. Then, that revenue is distributed evenly across all technicians assigned to that appointment.  This calculation is done independently for each appointment on a given job.  For example, in the following scenario:

• Appointment 1:  Service W and Service X / Technician A and Technician B
• Appointment 2:  Service Y / Technician C
• Appointment 3:  Service Z / Technician A, Technician C, and Technician D

Technician A would receive 50% of the expected revenue from Service W, 50% from Service X, and 33% from Service Z.  Technician B would receive 50% of the expected revenue from Service W and Service X.  Technician C would receive all of the expected revenue from Service Y and 33% of the revenue from Service Z.  Technician D would receive 33% of the revenue from Service Z.

Estimated service durations are distributed in like manner: the estimated durations of all services for each appointment are summed, then the resulting total duration is distributed evenly across all technicians assigned to that appointment.

IMPORTANT:  Services whose status is 'Void' or 'Canceled', and appointments whose status is 'Canceled', are ignored in this dataset, and are not used to calculate the expected and actual revenue distributions.

In addition, to prevent helper/apprentice technicians from being allocated a portion of expected and actual revenue, technicians whose office name contains the word 'helper' are ignored in this dataset only.  For instance, if an appointment was assigned to two technicians, one of which was a helper, 100% of the revenue and estimated duration for that appointment would be distributed to the other (non-helper) technician.

All Technician Productivity records are refreshed every time the data warehouse data updates.

Fields:

appointment_id - The internal ID for the appointment [number]
technician_id - The internal ID for the technician [number]
appointment_technician_id - A generated ID for the combination of this technician and this appointment [text]
job_id - The internal ID for this appointment's job (matches id field in the Jobs dataset) [number]
office_id - The ID of the office associated with the tech productivity
job_number - The job number [number]
job_type - The job type [text]
customer - The name of the ServiceTrade company which is the customer for this job [text]
location - The name of the job's location [text]
location_offices - A list of the names of the offices assigned to the job's location [comma-separated text]
location_id - The ID of the job's location (matches id field in the Locations dataset) [number]
customer_id - The ID of the job's customer [number]
technician_name - The full name of the technician [text]
technician_office - The name of the office assigned to the technician [text]
released - A flag indicating whether this appointment has been released [boolean]
status - The status of this appointment [text]
appointment_start - The date and time on which this appointment is scheduled to start [datetime]
appointment_end - The date and time on which this appointment is scheduled to end [datetime]
created - The date on which this appointment was created [datetime]
updated - The date on which this appointment was most recently updated [datetime]
actual_revenue - The invoiced revenue allocated to this technician on this appointment (see distribution rules above) [number]
estimated_revenue - The estimated revenue allocated to this technician on this appointment (see distribution rules above) [number]
estimated_duration - The estimated duration for the services allocated to this technician on this appointment, in seconds (see distribution rules above) [number]
estimated_duration_hours - The estimated duration for the services allocated to this technician on this appointment, in hours (see distribution rules above) [number]
estimated_actual_revenue_difference - The difference between actual_revenue and estimated_revenue for this technician on this appointment [number]
en_route_total_minutes - The sum total of the durations of all en route clock events for this technician on this appointment [number]
on_site_total_minutes - The sum total of the durations of all on site clock events for this technician on this appointment [number]
job_prep_total_minutes - The sum total of the durations of all job preparation clock events for this technician on this appointment [number]
job_first_invoice - The earliest transaction date of the invoices for the job [datetime]
job_last_invoice - The latest transaction date of the invoices for the job; if the job has only one invoice, this will be the same as job_first_invoice [datetime]

Technician Service Lines

The 'tech_service_lines' dataset contains information about your technicians and the service line capabilities that are assigned to them.  In this dataset, each row represents a single service line assignment to a technician.

All Tech_Service_Line records are refreshed every time the data warehouse data updates.

Fields:

technician_id - The internal ID for the technician [number]
technician_name - The full name of the technician [text]
technician_office - The name of the office assigned to the technician [text]
service_line - The name of the assigned service line [text]
service_line_id - The internal ID of the assigned service line [number] office_id - The ID of the office associated with the tech service line

Marketing Impressions

The 'marketing_impressions' dataset contains information about marketing impressions -- actions you perform that your customer can experience -- that occur during service delivery.  In this dataset, each row represents a single action that created a customer marketing impression.

All Marketing Impression records are refreshed every time the data warehouse data updates.

Fields:

id - The internal ID of the action [number]
user_id - The internal ID of the user who initiated the action [number]
office_id - The ID of the office associated with the marketing impression
action - The internal code name of the action that was performed [text]
action_name - The name of the action that was performed [text]
created - The date on which the action was created [datetime]
updated - The date on which the action was most recently updated [datetime]
record_id - The internal ID of the record with which the action was associated (for instance, a job ID or a quote ID) [number]
record_number - The number of the record with which the action was associated [text]
record_type - The type of record with which the action was associated [text]
subrecord_id - The internal ID of the subrecord with which the action was associated (for instance, an attachment ID or a clock event ID on a job record) [number]
record_assigned_to - The name of the user to which the action's associated record is assigned text]
record_created - The date on which the action was created [datetime]
record_action_date - The date on which the action occurred [datetime]
customer - The name of the ServiceTrade company which is the customer for the associated record [text]
location - The name of the location of the associated record [text]
location_offices - A list of the names of the offices assigned to the location of the associated record [comma-separated text]
location_id - The ID of the location of the associated record (matches id field in the Locations dataset) [number]
customer_id - The ID of the customer of the associated record [number]
user_name - The full name of the user who performed this action [text]
user_username - The username of the user who performed this action [text]
user_first_name - The first name of the user who performed this action [text]
user_last_name - The last name of the user who performed this action [text]
user_email - The e-mail address of the user who performed this action[text]
user_status - The current status of the user (active, inactive, etc) who performed this action[text]
user_is_tech - A flag to indicate whether the user who performed this action is a technician [boolean]
user_is_admin - A flag to indicate whether the user who performed this action is an admin [boolean]
user_is_sales - A flag to indicate whether the user who performed this action is a sales person [boolean]
attachment_id - Attachment Id of the attachment associated with the MIPS action/record [number]
attachment_file_name - File name of the attachment associated with the MIPS action/record [text]
attachment_file_type - File type (Eg. Image) of the attachment associated with the MIPS action/record [text]
attachment_file_extension - File extension (Eg. JPEG) of the attachment associated with the MIPS action/record [text]
attachment_description - Description of the attachment associated with the MIPS action/record [text]
attachment_content_url - Content URL of the attachment associated with the MIPS action/record [text]
attachment_purpose - Purpose of the attachment associated with the MIPS action/record [text]

Summary Info

The 'summary_info' dataset is designed as a storage tank for high-level KPIs and other metrics of value that exist at an account-wide level. At present, this table only holds the timestamp of the last time the data warehouse data was refreshed (in timezone UTC + 0). In the future, more KPIs and metrics will be added to this table as new rows with their own distinct value in the measurement_name field.

All Summary Info records are refreshed every time the data warehouse data updates.

Fields:

id - Primary key / row id of the summary_info table [number]
measurement_name - The name of the measurement [text]
measurement_value - The value of the measurement in question, stored as a string [text]

Users

The 'users' dataset returns a wide array of information on various ServiceTrade app users.

All User records are refreshed every time the data warehouse data updates.

Fields:

id - Primary key / user id of the Users table [number]
company_id - Company id that the user belongs to [number]
location_id - location id of the default location/office of the user [number]
office_id - The ID of the office associated with the user
username - Username of the user [text]
email - E-mail address of the user [text]
first_name - User's first name [text]
last_name - User's last name [text]
status - Current status of the user (active, inactive, etc) [text]
phone - Phone number of the user [text]
details - User details [text]
is_tech - A flag to indicate whether the user is a technician [boolean]
is_admin - A flag to indicate whether the user is an admin [boolean]
is_sales - A flag to indicate whether the user is a sales person [boolean]
timezone - The user's time zone [text]
created - The date/time that the user record was created [datetime]
updated - The date/time that the user record was last updated [datetime]
created_by - The name of the ServiceTrade user who created the User [text]
updated_by - The name of the ServiceTrade user who updated the User [text]

Comments

The 'comments' dataset returns comments from the following entities: Job, Company, Invoice, Quote, Deficiency, Location, Account, Service Request, Comment, Contract.

A record in the Comment table will refresh when any change is made to the corresponding comment.

Fields:

id - Primary key / user id of the Comments table [number]
author_id - The ID of the user that authored the comment [number]
author_name - The name of the user associated with this comment [text]
content - The comment message itself [text]
entity_id - The ID of the parent entity that the comment was created on [number]
entity_type - The type of record, one of: Job, Company, Invoice, Quote, Deficien,cy Location, Account, Service ,Request Comment, Contract [text]
created - The date on which the comment was created [datetime]
updated - The date on which the comment was most recently updated [datetime]

External IDs

The 'external_ids' dataset contains mappings of ServiceTrade records to their external IDs. In this dataset, each row represents a mapping of one ServiceTrade record to its external ID for one external system.

All External ID records are refreshed every time the data warehouse data updates.

Fields:

entity_type - The type of record, one of: Company, Contact, Contract, Deficiency, Job, Item, Location, Quote, User, Warehouse [text]
entity_id - The internal ID of the record [number]
value - The record's external ID [number]
system_name - The code name for the external system [text]
system_description - The display name for the external system [text]

Tags

The 'tags' dataset contains mappings of ServiceTrade records to their tags. In this dataset, each row represents a mapping of one ServiceTrade record to one tag.

All Tag records are refreshed every time the data warehouse data updates.

Fields:

entity_type - The type of record, one of: Location, Quote, Deficiency, Job, Invoice, Contract [text]
entity_id - The internal ID of the record [number]
tag - The tag [number]
created - The date on which this tag was associated with this entity [datetime]

Attachments

The 'attachments' table contains information about the files attached to various entities in your ServiceTrade account. Each row represents a single attachment.

A record in the Attachments table will refresh when any change is made to the corresponding attachment.

Fields:

id - The internal ID of the attachment [number]
creator_id - The ID of the user who created the attachment [number]
file_name - The name of the attached file [text]
file_type - The type of the attached file [text]
description - The description of the attachment [text]
storage_type - The type of storage used for the attachment [number]
meta - The metadata associated with the attachment [text]
checksum - The checksum value of the attached file [text]
deleted - A flag indicating whether the attachment has been deleted [boolean]
entity_type_id - The type of the entity for the associated attachment [number]
entity_type_name - The name of the type of the entity for the associated attachment [text]
entity_id - The ID of the entity to which the attachment is attached [number]
viewable_by - The number of people who have view access to the attachment [number]
purpose_id - The purpose for which the attachment is attached [number]
purpose_name - The name of the purpose for the associated attachment [text]
created - The date on which the attachment was created [datetime]
updated - The date on which the attachment was most recently updated [datetime]
added_by - The name of the ServiceTrade user who added the attachment [text]
deleted_by - The name of the ServiceTrade user who deleted the attachment [text]

Contacts

The 'contacts' dataset contains information about the contacts associated with your ServiceTrade account.  Each row represents a single contact.

All Contact records are refreshed every time the data warehouse data updates.

Fields:

id - The internal ID of the contact [number]
first_name - The first name of the contact [text]
last_name - The last name of the contact [text]
type - The contact type [text]
phone - The primary phone number of the contact [text]
mobile - The mobile phone number of the contact [text]
alternate_phone - The alternate phone number of the contact [text]
email - The email address of the contact [text]
created - The date on which the contact was created [datetime]
updated - The date on which the contact was most recently updated [datetime]
company_id - The internal ID of the company associated with the contact [number]
location_id - The internal ID of the location asssociated with the contact [number]
created_by - The full name of the user who created the contact [text]

Companies

The 'companies' dataset contains information about the companies associated with your ServiceTrade account.  Each row represents a single company.

All Company records are refreshed every time the data warehouse data updates.

Fields:

id - The internal ID of the company [number]
name - The company name [text]
address - The company street address [text]
city - The company city [text]
state - The abbreviation of the company state or territory [text]
zipcode - The company zipcode [text]
phone - The company phone number [text]
status - The company status [text]
created - The date on which the company was created [datetime]
updated - The date on which the company was most recently updated [datetime]
created_by - The full name of the user who created the company [text]
is_customer - A flag indicating whether the company is a customer [boolean]
is_vendor - A flag indicating whether the company is a vendor [boolean]
is_parts_vendor - A flag indicating whether the company is a parts vendor [boolean]
ref_number - The company's reference number [number]

PartsLedger

PartsLedger Items

The 'partsledger_items' dataset contains information about partsledger items in your PartsLedger account.

A record in the PartsLedger_Items table will refresh when any change is made to the corresponding item.

Fields:

_id - The internal ID of the partsledger item [number]
description- The description of the partsledger item [text]
name - The name of the partsledger item [text]
number - The number of the partsledger item [text]
sku - The SKU of the partsledger item [text]
created - The date on which the partsledger item was created [datetime]
updated - The date on which the partsledger item was most recently updated [datetime]
inactive - Flag stating where the partsledger item is inactive [bool]
default_purchase_price - The base price for the partsledger item [number]
cogs_account_id- The internal ID of the cogs account associated with the partsledger item [number]
cogs_account_name - The name of the partsledger item's receiving cogs account [text]
cogs_account_number - The number of the partsledger item's receiving cogs account[text]
inventory_account_id- The internal ID of the inventory account associated with the partsledger item [number]
inventory_account_name - The name of the partsledger item's receiving inventory account [text]
inventory_account_number - The number of the partsledger item's receiving inventory account[text]
account_partsledger_uuid - The uuid of the partsledger account [number]

PartsLedger Purchase Orders

The 'partsledger_purchase_orders' dataset contains information about purchase orders in your PartsLedger account.

A record in the PartsLedger_Purchase_Orders table will refresh when any change is made to the corresponding PO.

Fields:

_id - The internal ID of the purchase order [number]
number - The purchase order number [number]
display_number - The purchase order number, as it is displayed in the PartsLedger user interface [text]
external_id - The purchase order's external ID [text]
description - The purchase order description [text]
issue_date - The date on which the purchase order was issued [datetime]
delivery_date - The date on which the purchase order was delivered [datetime]
status - The status of the purchase order [text]
amount - The total amount of the purchase order [number]
notes - The purchase order notes [text]
approved_date - The date on which the purchase order was approved [datetime]
approved_by - The full name of the user who approved the purchase order [text]
approved_by_email - The email address of the user who approved the purchase order [text]
vendor_id - The internal ID of the vendor associated with the purchase order [number]
vendor_name - The name of the vendor associated with the purchase order [text]
warehouse_id - The internal ID of the warehouse associated with the purchase order [number]
warehouse_name - The name of the warehouse associated with the purchase order [text]
location_id - The internal ID of the location associated with the purchase order [number]
location_name - The name of the location associated with the purchase order [text]
shipping_address_id - The internal ID of the purchase order shipping address [number]
shipping_address_name - The name of the purchase order shipping address [text]
shipping_address_line1 - The first line of the purchase order shipping address [text]
shipping_address_line2 - The second line of the purchase order shipping address [text]
shipping_address_city - The city the purchase order shipping address [text]
shipping_address_state - The state/province of the purchase order shipping address [text]
shipping_address_postal_code - The postal code of the purchase order shipping address [text]
billing_address_id - The internal ID of the purchase order billing address [number]
billing_address_name - The name of the purchase order billing address [text]
billing_address_line1 - The first line of the purchase order billing address [text]
billing_address_line2 - The second line of the purchase order billing address [text]
billing_address_city - The city the purchase order billing address [text]
billing_address_state - The state/province of the purchase order billing address [text]
billing_address_postal_code - The postal code of the purchase order billing address [text]
receiving_account_id - The internal ID of the receiving GL account [number]
receiving_account_name - The name of the receiving GL account [text]
receiving_account_number - The number of the receiving GL account [text]
created - The date on which the purchase order was created [datetime]
updated - The date on which the purchase order was most recently updated [datetime]

PartsLedger Purchase Order Line Items

The 'partsledger_purchase_order_items' dataset contains information about individual purchase order line items in your PartsLedger account.

A record in the PartsLedger_Purchase_Order_Items table will refresh when any change is made to the corresponding PO Item.

Fields:

_id - The internal ID of the purchase order item [number]
description - The purchase order item description [text]
quantity - The quantity ordered of the purchase order item [number]
quantity_received - The quantity received of the purchase order item [number]
amount - The total amount of the purchase order item [number]
tax_amount - The total tax amount of the purchase order item [number]
purchase_order_id - The internal ID of the purchase order item's parent purchase order [number]
purchase_order_number - The purchase order item's parent purchase order number [text]
purchase_order_display_number - The purchase order item's parent purchase order number, as it is displayed in the PartsLedger user interface [text]
purchase_order_external_id - The purchase order item's parent purchase order's external ID [text]
sales_order_id - The internal ID of the purchase order item's associated sales order [number]
sales_order_number - The number of the purchase order item's associated sales order [number]
sales_order_display_number - The number of the purchase order item's associated sales order, as it is displayed in the PartsLedger user interface [number]
sales_order_external_id - The external ID of the purchase order item's associated sales order [text]
sales_order_external_reference - The external reference of the purchase order item's associated sales order [text]
servicetrade_job_number - The purchase order item's associated sales order's corresponding ServiceTrade job number [number]
item_id - The internal ID of the purchase order item's associated item [number]
item_name - The name of the purchase order item's associated item [text]
item_number - The number of the purchase order item's associated item [text]
item_sku - The number of the purchase order item's associated SKU [text]
ledger_account_id - The internal ID of the purchase order item's GL account [number]
ledger_account_name - The name of the purchase order item's receiving GL account [text]
ledger_account_number - The number of the purchase order item's receiving GL account [text]
created - The date on which the purchase order item was created [datetime]
updated - The date on which the purchase order item was most recently updated [datetime]

PartsLedger Sales Orders

The 'partsledger_sales_orders' dataset contains information about sales orders in your PartsLedger account.

A record in the PartsLedger_Sales_Orders table will refresh when any change is made to the corresponding SO.

Fields:

_id - The internal ID of the sales order [number]
number - The sales order number [number]
display_number - The sales order number, as it is displayed in the PartsLedger user interface [text]
external_id - The sales order's external ID [text]
external_reference - The sales order's external reference [text]
status - The status of the sales order [text]
notes - The sales order notes [text]
amount - The total amount of the sales order [number]
order_date - The date on which the sales order was ordered [datetime]
delivery_date - The date on which the sales order was delivered [datetime]
complete_date - The date on which the sales order was completed [datetime]
location_id - The internal ID of the location associated with the sales order [number]
location_name - The name of the location associated with the sales order [text]
approved_date - The date on which the sales order was approved [datetime]
approved_by - The full name of the user who approved the sales order [text]
approved_by_email - The email address of the user who approved the sales order [text]
servicetrade_job_number - The sales order's corresponding ServiceTrade job number [number]
created - The date on which the sales order was created [datetime]
updated - The date on which the sales order was most recently updated [datetime]

PartsLedger Sales Order Line Items

The 'partsledger_sales_order_items' dataset contains information about individual purchase order line items in your PartsLedger account.

A record in the PartsLedger_Sales_Order_Items table will refresh when any change is made to the corresponding SO Item.

Fields:

_id - The internal ID of the sales order item [number]
description - The sales order item description [text]
quantity - The quantity ordered of the sales order item [number]
quantity_fulfilled - The quantity fulfilled of the sales order item [number]
amount - The total amount of the sales order item [number]
tax_amount - The total tax amount of the sales order item [number]
external_id - The sales order item external ID [text]
purchase_order_item_id - The internal ID of the sales order item's associated purchase order item [number]
purchase_order_number - The sales order item's associated purchase order number [text]
purchase_order_display_number - The sales order item's associated purchase order number, as it is displayed in the PartsLedger user interface [text]
purchase_order_external_id - The sales order item's associated purchase order's external ID [number]
sales_order_id - The internal ID of the sales order item's parent sales order [number]
sales_order_number - The number of the sales order item's parent sales order [number]
sales_order_display_number - The number of the sales order item's parent sales order, as it is displayed in the PartsLedger user interface [text]
sales_order_external_id - The external ID of the purchase order item's associated sales order [text]
sales_order_external_reference - The external reference of the purchase order item's associated sales order [text]
servicetrade_job_number - The purchase order item's associated sales order's corresponding ServiceTrade job number [number]
created - The date on which the sales order item was created [datetime]
updated - The date on which the sales order item was most recently updated [datetime]

PartsLedger Shipment Receipts

The 'partsledger_shipment_receipts' dataset contains information about shipment receipts in your PartsLedger account.

A record in the PartsLedger_Shipment_Receipts table will refresh when any change is made to the corresponding Shipment Receipt.

Fields:

_id - The internal ID of the shipment receipt [number]
receipt_date - The shipment receipt date [datetime]
reference - The shipment receipt reference number [text]
vendor_id - The internal ID of the vendor associated with the shipment receipt [number]
vendor_name - The name of the vendor associated with the shipment receipt [text]
created - The date on which the shipment receipt was created [datetime]
updated - The date on which the shipment receipt was most recently updated [datetime]

PartsLedger Shipment Receipt Line Items

The 'partsledger_shipment_receipt_items' dataset contains information about individual shipment receipt line items in your PartsLedger account.

A record in the PartsLedger_Shipment_Receipt_Items table will refresh when any change is made to the corresponding Shipment Receipt Item.

Fields:

_id - The internal ID of the shipment receipt item [number]
amount - The total amount of the shipment receipt item [number]
status - The status of the shipment receipt item [text]
shipment_receipt_id - The internal ID of the shipment receipt item's parent shipment receipt [number]
shipment_receipt_reference - The shipment receipt item's parent shipment receipt's reference number [text]
item_id - The internal ID of the shipment receipt item's associated item [number]
item_name - The name of the shipment receipt item's associated item [text]
item_number - The number of the shipment receipt item's associated item [text]
item_sku - The number of the shipment receipt item's associated SKU [text]
warehouse_id - The internal ID of the shipment receipt item's associated warehouse [number]
purchase_order_id - The internal ID of the shipment receipt item's associated purchase order [number]
purchase_order_number - The shipment receipt item's parent purchase order number [text]
purchase_order_display_number - The shipment receipt item's parent purchase order number, as it is displayed in the PartsLedger user interface [text]
purchase_order_external_id - The shipment receipt item's parent purchase order's external ID [text]
purchase_order_item_id - The internal ID of the shipment receipt item's associated purchase order item [number]
purchase_order_item_quantity - The quantity ordered of the shipment receipt item's associated purchase order item [number]
purchase_order_item_quantity_received - The quantity received of the shipment receipt item's associated purchase order item [number]
sales_order_id - The internal ID of the shipment receipt item's associated sales order [number]
sales_order_number - The number of the shipment receipt item's associated sales order [number]
sales_order_display_number - The number of the shipment receipt item's associated sales order, as it is displayed in the PartsLedger user interface [text]
sales_order_external_id - The external ID of the shipment receipt item's associated sales order [text]
sales_order_external_reference - The external reference of the pshipment receipt item's associated sales order [text]
servicetrade_job_number - The purchase order item's associated sales order's corresponding ServiceTrade job number [number]
sales_order_item_id - The internal ID of the shipment receipt item's associated sales order item [number]
sales_order_item_quantity - The quantity ordered of the shipment receipt item's associated sales order item [number]
sales_order_item_quantity_fulfilled - The quantity fulfilled of the shipment receipt item's associated sales order item [number]
created - The date on which the shipment receipt item was created [datetime]
updated - The date on which the shipment receipt item was most recently updated [datetime]

PartsLedger Transactions

The 'partsledger_transactions' dataset contains information about GL transactions in your PartsLedger account.  Each row in this table will represent either a credit line ('credit' value is nonzero, and 'debit' value is zero) or a debit line ('debit' value is nonzero, and 'credit' value is zero), never both a credit and a debit.

A record in the PartsLedger_Transactions table will refresh when any change is made to the corresponding Transaction.

Fields:

_id - The internal ID of the transaction [number]
description - The description of the transaction [text]
debit - The debit amount of the transaction; if debit is nonzero, then credit will be zero [number]
credit - The credit amount of the transaction; if credit is nonzero, then debit will be zero [number]
transaction_date - The date of the transaction [datetime]
posted_date - The posted date of the transaction [datetime]
ledger_account_id - The internal ID of the transaction's GL account [number]
ledger_account_name - The name of the transaction's GL account [text]
ledger_account_number - The number of the transaction's GL account [text]
vendor_id - The internal ID of the vendor associated with the transaction [number]
vendor_name - The name of the vendor associated with the transaction [text]
journal_id - The internal ID of the journal entry associated with the transaction [number]
journal_number - The number of the journal entry associated with the transaction [text]
journal_entry_type - The entry type of the journal entry associated with the transaction [text]
journal_source_ledger - The name of the source ledger of the journal entry associated with the transaction [text]
journal_reference - The reference information for the journal entry associated with the transaction [text]
journal_status - The status of the journal entry associated with the transaction [text]
location_id - The internal ID of the location associated with the transaction [number]
location_name - The name of the location associated with the transaction [text]
location_entity_name - The entity name of the location associated with the transaction [text]
location_entity_email - The entity email address of the location associated with the transaction [text]
location_entity_phone - The entity phone number of the location associated with the transaction [text] 
location_address_line1- The first line of the address of the location associated with the transaction [text]
location_address_line2- The second line of the address of the location associated with the transaction [text]
location_address_city - The city of the address of the location associated with the transaction [text]
location_address_state - The state/province of the address of the location associated with the transaction [text]
location_address_postal_code - The postal code of the address of the location associated with the transaction [text]
cost_center_id - The internal ID of the cost center associated with the transaction [number]
cost_center_name - The name of the cost center associated with the transaction [text]
cost_center_description - The description of the cost center associated with the transaction [text]
sales_order_id - The internal ID of the sales order associated with the transaction [number]
custom_1_id - The internal ID of the first custom field associated with this transaction [number]
custom_2_id - The internal ID of the second custom field associated with this transaction [number]
custom_3_id - The internal ID of the third custom field associated with this transaction [number]
created - The date on which the transaction was created [datetime]
updated - The date on which the transaction was most recently updated [datetime]

PartsLedger Stock Adjustments

The 'partsledger_stock_adjustments' dataset contains information about stock adjustments in your PartsLedger account.

A record in the PartsLedger_Stock_Adjustments table will refresh when any change is made to the corresponding Stock Adjustment.

Fields:

_id - The internal ID of the stock adjustment [number]
description- The description of the stock adjustment [number]
transaction_date - The transaction date of the stock adjustment [datetime]
posted_date - The posted date of the stock adjustment [datetime]
quantity - The quantity of the stock adjustment [number]
quantity_picked - The quantity picked for the stock adjustment [number]
quantity_available - The quantity available to be picked for the stock adjustment [number]
cost_basis - The cost basis for the stock adjustment [number]
average_cost - The cost basis for the stock adjustment [number]
price - The price for the stock adjustment [number]
cost_layers - The sets of cost information underlying the average cost, as a JSON object [text]
type - The type of the stock adjustment [text]
transfer_id - The internal ID of the stock transfer associated with the stock adjustment [number]
locked - Whether the stock adjustment is locked [boolean]
item_id - The internal ID of the item whose stock was adjusted [number]
item_name - The name of the item whose stock was adjusted [text]
item_number - The number of the item whose stock was adjusted [text]
item_sku - The SKU of the item whose stock was adjusted [text]
warehouse_id - The internal ID of the warehouse associated with the stock adjustment [text]
warehouse_name - The name of the warehouse associated with the stock adjustment [text]
warehouse_description - The description of the warehouse associated with the stock adjustment [text]
warehouse_address_name - The name of the address of the warehouse associated with the stock adjustment [text]
warehouse_address_line1 - The first line of the address of the warehouse associated with the stock adjustment [text]
warehouse_address_line2 - The second line of the address of the warehouse associated with the stock adjustment [text]
warehouse_address_city - The address city of the warehouse associated with the stock adjustment [text]
warehouse_address_state - The address state/province of the warehouse associated with the stock adjustment [text]
warehouse_address_postal_code - The address postal code of the warehouse associated with the stock adjustment [text]
adjustment_ledger_account_id - The internal ID of the adjustment GL ledger account associated with the stock adjustment [number]
journal_id - The internal ID of the journal entry associated with the stock adjustment [number]
journal_number - The number of the journal entry associated with the stock adjustment [text]
journal_entry_type - The type of the journal entry associated with the stock adjustment [text]
journal_source_ledger - The source ledger of the journal entry associated with the stock adjustment [text]
journal_reference - The reference of the journal entry associated with the stock adjustment [text]
journal_status - The status of the journal entry associated with the stock adjustment [text]
purchase_order_item_id - The internal ID of the purchase order item associated with the stock adjustment [number]
sales_order_item_id - The internal ID of the sales order item associated with the stock adjustment [number]
production_item_id - The internal ID of the production item associated with the stock adjustment [number]
shipment_receipt_item_id - The internal ID of the shipment receipt item associated with the stock adjustment [number]
purchase_order_item_id - The internal ID of the purchase order item associated with the stock adjustment [datetime]
created - The date on which the stock adjustment was created [datetime]
updated - The date on which the stock adjustment was most recently updated [datetime]

PartsLedger Transfers

The 'partsledger_transfers' dataset contains information about transfers in your PartsLedger account.

A record in the PartsLedger_Transfers table will refresh when any change is made to the corresponding Transfer record.

Fields:

_id - The internal ID of the transfer [number]
date - The date of the transfer [datetime]
quantity - The quantity of the transfer [number]
status - The status of the transfer [text]
item_id - The internal ID of the item that was transferred [number]
from_warehouse_id - The internal ID of the warehouse that the item was transferred from [number]
from_warehouse_name - The name of the warehouse that the item was transferred from [text]
from_warehouse_description - The description of the warehouse that the item was transferred from [text]
from_warehouse_address_line_1 - The first line of the address of the warehouse that the item was transferred from [text]
from_warehouse_address_line_2  - The second line of the address of the that the item was transferred from [text]
from_warehouse_address_city - The city of the address of the warehouse that the item was transferred from [text]
from_warehouse_address_state - The state/province of the address of the that the item was transferred from [text]
from_warehouse_address_postal_code - The postal code of the address of the that the item was transferred from [text]
to_warehouse_id- The internal ID of the warehouse that the item was transferred to [number]
to_warehouse_name- The name of the warehouse that the item was transferred to [text]
to_warehouse_description - The description of the warehouse that the item was transferred to [text]
to_warehouse_address_line_1 - The first line of the address of the warehouse that the item was transferred to [text]
to_warehouse_address_line_2 - The second line of the address of the that the item was transferred to [text]
to_warehouse_address_city - The city of the address of the warehouse that the item was transferred to [text]
to_warehouse_address_state - The state/province of the address of the that the item was transferred to [text]
to_warehouse_address_postal_code - The postal code of the address of the that the item was transferred to [text]
created - The date on which the transfer was created [datetime]
updated - The date on which the transfer was most recently updated [datetime]

PartsLedger Warehouses

The 'partsledger_warehouses' dataset contains information about warehouses in your PartsLedger account.

A record in the PartsLedger_Warehouses table will refresh when any change is made to the corresponding Warehouse.

Fields:

_id - The internal ID of the warehouse[number]
name - The name of the warehouse [text]
description - The description of the warehouse [text]
address_name - The name of the address of the warehouse [text]
address_line1 - The first line of the address of the warehouse [text]
address_line2 - The second line of the address of the warehouse [text]
address_city - The address city of the warehouse [text]
address_state - The address state/province of the warehouse [text]
address_postal_code - The address postal code of the warehouse [text]
account_partsledger_uuid - The uuid of the partsledger account [number]
location_name - The name of the location of the warehouse [text]
parent_warehouse_id - The internal ID of the warehouse’s parent warehouse[text]
parent_warehouse_name - The name of the warehouse’s parent warehouse [text]
created - The date on which the warehouse was created [datetime]
updated - The date on which the warehouse was most recently updated [datetime]

Project Management

Budgets

The 'budgets' dataset contains information about the individual budgets. Each row represents a single budget.

All Budget records are refreshed any time a change is made to the budget.

Fields:

id - The internal ID of the budget [number]
job_id - The ID of the budget's job [number]
baseline_id - The ID of the budget's baseline [number]
parent_id - The internal ID of the parent budget [number]
last_lock_user_id - The internal ID of the last ServiceTrade user to lock the budget [number]
status - The status of the budget [text]
type - The budget type - Granular or High Level [text]
total - The total cost of the budget [number]
total_price - The total price of the budget [number]
owner_id - The ID of the budget owner [number]
last_lock -The date on which the budget was last locked [datetime]
created - The date on which the budget was created [datetime]
updated - The date on which the budget was most recently updated [datetime]

Budget Categories

The 'budget_categories' dataset contains information about high level budgets. Each row represents a single budget category.

All Budget Category records are refreshed any time a change is made to the budget.

Fields:

id - The internal ID of the budget category [number]
budget_id - The internal ID of the budget associated with the budget category [number]
service_request_id - The internal ID of the service request associated with the budget category [number]
lib_item_type - The lib item type of the budget line item [number]
cost - The unit cost of the budget category [number]
quantity - The quantity of budget category [number]
price - The unit price of the budget category [number]
created - The date on which the budget category was created [datetime]
updated - The date on which the budget category was most recently updated [datetime]

Budget Line Items

The 'budget_line_items' dataset contains information about granular level budgets. Each row represents a single budget category.

All Budget Line Item records are refreshed any time a change is made to the budget.

Fields:

id - The internal ID of the budget line item [number]
budget_id - The internal ID of the budget associated with the budget line item [number]
service_request_id - The internal ID of the service request associated with the budget line item [number]
job_item_id - The database ID of the job item associated with the budget line item [number]
lib_item_id - The database ID of the lib item associated with the budget line item [number]
service_line_id - The database id of the service line associated with the budget line item [number]
reference_number - The reference number of the budget line item [number]
lib_item_type - The reference number of the budget line item [number]
price - The unit price of the budget line item [number]
total_price - The total price of the budget line items [number]
display_output - The display output of the budget line item [text]
quantity - The quantity of budget line item [number]
cost - The unit cost of the budget line item [number]
total - The total cost of the budget line items [number]
created - The date on which the budget line item was created [datetime]
updated - The date on which the budget line item was most recently updated [datetime]

Change Orders

The 'change_orders' dataset contains information about the individual change order. Each row represents a single change order.

All Change Order records are refreshed any time a change is made to the change order.

Fields:

id - The internal ID of the change order [number]
budget_id - The internal ID of the new budget created from the change order [number]
baseline_budget_id - The internal ID of the original budget that is being changed [number]
owner_id - The internal id of the change order owner [number]
office_id - The ID of the office associated with the change order
response_user_id - The internal ID of the change order response user [number]
status - The status of the change order [text]
type - The change order type [text] (Internal or External)
terms_id - The internal ID of the terms associated with the change order [number]
notes -The notes written for the change order [text]
response_notes - The name and email address of the person who responded to the change order [text]
created - The date on which the change order was created [datetime]
updated - The date on which the change order was most recently updated [datetime]
job_number - The job number for the repair job associated with this quote [number]
job_type - The job type for the associated change order [text]
job_status - The current status of the job for the associated change order [text]
job_created - The date on which the job was created for the associated change order [datetime]
job_updated - The date on which the job was most recently updated for the associated change order [datetime]
job_assigned_to - The name of the ServiceTrade user who is assigned as the owner of this job for the associated change order [text]
job_assigned_to_office - The name of the office assigned to the user who is assigned as the owner of this job for the associated change order [text]
job_sales - The name of the ServiceTrade user who is assigned as the salesperson on this job for the associated change order [text]
job_cost - The sum total of the costs of all job items on this job for the associated change order [number]
job_appt_first_start - The date and time on which the first appointment on the job is scheduled to start for the associated change order [datetime]
job_appt_first_end - The date and time on which the first appointment on the job is scheduled to end for the associated change order [datetime]
job_appt_last_start - The date and time on which the last appointment on the job is scheduled to start for the associated change order [datetime]
job_appt_last_end - The date and time on which the last appointment on the job is scheduled to end for the associated change order [datetime]
job_due_start - The date on which the due window for the job begins for the associated change order [datetime]
job_due_end - The date on which the due window for the job ends for the associated change order [datetime]
job_invoice_amount - The sum total (not including sales tax) of all invoices associated with the job for the associated change order [number]
job_first_invoice - The date and time on which the job's first invoice was created for the associated change order [datetime]
job_last_invoice - The date and time on which the job's last invoice was created for the associated change order.  If the job has only one invoice, this will be the same as job_first_invoice. [datetime]
job_expected_margin - The difference between job_cost and job_estimated_price, as a percentage of job_estimated_price (see the jobs dataset for job_estimated_price) for the associated change order [number]
job_actual_margin - The difference between job_cost and job_invoice_amount, as a percentage of job_invoice_amount for the associated change order [number]
job_is_invoiced - A flag indicating whether the job has at least one invoice for the associated change order [boolean]
job_is_completed - A flag indicating whether the job has been marked as complete for the associated change order [boolean]
job_all_appts_completed - A flag indicating whether all the job's appointments have been completed for the associated change order [boolean]
job_completed_in_due_window - A flag indicating whether the job was marked as complete before the end of its due window for the associated change order [boolean]
location - The name of the job's location for the associated change order [text]
location_state - The abbreviation of the state or territory of the job's location for the associated change order [text]
location_postal_code - The postal code of the state or territory of the job's location for the associated change order [text]
location_offices - A list of the names of the offices assigned to the job's location for the associated change order [comma-separated text]
location_regions - A list of the names of the regions containing the job's location for the associated change order [comma-separated text]
location_latitude - The latitude of the job's location for the associated change order [number]
location_longitude - The longitude of the job's location for the associated change order [number]
location_id - The ID of the job's location for the associated change order (matches id field in the Locations dataset) [number]
customer_id - The ID of the job's customer for the associated change order [number]
customer_name - The name of the ServiceTrade company which is the customer for this job for the associated change order [text]
owner_name - The name of the user company that currently owns the change order for the associated change order [text] quote_first_sent - The date on which the quote was first sent to the customer for approval for the associated change order [datetime]
quote_last_sent - The date on which the quote was most recently sent to the customer for approval for the associated change order [datetime]
quote_last_sent_by - The name of the ServiceTrade user who last sent the quote to customer for the associated change order [text]
quote_sent_count - The number of times the quote was sent to the customer for approval for the associated change order [number]
is_sent - A flag indicating whether the quote was sent to the customer for approval for the associated change order [boolean]
quote_approved - The date on which the quote was approved for the associated change order [datetime]
quote_first_approved - The date on which the quote was first approved by the customer for the associated change order [datetime]
quote_last_approved - The date on which the quote was most recently approved by the customer for the associated change order [datetime]
quote_approved_count - The number of times the quote was approved by the customer for the associated change order [number]
is_approved - A flag indicating whether the quote was approved by the customer for the associated change order [boolean]
quote_first_attachment_added - The date on which the quote first had an attachment added for the associated change order [datetime]
quote_last_attachment_added - The date on which the quote most recently had an attachment added for the associated change order [datetime]
quote_attachment_added_count - The number of times the quote had an attachment added for the associated change order [number]
is_attachment_added - A flag indicating whether the quote had an attachment added for the associated change order [boolean]
service_lines - A list of the names of the service lines associated with this change order's services [comma-separated text]

Change Order Categories

The 'change_order_categories' dataset contains information about high level change orders. Each row represents a single change order category.

All Change Order Category records are refreshed any time a change is made to the change order.

Fields:

id - The internal ID of the change order category [number]
change_order_id - The Internal ID of the Change Order this Line Item belongs to [number]
service_request_id - The service request that this line item is associated with [number]
action - The purpose of the change order Line Item(add/change) [text]
notes - The notes written for the change order line item [text]
lib_item_type - The item type for the Change Order Line Item [number]
cost - The total cost of the Change Order Line Item [number]
quantity - The quantity for the Change Order Line Item [number]
price - The estimated revenue for the Change Order Line Item [number]
created - The date on which the change order category was created [datetime]
updated - The date on which the change order category was most recently updated [datetime]
service_line_id - The internal ID of the service line associated with the change order category [number]

Change Order Line Items

The 'change_order_line_items' dataset contains information about granular level change orders. Each row represents a single change order line item.

All Change Order Line Item records are refreshed any time a change is made to the change order.

Fields:

id - The internal ID of the change order line item [number]
change_order_id - The internal ID of the change order associated with the change order line item [number]
service_request_id - The internal ID of the service request associated with the change order line item [number]
job_item_id - The internal ID of the job item associated with the change order line item [number]
lib_item_id - The internal ID of the lib item associated with the change order line item [number]
service_line_id - The internal ID of the service line associated with the change order line item [number]
action - The purpose of the change order Line Item(add/change) [text]
notes - The notes written for the change order line item [text]
lib_item_type - The item type for the Change Order Line Item [number]
price - The unit price of the Change Order Line Item [number]
display_output - The item description shown in the change order [text]
quantity - The quantity for the Change Order Line Item [number]
cost - The unit cost of the Change Order Line Item [number]
created - The date on which the Change Order Line Item was created [datetime]
updated - The date on which the Change Order Line Item was last updated [datetime]