Skip to main content

New Features - Custom Fields & Audit Template Status

· 3 min read
Ed Controls Development Team
Ed Controls Development Team
Ed Controls Platform Development

We're excited to announce two important new features that enhance your data analysis capabilities in the Ed Controls PowerBI Connector!

🎯 Audit Template Status Filtering​

Why We Added This Feature​

We've added the ability to filter out draft audit templates to ensure you're only working with published, finalized templates in your reports. This prevents confusion and ensures data quality by separating draft content from production-ready templates.

What Changed​

  • New Column Added: All audittemplates tables now include a status column
  • Values: 'published' or 'draft'

How to Filter Out Draft Templates​

To exclude draft templates from your PowerBI reports, use this filter in your queries:

SELECT * FROM audittemplates
WHERE status = 'published'

Or to exclude drafts specifically:

SELECT * FROM audittemplates
WHERE status != 'draft'

🔧 Ticket Custom Fields​

Why We Added This Feature​

This functionality was recently added to Ed Controls to support custom fields with tickets, allowing you to capture and analyze additional ticket metadata specific to your organization's needs.

Database Structure​

We've created three new tables for each dataset to support flexible custom field functionality:

  1. tickets_custom_fields (Main table)

    • ticketid, field_id, field_label, field_type, field_value
    • Stores the primary custom field data and values

  2. tickets_custom_field_options (Choice options)

    • ticketid, field_id, option_id, option_text, option_image
    • Stores available options for dropdown/choice fields

  3. tickets_custom_field_selections (Selected choices)

    • ticketid, field_id, selected_option_id
    • Tracks which options were selected for multi-choice fields

Field Types Supported​

  • freetext: Free text input
  • numeric: Numeric values
  • date: Date values
  • singleSelectMultipleChoice: Single selection dropdowns
  • multiSelectMultipleChoice: Multiple selection checkboxes

Example Queries for Custom Fields​

Get all custom fields for a ticket:

SELECT field_label, field_type, field_value
FROM tickets_custom_fields
WHERE ticketid = 'your_ticket_id'

Get tickets with specific custom field values:

SELECT t.title, tcf.field_label, tcf.field_value
FROM tickets t
JOIN tickets_custom_fields tcf ON t.id = tcf.ticketid
WHERE tcf.field_label = 'Priority' AND tcf.field_value = 'High'

For choice fields with options:

SELECT
    tcf.field_label,
    tcf.field_value,
    tcfo.option_text
FROM tickets_custom_fields tcf
LEFT JOIN tickets_custom_field_options tcfo
    ON tcf.ticketid = tcfo.ticketid AND tcf.field_id = tcfo.field_id
WHERE tcf.field_type IN ('singleSelectMultipleChoice', 'multiSelectMultipleChoice')
note

Depending on the field type, you may need to query different tables:

  • Simple fields (freetext, numeric, date): Use only tickets_custom_fields table
  • Choice fields: Join with tickets_custom_field_options for available options
  • Multi-select fields: Join with tickets_custom_field_selections for individual selections

🚀 Getting Started​

These features are automatically available in your datasets. If you need assistance implementing these filters in your PowerBI reports or have questions about the new schema, please don't hesitate to reach out to our support team.

For technical questions or support, contact us at support@edcontrols.com