Join 34,000+ subscribers and receive articles from our blog about software quality, testing, QA and security.
 

[UI Script] Bulk Test Update helper


#1

The following UI Script places a button and a text box at the bottom of the Test Suite pages.

Tick the test cases that you would like to bulk update and click the Create SQL button to generate an SQL statement template with all the case id’s. All you need to do is modify the output query (column_name=valuetoset) with the values required and execute it in your SQL interface.

You can modify this so that it is only available to certain users / permissions / projects by using the context information.

http://docs.gurock.com/testrail-custom/uiscripts-introduction#context_information

Note: Be VERY careful when using this. You can use TestRail’s import / export functionality to update tests, however I find this to be much more efficient.

name: SQL Bulk Update helper
description: Creates a base SQL script for use on multiple test cases
author: gmcdonald
version: 1.0
includes: ^suites
excludes: 

js:
$(document).ready(
     function() 
     {
    // Only display the buttons if the test suite has sections
        if ($('#noSectionContainer').hasClass('hidden'))
        {
          $('#groupContainer').after('<div class=\'actions\' style=\'margin-top: 20px\'><a id=\'massAddResult\' style=\'display: block\' href=\'javascript:void(0)\' onclick=\'this.blur(); $("#sql_data").val("UPDATE [TestRail].[dbo].[cases] SET column_name=valuetoset WHERE id in (" + App.Tests.getSelected() + ")"); return false;\'><img src=\'images/icons/add.png\' width=\'16\' height=\'16\' alt=\'\'>   Create SQL   </a></div><div class="ioTableContainer" style="margin-top: 8px"><label for="custom_steps">Base SQL Query</label><textarea name=\'sql_data\' id=\'sql_data\' rows=\'2\' class=\'custom text full \' ></textarea></div>')
        }
     ;}
);

#2

Hi Glenn,

Thanks for posting the script. I understand that updating the database looks like a reasonable approach if you have a local TestRail server instance. Unfortunately we do not support making changes in TestRail’s database directly.

Please do not make any changes/updates to the database using SQL. Please use TestRail’s API or the export/import functionality to update the cases. The reason for this is that manual changes in TestRail’s database can lead to inconsistencies. E.g. if you update an entry in the ‘cases’ table, you would need to make changes in other tables as well (e.g. for the test case history). This is often not obvious and a better way would be to use the export/import feature or TestRail’s API. Both approaches handle all these things transparently.

I understand that a bulk update feature would be useful to have and it’s one of the next things we would like to work on.

Thanks,
Dennis


#3

Thanks Dennis,

Unfortunately we have many suites so using the import / export functionality per suite is not really workable (especially when the changes need to be made globally).

The export functionality also doesn’t let you filter on specific test cases, so breaking the XML down into editable sections (for search and replace, for instance) is very time consuming.

Finally, for the export / import functionality, if a custom field is not currently set, we would need to add new lines to the XML in order to add the new data.

It would take too much time for us to build an API based application to handle the updates, and the API would be slow in comparison to the direct SQL calls given it can only update one case at a time.

We only use direct SQL to change custom fields, we test the changes on our staging server first, and we take database backups daily to reduce the risk of losing our data. We understand that we will lose out on historical test changes for test cases, however the benefits of updating via SQL far outweigh the negatives.

Along with many other customers, we would love to see an integrated bulk editing feature in TestRail - we have asked about this a couple of times - but until that happens we are happy to live with the consequences :slight_smile:

Hopefully we don’t get to a point where you have to say “I told you so”!!!


#4

We would also like to see a bulk-editing feature. Editing XML/CSV/etc can be a PITA. Would be much better to have the bulk way of doing things… In addition, would also be nice to have an in-line way of editing values for test cases too!


#5

Hello Richard,

Thanks for your feedback on this. Yes, this is planned and we also evaluate the option to add support for inline-editing.

Regards,
Tobias


#6

[quote=tgurock]Hello Richard,

Thanks for your feedback on this. Yes, this is planned and we also evaluate the option to add support for inline-editing.

Regards,
Tobias[/quote]

That’s great to hear! Any idea when we’ll be able to get our hands on this?


#7

Hello Richard,

We currently don’t have any specific estimate for this but it is planned to look into this for one of the next larger updates.

Regards,
Tobias


#8

Hello,
I created a bulk update script that relies on TestRail’s REST API (requires “Enable session authentication for API” setting). It was created for our team’s purposes (setting a custom field for test automation state), but I hope you can understand how to extend it and tailor to your needs:

name: Bulk change automation state
description: Adds possibility to apply desired automation state to all selected cases using TestRail REST API
author: Paul Danyliuk
version: 2.0
includes: ^suites/view
excludes: 

