The Job table provides information about jobs, which are the scheduled stops on vehicle routes.
This table supports Response integrations for Fleet accounts.
This topic contains the following sections:
Jobs are the stops you schedule your vehicles to make on a route. These can be deliveries, stops where your drivers perform tasks, locations where your vehicles make scheduled pick-ups, and so on. Each job can be configured with a name, a physical location (specified by providing a marker or a geographical location and a territory id), job type id, delivery date and time, and other attributes. If you create jobs by specifying geographical locations, make sure that you reuse the same job name for subsequent jobs that are at the same location - this results in the marker that was created for the first job being reused for subsequent jobs, and avoids the duplication of markers that would have a negative impact on reporting and other features.
The following key concepts define how jobs work:
Job types identify the types of tasks drivers perform on a job. Like Category for markers, job types have associated colors that make them easy to distinguish in charts, list views, reports, and clusters on the map.
A marker represents a physical location. Each job must have a marker, which you can specify by providing data for any of the following column sets:
When you create a new job, you must provide the data for at least one of these column sets.
If a MarkerId is not available, TDE uses the other column sets provided to try to find a nearby existing marker to use for the job. If an existing marker is found within one meter of the location specified, and that marker also has the same name as the job being created, then it is used as the marker for the job. This is the best case scenario, as it prevents duplicate markers. If there are no markers with the same name as the job within one meter of the location specified, a new marker is created and given the same name as the job. Note that this can result in a collection of duplicate markers over time, negatively affecting the function of other features, such as reports.
To prevent duplicate markers, ensure that you follow all of these guidelines:
This same logic is applied for updates.
If the Marker address data is updated, the changes are reflected in the Job table.
Depots are special types of markers. They represent the home base for a set of jobs. Depots are located within a territory. Drivers belong to the territory of their assigned base depot. If the driver is assigned to a vehicle, the vehicle must also be based at a depot in the territory.
The depot location is also the start and end point for a job route, or the location where materials are picked up or deposited.
A territory is a geographical area that can contain groups of customers, depots, drivers, and vehicles. A job cannot be directly assigned to a territory. Instead, a job inherits its territory based on either the assigned marker (MarkerId) or the assigned route (RouteId).
If you provide a TerritoryId on Create or Update, the way that Id is used depends on the other available data:
|X||X||The marker assigned to the job is given the route's territory, and the TerritoryId provided is ignored.|
|X||The marker assigned to the job is given that TerritoryId.|
|X||The marker assigned to the job is given the route's territory.|
When you Retrieve the TerritoryId for a job, the value returned can vary based on the following rules:
When you create a job, you must provide the following information:
When you write templates that access this table, follow these requirements:
Specify the Symbolic Name for the table version to use:
TableID = Job-1.0
Specify an Intent that this table supports:
Input templates: In your [Output] section, be sure to map an input value to every table column that is flagged as being a primary key or required.
If you change the values for the ExpectedArrivalTime, ExpectedDepartureTime, or RouteId columns for a job, the JobStatus flag is reset to "OK".
[Filter] Id(ExtId) = Equals(Id1) # You can filter on literals or on variables from the [User] or [Calculated] sections.
[Filter] Id(ExtId) = In(["Id1","Id2","Id3"])
[Filter] UnitId(ExternalIdName) = Equals("ABC123") # or use Telogis Id: UnitId = Equals("123456...") RouteStartTime = Between(TimeStart,TimeEnd)
[Filter] RouteId(ExternalIdName) = Equals("RST456") # or use Telogis Id: RouteId = Equals("123456...") # You can either use the Equals function as shown # or use the In function: In([RouteId1,RouteId2,RouteId3]) # to test whether the key value matches any in the set.
[Filter] RouteStartTime = Between(TimeStart,TimeEnd)
When filtering by ExpectedArrivalTime, you can improve search performance by adding one or more of the following optional filters: Code(Equals/In), DriverId(Equals/In), JobTypeId(Equals/In), LastUpdateTime(Between/GreaterThan), or TerritoryId(Equals/In).
For Id values, you can specify a value or use Telogis Id.
[Filter] ExpectedArrivalTime = GreaterThan(TimeStart) # Use either a GreaterThan or Between function # such as Between(TimeStart,TimeEnd) # Optional filters: LastUpdateTime = GreaterThan(UpdateTimeStart) # GreaterThan or Between TerritoryId(ExtId) = Equals("USA123") # Equals or In Code = Equals("MyCode1") # Equals or In DriverId(ExternalIdName) = Equals("DEF123") # Equals or In JobTypeId(ExternalId) = Equals("Depot") # Equal or In
This list includes each column's name, which you use in expressions to retrieve or create data, and data type, which defines how the data is retrieved and set.
|If a column of data type Id is a primary key OR if it refers to other tables (Id <OtherTable>), then you can map it to an external key or, in some cases, a Telogis key.|
|Id||Id||(primary key) The ID of the job.|
|ExpectedArrivalTime||Timestamp||The time in the planned route when the scheduled vehicle is expected to arrive at the job site. This is a static value based on the original plan.|
|ExpectedDepartureTime||Timestamp||The time in the planned route when the vehicle is expected to leave the job site. This is a static value based on the original plan.|
|ExpectedTravelDistance||Distance||The distance the driver is expected in the planned route to travel from the last job to arrive at this one. This is a static value based on the original plan.|
|EstimatedArrivalTime||Timestamp||(read-only) The time at which the driver is estimated to arrive at this job based on the current position of the vehicle. This value is re-calculated as the vehicle reports its current location.|
|EstimatedDistance||Distance||(read-only) The distance that the driver is estimated to drive between this job and the one immediately before it or between the current position of the vehicle and this job if the vehicle is en-route to the job. This value is re-calculated as the vehicle reports its current location.|
|EarliestArrivalTime||Timestamp||The earliest time of day when the driver can arrive to service this job.|
|LatestArrivalTime||Timestamp||The latest time of day when the driver can arrive to service this job.|
|Priority||Text||The priority of the job. Values: Lowest, Low, Normal, High, Highest.|
|Revenue||Number||The revenue associated with the job|
|RouteId||Id<Route>||The ID for the route that includes this job. If a job's status is Assigned or Unassigned, then the job can be removed from the route by setting the RouteId to 0.|
|RouteStartTime||Timestamp||(read-only) The start time for the route that includes this job.|
|RouteEndTime||Timestamp||(read-only) The end time for the route that includes this job.|
|RouteStatus||EnumType||(read-only) The status of the route that includes this job. Values: NotStarted, InProgress, Complete, NotComplete.|
|DriverId||Id<Driver>||(read-only) The ID of the driver who is assigned to the job.|
|DriverName||Text||(read-only) The name of the driver who is assigned to the job.|
|UnitId||Id<Unit>||(read-only) The ID of the unit that is assigned to the job's driver.|
|TerritoryId||Id<Territory>||The Id of the territory that the job has inherited from either its assigned route or marker. For more information about the relationship between jobs and territories, see the intro on the Job table page.|
|TerritoryName||Text||(read-only) The name of the territory that the job has inherited from either its assigned route or marker. For more information about the relationship between jobs and territories, see the intro on the Job table page.|
|MarkerId||Id<Marker>||The ID of the marker for this job site.|
|StreetNumber||StreetNumber||The street number portion of the job site's address.|
|StreetName||Text||The street name portion of the job site's address.|
|City||Text||The city of the job site.|
|Region||Text||The state or region of the job site.|
|PostalCode||Text||The zip code or postal code of the job site.|
|Country||Text||The Country of the job site.|
|Lat||Angle||The latitude of the job site, between -90 and 90 degrees.|
|Lon||Angle||The longitude of the job site, between -180 and 180 degrees.|
|DispatcherId||Id||(read-only) The ID of the dispatcher for this job.|
|LastUpdateTime||Timestamp||(read-only) The time when the job was last edited.|
|JobTypeId||Id<JobType>||(required on Create) The ID of the type of this job. This can refer to STOP, DEPOT, or a job type that you have defined.|
|Description||Text||A brief description of the job.|
|Notes||Text||Free-form notes added to the job definition.|
|Tag||Text||The name of the job.|
|JobStatus||Text||The status of the job. Values: Assigned (job assigned to driver but not current route), EnRoute (driver completed prior job but not yet arrived), OnSite (driver currently on site), Completed, Unassigned (job not assigned to a driver).|
|ActualDepartureTime||Timestamp||The time when the vehicle actually left the job site.|
|ActualArrivalTime||Timestamp||The time when the vehicle actually arrived at the job site.|
|ActualTravelDistance||Distance||(read-only) The distance the vehicle actually traveled from the last job before stopping at this one.|
|JobStatusFlag||Text||(read-only) Status information about actual performance relative to the planned stop at the job. Values: OK (performance matches plan), OffRoute (not following expected route), Late (late in arriving), WrongOrder (arrives before a job earlier in the route), OverlapAdjusted (time on site adjusted because location could serve multiple jobs), IdledAtJob (idling on site rather than turning off engine), RunningLateForPlannedArrival(being late in the planned arrival time), RunningLateForLatestArrival(being late in the latest arrival time), Missed(not completed).|
|Forms||Set<Id>||(read-only) Array of IDs for the forms that are associated with this job.|
|Code||Text||Free-form text that can be used to categorize the job.|
|PreferredDriverId||Id<Driver>||The Id of the preferred driver for the job's schedule. The preferred driver (PreferredDriverId) may be different than the driver assigned to the job (DriverId).|
|RequiredDriverTags||Set<Text>||A list of tags that a driver must have to be assigned to the job. The assigned driver must have the specified tags to avoid routing violations. Note that the tags must already be assigned for the account: you cannot create new tags here.|
|RequiredVehicleTags||Set<Text>||A list of tags that a vehicle must have to be assigned to the job. The assigned vehicle must have the specified tags to avoid routing violations. Note that the tags must already be assigned for the account: you cannot create new tags here.|
|Tags||Set<Text>||A list of tags assigned to the job. Note that the tags must already be assigned for the account: you cannot create new tags here.|
|ForbiddenDriverIds||Set<Id<Driver>>||A list of drivers that cannot be assigned to this job due to routing restrictions.|
|Load||Map<Text,Number>||Specifies the load details of the job. The key is the name of the capacity metric, and the value is either the amount to be delivered if the value is positive or the amount to be picked up if the value is negative. Use of this column requires that you have the Load Management feature in Fleet.|
|TimeWindows||Array<ServiceTimeWindow>||The windows of time when this job can be serviced. You can specify up to two windows for a job. All values should have the same Days.|
|ScheduleId||Id<Schedule>||(read-only) The ID of the job's schedule.|
|FixedTimeOnSite||TimeInterval||The fixed time on site for the job. Must equal ExpectedDepartureTime - ExpectedArrivalTime - Load time on site. Load time on site is not retrievable or settable through TDE.|
|PreJobDepotRequirements||Set<Text>||A list of tags that an earlier depot in the route must have to avoid routing violations. Note that the tags must already be assigned for the account. You cannot create new tags here.|
|PostJobDepotRequirements||Set<Text>||A list of tags that a later depot in the route must have to avoid routing violations. Note that the tags must already be assigned for the account. You cannot create new tags here.|
|Custom.name||Text||Specifies a user-defined custom column name to store additional information in the table. If the column name contains non-alphanumeric characters, enclose the value in brackets, for example Custom.[Special column].|
Generally, custom column names are case-sensitive. However, if you add a custom column to the Job table, the value is stored as lowercase characters. For example, if you create a custom Job column named ParkingNotes, when viewed in Plan, the column name is displayed as parkingnotes.