Checklist templates via Excel

Owned by Testify
Last updated: Dec 13, 2023
15 min read

Table of contents

Checklist templates can be created, edited and updated via Manage checklists. This is possible in two ways: The default variant is using the checklist designer. The other possibility is to use the excel import-template. The following are the steps to be taken in order to create checklists via Excel.

Please note that checklist templates files can only be imported but not exported via excel. An export of a checklist template is possible as a json file. More information under https://testify.atlassian.net/wiki/spaces/TWN/pages/1507197300/Manage+Checklists#Checklist-context-menu

For Excel import (with a few exceptions - see description), the binary system must always be used.
0 = No/false

1 = Yes/true

Download and Import

Download the blank Excel checklist template

To get the template, please click on the context menu, select Import and “Download import-template”.

Download optimised Excel checklist template

Alternatively, you can download an optimised Excel checklist template here. After activating macros, you can create checklists in a simplified way via Excel. Simply click on the corresponding buttons in the first tab "Information". Before importing into Testify, the template must be converted again, i.e. the macros must be disabled. This is possible via the "Save Import Excel" button.

Download optimised template:

Import a completed Excel checklist template

To import a checklists template via excel, please click on the context menu, select Import and click on “Select file to import”.

Create a checklist with the Excel checklist template

The excel file is divided in two tabs: Header and Content. The first row is the heading. Short explanations are deposited there as a note. It is always listed whether a column is mandatory or not and if yes, when this is the case.

Please note: At least one check must be created.

Id

partly mandatory

An own Id can be created but is not recommended. If not needed, this field should be left blank when creating a new checklist template. The Id is only necessary and mandatory when revising a checklist template.

If you want to revise a Checklist-Template insert the Checklist-ID of the desired checklist. When working with own Id’s, these Id’s must always be used, which is especially relevant when revising a checklist template.

The checklist ID can be accessed as following: Manage checklists - Periodic assignments - URL

 

Attention: Import is only successful if there is no existing draft of the Checklist-Template (only published or disabled versions of the checklist template are allowed.)

Title

mandatory

Defines the title of the checklist template

Description

mandatory

Defines the description of the checklist template

DefaultLocale

mandatory

Defines the default language of the checklist template. Please note that the locale cannot be updated. Translations can be made via https://testify.atlassian.net/wiki/spaces/TWN/pages/1538621541. The locale codes are as following:

  • Bosanski = bs

  • Deutsch = de-AT

  • English (UK) = en-GB

  • English (India)= en-IN

  • English (US) = en-US

  • Español = es-MX

  • Français = fr

  • Nederlands = nl

  • Português = pt-BR

  • Русский = ru

  • 中文(中国) = zh-CN

  • 中文(台灣) = zh-TW

Limitation

mandatory

Defines how many users can run the checklist at the same time

  • No Limit = 1

  • Limited To One = 2

SignatureSetting

mandatory

Add signatures at closing of a checklist

  • Disabled = 0

  • Optional = 1

  • mandatory = 2

EnableFastNavigation

mandatory

Activate fast navigation via checkidentifier

  • true = 1

  • false = 0

FocusInputFieldAfterEveryCheck

mandatory

Focusing the input field of the checkidentifier after every check

  • True = 1

  • False = 0

AutomaticNavigationToNextCheck

mandatory

Automatic navigation to next check after setting check result

  • true = 1

  • false = 0

DisableAutomaticNavigationToNextPage

mandatory

Disable automatic navigation to next page or section after clicking next-button

  • true = 1

  • false = 0

EnableReuseCheckResult

mandatory

Enable reuse of check result of last completed checklist of same test object

  • true = 1

  • false = 0

TestObjectTypeLimitations

not mandatory

Insert ID of Test object type to add an limitation

TestObjectLimitations

not mandatory

Insert ID of Test object to add an limitation

Content

Id

mandatory

This ID can be chosen freely but must be unique and is necessary to build a structure. This ID must then be used in the column ParentID of the subordinated pages, sections or checks. For example: 12345678

Type

mandatory

Please insert the number of the type you want to create:

  • Page: Type = 1
    Only use: Id, Type, Title, Description, ParentId columns

  • Section: Type = 2
    Only use Id, Type, title, Description, ParentId, OptionalSetting, Identifier, IsGateKeeper columns
    Dependencies cannot be created via the Excel import

  • Check: Type = 3
    Only column that cannot be used is the OptionalSetting column

For example: 1 (if you want to create a page)

Title

Mandatory

Defines the title of the page, section, cor action. For example:

  • Page: Fact sheet

  • Section: General information

  • Check: Is the machine clean?

  • Action: Assign checklist