js:
// Definition of our custom field (type: select), extracted here for pure convenience
var automationStateOptions = {
    1 : "Manual",
    2 : "Scoped",
    3 : "Not suitable for automation",
    4 : "Reviewed",
    5 : "Automated"
};

// The 'descriptor' parameter must be a valid object as described in REST (v2) documentation and can contain any fields
var updateSelectedCases = function(descriptor, onSuccess, onFailure) {

    // Get ID's of selected cases and notify if selection is empty
    var casesString = App.Tests.getSelected();
    if (casesString == "") {
        alert("Select one or more cases first");
        onFailure.apply();
        return;
    }

    var promises = [];

    // For each case send an individual update request to the API
    casesString.split(',').forEach(function(caseID) {
        var deferred = $.Deferred();

        $.ajax("index.php?/api/v2/update_case/" + caseID, {
            type        : "POST",
            data        : JSON.stringify(descriptor),
            dataType    : 'json',
            contentType : 'application/json'
        }).done(function(data, textStatus, jqXHR) {
            if (data.result !== false) {
                deferred.resolve();
            } else {
                deferred.resolve("Error updating C" + caseID + ": " + data.error);
            }
        }).error(function(data, textStatus, jqXHR) {
            deferred.resolve(textStatus);
        });

        promises.push(deferred.promise());
    });

    // When all requests have finished, either report errors if any, or perform the onSuccess routine (reload the page)
    $.when.apply(null, promises)
        .done(function(promises) {
            var errors = [];
            for (var i = 0; i < arguments.length; i++) {
                if (typeof arguments[i] != 'undefined') {
                    errors.push(arguments[i]);
                }
            }
            if (errors.length > 0) {
                alert("There were errors during case update:\n" + errors.join('\n'));
                onFailure.apply();
            } else {
                onSuccess.apply();
            }
        });
};

$(document).ready(function() {
    // Create a dropdown element and the corresponding jQuery object
    var dropDownHtml = '<select id="massSetAutomationStateDropdown" style="display: inline">';
    for (var field in automationStateOptions) if (automationStateOptions.hasOwnProperty(field)) {
        dropDownHtml += '<option value="' + field + '">' + automationStateOptions[field] + '</option>';
    }
    dropDownHtml += '</select>';
    var $dropDown = $(dropDownHtml);

    // Create the Apply button
    var $applyButton = $('<a id="massSetAutomationStateButton" style=""><img src="images/icons/accept.png"/> <span>Apply</span></a>');

    // Define behavior for the Apply button: while cases are updating, make the button inactive and show loading state
    var onClick = function(button, descriptor) {
        var $btn = $(button);
        if (!$btn.hasClass('disabled')) {
            $btn.addClass('disabled');
            var cachedSrc = $btn.find('img').attr('src');
            var cachedCaption = $btn.find('span').text();
            $btn.find('img').attr('src', 'images/animations/progressStandard.gif');
            $btn.find('span').text("Processing...");
            updateSelectedCases(
                descriptor,
                function() {
                    // In case of success, make the button react and reload the page so that user can see updated table
                    $btn.removeClass('disabled');
                    $btn.find('img').attr('src', cachedSrc);
                    $btn.find('span').text("Done. Reloading page...");
                    window.location.reload();
                },
                function() {
                    // In case of failure revert the state of the button so that the attempt can be repeated
                    $btn.removeClass('disabled');
                    $btn.find('img').attr('src', cachedSrc);
                    $btn.find('span').text(cachedCaption);
                }
            );
        }
    };

    // Add listener to the button
    $applyButton.on('click', function() {
        // Modify this descriptor to your needs. This one sends a selected custom field value from the dropdown
        var descriptor = {
            "custom_automation_state" : $dropDown.val()
        };
        onClick(this, descriptor);
    });

    // Add a section _after_ the table (so that when the table is reloaded asynchronously, the button doesn't jump to
    // the top) with all controls within it
    var $actions = $('<div class="actions" style="margin-top: 2em">' +
        '<div class="section top"><span class="title">Update automation state on selected cases:</span></div>' +
        '</div>')
        .append($('<label for="massSetAutomationStateDropdown" style="display: inline">New state: </label>'))
        .append($dropDown)
        .append($('<br>'))
        .append($applyButton);
    $("#groupContainer").after($actions);

    // Optional: select some value by default, since it's the value we'll use most frequently
    $dropDown.val(5);
});

With best regards,
Paul


#9

That’s great that you are looking at a bulk update feature - I was just about to add a request for that :slight_smile: