OData field reference: calculations and reporting tips

This article explains how some commonly used OData fields are calculated, and how to report on things that need more than one field or table. For the full list of tables and fields, see the OData dictionary. If your OData feed isn't returning data, see OData: Troubleshooting guide.

The Users table includes these login fields:

• UserSummary_LoginsNumberThisAcademicYear

• UserSummary_LoginsNumberMonth

• UserSummary_LoginsNumber7Days

• UserSummary_LoginsNumber

• UserSummary_LastLoginDate

• UserSummary_FirstLoginDate

Other fields summarise employability activity, such as job links clicked, applications made, placement hours logged and Advice Centre use. The Users table also summarises each learner's learning plan, reviews and tasks.

The learning plan summary fields in the Users table all start with UserLearningPlanSummary_.

UserLearningPlanSummary_CompletedTime is the learner's total completed hours, returned in minutes. Divide by 60 to get hours. For example, 1584 minutes ÷ 60 = 26.4 hours, which is 26 hours and 24 minutes.

This is the same value as the completed hours on the learning plan overview page in Aptem Classic and the Off-The-Job Hours widget on the learner dashboard.

PersonalInformation_AgeAtStart isn't calculated the same way as the age on the ILR page:

• ILR page: the learner's age on 31 August of the funding year in which their learning start date falls. The learning start date comes from the most recent programme aim, or the most recent non-programme aim if there's no programme aim.

• PersonalInformation_AgeAtStart: a simpler calculation from the learner's date of birth (UserPersonalDetails_Dob) and the start date of their current programme (UserProgram_StartDate), without the 31 August cut-off.

The ILR age isn't available as a field. To report on it, work it out in Power Query in Excel or Power BI from UserPersonalDetails_Dob and UserILRSummary_ProgrammeStartDate. For more on the ILR age, see the article, Where ILR learner and employment data comes from.

UpdatedDate in the Users table updates straight away when:

• the Edit user page in Aptem Classic is saved, with or without changes, including custom fields

• the Learner details page in the learner profile in Aptem Console is saved, with or without changes. This covers Profile, Contact information, Employer, Referrer, Additional information (custom fields) and Mandation, but not Markers

• the About you page is saved in Aptem Classic or Aptem Console, with or without changes

• the programme status or subscription status changes

• programme details are saved from the user profile in Aptem Classic or Programme details in Aptem Console, with or without changes

• a process automation tracker updates learner details: change of name, address, contact details, working conditions or employer; withdrawal from learning; break in learning; or return to learning

• the programme is stopped, or the learner is moved onto another programme

• learner details are updated by CSV import

• an updateUser API request runs, even if nothing changes

• the learner's programme template is updated or saved, including onboarding, delivery and sub-programmes.

It doesn't update when secondary details change, such as tasks, markers, Functional Skills, cognitive assessment, placements and workshops, competencies, the ILR or Extended ILR summary, the learning plan summary, or personal information entered as an onboarding wizard step.

To report on all the groups a learner belongs to, use the expandable Groups table within the Users table. Each group the learner is in appears as its own row.

The learner reference number (LRN) is UserILRSummary_LRN, and the unique learner number (ULN) is UserILRSummary_ULN. Both are in the Users table.

UserRoles_Roles in the Users table returns the roles and permissions assigned to each admin. To see what a role allows, see the Recommended permission matrix.

Aptem holds three employer addresses, each from a different place:

• CRM employer address: the main address on the employer's organisation profile.

• Extended ILR employer address: the address the apprentice gives during onboarding.

• Employer address on the user profile: the address in Aptem Console under Learner details > Employer, linked to the learner's user record.

If you're reporting on users and can't find the employer address you expect, you probably need the third one. It's in the Users data, not the CRM or Extended ILR data. Use OData or Power BI and report on Employer Address Line 1, Employer Address Line 2, Employer Town, Employer County and Employer Postal Code. To find learners with no employer address, filter for users where these fields are blank.

• TasksDetails_ReviewMeetingOverdue: the number of scheduled reviews that are overdue and not complete. It counts reviews that are In progress or Not started and past their due date, and ignores reviews that are Not scheduled.

• TasksDetails_ReviewMeetingCompleted: the total number of completed reviews, meaning all reviews on the Finished tab of the Reviews page.

The last feedback date is the most recent date any evidence was assessed or given feedback, across all the learner's learning plan components. 

The LearningPlanComponents table has a row for every learning plan component, for every learner and programme, including historic episodes.

• PlannedHours: the hours in the component's Planned hours field, as a whole number of hours.

• ActualHours: the total off-the-job hours across all the component's evidence with a status of Accepted or QA Verified, as a decimal number of hours. For example, 2.05 is 2 hours and 3 minutes.

DueDate is the component's Complete by date on the learning plan.

Components with a task also show a task date on the learning plan. Its label depends on the component type:

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

• Dates: Offline learning (placement/workshop) and Scheduled online event

• Part of review meeting summary: Review components.

Task dates aren't in the LearningPlanComponents table. To get a component's task date, use the Tasks table, filter by LearningPlanComponentId and select Start. For example:

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

The Episodes table lists every programme and sub-programme ever assigned to a learner, current and historic, including ones that were later removed, for example because they were added in error.

Filter the Episodes table on ProgramType=SubProgramme to return every sub-programme assigned to at least one learner.

To find which programme and sub-programmes a learner is on now:

1. Look up LearnerId against Id in the Users table to add UserProgram_ProgramId (the learner's current programme) to the Episodes table.

2. Filter the Episodes table where ProgramId or ParentProgramId matches that current programme ID.

The Programme section of the user profile in Aptem Classic shows only episodes where IsActive is TRUE:

• the current main programme (onboarding or delivery)

• active sub-programmes linked to the current main programme

• previous, stopped main programmes

• sub-programmes that were active when their main programme was stopped.

It doesn't show sub-programmes that were assigned and then stopped within their main programme. These have IsActive set to FALSE.

In Aptem Console, the Programme details page shows only the current programme and its active sub-programmes.

The Episodes table doesn't say which programme is current. To find it, use these Users table fields:

• UserProgram_ProgramId: the current main programme ID

• UserProgram_CurrentProgramme: the current main programme name

• UserProgram_SubPrograms: the active sub-programmes linked to the current main programme.

EmploymentStartDate depends on the employment record's status:

• In paid employment: the Start date with employer.

• Any other status: the Date status applies, which is DateEmpStatApp in the ILR.

EmploymentEndDate is the day before the next employment status record starts, by Date status applies (DateEmpStatApp). If there's no later record, it's blank (null).

For example, a learner with three employment status records has an end date on the first two records (here, 17/03/2025 and 04/04/2025), and no end date on the third.

The IlrAims table lists every ILR aim for each learner. ProgType is the ILR programme type code for each aim, as set out in the ILR specification.

Aptem supports all these ProgType codes from the current and archived specifications:

You can also see the supported codes on a learner's ILR: in the Aims section, select the edit button and open the programme type dropdown.

• Apprenticeship standard progress: the Current and Target percentages shown for apprenticeship standard progress aren't in the OData feed.

• OData dictionary

• Using the OData dictionary

• OData: Troubleshooting guide

• Connecting your organisation's live data to Power BI via an API

• Off-The-Job Hours widget - Learner Dashboard