Description

Not mandatory

Sets the description of the page, section, check or action. For example:

  • Page: Fact sheet

  • Section: All general information checks

  • Check: Please specify if the machine is clean

  • Action: Assign checklist to the next department

ParentId

Partially mandatory

The ParentID is optional for pages (1), but mandatory for sections (2), checks (3) and action (4). If there are neither pages (1) nor sections (2), the ParentID is not relevant for checks (3) and actions (4). Actions can exist everywhere and thus have both pages (1) and sections (2), but no checks (3) as parents.

Otherwise, the desired structure cannot be created. The ParentId determines the structure of the checklist template. The structure of the checklist must follow these rules:

  • Sections and pages can only have pages as parents, checks can only have pages and sections as parents.

  • All children must always be of the same type (page/section/check).

  • The following structure options between pages, sections and checks are possible:

    • Page > Page > ... > Section > Check or

    • Page > Page > Check or

    • Page > Section > Check" or

    • Page > Check" or

    • Section > Check" or

    • Check

OptionalSetting

partly mandatory

Mandatory for Sections (2), not possible for Pages (1) and Checks (3). Defines the status of the section:

  • Required = 0

  • DefaultDisabled = 1

  • DefaultEnabled = 2

Identifier

partly mandatory

The Identifier is only mandatory for:

  • Header Options:

    • EnableFastNavigation

    • FocusInputFieldAfterEveryCheck

  • Content Option:

    • IsGateKeeper

The Identifier can be chosen freely but must be unique. This needs to be specified if one (or more) of these options has been selected. For example column H "IsGateKeeper" = 1 and column G "Identifier" = abcd

IsGateKeeper

partly mandatory

Mandatory for sections (2) and checks (3), not possible for pages (1).

Only allow execution via Identifier, if this setting is 1.

  • false = 0

  • true = 1

    • If true, an Identifier in column G must be specified. This Identifier can be chosen freely but must be unique.

CheckType

partly mandatory

mandatory for Type Check (3)

Enter the number of the desired check:

  • Photo = 1

  • Multiple choice = 2

  • Numeric = 3

  • Time = 4

  • Calculation = 5

  • Single choice = 6

  • Date = 7

  • Test Object Selection = 8

  • Logical = 9

  • Text = 10

  • File = 11

  • GeoPosition = 12

  • Signature = 13

CheckLabel

partly mandatory

mandatory for

  • Multiple choice (2): specify a label for each option that should be available, separated by a semicolon, e.g. A;B;C

  • Numeric (3): specify the placeholder text for the input, e.g. enter a number...

  • Time (4): specify the placeholder text for the input, e.g. select a time...

  • Calculation (5): specify a placeholder text for each input for the calculation, separated by a semicolon,e.g. enter value1;enter value2;...

  • Single choice (6): specify a label for each option that should be available, separated by a semicolon, e.g. A;B;C

  • Date (7): specify the placeholder text for the input, e.g. select a date..

  • Logical (9): specify a label for the positive and the negative option, separated by a semicolon, e.g. YES;NO

  • Text (10): specify the placeholder text for the input, e.g. enter a text...

  • GeoPosition (12): Specify the placeholder text for the input, e.g. Specify coordinates...

do not use this on these checks

  • Photo (1)

  • Test Object Selection (8)

  • File (11)

  • Signature (13)

CheckValidation

partly mandatory

mandatory for

  • Multiple choice (2): specify the result value for each option with 1/0, separated by a semicolon, e.g. 1;1;0

  • Single choice (6): specify the result value for each option with 1/0, separated by a semicolon, e.g. 1;1;0

  • File (11): specify min and max files to upload, separated by a semicolon, e.g. 1;

optional for

  • Numeric (3): specify the min and max value for the check to be valid (optional), e.g. 0;10

  • Time (4): specify the min and max value for the check to be valid (optional), e.g. 10:00;10:30

  • Calculation (5): specify the min and max value for the check to be valid (optional), e.g. 0;10

  • Date (7): specify the min and max value for the check to be valid (optional), e.g. 1.1.2020;1.1.2021

  • Text (10): specify a matchString the input should match with (optional)

do not use this on these checks

  • Photo (1)

  • Test Object Selection (8)

  • Logical (9)

  • GeoPosition (12)

  • Signature (13)

CheckOptions

Partly mandatory

Format for combinations of CheckOptions: <property1 name>:<value1>;<property2 name>:<value2>

  • For example: decimalPlaces:3;maxWarnValue:6;warnText:close

