Versioning Impacts on Reporting
Outline
Versioning has the following impacts on the data from your account related to activity versions and states - as well as enrollments. Use this guide as a reference if you are pulling platform data into an external reporting tool like Big Query or similar.
This guide walks you through the impacts of versioning on the data in the courses (activity) and enrollment tables for your account.
Versioning Impacts: Overview
When an activity is created, a draft version of the activity is also created. At the point of creation, the active version of the activity is the draft version - until the draft is published.
Versioning publish/edit process overview
When a draft is published, several things happen:
the active, published version (if any) is archived (e.g. version 1)
the new draft version becomes visible as the published version
the new draft version is maintained as the draft
At this point draft and the published versions are identical.
Edits can then be made to the draft version, and it can again be published. When a new draft is published:
the active, published version is archived (e.g. version 2)
the draft version becomes visible as the published version
the draft version is maintained, and again it will be identical to the published version
This process means that there is always one and only one draft version of an activity and there is one and only one published version of each activity. There can be multiple archived versions.
Exclude draft & archived course information
Records in the enrollments table with a 0 or NULL course_version_number are connected to enrollments in draft versions.
Records in the courses table have a version_number as follows:
- draft versions (if the version_number = 0)
- archive versions (if the version_number not equal to 0 and not NULL)
- published versions (if the version_number is NULL)
Use course ID for unique records
The uniqueness constraint for the Code property has been removed for accounts with versioning enabled. If you are using the code field to identify activities in your report, a record will be returned for each version of the activity (Draft, Published, Archived)
Each version of the activity (including the draft) is maintained in the courses table, and the id column in this table can never be duplicated (primary key).
Course ID
Activities are stored in the courses table. To locate unique records for an activity, use course ID in your query or as a filter.
published_course_ID is null returns records for the currently published version of an activity.
To find the course ID of the draft that corresponds to a published version, query the courses table with:
select courses.id
from courses
where courses.published_course_id = < < ID of the published version > >
and courses.version_number = 0
To determine the course ID of the published version that corresponds to the draft of an activity, query the courses table with:
select courses.published_course_id
from courses
where courses.id = < < ID of the draft version > >
and courses.version_number = 0
Review draft statuses
The courses table also maintains the status of drafts currently being edited. Use the draft_state column in the courses table to include the draft status of activities.
There are several statuses available in the draft state. These steps allow activities to move through a content development workflow in the UI prior to publishing:
- editing
- in-review
- in-translation
- approved
Using the draft_state column can help you monitor progress on activities as they are being developed or updated.
Enrollments in versioned activities
Enrollments are stored in the enrollments table. To distinguish enrollments by the published version or draft versions, query the enrollments table using course_version_number:
- **0 indicates the enrollment was for a draft version
- NULL indicates the enrollment was for a non-published and non-draft version. This occurs when enrollments are matched to an activity that has never been published.
- This typically only happens when an integration is used to upload historical enrollment data to Intellum and the integration occurred prior to publishing the activity.
- Non-0 indicates the enrollment was for a published version**
Did this article help?
Let us know by leaving a star rating or review at the top of this article