How to configure an Excel spreadsheet to import a request (for tender) into 360

Owned by Asfand Khan
Last updated: Sept 30, 2023 by Rob Wade
4 min read

It is quite common for request managers to prepare their questions and sections outside of 360 within an Excel spreadsheet.

This is especially true for category-based requests where each category has the same or very similar questions because it's easy to copy a series of questions and do have find-and-replace operation to change key words (e.g. "electrical" with "plumbing").

Instructions

Before you begin

This help article is closely related to the "Question/statement configuration options in 360" article.
It's best to have a good understanding of what these options are before trying to create an import spreadsheet.


There are five types of rows that the import algorithm can process:

  1. New Request Row has up to ten columns.  The first column has either an R or a T in it:
    1. Identifier for the first type of row:
      • R = Request (note: J also works for this value)
      • T = Template
    2. Flags (see Request Flags below)
    3. Either:
      1. Score Upper Limit (an integer)
      2. Score Labels - a comma delimited list of score labels (lowest score to highest score)
    4. Request Title
    5. Closing Date (in your time zone)
    6. Request Description
    7. Leading Request Title (for hierarchical requests)
    8. Leading Request Weight (number without percentage sign)
    9. Price Weight (number without percentage sign)
    10. Location Weight (number without percentage sign)
    11. Additional configuration data in JSON format (undocumented feature)
  2. New Section Row has up to five columns.  The first column has either an S or a C in it:
    1. Identifier for the second type of row:
      • S = Section (a normal group of questions)
      • C = Category Section (a section that is assessed independently)
        Note: Category sections require the ADCOM extensions
    2. Flags (see Section Flags below)
    3. Either:
      1. Relative Weight for normal sections
      2. Leading Part Weight for category sections
    4. Name
    5. Dependency Section Rule (see below)
  3. New Question Row has up to sixteen columns.  The first column has a Q in it:
    1. Identifier for the third type of row:
      • Q = Question
    2. Flags (see Question Flags below)
    3. Relative Weight (see the W question flag below)
    4. Question
    5. Evaluation Guide
    6. Statement List - a comma-delimited list of compliance statements (self-assessment choices)
    7. Max-Length (deprecated - replaced by column R)
    8. Multi-Select Option - a comma-delimited list of options (deprecated - replaced by column R)
    9. Short Name
    10. Question Notes
    11. Response Format Flag (see below)
    12. Min-Value (deprecated - replaced by column R)
    13. Max-Value (deprecated - replaced by column R)
    14. Score Labels - a comma delimited list of score labels (lowest score to highest score) or Is Skippable (True or False) if providers can skip question or not
    15. Dependency Question Rule (see below)
    16. Correlation ID
    17. Referee template ID
    18. Configuration data - a JSON data structure that varies depending on the Response Format (see below)
    19. Default score (for evaluate-by-exception)
    20. Default reason/comment (for evaluate-by-exception)
    21. Evaluation plan
  4. Comment Row has at least one column.  The first column's text can be any length but must start with #
    1. Identifier for the fourth type of row:
      • # = Comment
  5. End of File Row has no value entered in the first column.
    1. Empty Cell
      No more processing will occur once the algorithm finds an empty cell in the first column

Flags

  1. Request Flags
    • C = Cost associated (respondents enter price)
    • E = Cost associated (evaluators enter price)
    • H = Hire request (equipment and equipment must be specified in 360)
    • P = Prequalification request
    • W = The manual VFM algorithm (price weighting) is to be used
  2. Section Flags
    • H = Is Hidden (Private) Section?
    • O = Is Optional/Skippable Section?
    • -   = Is Skipped By Default?
  3. Question Flags
    • A = Attachment(s) allowed
    • C = For contract
    • D = Default statements (ignored if statements are provided - not compatible with response format "B")
    • H = Hidden (only displayed to evaluators or, if trigger rules are defined, display is dependent on preceding responses)
    • M = Mandatory criterion
    • O = Optional/Skippable question
    • R = Response allowed (default response format is multi-lined text)
    • S = Display in a series
    • N = Display in a new series
    • W = Weighted criterion (relative weight is required)
    • -   Skipped by default
  4. Response Format Flags (case sensitive)
    • T = Text (default)
    • S = Short Text (single line only)
    • <> = Formatted Text (HTML)
    • B = No Text (no comments permitted)
    • D = Date values only (dd/mm/yyyy)
    • DR = Date range values only (From: dd/mm/yyyy, To: dd/mm/yyyy)
    • DT = Date (with time) values only (dd/mm/yyyy hh:mm)
    • $ = Currency values only ($1,234.45)
    • $R = Currency range values only ($10.00 to $100.00)
    • N = Number values only (1,234.45)
    • NR = Number range values only (10.00 to 100.00)
    • %= Percentage values only (30.00%)
    • %R = Percentage range values only (10.00% to 50.00%)
    • I = Integer values only (1,234)
    • IR = Integer range values only (10 to 1000)
    • # = Digits only without number formatting (001234)
    • ? = Yes/No Options
    • A = ABN (with ABR look-up)
    • A* = Formatted ABN (with ABR look-up)
    • a = ABN Details (with ABR look-up)
    • W = Web site addresses only (http://www.example.com)
    • E = Email addresses only (someone@example.com)
    • M = Options list (previously know as multi-select)
    • R = Referee check (requires XL licence)
    • L = Look-up
    • P = Price/Discount list
    • TB = Table (up to 10 columns of data)
    • VA = Verified Address (with Geocoded National Address File look-up)
    • CC = Pre-submission Compliance Check

Dependency Section/Question Rules

When a question or section is hidden with the H flag, 360 has 4 rules that can be applied to make it visible to the providers:

  1. Response Equals
    If the response to a previous question (the one on line 6 in the spreadsheet) equals a specified value ("Comply"), then this section (or question) will be visible to the provider
    {"Rule":"ResponseEquals","QuestionLineNumber":6,"ComplianceStatement":"Comply"}
  2. Multi-select Contains
    If a previous question (the one on line 4 in the spreadsheet) is a multi-select (flag M) and the provider selected a specified value ("Choice 2"), then this section (or question) will be visible to the provider
    {"Rule":"MultiSelectContains","QuestionLineNumber":4,"Value":"Choice 2"}
  3. Withdrawable Response Equals
    Same logic as Response Equals except the job manager can withdraw and reinstate this section (or question)
    {"Rule":"WithdrawableResponseEquals","QuestionLineNumber":6,"ComplianceStatement":"Comply"}
  4. Withdrawable Multi-select Contains
    Same logic as Multi-select Contains except the job manager can withdraw and reinstate this section (or question)
    {"Rule":"WithdrawableMultiSelectContains","QuestionLineNumber":4,"Value":"Choice 2"}


Relax!

  • There are many job configuration options so preparing an import file for the first time can seem very difficult
  • There is no rocket-science in this process
  • It's simply a matter of putting the desired values in the correct columns and remembering that Flags are used to indicate the features that apply to the job, section, or question
  • Training and assistance can also be provided should it all seem too hard


Terms of use

Visitors who are not part of the intended audience (as specified on this space's overview page) may not access this content.
If you are accessing content for which you are not the intended audience you may not use it for any purpose.
As private content can be published in error, if in doubt, you are not the intended audience.
Copyright © - Sharrowlane Pty Ltd