Scoring: A score can only be added if scoring is enabled. If the function is enabled but no score is set, default values are used.

  • Photo (1)

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScorePositiveResult: optional, specifies the score you get when the check is positive"

      • checkScoreNegativeResult: optional, specifies the score you get when the check is negative

      • e.g. isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

  • Multiple choice (2):

    • mustSelectAll: required, specifies whether all positive options must be selected, values 0/1

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScores: optional, specifies for single and multiple choice checks the score you get for each option, integers separated by commas"

      • e.g. isScoringEnabled:1;checkScores:5/3/0/-50

  • Numeric (3):

    • decimalPlaces: required, specifies the number of decimal places to be maximally displayed, all integer value >0

    • maxValue: optional, sets the upper value to be valid

    • minValue: optional, sets the lower value for validity

    • maxWarnValue: optional, specifies the upper threshold, from which a warning message is displayed

    • minWarnValue: optional, specifies the lower threshold, from which a warning message is displayed

    • warnText: optional, specifies a text to be displayed in the warning message

    • setPointValue: optional, specifies a set point number to be displayed in the warning message

    • quickInput: optional, specifies whether the quick input shall be used - values 0/1

    • unitSuffix: optional, specifies the unit suffix

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScorePositiveResult: optional, specifies the score you get when the check is positive"

      • checkScoreNegativeResult: optional, specifies the score you get when the check is negative

      • e.g. isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

  • Time (4)

    • maxValue: optional, sets the upper value to be valid

    • minValue: optional, sets the lower value for validity

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScorePositiveResult: optional, specifies the score you get when the check is positive"

      • checkScoreNegativeResult: optional, specifies the score you get when the check is negative

      • e.g. isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

  • Calculation (5):

    • function: required, specifies the calculation type, values Avg=0 Sum=1 Stddev=2 Min=3 Max=4 Custom=5

    • decimalPlaces: required, specifies the number of decimal places to be maximally displayed, all integer value >0

    • maxValue: optional, sets the upper value to be valid

    • minValue: optional, sets the lower value for validity

    • maxWarnValue: optional, specifies the upper threshold, from which a warning message is displayed

    • minWarnValue: optional, specifies the lower threshold, from which a warning message is displayed

    • warnText: optional, specifies a text to be displayed in the warning message

    • setPointValue: optional, specifies a set point number to be displayed in the warning message

    • quickInput: optional, specifies whether the quick input shall be used - values 0/1

    • unitSuffix: optional, specifies the unit suffix

    • formula: optional, specifies the formula to use for the calculation, only use for custom calculation

    • resultFieldLabel: optional, specifies a placeholder text in the result field, only use for the custom calculation

    • checkIdentifier: optional, specifies a list of check identifiers of a referred check result, e.g. [checkIdentifier1,checkIdentifier2]

    • defaultValue: optional, specifies a list default values, e.g. [1,2]

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScorePositiveResult: optional, specifies the score you get when the check is positive"

      • checkScoreNegativeResult: optional, specifies the score you get when the check is negative

      • e.g. isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

  • Single choice (6):

    • quickInput: optional, specifies whether the quick input shall be used - values 0/1

    • showOptionsHorizontally: optional, specifies whether the options are shown horizontally or vertically - vertical=0, horizontal=1

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScores: optional, specifies for single and multiple choice checks the score you get for each option, integers separated by commas"

      • e.g. isScoringEnabled:1;checkScores:5/3/0/-50

  • Date (7)

    • maxValue: optional, sets the upper value to be valid

    • minValue: optional, sets the lower value for validity

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScorePositiveResult: optional, specifies the score you get when the check is positive"

      • checkScoreNegativeResult: optional, specifies the score you get when the check is negative

      • e.g. isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

  • Test Object Selection (8):

    • selectionType: required, specify what testobjects shall be available, values AllTestObjects = 0, SubComponents = 1

    • positiveValidation: required, specify the checkresult if a testobject is selected - Successful=1, Failed=0

    • Multiselect: required, specify whether multiple testobjects can be selected, values 0/1

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScorePositiveResult: optional, specifies the score you get when the check is positive"

      • checkScoreNegativeResult: optional, specifies the score you get when the check is negative

      • e.g. isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

  • Logical (9):

    • quickInput: optional, specifies whether the quick input shall be used - values 0/1

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScorePositiveResult: optional, specifies the score you get when the check is positive"

      • checkScoreNegativeResult: optional, specifies the score you get when the check is negative

      • e.g. isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

  • Text (10):

    • quickInput: optional, specifies whether the quick input shall be used - values 0/1

    • jsonStringArray: optional, specifies properties that can be put in as json - e.g. [first,second]

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScorePositiveResult: optional, specifies the score you get when the check is positive"

      • checkScoreNegativeResult: optional, specifies the score you get when the check is negative

      • e.g. isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

  • FileUpload (11)

    • maxNumberOfFiles: optional, specifies the maximum number of files that can be uploaded.

    • minNumberOfFiles: optional, specifies how many files must be uploaded at least.

    • Scoring

      • isScoringEnabled: optional, specifies whether the scoring function is active or not, values 0/1"

      • checkScorePositiveResult: optional, specifies the score you get when the check is positive"

      • checkScoreNegativeResult: optional, specifies the score you get when the check is negative

      • e.g. isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

  • Geoposition (12)

    • Scoring

      • isScoringEnabled: optional, indicates whether the scoring feature is active or not, values 0/1

      • checkScorePositiveResult: optional, specifies the score to get if the check is positive

      • checkScoreNegativeResult: optional, specifies the score to get if the check is negative

      • Example isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

  • Signature (13)

    • Scoring

      • isScoringEnabled: optional, indicates whether the scoring feature is active or not, values 0/1

      • checkScorePositiveResult: optional, specifies the score to get if the check is positive

      • checkScoreNegativeResult: optional, specifies the score to get if the check is negative

      • Example isScoringEnabled:1;checkScorePositiveResult:5;checkScoreNegativeResult:0

