consultations.yaml

dataset_type: consultations
target_dataset: consultations

title: Open Dialogue - Consultations
shortname: Consultations
notes: Access, upload and modify consultation reports for your organization

template_version: 3
template_updated: 2019-04-15

portal_type: dialogue
collection: consultations

sidebar_extra_content:
  en: 'Description and guidance are being developed. If you have questions, please email [consultation@pco-bcp.gc.ca](mailto:consultation@pco-bcp.gc.ca).'
  fr: 'La description et les directives sont en développement. Si vous avez des questions, veuillez écrire à [consultation@pco-bcp.gc.ca](mailto:consultation@pco-bcp.gc.ca).'

gcpedia_link: http://www.gcpedia.gc.ca/wiki/Consultations_Registry_on_Open.Canada.ca

resources:
- title: Open Dialogue - Consultations
  resource_name: consultations

  published_resource_id: 92bec4b7-6feb-4215-a5f7-61da342b2354

  create_form: true
  edit_form: true
  fields:

  # 3.1
  - datastore_id: registration_number
    label:
      en: Registration Number
      fr: Numéro d’enregistrement
    description:
      en: "This field is populated by the user. It is a unique reference number given to each line item in the spreadsheet. Having a unique identifier for each item will allow users locate a specific item in the registry should they need to modify or delete. The Registration Number should respect the following format: C-XXXXXXX."
      fr: "Cette zone doit être remplie par l’utilisateur. Il s’agit d’un numéro de référence unique donné à chaque article de la feuille de calcul. Les utilisateurs peuvent ainsi trouver, dans un registre donné, un article précis à modifier ou à supprimer. Le numéro d’enregistrement doit respecter le format suivant : C-XXXXXXX."
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Free text
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Single
    datastore_type: text

  # 3.2
  - datastore_id: publishable
    label:
      en: Publish Record
      fr: Prêt à publier?
    description:
      en: This field identifies whether this record can be published as a summary on the appropriate public facing website.
      fr: Cette zone indique si un résumé du dossier peut être publié sur le site Web public approprié.
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Controlled List
    validation: This field must not be empty (if status is set to Not Going Forward, publishable must be no)
    visible_to_public: No
    occurrence: Single
    datastore_type: text
    excel_error_formula: 'OR({default_formula},AND(TRIM(LEFT({publishable},FIND(":",{publishable}&":")-1))="Y",TRIM(LEFT({status},FIND(":",{status}&":")-1))="NF"))'
    excel_full_text_choices: true
    choices:
      Y:
        en: "Yes"
        fr: Oui
      N:
        en: "No"
        fr: Non

  # 3.3
  - datastore_id: partner_departments
    label:
      en: Partner Department(s)
      fr: Ministère(s) partenaire(s)
    description:
      en: This field identifies the name(s) of the departments who are collaborating on the identified engagement process. Please provide the numerical code associated to each department and separated by commas.
      fr: Cette zone indique le ministère ou les ministères qui collaborent au processus de participation en question. Il faut indiquer le nom complet des ministères (aucun acronyme).
    obligation: Optional
    format_type: Free text (comma separated list of codes from reference table)
    validation: None
    visible_to_public: Yes
    occurrence: Repeatable
    datastore_type: _text
    excel_full_text_choices: true
    choices_file: choices/consultation_org.yaml

  # 3.4
  - datastore_id: subjects
    label:
      en: Subjects
      fr: Sujets
    description:
      en: This field indicates the different subjects related to the engagement process.
      fr: Cette zone indique les différents sujets sur lesquels porte le processus de participation.
    obligation: Mandatory
    excel_required_formula: 'or(isblank({end_date}),{end_date}>=DATE(2018,1,1))'
    form_required: true
    format_type: Free text (comma separated list of codes from reference table)
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Repeatable
    datastore_type: _text
    excel_column_width: 30
    excel_full_text_choices: true
    choices_file: choices/consultation_subject.yaml

  # 3.5
  - datastore_id: title_en
    label:
      en: Consultation Title (English)
      fr: Titre de la consultation (en anglais)
    description:
      en: This field identifies the complete name of the consultation in English.
      fr: Cette zone indique le titre, en anglais, de la consultation.
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Free text
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Single
    datastore_type: text
    excel_column_width: 30
    form_attrs:
      size: 60

  # 3.6
  - datastore_id: title_fr
    label:
      en: Consultation Title (French)
      fr: Titre de la consultation (en français)
    description:
      en: This field identifies the complete name of the consultation in French.
      fr: Cette zone indique le titre, en français, de la consultation.
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Free Text
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Single
    datastore_type: text
    excel_column_width: 30
    form_attrs:
      size: 60

  # 3.7
  - datastore_id: description_en
    label:
      en: Description (English)
      fr: Description (en anglais)
    description:
      en: This field provides information regarding the nature of consultation and engagement activities in English. This includes a description of the main objectives and expected outcomes of the engagement process. Users are invited to explain how the engagement activities, methods and/or tools will help meet the consultation’s objectives.
      fr: Cette zone fournit de l’information, en anglais, sur la nature des activités de consultation et de participation. On y trouve notamment une description des principaux objectifs et des résultats attendus du processus de participation. On invite les utilisateurs à expliquer en quoi les activités, les méthodes et les outils permettront d’atteindre les objectifs de la consultation.
    obligation: Mandatory
    excel_required_formula: 'or(isblank({end_date}),{end_date}>=DATE(2018,1,1))'
    form_required: true
    format_type: Free Text (separate paragraphs with two blank lines)
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Repeatable
    datastore_type: text
    form_snippet: scheming/form_snippets/textarea.html
    form_attrs:
      style: "width: 100%; display: block"

  # 3.8
  - datastore_id: description_fr
    label:
      en: Description (French)
      fr: Description (en français)
    description:
      en: This field provides information regarding the nature of consultation and engagement activities in French. This includes a description of the main objectives and expected outcomes of the engagement process. Users are invited to explain how the engagement activities, methods and/or tools will help meet the consultation’s objectives.
      fr: Cette zone fournit de l’information, en français, sur la nature des activités de consultation et de participation. On y trouve notamment une description des principaux objectifs et des résultats attendus du processus de participation. On invite les utilisateurs à expliquer en quoi les activités, les méthodes et les outils permettront d’atteindre les objectifs de la consultation.
    obligation: Mandatory
    excel_required_formula: 'or(isblank({end_date}),{end_date}>=DATE(2018,1,1))'
    form_required: true
    format_type: Free Text (separate paragraphs with two blank lines)
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Repeatable
    datastore_type: text
    form_snippet: scheming/form_snippets/textarea.html
    form_attrs:
      style: "width: 100%; display: block"

  # 3.9
  - datastore_id: target_participants_and_audience
    label:
      en: Target Participants and Audience
      fr: Participants visés et public cible
    description:
      en: This field provides information regarding the target participants and audience of the engagement process.
      fr: Cette zone précise les participants visés et le public cible du processus de participation.
    obligation: Mandatory
    excel_required_formula: 'or(isblank({end_date}),{end_date}>=DATE(2018,1,1))'
    form_required: true
    format_type: Free text (comma separated list of codes from reference table)
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Repeatable
    datastore_type: _text
    excel_full_text_choices: true
    choices_file: choices/consultation_audience.yaml

  # 3.10
  - datastore_id: start_date
    label:
      en: Start Date
      fr: Date de début
    description:
      en: This field indicates when a planned consultation will begin to accept input from participants.
      fr: Ce champ indique quand une séance de planification prévue commencera à accepter les commentaires des participants.
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Date (Please format the data as YYYY-MM-DD)
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Single
    datastore_type: date
    form_snippet: scheming/form_snippets/date.html

  # 3.11
  - datastore_id: end_date
    label:
      en: End Date
      fr: Date de fin
    description:
      en: This field indicates the end date of the consultation. The consultation is considered closed when no more engagement activities will take place and/or when a department begins analysing the input received during the engagement process.
      fr: Ce champ indique la date à laquelle la consultation a pris fin. Une consultation est terminée lorsqu’il n’y a plus d’activités de participation qui se tiendront et/ou lorsqu’un ministère entreprend l’analyse des commentaires reçus dans le cadre du processus de participation.
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Date (Please format the data as YYYY-MM-DD)
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Single
    datastore_type: date
    form_snippet: scheming/form_snippets/date.html

  # 3.12
  - datastore_id: status
    label:
      en: Status
      fr: État
    description:
      en: The field indicates the current status of the engagement process.
      fr: Cette zone indique l’état d’avancement du processus de participation.
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Controlled List
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Single
    datastore_type: text
    excel_error_formula: 'OR({default_formula},AND(TRIM(LEFT({publishable},FIND(":",{publishable}&":")-1))="Y",TRIM(LEFT({status},FIND(":",{status}&":")-1))="NF"))'
    excel_full_text_choices: true
    choices:
      P:
        en: Planned
        fr: Prévue
      O:
        en: Open – Accepting Input
        fr: Ouverte – Avis acceptés
      CA:
        en: Closed – Analyzing Input
        fr: Fermée – Analyse en cours
      CN:
        en: Closed – No Report Expected
        fr: Fermée – Aucun rapport en attente
      CR:
        en: Closed – Report Available
        fr: Fermée – Rapport disponible
      NF:
        en: Not Going Forward
        fr: Ne va pas de l'avant

  # 3.13
  - datastore_id: profile_page_en
    label:
      en: Link to Consultations Profile Page (English)
      fr: Lien vers la page du profil des consultations (en anglais)
    description:
      en: This field provides the link to the English engagement information profile page.
      fr: Cette zone contient le lien vers la page de profil (en anglais) qui donne des renseignements sur le processus de participation du public.
    obligation: Mandatory
    excel_required_formula: 'or(isblank({end_date}),{end_date}>=DATE(2018,1,1))'
    form_required: true
    format_type: Web address
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Single
    datastore_type: text
    form_attrs:
      size: 60

  # 3.14
  - datastore_id: profile_page_fr
    label:
      en: Link to Consultations Profile Page (French)
      fr: Lien vers la page du profil des consultations (en français)
    description:
      en: This field provides the link to the French engagement information profile page.
      fr: Cette zone contient le lien vers la page profil française qui contient des renseignements sur le processus de participation.
    obligation: Mandatory
    excel_required_formula: 'or(isblank({end_date}),{end_date}>=DATE(2018,1,1))'
    form_required: true
    format_type: Web address
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Single
    datastore_type: text
    form_attrs:
      size: 60

  # 3.15
  - datastore_id: report_available_online
    label:
      en: Report Available online
      fr: Rapport disponible en ligne
    description:
      en: This field indicates if the “What we Heard” report is completed, published and made available online on the Consulting with Canadians website.
      fr: Cette zone indique si le rapport intitulé « Ce que nous avons entendu » est terminé, publié et disponible sur le site Web « Consultations auprès des Canadiens ».
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Controlled List
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Single
    datastore_type: text
    excel_full_text_choices: true
    choices:
      Y:
        en: "Yes"
        fr: Oui
      N:
        en: "No"
        fr: Non

  # 3.16
  - datastore_id: report_link_en
    label:
      en: Link to the “What we Heard” Report in English
      fr: Lien – rapport « Ce que nous avons entendu » en anglais
    description:
      en: This field provides the link to the “What we Heard” report when it is available online.
      fr: Cette zone contient le lien vers le rapport intitulé « Ce que nous avons entendu » en anglais après que le rapport est affiché en ligne.
    obligation: Optional
    format_type: Web address
    validation: None
    visible_to_public: Yes
    occurrence: Single
    datastore_type: text
    form_attrs:
      size: 60

  # 3.17
  - datastore_id: report_link_fr
    label:
      en: Link to the “What we Heard” Report in French
      fr: Lien – rapport « Ce que nous avons entendu » en français
    description:
      en: This field provides the link to the “What we Heard” report when it is available online.
      fr: Cette zone contient le lien vers le rapport intitulé « Ce que nous avons entendu » après que le rapport est affiché en ligne.
    obligation: Optional
    format_type: Web address
    validation: None
    visible_to_public: Yes
    occurrence: Single
    datastore_type: text
    form_attrs:
      size: 60

  # 3.18
  - datastore_id: contact_email
    label:
      en: Departmental Consultation Contact
      fr: Responsable de la consultation au ministère
    description:
      en: This field provide the email address of the departmental consultation and public engagement contact (if applicable). The department consultation contact is defined as the primary contact for departmental consultations.
      fr: Cette zone indique l’adresse courriel de la personne du ministère responsable de la consultation et de la participation du public (s’il y a lieu). Le responsable de la consultation au ministère est le principal responsable des consultations du ministère.
    obligation: Optional
    format_type: Email
    validation: None
    visible_to_public: No
    occurrence: Single
    datastore_type: text
    form_attrs:
      size: 40

  # 3.19
  - datastore_id: policy_program_lead_email
    label:
      en: Policy/Program Lead for the Consultation
      fr: Responsable des politiques ou des programmes pour la consultation
    description:
      en: This field provides the email address of the policy or program lead for the consultation. The policy or program lead is defined as the person leading the specific consultation and accountable for the engagement activities.
      fr: Cette zone indique l’adresse courriel du responsable des politiques ou des programmes pour la consultation. Il s’agit de la personne qui dirige la consultation en question et qui doit rendre des comptes sur les activités de participation.
    obligation: Optional
    format_type: Email
    validation: None
    visible_to_public: No
    occurrence: Single
    datastore_type: text
    form_attrs:
      size: 40

  # 3.20
  - datastore_id: remarks_en
    label:
      en: Remarks (English)
      fr: Remarques (en anglais)
    description:
      en: This field identifies additional information on the consultation that could not be submitted in other fields, in English
      fr: Cette zone comprend des renseignements supplémentaires sur la consultation qu’il n’était pas possible d’inscrire dans d’autres zones (en anglais).
    obligation: Optional
    format_type: Free Text (separate paragraphs with two blank lines)
    validation: None
    visible_to_public: No
    occurrence: Repeatable
    datastore_type: text
    form_snippet: scheming/form_snippets/textarea.html
    form_attrs:
      style: "width: 100%; display: block"

  # 3.21
  - datastore_id: remarks_fr
    label:
      en: Remarks (French)
      fr: Remarques (en français)
    description:
      en: This field identifies additional information on the consultation that could not be submitted in other fields, in French.
      fr: Cette zone comprend des renseignements supplémentaires sur la consultation qu’il n’était pas possible d’inscrire dans d’autres zones (en français).
    obligation: Optional
    format_type: Free Text (separate paragraphs with two blank lines)
    validation: None
    visible_to_public: No
    occurrence: Repeatable
    datastore_type: text
    form_snippet: scheming/form_snippets/textarea.html
    form_attrs:
      style: "width: 100%; display: block"

  # 3.22
  - datastore_id: high_profile
    label:
      en: High Profile
      fr: Haute visibilité
    description:
      en: This field indicates whether the consultation or public engagement initiative is considered high profile. The consultation is considered high profile if it meets one or more of the criteria in the ‘Rationale’ field.
      fr: "Cette zone indique si la consultation ou initiative de participation est à haute visibilité. La consultation est considérée comme de haute visibilité si elle réponde à un ou plus de critères à la zone 'Raison'."
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Controlled List
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Single
    datastore_type: text
    excel_full_text_choices: true
    choices:
      Y:
        en: "Yes"
        fr: Oui
      N:
        en: "No"
        fr: Non

  # 2.23
  - datastore_id: rationale
    label:
      en: Rationale
      fr: Raison
    description:
      en: The field indicates the rationale that prompted the public engagement initiative. This field includes only criteria that define high profile public engagement.
      fr: Cette zone comprend le(s) motif(s) de l’activité de participation du public. Cette zone inclut uniquement les critères qui définissent une consultation ou participation public de profil élevé.
    obligation: Mandatory
    excel_required_formula: 'LEFT({high_profile},1)="Y"'
    format_type: Controlled List
    validation: This field must not be empty
    visible_to_public: Yes
    occurrence: Repeatable
    datastore_type: _text
    excel_full_text_choices: true
    choices:
      BG:
        en: Budget
        fr: Budget
      IN:
        en: International Commitment
        fr: Engagement international
      LC:
        en: Duty to Consult
        fr: Obligation de consulter
      ML:
        en: Mandate Letter
        fr: Lettre de mandat
      MP:
        en: Minister and/or Parliamentary Secretary Involvement and/or announcement
        fr: Implication ou annonce du ministre ou du secrétaire parlementaire
      PC:
        en: Parliament Committee
        fr: Comité parlementaire
      ST:
        en: Speech of the Throne
        fr: Discours du Trône

  - datastore_id: record_created
    label: Record Creation Time
    import_template_include: false
    visible_to_public: false
    datastore_type: timestamp
    preview_class: bg-info

  - datastore_id: record_modified
    label: Last Record Modification Time
    import_template_include: false
    visible_to_public: false
    datastore_type: timestamp
    preview_class: bg-info

  - datastore_id: user_modified
    label: User Last Modified Record
    import_template_include: false
    visible_to_public: false
    datastore_type: text
    preview_class: bg-info

  datastore_primary_key: registration_number
  datastore_indexes: ""

  default_preview_sort: registration_number

  excel_example_height: 32
  excel_data_num_rows: 500

  triggers:
  - consultations_trigger: |
      DECLARE
        errors text[][] := '{{}}';
        crval RECORD;
      BEGIN
        errors := errors || required_error(NEW.registration_number, 'registration_number');
        IF NEW.registration_number LIKE '%,%' THEN
          errors := errors || ARRAY[['registration_number', {registration_number_error}]];
        END IF;
        errors := errors || required_error(NEW.publishable, 'publishable');
        errors := errors || choice_error(NEW.publishable, {publishable}, 'publishable');
        crval := choices_clean_error(NEW.partner_departments, {partner_departments},
          'partner_departments');
        errors := errors || crval.error;
        NEW.partner_departments := crval.clean;
        IF NEW.end_date > '2018-01-01'::date THEN
          errors := errors || required_error(NEW.subjects, 'subjects');
        END IF;
        crval := choices_clean_error(NEW.subjects, {subjects}, 'subjects');
        errors := errors || crval.error;
        NEW.subjects = crval.clean;
        errors := errors || required_error(NEW.title_en, 'title_en');
        errors := errors || required_error(NEW.title_fr, 'title_fr');
        IF NEW.end_date > '2018-01-01'::date THEN
          errors := errors || required_error(NEW.description_en, 'description_en');
          errors := errors || required_error(NEW.description_fr, 'description_fr');
          errors := errors || required_error(NEW.target_participants_and_audience,
            'target_participants_and_audience');
        END IF;
        crval := choices_clean_error(NEW.target_participants_and_audience,
          {target_participants_and_audience}, 'target_participants_and_audience');
        errors := errors || crval.error;
        NEW.target_participants_and_audience := crval.clean;
        errors := errors || required_error(NEW.start_date, 'start_date');
        errors := errors || required_error(NEW.end_date, 'end_date');
        errors := errors || choice_error(NEW.status, {status}, 'status');
        errors := errors || required_error(NEW.profile_page_en, 'profile_page_en');
        errors := errors || required_error(NEW.profile_page_fr, 'profile_page_fr');
        errors := errors || choice_error(NEW.report_available_online, {report_available_online},
          'report_available_online');
        errors := errors || required_error(NEW.high_profile, 'high_profile');
        errors := errors || choice_error(NEW.high_profile, {high_profile}, 'high_profile');
        IF NEW.high_profile = 'Y' THEN
          errors := errors || required_error(NEW.rationale, 'rationale');
        END IF;
        IF NEW.status = 'NF' AND NEW.publishable = 'Y' THEN
          errors := errors || ARRAY[['status', {status_error}]];
        END IF;
        crval := choices_clean_error(NEW.rationale, {rationale}, 'rationale');
        errors := errors || crval.error;
        NEW.rationale = crval.clean;

        IF errors = '{{}}' THEN
          RETURN NEW;
        END IF;
        RAISE EXCEPTION E'TAB-DELIMITED\t%', array_to_string(errors, E'\t');
      END;
  - update_record_modified_created_trigger

  trigger_strings:  # yaml | not supported
    status_error: "If Status is set to: Not Going Forward, Publish Record must be set to No"
    registration_number_error: Comma is not allowed in Registration Number field

  examples:
    record:
      contact_email: first.last@example.gc.ca
      description_en:
        To engage and obtain Provincial and Territorial partner views
      description_fr:
        Obtenir les opinions des partenaires provinciaux et territoriaux
      partner_departments: ["D271", "D141"]
      end_date: "2018-05-30"
      start_date: "2018-05-15"
      policy_program_lead_email: program.lead@example.gc.ca
      publishable: "N"
      registration_number: CCC0249
      remarks_en: ""
      remarks_fr: ""
      report_available_online: "N"
      report_link_en: ""
      report_link_fr: ""
      status: P
      profile_page_en: http://example.gc.ca/en
      profile_page_fr: http://example.gc.ca/fr
      subjects: ["IP", "CD", "HS", "SE"]
      target_participants_and_audience: ["IP", "IG", "PT"]
      title_en: Family Services Program Reform
      title_fr: Réforme du programme des services à l'enfance
      high_profile: "Y"
      rationale: ["BG", "PC"]
    filters:
      publishable: "Y"
    filter_one:
      registration_number: CCC0249
    sort: start_date desc

excel_edge_style:
  PatternFill:
    fgColor: FF336B87
    patternType: solid
excel_header_style:
  PatternFill:
    patternType: solid
    fgColor: FF90AFC5
excel_column_heading_style:
  PatternFill:
    patternType: solid
    fgColor: FF90AFC5