Roll back a View or Form to a Previous Version, Force Check In, and Cancel Check Out of a View or Form

  • 15 February 2022
  • 1 reply
  • 1755 views

Badge +6
 

Roll back a view or form to a previous version, force check-in, or cancel check out of a view or form

KB003346

PRODUCT
K2 Cloud
K2 Five

 

Introduction

  • K2 does not recommend this procedure since the validation logic of the K2 Designer tool is ignored, and can result in unexpected behaviour. Before you make any change to a SQL database, always make a backup or a snapshot of the SQL database. Do not use this procedure in production environments.
  • If you on K2 Cloud, contact the K2 Cloud support team to perform these steps for you.

You may need to roll back views or forms to a previous or deleted version, for example if a recent version of the view or form is broken to a point where it is not operational or recoverable and must be recreated.

You may also need to force a check in (or cancel a check out) of a view or form, for example if the person who has a form checked out is no longer available to check the form back in.

This article describes how to perform the following operations:

 

 

Roll back a view or form to a previous version

To roll back a view or form to a previous or deleted version of the view of form, follow the steps below:

  1. Obtain the ID and Name of the view or form. You can find the ID and Name in different tables in the K2 database, depending on the current state of the view or form:
      • View (checked in): Form.View table
      • View (checked out): Form.View_Design table
      • Form (checked in): Form.Form table
      • Form (checked out): Form.Form_Design table
  2. When you save or finish a view or form, an entry is made in the Form.AuditLog table of the K2 database. To retrieve a list of the available versions of the view or form to roll back, replace the GUID in the SQL query below with the ID you obtained in step 1.
    SELECT * FROM [Form].[AuditLog]
    where [Data] Like '%GUID%'
    and [Xml] is not null
    order by datetime desc


    Use the record ID and XML fields as shown above to identify the specific view or form. The XML field contains the matching IDs (represented as a GUID) of the view or form. Open the XML to see the view or form ID and Name as shown below.
  3. Execute the following Stored Procedure in the K2 database to revert to the specified version (you can use the same Stored Procedure to roll back both views and forms). Change the ID to the corresponding version ID as identified in the table shown above, in the ID field. Replace the Domain and Username with appropriate values. The Username must preferably be the person who created the view or form, as it will be checked out to that person when the rollback is complete:
    EXEC Form.mRevertToVersion'K2:{Domain}{UserName}', {ID}

    Example: To roll back to the second-latest version of the view (ID=4056), the statement is:
    EXEC Form.mRevertToVersion'K2:denallixob', 4056
    The recommended approach is to begin with the one version earlier than the latest version of the view or form (for example ID 4056 in the screenshot in Step 2) to identify the version that you need. If you revert to a previous version, that version becomes the latest version of the view or form.
  4. Use command prompt to issue an IISRESET command on the IIS server hosting the K2 Designer site, and then open the K2 Designer to access the reverted view or form.

Force Check In of a view or form

The recommended approach is to force Check In of a view or form one at a time so that you can verify that the action was successful before forcing Check In for the next item.

To force a Check In of a view or form, follow the steps below:

  1. Obtain the ID, DisplayName, and CheckedOutBy values of the view or form. You can find the ID, DisplayName and CheckedOutBy in the K2 database in one of the following tables:
    • View (checked out): Form.View_Design table
    • Form (checked out): Form.Form_Design table
    The image below shows an example of a view currently checked out to Bob:
  2. Execute the following SQL statement in the K2 database. Change the ID to the corresponding record ID as identified in the table shown above in the ID field. Replace the T with the correct value depending whether this is a view or a form: use 'F' if you are checking in a form and a 'V' if you are checking in a view. Replace the Domain and Username with the appropriate values - the Username must be the person who checked out the view or form.
    DECLARE @MyTable Form.ContextTableType;
    INSERT INTO @MyTable([ID], [Type])
    VALUES (N'{ID}', '{T}');
    EXEC [Form].[aCheckInContexts] @UserID= N'K2:{Domain}{UserName}', @ContextData=@MyTable;

    Example:To force a Check In of the Employees Editable List view as shown above, the statement is:
    DECLARE @MyTable Form.ContextTableType;
    INSERT INTO @MyTable([ID], [Type])
    VALUES (N'70E1D06D-8806-4135-9433-FB4ABB446150', 'V');
    EXEC [Form].[aCheckInContexts] @UserID= N'K2:DENALLIXob', @ContextData=@MyTable;
  3. Use command prompt to issue an IISRESET command on the IIS server hosting the K2 Designer site, and then open the K2 Designer to access the checked-in view or form.

Cancel Check out of a view or form

  • If the view or form was never checked in and checked out again, using this procedure will delete the view or form from the database and not perform a Cancel Check out as expected. For example, when you use the Generate Views feature to automatically generate a view or form, it is saved to the Form.View_Design table for views and to the Form.Form_Design table for forms. If you apply the procedure mentioned below in this specific instance, it will delete the view or form. Make sure the view or form was at least checked in and out once before you run this statement.
  • The recommended approach is to force Cancel Check out of a view or form one at a time so that you can verify that the action was successful before forcing Cancel Check out for the next item.

To force a Cancel Check out of a view or form, follow the steps below:

  1. Obtain the ID, DisplayName, and CheckedOutBy values of the view or form. You can find the ID, DisplayName and CheckedOutBy in the K2 database in one of the following tables:
    • View (checked out): Form.View_Design table
    • Form (checked out): Form.Form_Design table
    The image below shows an example of a view currently checked out to Bob:
  2. If you are cancelling check-out of a view, run the following statement in the K2 database. Change the ID to the corresponding record ID as identified in the table shown above in the ID field. Replace the Domain and Username with the correct values. The Username must be the person who checked out the view.
    EXEC [Form].[aUndoViewsCheckOut] @UserID = N'K2:{Domain}{UserName}', @IDs = N'{ID}';

    If you are cancelling check-out of a form, run the following statement in the K2 database. Change the ID to the corresponding record ID as identified in the table shown above in the ID field. Replace the Domain and Username with the correct values. The Username must be the person who checked out the form:
    EXEC [Form].[aUndoFormsCheckOut] @UserID = N'K2:{Domain}{UserName}', @IDs = N'{ID}';

    Example:To force a Cancel Check out of the Employees Editable List view as shown above, the statement is:
    EXEC [Form].[aUndoViewsCheckOut] @UserID = N'K2:Denallixob', @IDs = N'70E1D06D-8806-4135-9433-FB4ABB446150';
  3. Use command prompt to issue an IISRESET command on the IIS server hosting the K2 Designer site, and then open the K2 Designer to access the checked-in view or form.

 


1 reply

Badge +6

Hi All,


To generate the corresponding script, and quick analysis, you can use these queries:


 


--Undo checkout for one form/view:
--UndoCheckout cancel checkout:
--kb003346
--https://community.nintex.com/t5/How-To/Roll-back-a-View-or-Form-to-a-Previous-Version-Force-Check-In/ta-p/125870

--To copy correctly the advise statement with carriage returns,
--go to Tools > Options, Expand Query Results > SQL Server > Results to Grid, Tick "Retain CR/LF on copy or save"
--Need to restart the Sql Server Management Studio after.


--List of last checkout forms and views:
SELECT ModifiedDate,ModifiedBy, 'Form' as "Type",[Name],[DisplayName], CheckedOutBy
FROM [Form].[Form_Design]
WHERE CheckedOutBy IS NOT NULL
UNION
SELECT ModifiedDate,ModifiedBy,'View' as "Type", [Name],[DisplayName],CheckedOutBy
FROM [Form].[View_Design]
WHERE CheckedOutBy IS NOT NULL
ORDER BY ModifiedDate DESC


DECLARE @DisplayNameToFound nvarchar(100)
SET @DisplayNameToFound='CurrentTickets' --FILL HERE THE FORM OR VIEW DISPLAY NAME


--Check first on the view:
DECLARE @FormOrViewId nvarchar(100)
SET @FormOrViewId = (SELECT [View].ID FROM [Form].[View] WHERE [View].DisplayName = @DisplayNameToFound)

IF @FormOrViewId IS NULL BEGIN
SET @FormOrViewId = ( SELECT [Form].ID FROM [Form].[Form] WHERE [Form].DisplayName = @DisplayNameToFound)
END

SELECT 'Form found: ''' + DisplayName + ''' Version: '+ convert( varchar, [Form].Version) AS 'Information' FROM [Form].[Form] WHERE Id=@FormOrViewId
UNION
SELECT 'CheckedOutBy:' + ISNULL([Form].CheckedOutBy,'WARNING: Seems to be not checked out!' ) FROM [Form].[Form] WHERE Id=@FormOrViewId
UNION
SELECT 'View found: ''' + DisplayName + ''' Version: '+ convert( varchar, [View].Version) FROM [Form].[View] WHERE Id=@FormOrViewId
UNION
SELECT 'CheckedOutBy:' + ISNULL([View].CheckedOutBy,'WARNING: Seems to be not checked out!' ) AS 'Information' FROM [Form].[View] WHERE Id=@FormOrViewId
ORDER BY 1 DESC

--Proposition of StatementToExecute:
SELECT 'declare @return_value int' + char(13) + char(10) +
'exec @return_value = [Form].[aUndoFormsCheckOut] ' + char(13) + char(10) +
'@UserID = ''BY SQL - Original:' + ISNULL(CheckedOutBy, (select CheckedOutBy from [Form].[Form_Design]
WHERE [Form_Design].ID=[Form].ID) + ' Only on Form_Design)')
+ ''', ' + char(13) + char(10) + --UserId is to used only for logs (procedure eLogMessage: table [AuditLog])
'@IDs = '''+@FormOrViewId +''', ' + char(13) + char(10) +
'@ClientStack = null' + char(13) + char(10) + char(13) + char(10) +
'select TOP 50 * from [Form].[AuditLog] order by 1 desc ' + char(13) + char(10) + char(13) + char(10) +
'--For corresponding Form :'+ char(13) + char(10) +
'select TOP 50 * from [Form].[AuditLog] WHERE Data='''+@FormOrViewId +''' order by 1 desc ' AS "Advised Statement" FROM [Form].[Form] WHERE Id=@FormOrViewId
UNION
SELECT 'declare @return_value int' + char(13) + char(10) +
'exec @return_value = [Form].[aUndoViewsCheckOut] ' + char(13) + char(10) +
'@UserID = ''BY SQL - Original:' + ISNULL(CheckedOutBy, (select CheckedOutBy from [Form].[View_Design]
WHERE [View_Design].ID=[View].ID) + ' Only on View_Design)')
+ ''', ' + char(13) + char(10) + --UserId is to used only for logs (procedure eLogMessage: table [AuditLog])
'@IDs = '''+@FormOrViewId +''', ' + char(13) + char(10) +
'@ClientStack = null' + char(13) + char(10) + char(13) + char(10) +
'select TOP 50 * from [Form].[AuditLog] order by 1 desc '+ char(13) + char(10) + char(13) + char(10) +
'--For corresponding view :'+ char(13) + char(10) +
'select TOP 50 * from [Form].[AuditLog] WHERE Data='''+@FormOrViewId +''' order by 1 desc ' AS "Advised Statement" FROM [Form].[View] WHERE Id=@FormOrViewId


 





 


 

Reply