CheckCategories

Optional for checks (3), not possible for pages (1) and sections (2).

Add the identifier(s) of the needed check category, separated by a semicolon, e.g. ISO 90001:18;ISO 22000

Prerequisite: The feature https://testify.atlassian.net/wiki/spaces/TWN/pages/2412871731 has been activated in advance via the Solution Team.

ActionType

Optional within a checklist, whether in a page, section or before/after a check.

  • Type: 1

ActionParameter

  • Assigned to: Definition to whom the checklist should be assigned. ID of the user or group, e.g. assignToId:106c0bff-e2a3-4b30-b5eb-0016b7894a9f.

  • Due date: if the due date should be adjusted by days (e.g. dateOffsetDays) or hours (1;dateOffsetHours)

  • From when the calculation takes place: Date of execution (dateOffsetCalculatedFromType:1) or Scheduled for (dateOffsetCalculatedFromType:2)

  • Display text in the button (label) e.g. label:Action Click

  • Example: assignToId:106c0bff-e2a3-4b30-b5eb-0016b7894a9f;dateOffsetDays:1;dateOffsetHours:1;dateOffsetCalculatedFromType:1;label:Action Click

Error

In order to support you in the best possible way in case of incorrect entries, we inform you with different error messages in case of incorrect entries in the Excel list. If entries in the Excel file lead to problems during the upload, these are displayed directly during the import attempt. Otherwise, the error messages appear when publishing the draft.

Example

In this example, the goal is to gain a deeper understanding of creating checklists with Excel. The template comprises two pages, three sections, and all types of checks. Each check has specific settings explained in the sub-bullets. Below is a detailed description of the example.

Download example in the original Excel template:

Download example in the optimised Excel template:

* more about this under

Editing-file

Import-file

Header

The title of the checklist is "Example checklist" and the description of the checklist is "This checklist is an example". The following settings have been made:

  • Default language is English (DefaultLocale = en-US)

  • No restriction (Limitation = 1)

  • Signature is mandatory (SignatureSetting = 2)

  • Fast navigation is switched off (EnableFastNavigation = 0)

  • Focusing of the input field is disabled (FocusInputFieldAfterEveryCheck = 0)

  • Automatic navigation to the next Check is switched off (AutomaticNavigationToNextCheck = 0)

  • Disable automatic navigation to next page is enabled (DisableAutomaticNavigationToNextPage = 1)

  • Reuse of the check result is enabled (EnableReuseCheckResult = 1)

  • No restrictions on the test object (type)

Content

Pages (Type 1)

First Page

  • The First Page has an ID (70733914), a title (First page) and no description

Second Page

  • The second page has an ID (13805331), a title (Second page) and no description

Sections (Type 2)

First section

  • The first section has an ID (21040822), a title (first section), no description, a ParentId (70733914) and is required (OptionalSetting: 0)

Second section

  • The second section has an ID (64208823), a title (Second section), no description, a ParentId (70733914) and is disabled by default (OptionalSetting: 1)

Third section

  • The third section has an ID (16410116), a title (third section), no description, a ParentId (13805331) and is activated by default (OptionalSetting: 2)

Checks (Type 3)

First Check

