Communications
- Last UpdatedAug 01, 2024
- 6 minute read
The following table lists the fields which are available in the Communications data model.
Any custom fields which have been configured in the communication template are also available and can be used according to their type. The field names for custom fields are prefixed with Communication_Custom_Field_, e.g. Communication_Custom_Field_Action_Closed_Date.
Tags which have been applied to communications can also be used. The field names for tag groups are prefixed with Communication_Tag_Group_, e.g. Communication_Tag_Group_Area_Code.
If using a third-party application such as PowerBI, we recommend you select only the required fields. Many fields in the Analytics data models are duplicated and can be excluded from queries; see Duplicated fields for more information.
Warning If the custom field or tag group name contains any characters that are not alphanumeric, these are replaced by underscores in the Analytics data model.
For example:
• The communication custom field "Doc. Ref" has the Analytics field name of Communication_Custom_Field_Doc__Ref - the . and space are replaced by _
• The communication tag group "H&S" has the Analytics field name of Communication_Tag_Group_H_S - the & is replaced by _
Note Data is not returned for communications created by the contractor which have not been sent; i.e. Draft communications and communications which were Rejected while in Draft.
Note For DateTime fields, only the date is stored. The time portion of the value of DateTime fields is therefore always 00:00.
|
Field name |
Type |
Description |
Values |
Data type and size |
Nullable |
|
CommunicationTitle |
String |
The title (subject) of the communication. |
nvarchar(510) |
No |
|
|
CommunicationReference |
String |
The reference of the communication - unique in the contract. |
nvarchar(120) |
No |
|
|
CommunicationRoutingPath |
String |
The routing path of the communication. |
|
nvarchar(200) |
Yes |
|
CommunicationTemplateTitle |
String |
The title of the communication template from which the communication was created. |
nvarchar(510) |
No |
|
|
CommunicationTemplateReportingCategory |
String |
The reporting template of the communication template from which the communication was created. |
nvarchar(510) |
No |
|
|
CommunicationReportingCategory |
String |
The reporting category to which the communication is assigned. |
|
nvarchar(200) |
No |
|
CommunicationTemplateAbbreviation |
String |
The abbreviation of the communication template from which the communication was created. |
nvarchar(40) |
No |
|
|
CommunicationCurrency |
String |
The base currency of the communication. |
nvarchar(510) |
No |
|
|
TotalFinancialImpact |
Decimal |
The financial impact value of the communication in the communication currency. If this communication is defined as Expenditure, this is the total of all expenditure financial values, including line items. |
money(19,4) |
Yes |
|
|
TotalFinancialImpactBase |
Decimal |
The financial impact value of the communication in the base currency of the data. If this communication is defined as Expenditure, this is the total of all expenditure financial values, including line items. |
money(19,4) |
Yes |
|
|
TotalScheduleImpact |
Int32 |
The schedule impact value of the communication. |
Integer |
Yes |
|
|
DaysTakenToApprove |
Int32 |
The number of days between the Originated On date and the date the communication was approved/agreed. |
Integer |
Yes |
|
|
DaysTakenToReject |
Int32 |
The number of days between the Originated On date and the date the communication was rejected. |
Integer |
Yes |
|
|
DaysTakenToRespond |
Int32 |
The number of days between the date that the communication was sent/published and the date on which a response was received. Only applies to communications which require a response. |
Integer |
Yes |
|
|
CommunicationState |
String |
The state of the communication. |
|
nvarchar(100) |
No |
|
RequiredResponse |
String |
Indicates if a response is required for the communication: agreement, reply, or both. |
|
nvarchar(100) |
No |
|
IsCommunicationOverdue |
String |
Communications which require response only. Indicates that the communication is overdue. |
|
nvarchar(6) |
No |
|
CommunicationCycleTime |
Int32 |
Company to Contractor communications only. The number of days taken for the communication to move from Draft to Approved/Awaiting Agreement from Recipient (the difference between the Created On Date and the Date Issued). |
Integer |
Yes |
|
|
ProcessTimeLimitDays |
Int32 |
Communications which require response only. The number of days within which the communication must be responded to once it has been sent. |
Integer |
No |
|
|
CommunicationProcessTimeLimitType |
String |
Communications which require response only. The type of due date of the communication. If this is Not Defined, the communication does not require a response. |
|
nvarchar(100) |
No |
|
OriginatedOnDate |
DateTime |
The date set as the communication's Originated On date. By default, the CreatedOnDate. |
datetime |
No |
|
|
CreatedOnDate |
DateTime |
The date on which the communication was created. |
datetime |
Yes |
|
|
DateIssued |
DateTime |
The date on which the communication was sent. |
datetime |
Yes |
|
|
ResponseDueOnDate |
DateTime |
The date on which the communication's response is due. Only applies to communications which require a response. |
datetime |
Yes |
|
|
CommunicationReviewCount |
Int32 |
The number of reviews on the communication. |
Integer |
No |
|
|
CommunicationReviewInProgressCount |
Int32 |
The number of reviews on the communication which are still in progress. |
Integer |
No |
|
|
ContractReference |
String |
The unique reference for the contract. |
nvarchar(510) |
No |
|
|
ContractName |
String |
The name of the contract. |
nvarchar(510) |
No |
|
|
ContractType |
String |
The type of the contract. |
|
nvarchar(510) |
No |
|
ContractorName |
String |
The name of the contractor. |
nvarchar(1022) |
No |
|
|
ContractCurrency |
String |
The base currency of the contract. |
nvarchar(510) |
No |
|
|
ProjectDepartmentName |
String |
The name of the project/dept in which the contract is located. |
nvarchar(300) |
No |
|
|
BusinessUnitId |
Int32 |
The unique ID of the business unit in which the contract is located. |
Integer |
No |
|
|
CompanyRepresentative |
String |
The name of the company user assigned to the Company Representative contract role. |
nvarchar(302) |
No |
|
|
ContractOwner |
String |
The name of the company user assigned to the Contract Owner contract role. |
nvarchar(302) |
No |
|
|
GeneralExpenditure |
Decimal |
The total of any General Expenditure financial values in the communication currency. |
money(19,4) |
Yes |
|
|
GeneralExpenditureBase |
Decimal |
The total of any General Expenditure financial values in the base currency of the data. |
money(19,4) |
Yes |
|
|
RetentionExpenditure |
Decimal |
The total of any Retention Expenditure financial values in the communication currency. |
money(19,4) |
Yes |
|
|
RetentionExpenditureBase |
Decimal |
The total of any Retention Expenditure financial values in the base currency of the data. |
money(19,4) |
Yes |
|
|
WithholdingTaxExpenditure |
Decimal |
The total of any Withholding Tax Expenditure financial values in the communication currency. |
money(19,4) |
Yes |
|
|
WithholdingTaxExpenditureBase |
Decimal |
The total of any Withholding Tax Expenditure financial values in the base currency of the data. |
money(19,4) |
Yes |
|
|
CreditNoteExpenditure |
Decimal |
The total of any Credit Note Expenditure financial values in the communication currency. |
money(19,4) |
Yes |
|
|
CreditNoteExpenditureBase |
Decimal |
The total of any Credit Note Expenditure financial values in the base currency of the data. |
money(19,4) |
Yes |
|
|
AdvancePaymentExpenditure |
Decimal |
The total of any Advance Payment Expenditure financial values in the communication currency. |
money(19,4) |
Yes |
|
|
AdvancePaymentExpenditureBase |
Decimal |
The total of any Advance Payment Expenditure financial values in the base currency of the data. |
money(19,4) |
Yes |
|
|
MilestoneLineItems |
Decimal |
The total of any Milestone Payments line items in the communication currency. |
money(19,4) |
Yes |
|
|
MilestoneLineItemsBase |
Decimal |
The total of any Milestone Payments line items in the base currency of the data. |
money(19,4) |
Yes |
|
|
UnitRateLineItems |
Decimal |
The total of any Unit Rate line items in the communication currency. |
money(19,4) |
Yes |
|
|
UnitRateLineItemsBase |
Decimal |
The total of any Unit Rate line items in the base currency of the data. |
money(19,4) |
Yes |
|
|
LumpSumLineItems |
Decimal |
The total of any Lump Sum line items in the communication currency. |
money(19,4) |
Yes |
|
|
LumpSumLineItemsBase |
Decimal |
The total of any Lump Sum line items in the base currency of the data. |
money(19,4) |
Yes |
|
|
ProgressiveLineItems |
Decimal |
The total of any Progressive Payment line items in the communication currency. |
money(19,4) |
Yes |
|
|
ProgressiveLineItemsBase |
Decimal |
The total of any Progressive Payment line items in the base currency of the data. |
money(19,4) |
Yes |
|
|
ReimbursableLineItems |
Decimal |
The total of any Reimbursable line items in the communication currency. |
money(19,4) |
Yes |
|
|
ReimbursableLineItemsBase |
Decimal |
The total of any Reimbursable line items in the base currency of the data. |
money(19,4) |
Yes |
|
|
TotalNumberOfRates |
Int32 |
The total number of rates associated with the Reimbursable line items. |
Integer |
Yes |
|
|
IsDeleted |
String |
Indicates whether the contract to which the communication belongs is in the Recycle Bin. |
|
nvarchar(100) |
No |
|
CommunicationCreatedBy |
String |
The name of the user who created the communication. |
nvarchar(1320) |
No |
|
|
CommunicationResolvedOn |
DateTime |
The date and time that the communication state was set to one of the following:
|
datetime |
Yes |
|
|
IsLatestRevision |
String |
Indicates whether the communication is the latest revision. |
|
nvarchar(6) |
No |
|
BusinessUnitName |
Int32 |
The name of the business unit in which the contract is located. |
nvarchar(300) |
No |
|
|
ProjectReference |
String |
The unique reference of the project/dept in which the contract is located. |
nvarchar(40) |
No |
|
|
DaysSinceCreated |
Int32 |
The number of days since the communication was created. |
Integer |
Yes |
|
|
DaysSinceOriginated |
Int32 |
The number of days since the Originated On date of the communication. |
Integer |
Yes |
|
|
DaysSinceApproved |
Int32 |
The number of days since the communication was approved/agreed. |
Integer |
Yes |
|
|
DaysToResponseDueOnDate |
Int32 |
The number of days until the communication's response is due. Only applies to communications which require a response. |
Integer |
Yes |
|
|
DaysCommunicationIsOverdue |
Int32 |
Communications which require response only. The number of days that the communication is overdue (the difference between the current date and the due date). |
Integer |
Yes |