Smartsheet RTI Register#
Column Summary#
Columns filled in by user through the RTI entry form#
| Title | Brief Description (Where Applicable) |
|---|---|
| Details | |
| Dispensation Details | |
| Dispensation Reference | |
| Dispensation Required? | |
| PDF Version Used | |
| Possible Impact - Cost | |
| Possible Impact - Other | |
| Possible Impact - Schedule | |
| Possible Project Impact? | |
| Project ID | |
| Project Impact (Preliminary Estimate Only) | |
| Recommendation | |
| Reference Documents or Drawings | |
| Response Required by Date | |
| RTI Automation Used (RFI) | Indicates that the RFI should be processed through the workflows (if ticked) or will be processed via Word document (classical method) sent via email, in which case this sheet is used just to generate an RFI number. |
| RTI Automation Used (TQ) | Indicates that the TQ should be processed through the workflows (if ticked) or will be processed via Word document (classical method) sent via email, in which case this sheet is used just to generate an TQ number. |
| Subject/Title | |
| Technical Approver | |
| Type of Request |
Columns manually entered via Update Request forms#
| Title | Brief Description |
|---|---|
| Carbon Copy Recipients | Used by Client PM to send carbon copy of the RTI response to any other recipients. |
| Directions for Nominated SME (if Req.) | Used by Client PM when delegating RTI to SME to enter further details about the request. |
| Nominated Delegate or SME (if Req.) | Used by Client PM to enter email address of the nominated SME when delegating the RTI. |
| PDF Version Closed | Used by GTE PM when RTI is handled via PDF version to indicate that the response from client has been received and the RTI can be closed. |
| Response (Complete When Ready to Issue to GTE) | Used by Client PM to enter RTI response. |
| SME Response Required by Date | Used by Client PM when delegating RTI to an SME to indicate the date by which the technical response should be completed (no logic is implemented to this date). |
| Technical Response | Used by SME to enter a requested response to the RTI. Client PM uses this as a bases for collating the final RTI response before it is sent to GTE. |
Columns mirroring manual entry fields#
The purpose is to display the values on the Update Request forms without possibility to make any changes.
| Title | Brief Description |
|---|---|
| Amendments Requested | A mirror of "Amendments Required" column. |
| Client's Response | A collation client's recommendation approval and "Response (Complete When Ready to Issue to GTE)" column. |
| Directions/Comments (by the Client's PM) | A mirror of "Directions for Nominated SME (if Req.)" column. |
| GTE's Recommendation | A mirror of "Recommendation" column. |
| Reference Documents & Drawings | A mirror of "Reference Documents or Drawings" column. |
| Request Details | A mirror of "Details" column. |
| Subject | A mirror of "Subject/Title" column. |
| Technical Response by SME | A mirror of "Directions for Nominated SME (if Req.)" column. |
Columns linked to corresponding fields in the RTI Parameteres configuration sheet#
| Title | Brief Description |
|---|---|
| Agreed Maximum Response Days | System starts counting from the date of RTI issue to Client and if the response is not received by the date defined by this parameter, the GTE PM gets notified. Default value = 500 days (which makes this check practically ineffective.) |
| Client PM | Linked to Project Register, but can be overriden in RTI Parameters sheet. |
| Disable All Workflows | If this value is set to "disabled", all worflows will get disabled. |
| GTE PM (Authoriser) | Linked to Project Register, but can be overriden in RTI Parameters sheet. |
| Issue Notification Emails Sent To | Specifies whol will be receiveing stalled RTI notification emails. By default this is set to GTE PM, but can be overriden in RTI Parameteres sheet. |
| Maximum Days in Potential Limbo (AP) | Maximum amount of days RTI can be in Approval Request, once this is expired the stalled RTI mechanism kicks in. Default value = 5 days. |
| Maximum Days in Potential Limbo (OP) | Maximum amount of days RTI can be in Update Request (from the same person), once this is expired the stalled RTI mechanism kicks in. Default value = 5 days. |
| Maximum Days in Potential Limbo (SP) | Maximum amount of days RTI can be in Update Request (from the other person), once this is expired the stalled RTI mechanism kicks in. Default value = 5 days. |
| Retrigger Potential Limbo (AP) Items | Specifies the action once the Approval Reqest RTI reaches "Limbo State" (inactivity timer expires) - system can either re-trigger the RTI or send email notification. Defaul = retrigger. |
| Retrigger Potential Limbo (OP) Items | Specifies the action once the Update (from the same person) Reqest RTI reaches "Limbo State" (inactivity timer expires) - system can either re-trigger the RTI or send email notification. Defaul = retrigger. |
| Retrigger Potential Limbo (SP) Items | Specifies the action once the Update (from the other person) Reqest RTI reaches "Limbo State" (inactivity timer expires) - system can either re-trigger the RTI or send email notification. Defaul = retrigger. |
"Key" columns changed by Approval/Update Requests#
Once the Key column value is changed, the RTI progresses to the next workflow step.
| Title | Brief Description |
|---|---|
| Amendments Required | Used by GTE PM to specify amendments required from Client in case the RTI response is not accepted. |
| Status: Acceptance of Client Response (by GTE PM) | Result of Approval Request action. |
| Status: Authorisation (by GTE PM) | Result of Approval Request action. |
| Status: Technical Approval (by GTE Tech Approver) | Result of Approval Request action. |
| Workflow: Approver's Direction | Changed by Update Request action. |
| Workflow: Authoriser's Direction for RFI | Changed by Update Request action. |
| Workflow: Authoriser's Direction for TQ | Changed by Update Request action. |
| Workflow: Client's Response to RFI | Changed by Update Request action. |
| Workflow: Client's Response to TQ | Changed by Update Request action. |
| Workflow: Client's RFI response after SME Action | Changed by Update Request action. |
| Workflow: Client's TQ response after SME Action | Changed by Update Request action. |
| Workflow: Means to Address RFI | Changed by Update Request action. |
| Workflow: Means to Address TQ | Changed by Update Request action. |
| Workflow: Originator's Direction after Revision | Changed by Update Request action. |
| Workflow: SME's Action](#workflow-smes-action) | Changed by Update Request action. |
System columns used for monitoring RTI status and further troubleshooting#
| Title | Brief Description |
|---|---|
| Client Actioning | Indicates that the RTI is currently with the Client to be actioned (used in re-triggering logic). |
| Detailed Status | The detailed status of the RTI which shows precisely where it is within the workflow. |
| Detailed Status (TQ) | Indicates the detailed status for technical queries. |
| Detailed Status (RFI) | Indicates the detailed status for requests for information. |
| Exceeds Max Response Days | Indicates that the RTI has not been actioned within "Agreed Maximum Response Days" time limit. |
| GTE Workflow | Indicates persons invloved in the RTI's processing at GTE side. |
| Overdue (or Late) | Indicates that the RTI has not been action by "Response Required by Date". |
| Person Actioning | Indicates who is the person to take the next action on the RTI (used in the text of alert messages). |
| Status | The high-level status of the RTI. |
| Workflow Log | A detailed information indicatin the current state of the RTI, set by automation. |
System columns used for in stalled RTI recovery engine#
| Title | Brief Description |
|---|---|
| Days in Potential Approval Limbo | Indicates how many days have passed since the current RTI state has been entered, in case the current state is Approval Request. |
| Days in Potential Limbo (Other Person) | Indicates how many days have passed since the current RTI state has been entered, in case the current state is Update Request (from the other person). |
| Days in Potential Limbo (Same Person) | Indicates how many days have passed since the current RTI state has been entered, in case the current state is Update Request (from the same person). |
| In Potential Approval Limbo | A checkbox indicating that the RTI is in limbo state, whereas the current state is Approval Request. |
| In Potential Limbo (Other Person) | A checkbox indicating that the RTI is in limbo state, whereas the current state is Update Request (from the other person). |
| In Potential Limbo (Same Person) | A checkbox indicating that the RTI is in limbo state, whereas the current state is Update Request (from the same person). |
| Limbo Resend Trigger | A flag that is set by automation and initiates the RTI recovery workflow (retrigger and/or sending notifications). |
| Manual Resend Trigger | A manual flag that - when set - retriggers the current RTI state. |
| Manual Trigger | A dropdown list allowing manually moving the RTI to any state.. |
| Potential Approval Limbo Start Date | Indicates the date when the RTI entered current state, in case the current state is Approval Request. Set by automation. |
| Potential Limbo Start Date (Other Person) | Indicates the date when the RTI entered current state, in case the current state is Update Request (from other person). Set by automation. |
| Potential Limbo Start Date (Same Person) | Indicates the date when the RTI entered current state, in case the current state is Update Request (from same person). Set by automation. |
| Potential Limbo Type | If the RTI is currently in limbo state, this column indicates the limbo type name (approval, other peron or same person). |
Other system columns#
| Title | Brief Description |
|---|---|
| Closed Date | Indicates the date when the RTI was closed (set by automation). |
| Issued Date | Indicates the date when the RTI was issued to the client (set by automation). |
| Latest Comment | System column - indicates the most latest comment in the RTI conversation. |
| Raised By | A high-level status for RTIs for client reporting purposes. |
| PDF Insert Text | A text that is inserted into the email notification body if the PDF version of the RTI is being used. |
| Project | Indicates full project name (including Project ID and Client Name). |
| Project ID Error | Indicates that the project number entered via a New RTI Form is incorrect (not existing project) and needs to be fixed. |
| Project Name | System column, indicates project name (excluding Project ID and Client Name). |
| Raised Date | Indicates the date when the RTI was raised (automatically set upon RTI creation). |
| Record Limbo Start Date (AP) | Automation helper column - indicates that the "Potential Approval Limbo Start Date" has to be reset to current date. |
| Record Limbo Start Date (OP) | Automation helper column - indicates that the "Potential Limbo Start Date (Other Person)" has to be reset to current date. |
| Record Limbo Start Date (SP) | Automation helper column - indicates that the "Potential Limbo Start Date (Same Person)" has to be reset to current date. |
| Reminder Subject | The text that will be added to the start of the email notification Subject in case it is a re-triggered action. |
| Reminder Text | The text that will be added to the start of email notification body in case it is a limbo re-triggered action. |
| Responded Date | Indicates the date when the RTI was responded by the client (set by automation). |
| Retrigger Mask | A flag indicating that the RTI (being actioned by client) is not yet overdue - it will net be fully re-triggered when limbo state reached, only internal notification will be sent. |
| RTI # | A unique RTI identification number. |
| RTI Number | A sequential number exclusive to the project, whcih is then used to build up the "RTI #". |
| Seq # | A system generated number assigned automatically to the new RTI, use to generate "RTI Number". |
| Sheet | A mirrored value of sheet summary field. |
| Today | Due to the issue with TODAY() flickering throughout the day (known problem - caused by background update using US timezone), the TODAY date has to be devised using RecordDate in a workflow (in the other, common sheet). |
| Trigger 1 (For testing purposes ONLY) | A helper column - manual trigger used for automation testing. |
| Trigger 2 (For testing purposes ONLY) | A helper column - manual trigger used for automation testing. |
| Workflow Completed | A helper column, used in automation - indicates that the workflow has been completed, set by automation. |
| Workflow Trigger | A helper column, used by automation - it acts as a trigger for manual/automatic workflow retriggering. |
Note
There is a set of hidden columns, the names of which start with [AUTO]. These are used for generating a PDF version of an RTI - by a Smartsheet "Generate Document" function as well as by a purpose made external progrem (developed by GTE). All these columns mirror the value of other columns that are required to be inserted into a PDF version.
Column Details#
Client's Response#
Formula
=IF(
[Type of Request]@row = "Technical Query (TQ)",
IF(
OR(
[Workflow: Client's Response to TQ]@row = "Approved As Recommended",
[Workflow: Client's TQ response after SME Action]@row = "Approved As Recommended",
[Workflow: Means to Address TQ]@row = "Approved As Recommended"
),
IF(
[Response (Complete When Ready to Issue to GTE)]@row <> "",
"Approved As Recommended" + " – " + [Response (Complete When Ready to Issue to GTE)]@row,
"Approved As Recommended"
),
IF(
OR(
[Workflow: Client's Response to TQ]@row = "Not Approved As Recommended",
[Workflow: Client's TQ response after SME Action]@row = "Not Approved As Recommended",
[Workflow: Means to Address TQ]@row = "Not Approved As Recommended"
),
IF(
[Response (Complete When Ready to Issue to GTE)]@row <> "",
"Not Approved As Recommended" + " – " + [Response (Complete When Ready to Issue to GTE)]@row,
"Not Approved As Recommended"
)
)
),
IF(
[Response (Complete When Ready to Issue to GTE)]@row <> "",
[Response (Complete When Ready to Issue to GTE)]@row
)
)
Client Actioning#
Formula
=IF(
OR(
LEFT([Workflow Log]@row, 4) = "[3.0",
LEFT([Workflow Log]@row, 4) = "[4.1",
LEFT([Workflow Log]@row, 2) = "[5",
LEFT([Workflow Log]@row, 4) = "[6.3",
LEFT([Workflow Log]@row, 4) = "[6.5"
),
1
)
Days in Potential Approval Limbo#
Formula
=IFERROR(
NETWORKDAYS([Potential Approval Limbo Start Date]@row, Today@row) - 1,
0
)
Days in Potential Limbo (Other Person)#
Formula
=IFERROR(
NETWORKDAYS([Potential Limbo Start Date (Other Person)]@row, Today@row) - 1,
0
)
Days in Potential Limbo (Same Person)#
Formula
=IFERROR(
NETWORKDAYS([Potential Limbo Start Date (Same Person)]@row, Today@row) - 1,
0
)
Detailed Status#
Formula
=IF(
[Detailed Status (TQ)]@row <> "",
[Detailed Status (TQ)]@row,
[Detailed Status (RFI)]@row
)
Detailed Status (TQ)#
Formula
=IF(
[Type of Request]@row = "Technical Query (TQ)",
IF(
OR(
LEFT([Workflow Log]@row, 6) = "[1.0T]",
LEFT([Workflow Log]@row, 6) = "[1.1T]"
),
"Approving",
IF(
LEFT([Workflow Log]@row, 15) = "[1.4, 2.4, 3.4]",
"Cancelled",
IF(
LEFT([Workflow Log]@row, 12) = "[2.0T, 2.5T]",
"Authorising",
IF(
LEFT([Workflow Log]@row, 12) = "[2.2T, 3.2T]",
"Approver Actioning",
IF(
LEFT([Workflow Log]@row, 12) = "[2.3T, 3.3T]",
"Revising",
IF(
LEFT([Workflow Log]@row, 11) = "[3.0T, 3.5]",
"Client Actioning",
IF(
LEFT([Workflow Log]@row, 6) = "[3.1T]",
"Authoriser Actioning",
IF(
OR(
LEFT([Workflow Log]@row, 11) = "[4.1T, 5.1]",
LEFT([Workflow Log]@row, 6) = "[5.3T]",
LEFT([Workflow Log]@row, 6) = "[6.5T]"
),
"SME Actioning",
IF(
OR(
LEFT([Workflow Log]@row, 36) = "[4.2T, 4.6T, 5.4T, 5.6T, 6.4T, 6.6T]",
LEFT([Workflow Log]@row, 6) = "[6.2T]"
),
"Assessing Response",
IF(
LEFT([Workflow Log]@row, 6) = "[5.2T]",
"Client Finalising Response",
IF(
LEFT([Workflow Log]@row, 6) = "[6.3T]",
"Further Requested",
IF(
LEFT([Workflow Log]@row, 5) = "[6.1]",
"Closed"
)
)
)
)
)
)
)
)
)
)
)
)
)
Detailed Status (RFI)#
Formula
=IF(
[Type of Request]@row = "Request for Information (RFI)",
IF(
OR(
LEFT([Workflow Log]@row, 6) = "[1.0R]",
LEFT([Workflow Log]@row, 6) = "[1.1R]"
),
"Authorising",
IF(
LEFT([Workflow Log]@row, 15) = "[1.4, 2.4, 3.4]",
"Cancelled",
IF(
LEFT([Workflow Log]@row, 11) = "[3.0R, 3.5]",
"Client Actioning",
IF(
LEFT([Workflow Log]@row, 6) = "[3.1R]",
"Authoriser Actioning",
IF(
LEFT([Workflow Log]@row, 6) = "[3.3R]",
"Revising",
IF(
OR(
LEFT([Workflow Log]@row, 11) = "[4.1R, 5.1]",
LEFT([Workflow Log]@row, 6) = "[5.3R]",
LEFT([Workflow Log]@row, 6) = "[6.5R]"
),
"SME Actioning",
IF(
OR(
LEFT([Workflow Log]@row, 18) = "[4.2R, 5.4R, 6.4R]",
LEFT([Workflow Log]@row, 6) = "[6.2R]"
),
"Assessing Response",
IF(
LEFT([Workflow Log]@row, 6) = "[5.2R]",
"Client Finalising Response",
IF(
LEFT([Workflow Log]@row, 6) = "[6.3R]",
"Further Requested",
IF(
LEFT([Workflow Log]@row, 5) = "[6.1]",
"Closed"
)
)
)
)
)
)
)
)
)
)
)
Exceeds Max Response Days#
Formula
=IF(
AND(
[Agreed Maximum Response Days]@row <> "",
[Workflow Log]@row <> "[1.4, 2.4, 3.4] RTI Cancelled",
[Issued Date]@row <> ""
),
IF(
IF(
[Responded Date]@row <> "",
NETWORKDAYS([Issued Date]@row, [Responded Date]@row),
NETWORKDAYS([Issued Date]@row, Today@row)
) > [Agreed Maximum Response Days]@row,
WORKDAY(
[Issued Date]@row,
[Agreed Maximum Response Days]@row + 1
)
)
)
GTE Workflow#
Formula
= "Raised: " + [Raised By]@row
+ IF(
FIND("TQ", [Type of Request]@row) > 0,
", Approved: " + [Technical Approver]@row
)
+ ", Authorised: " + [GTE PM (Authoriser)]@row
In Potential Approval Limbo#
Formula
=IF(
[Days in Potential Approval Limbo]@row >= [Maximum Days in Potential Limbo (AP)]@row,
1,
0
)
In Potential Limbo (Other Person)#
Formula
=IF(
[Days in Potential Limbo (Other Person)]@row >= [Maximum Days in Potential Limbo (OP)]@row,
1,
0
)
In Potential Limbo (Same Person)#
Formula
=IF(
[Days in Potential Limbo (Same Person)]@row >= [Maximum Days in Potential Limbo (SP)]@row,
1,
0
)
Overdue (or Late)#
Formula
=IF(
AND(
[Issued Date]@row <> "",
[Responded Date]@row = "",
Workflow Log]@row <> "[1.4, 2.4, 3.4] RTI Cancelled",
Workflow Log]@row <> "[6.1] RTI Closed"
),
IF(
[Response Required by Date]@row <> "",
Today@row > [Response Required by Date]@row
)
)
PDF Insert Text#
Formula
=IF(
[PDF Version Used]@row,
"transferred into PDF format (manual action) and"
)
Person Actioning#
Formula
=IF(
OR(
LEFT([Workflow Log]@row, 5) = "[1.0R",
LEFT([Workflow Log]@row, 5) = "[1.1R",
LEFT([Workflow Log]@row, 5) = "[2.0T",
LEFT([Workflow Log]@row, 5) = "[2.5T",
LEFT([Workflow Log]@row, 4) = "[3.1",
LEFT([Workflow Log]@row, 4) = "[4.2",
LEFT([Workflow Log]@row, 4) = "[6.2",
AND(
[PDF Version Used]@row = 1,
OR(
LEFT([Workflow Log]@row, 4) = "[3.0",
LEFT([Workflow Log]@row, 4) = "[3.5"
)
)
),
[GTE PM (Authoriser)]@row,
IF(
OR(
LEFT([Workflow Log]@row, 5) = "[1.0T",
LEFT([Workflow Log]@row, 5) = "[1.1T",
LEFT([Workflow Log]@row, 5) = "[2.2T",
LEFT([Workflow Log]@row, 5) = "[3.2T"
),
[Technical Approver]@row,
IF(
OR(
LEFT([Workflow Log]@row, 5) = "[2.3T",
LEFT([Workflow Log]@row, 4) = "[3.3"
),
[Raised By]@row,
IF(
OR(
AND(
[PDF Version Used]@row = 0,
OR(
LEFT([Workflow Log]@row, 4) = "[3.0",
LEFT([Workflow Log]@row, 4) = "[3.5"
)
),
LEFT([Workflow Log]@row, 4) = "[5.2",
LEFT([Workflow Log]@row, 4) = "[6.3"
),
[Client PM]@row,
IF(
OR(
LEFT([Workflow Log]@row, 4) = "[4.1",
LEFT([Workflow Log]@row, 4) = "[5.1",
LEFT([Workflow Log]@row, 4) = "[5.3",
LEFT([Workflow Log]@row, 4) = "[6.5"
),
[Nominated Delegate or SME (if Req.)]@row
)
)
)
)
)
Potential Limbo Type#
Formula
=IF(
[In Potential Limbo (Same Person)]@row,
"Same Person",
IF(
[In Potential Limbo (Other Person)]@row, "Other Person",
IF(
[In Potential Approval Limbo]@row,
"Approval",
""
)
)
)
Project#
Formula
="P" + [Project ID]@row + " - " + [Project Name]@row
Project ID Error#
Formula
=ISERROR([Project Name]@row)
Project Name#
Formula
=INDEX(
{Project Register - Project Name},
MATCH(
[Project ID]@row,
{Project Register - Project ID},
0
)
)
Reminder Subject#
Formula
=IF(
[Limbo Resend Trigger]@row,
"Reminder:"
)
Reminder Text#
Formula
=IF(
[Limbo Resend Trigger]@row,
"This is a friendly reminder for the below RTI which was sent to you earlier
and requires your urgent action. In case you have actioned it already, please
try again, or contact GTE PM if further assistance is required."
)
Retrigger Mask#
Formula
=IF(
AND(
[Client Actioning]@row,
Today@row < [Response Required by Date]@row
),
1
)
RTI ##
Formula
="GTE-"
+ [Project ID]@row
+ "-RTI-"
+ [RTI Number]@row
+ " ["
+ IF(
FIND("TQ", [Type of Request]@row) > 0,
"TQ",
"RFI"
)
+ "]"
RTI Number#
Formula
=RIGHT(
1000 + (1 + COUNTIFS(
[Project ID]:[Project ID], [Project ID]@row,
[Seq#]:[Seq#], <[Seq#]@row
)
),
3
)
Status#
Formula
=IF(
OR(
[Detailed Status]@row = "Approving",
[Detailed Status]@row = "Authorising",
[Detailed Status]@row = "Authoriser Actioning",
[Detailed Status]@row = "Approver Actioning",
[Detailed Status]@row = "Revising"
),
"Preparing",
IF(
OR(
[Detailed Status]@row = "Client Actioning",
[Detailed Status]@row = "SME Actioning",
[Detailed Status]@row = "Client Finalising Response"
),
IF(
[Overdue (or Late)]@row = 0,
"Issued",
"Overdue"
),
IF(
[Detailed Status]@row = "Further Requested",
IF(
[Overdue (or Late)]@row = 0,
"Re-Issued",
"Overdue"
),
IF(
OR(
[Detailed Status]@row = "Cancelled",
[Detailed Status]@row = "Closed"
),
"Closed",
IF(
[Detailed Status]@row = "Assessing Response",
"Responded",
"Unknown Status"
)
)
)
)
)
Today#
Formula
={Update Sheet - Today}
Workflow Trigger#
Formula
=IF(
OR(
[Limbo Resend Trigger]@row,
[Manual Resend Trigger]@row
),
[Workflow Log]@row,
IF(
[Manual Trigger]@row <> "",
[Manual Trigger]@row
)
)
Retrigerring Mechanism Workflow#
Following picture provides details about RTI retriggering mechanism:
