OData: Troubleshooting guide

Contents

Here are some common questions and answers about OData in Aptem.

1. Why am I unable to read data from OData feeds?
2. How is the horizontal progress bar in the OTJ Hours widget calculated using OData fields?
3. What are the fields used in OData to retrieve information on usage?
4. How do I report on hours uploaded to a specific learning plan component?
5. In OData, how do I see the sub-programmes and the learners linked to them?
6. What date does the Last feedback date field display in OData?
7. Is it possible to report on Total completed hours using OData?
8. How is the AgeAtStart field calculated?
9. What are the equivalent OData fields for Complete by date and Due date in the learning plan?
10. In OData, how is the EmploymentEndDate field calculated?
11. Is there an OData field for the Target and Current progress for Apprenticeship Standard progress?
12. Where can I find the programme type corresponding to the ProgType number in the ILRAims entity?
13. Can multiple groups assigned to a learner be reported on in OData?
14. Can I report on the Learner Reference Number using OData?
15. Can I report on the permissions granted to administrator users using OData?
16. How are the TasksDetails_ReviewMeetingOverdue and TasksDetails_ReviewMeetingCompleted fields calculated?
17. Why does the Episodes table show more programmes than are visible on the user profile?
18. In OData, how is the EmploymentStartDate field calculated?
19. Why does my Power BI report fail to load columns from the Onboarding_ADET_GRModel fields?
20. Why is the last submission date in the Power BI Caseload report not showing the most recent evidence?

Why am I unable to read data from OData feeds?

There are several reasons why data may not be returned from OData:

• The OData key belongs to an administrator account that has expired or been cancelled. For security reasons the key is blocked. Contact the Aptem Support team to resolve this.
  • If you have built reports using a specific administrator's API key and that account has been cancelled, the reports will stop working until the API key is updated.
• The OData key has been mistyped in your query and is therefore rejected.
• The group access settings on the administrator account assigned to the OData key do not permit access to certain groups of learners, so their data is not returned.
• Your OData query has not specified column names. By default, on large tables only the first 10 columns are returned.
• Where column names have been defined, the query URL can become very long. This is common with custom fields and skills radar fields in the Users table. For security reasons, the request URL cannot exceed 8,000 characters. Queries exceeding this limit are blocked. The solution is to split the required columns into groups so that each query stays under 8,000 characters.
• If you see the warning "We couldn't authenticate with the credentials provided. Please try again." when connecting via Excel, the OData key may belong to an administrator account that has not yet been activated: its subscription status is still 'Invited'. Once that account is activated, you can access the OData feed using its API key.

How is the horizontal progress bar in the OTJ Hours widget calculated using OData fields?

The OTJ (off-the-job) Hours widget metrics on the learner dashboard can be calculated using the following OData fields.

Submitted and completed hours

• Submitted hours = UserLearningPlanSummary_SubmittedTime
• Completed hours = UserLearningPlanSummary_CompletedTime

Both values are returned in minutes. Divide by 60 to convert to hours.

Target hours

The target is calculated using these OData fields:

• Learning start date = UserILRSummary_ProgrammeStartDate
• Learning end date = UserILRSummary_PlannedEndDate
• Planned hours = UserILRSummary_PlannedHours

The target represents the number of OTJ hours the learner is expected to have completed based on their progress through the programme timeline. The maximum target value equals the learner's planned hours.

The target is calculated as follows:

1. Programme has not started. If the programme start date is later than the current date, target hours = 0.
2. Programme has ended. If the planned end date is on or before the current date, target hours = planned hours.
3. Programme is in progress. If the programme start date is on or before the current date and the planned end date is later than the current date: target hours = planned hours × (days elapsed ÷ total programme duration).

Where:

• Total programme duration (days) = planned end date − programme start date
• Days elapsed (days) = current date − programme start date

Worked example

Using these OData values:

• Programme start date (UserILRSummary_ProgrammeStartDate): 1 January 2026
• Planned end date (UserILRSummary_PlannedEndDate): 31 August 2026
• Current date: 2 June 2026
• Planned hours (UserILRSummary_PlannedHours): 40 hours
• Submitted time (UserLearningPlanSummary_SubmittedTime): 800 minutes
• Completed time (UserLearningPlanSummary_CompletedTime): 600 minutes

This gives:

• Submitted hours: 800 ÷ 60 = 13.33 hours
• Completed hours: 600 ÷ 60 = 10 hours

The programme is in progress, so the target hours are:

• Programme duration: 31 August 2026 − 1 January 2026 = 242 days
• Days elapsed: 2 June 2026 − 1 January 2026 = 152 days
• Target hours: 40 × (152 ÷ 242) = 25.12 hours

Progress status: submitted hours 13.33, completed hours 10, target hours 25.12.

What are the fields used in OData to retrieve information on usage?

The Users table in the OData feed includes the following user login fields:

• UserSummary_LoginsNumberThisAcademicYear
• UserSummary_LoginsNumberMonth
• UserSummary_LoginsNumber7Days
• UserSummary_LoginsNumber
• UserSummary_LastLoginDate
• UserSummary_FirstLoginDate

Additional fields provide summary information on employability-related activity such as clicked job links, applications made, placement logged hours, and Advice Centre usage. The Users table also provides summary information on a learner's learning plan, reviews, and tasks activity.

For a full list of available data fields, see the OData dictionary.

How do I report on hours uploaded to a specific learning plan component?

The LearningPlanComponents table in OData provides information on every learning plan component for every learner and programme.

The PlannedHours and ActualHours columns provide this information per component.

PlannedHours – the hours recorded against the component in the Planned hours field. Returned as an integer in hours.

ActualHours – the sum of off-the-job hours spent across all evidence for the selected component where the evidence status is Accepted or QA Verified. Returned as a decimal in hours (for example, 2.05 means 2 hours and 3 minutes).

See the OData dictionary for more information on available fields.

In OData, how do I see the sub-programmes and the learners linked to them?

The Episodes table in OData provides information on all programmes and sub-programmes ever assigned to a learner, including current and historic records.

Filtering the Episodes table with ProgramType=SubProgramme returns all sub-programmes assigned to at least one learner.

To identify which programmes and sub-programmes a learner is currently assigned to:

• Use a lookup for the LearnerId against the Id on the Users table to add the UserProgram_ProgramId field (the learner's current programme) to the Episodes table.
• Filter the Episodes table where either Episodes[ProgramId] or Episodes[ParentProgramId] matches the current programme ID from the Users table.

What date does the Last feedback date field display in OData?

The Last feedback date field displays the most recent assessment or feedback date for any piece of evidence across all learning plan components for a learner. It is effectively the last date any evidence was marked in the learning plan.

Is it possible to report on Total completed hours using OData?

The Users OData table includes a number of learning plan summary fields, all prefixed with UserLearningPlanSummary_.

The UserLearningPlanSummary_CompletedTime field corresponds to a learner's Total completed hours metric, returned as the total number of minutes. Convert to hours by dividing by 60.

For example, if OData returns "UserLearningPlanSummary_CompletedTime": 1584, dividing 1584 by 60 gives 26.4 hours, which should be read as 26 hours and 24 minutes.

Note: UserLearningPlanSummary_CompletedTime returns the same value as the Completed hours shown on the Learning plan overview page (in Aptem Classic) and the off-the-job hours widget on the learner dashboard.

How is the AgeAtStart field calculated?

There is a distinction between the PersonalInformation_AgeAtStart field in OData and the age displayed on the ILR page. These are calculated differently.

ILR page – age on 31 August of the year the programme started

• Based on the learner's age as of 31 August of the funding year in which their learning start date falls.
• The learning start date comes from the ILR aims (the most recent programme aim, or if not available, the most recent non-programme aim).
• This calculation is not directly available in OData and must be derived manually (see below).
  

OData – PersonalInformation_AgeAtStart (Users table)

• A simplified calculation based on the current programme's start date (from Programme Details) and the date of birth.
• Does not apply the 31 August cut-off used in ILR calculations.

Both ages can be derived using Power Query in Excel or Power BI:

• ILR age can be derived using the UserPersonalDetails_Dob and UserILRSummary_ProgrammeStartDate fields from the OData Users table.
• Age at start is already available in the Users table as PersonalInformation_AgeAtStart, calculated using UserPersonalDetails_Dob and UserProgram_StartDate.

What are the equivalent OData fields for Complete by date and Due date in the learning plan?

The LearningPlanComponents table in OData provides information on every learning plan component for every learner and programme, including historic episodes.

The OData field DueDate reflects the Complete by field shown on the learning plan page.

For components with associated tasks, the learning plan also shows a task date. The label depends on the component type:

Due date (Meeting, Assignment (task), Online training – External, End-Point Assessment, Mentoring, Shadowing)

Dates (Offline learning (placement/workshop), Scheduled online event)

Part of review meeting summary (Review components)

These task-related date fields are not available from the LearningPlanComponents OData table. To retrieve a component's task date, use the Tasks OData table instead, filtering by the LearningPlanComponentId field and selecting the Start field.

Example: ~/odata/1.0/Tasks?$filter=LearningPlanComponentId eq '1068'&$select=Id,LearningPlanComponentId,Start

In OData, how is the EmploymentEndDate field calculated?

The LearnerEmployment table in OData contains a field named EmploymentEndDate. This is calculated as the day before the start of the next chronological employment status record, if one exists. Otherwise the value is null.

The start date of the employment status record refers to the ILR Date status applies field (DateEmpStatApp).

In the example below, the EmploymentEndDate for each record equals:

• First record: 17/03/2025
• Second record: 04/04/2025
• Third record: null

Is there an OData field for the Target and Current progress for Apprenticeship Standard progress?

The Current and Target percentages shown in the screenshot below are not currently available in the OData feed.

Where can I find the programme type corresponding to the ProgType number in the ILRAims entity?

The IlrAims table in OData lists all ILR episodes for learners. The ProgType field displays the ILR programme type value for each aim. This corresponds to the attribute code in the ILR specification.

Note: Aptem supports all ProgType codes from both the current and archive specifications listed below.

You can also view the list of supported ProgType codes from within the ILR page. Navigate to a live ILR, go to the Aims section, and select the edit button.

The Programme types and their corresponding ProgType numbers are listed in the dropdown.

Can multiple groups assigned to a learner be reported on in OData?

Yes. This is possible using the expandable Groups table within the Users table.

Each group assigned to the learner appears as its own row in the data feed.

Can I report on the Learner Reference Number using OData?

Yes. The Learner Reference Number (LRN) is available in the Users table as the field UserILRSummary_LRN. The ULN (Unique Learner Number) is available as UserILRSummary_ULN.

See the OData dictionary for a full list of data fields.

Can I report on the permissions granted to administrator users using OData?

Yes. The Users table includes the field UserRoles_Roles, which returns the roles and permissions assigned to each administrator account. To check what a specific role permits, see the Recommended permission matrix.

How are the TasksDetails_ReviewMeetingOverdue and TasksDetails_ReviewMeetingCompleted fields calculated?

TasksDetails_ReviewMeetingOverdue shows the number of scheduled reviews that are overdue and incomplete. It excludes reviews with a Not scheduled status and includes only those that are In progress or Not started and past their due date.

TasksDetails_ReviewMeetingCompleted shows the total number of completed reviews: all reviews listed under the Finished tab on the Reviews page.

Why does the Episodes table show more programmes than are visible on the user profile?

The Episodes table lists all programmes and sub-programmes ever assigned to a learner, including those that were later removed (for example, added in error). This is a wider scope than what the user profile shows.

The Programme section on the user profile page (Aptem Classic) lists only:

• The current main programme (delivery or onboarding) – IsActive = TRUE in the Episodes table.
• Active sub-programmes linked to the current main programme – IsActive = TRUE.
• Previous (stopped) main programmes (delivery or onboarding) – IsActive = TRUE.
• Sub-programmes that were active when their main programme was stopped – IsActive = TRUE.

It does not list:

• Sub-programmes that were assigned and stopped within the associated main programme – IsActive = FALSE in the Episodes table.

Note: in the console, the Programme details page shows only the current programme and its active sub-programmes.

The Episodes table does not indicate which programme is the learner's current one. To determine this, use the Users table and request the following fields:

• UserProgram_ProgramId – the current main programme ID.
• UserProgram_CurrentProgramme – the current main programme name.
• UserProgram_SubPrograms – the list of active sub-programmes linked to the current main programme.

In OData, how is the EmploymentStartDate field calculated?

The LearnerEmployment table in OData contains a field named EmploymentStartDate. The value depends on the Status field of the employment record:

• If the status is In paid employment, the date is taken from the Start date with employer field.
  
• For all other statuses, the date reflects the Date status applies field, corresponding to the DateEmpStatApp field in the ILR schema.
  

Why does my Power BI report fail to load columns from the Onboarding_ADET_GRModel fields in the Users table?

This typically occurs when the OData query URL exceeds the maximum supported length of 8,060 characters. Tenants with a large number of custom onboarding fields are most likely to encounter this, as combining all field names in a single query can push the URL over the limit.

There are two options:

Option 1 – Split the query

Divide the Users table query into multiple smaller queries, each staying under the URL length limit. Include the Id field in every split query. Combine the results in Power BI using a merge or join on Id.

Option 2 – Reduce the fields selected

Select only the specific fields you need in your report rather than all available columns.

For guidance on building OData queries for Power BI, see Connecting your organisation's live data to Power BI via an API.

Why is the last submission date in the Power BI Caseload report not showing the most recent evidence submitted by a learner?

The last submission date may not be updating if the relevant learning plan component does not have a due date defined.

This is expected behaviour. The Power BI Caseload report uses the same logic as the Users OData feed: the expected number of components completed is calculated based on component due dates. Components without a due date, or with a due date set in the future, are excluded from this calculation. As a result, only evidence from components with a defined, non-future due date is reflected in the last submission date field.

To ensure a learner's latest evidence is captured in this report, check that the relevant learning plan components have a due date set.