The first check has an ID (31780208), a title (Please upload a photo), a description (Photo Test check) and a ParentId (21040822) as well as the following settings:

  • GateKeeper is disabled

  • The CheckType is photo (1)

Second Check

The second check has an ID (61700408), a title (Digital checklists are), a description (multiple choice Test check and a ParentId (21040822) as well as the following settings:

  • GateKeeper is disabled

  • The CheckType is multiple choice (2)

  • The three CheckLabel options are "good", "great" and "practical" (good;great;practical)

  • The CheckValidation is true, true, true (result value: 1;1;1)

  • CheckOptions specifies that all positive options must be selected (mustSelectAll: 1)

Third Check

The third Check has an ID (64427191), a title (The answer to life, the universe and everything else), a description (Numeric Test check), a ParentId (21040822) and the following settings:

  • GateKeeper is disabled

  • CheckType is Numeric (3)

  • The CheckLabel placeholder text is "Please specify" (Please specify)

  • The CheckOptions specify that there are no decimal places, the minimum warning value is 40 and the maximum warning value is 42, the warning message "Are you sure?" is displayed and the set value is 42 (decimalPlaces:0;minWarnValue:40;maxWarnValue:42;warnText:Are you sure?;setPointValue:42)

Fourth Check

The fourth check has an ID (64246142), a title (What time is it right now?), a description (Time Test check), a ParentId (16410116) and the following settings:

  • GateKeeper is disabled

  • CheckType is time (4)

  • The CheckLabel placeholder text is "Select time"

Fifth check

The fifth check has an ID (87968399), a title (average of two values), a description (Calculation Test check), a ParentId (16410116) and the following settings:

  • GateKeeper is disabled

  • CheckType is calculation (5)

  • The CheckLabel placeholder text is "Result"

  • The CheckOptions specify that the calculation type is Average and that there are two decimal places (function:0;decimalPlaces:2)

Sechste Check

The sixth check has an ID (64793757), a title (What is the best checklist tool?), a description (Single choiceTest check), a ParentId (64208823) and the following settings:

  • GateKeeper is disabled

  • CheckType is single selection (6)

  • The four CheckLabel options are "Testify", "I have no idea, "I don't know" and "unclear" (Testify;I have no idea;I don't know;unclear)

  • The CheckValidation is true, false, false, false (result value: 1;0;0;0;0)

  • The CheckOptions indicate that the options are displayed horizontally (showOptionsHorizontally:1)

Seventh check

The seventh check has an ID (44996990), a title (Which date was yesterday?), a description (Date Test check), a ParentId (16410116) and the following settings:

  • GateKeeper is disabled

  • CheckType is Date (7)

  • The CheckLabel placeholder text is "Select date"

Eighth check

The eighth check has an ID (91528116), a title (Please select a test object), a description (Test object selection Test check), a ParentId (16410116) and the following settings:

  • GateKeeper is disabled

  • CheckType is selection of the test object (8)

  • The CheckOptions specify that all test objects should be available, that the check is positive when a test object is selected and that only one test object can be selected (selectionType:0;positiveValidation:1;multiselect:0)

Ninth check

The ninth check has an ID (21744304), a title (Do you like checklists?), a description (Logical Test check), a ParentId (16410116) and the following settings:

  • GateKeeper is disabled

  • CheckType is Logical (9)

  • The two CheckLabel options are "Yes" and "No" (Yes;No)

Tenth check

The tenth check has an ID (74740233), a title (What is the meaning of life?), a description (Text Test check), a ParentId (16410116) and the following settings:

  • GateKeeper is disabled

  • CheckType is Text (10)

  • The CheckLabel placeholder text is "Please specify" (Please specify)

  • The CheckValidation that defines the matchString that must match the input is "Checklists" (Checklists)

Eleventh check

The eleventh check has an ID (17026380), a title (Please upload a file), a description (File Test check), a ParentId (16410116) and the following settings:

  • GateKeeper is disabled

  • CheckType is File (11)

  • The CheckValidation specifies that at least one file and a maximum of two files should be uploaded, i.e. Min and Max Files(1;2)

Twelfth check

The twelfth check has an ID (92704685), a title (Where is this checklist executed?), a description (GeoPosition Test check), a ParentId (16410116) and the following settings:

  • GateKeeper is disabled

  • CheckType is GeoPosition (12)

  • The CheckLabel placeholder text is "Enter coordinates" (Enter coordinates)

Thirteenth check

The thirteenth check has an ID (43005960), a title (Please sign), a description (Signatur Test check), a ParentId (16410116) and the following settings:

  • GateKeeper is disabled

  • CheckType is signature (13)