Common Data Sources & Relationships

This pages provides information on the common relationships within report writer

Relationships

Tips

Relationships need to be:

• Between compatible data source types

• i.e. varchar to varchar or int to int

Relationships usually are:

• Based on a shared field

• i.e. userid to userid or coursecalid to coursecalid

• Based on ID fields

i.e. userid, coursecalid, rsrcid, courseid, companyid

Key Data Sources

ap_courses_view    View of all the course templates in the system
ap_coursescalendar_view    View of all the course dates in the system
ap_coursescalendar_bookings_view View of all bookings on course dates
ap_users_coursescalendar_view    View of all the users course date records
ap_users_view    View of all user information
ap_users_employmentdetails_view    View of user's employment records
ap_companies_view View of all the companies in the system
ap_users_awards_view    View of all users training histories
ap_awards_view View of all awards in the system

For Custom Fields on the system, they can be found in the following data sources:

User fields - users
Course Date fields - coursescalendar
Course Template fields - coursescompanies
Delegate fields - userscoursescalendar
Invoice fields - invoices

Common use cases

Course information to candidate information

User information to company group information

Candidate information to invoicing information to the company being invoiced

Course date information to course date venue information

Course date information to course category information

Course date information to resource information

If you are using one of these views, the table below will show you possible links to other views:

View	Contains	Use with…

View	Contains	Use with…
ap_awards_view	All awards	ap_courses_awards_view ap_users_awards_view
ap_communications_view	All communications	ap_users_view ap_companies_view ap_coursescalendar_bookings_view ap_invoices_view ap_opportunties_view ap_users_coursescalendar_view
ap_companies_financeoptions_view	Customer to training provider finance terms	ap_companies_view
ap_companies_industries_view	Companies in industries	ap_companies_view
ap_companies_transactionmethods	The transaction methods available and to which companies	ap_companies_view
ap_companies_view	All companies	ap_communications_view ap_companies_financeoptions_view ap_companies_industries_view ap_companies_transactionmethods ap_companygroupscompanies_view ap_users_employmentdetails_view
ap_companygroupscompanies_view	The companies in company groups	ap_companies_view
ap_courses_awards_view	Awards related to courses	ap_awards_view ap_courses_view
ap_courses_categories_view	The categories associated with courses	ap_courses_view
ap_courses_view	All courses	ap_courses_categories_view ap_coursescalendar_view
ap_coursescalendar_bookings_view	All course bookings	ap_courses_view
ap_coursescalendar_status_view	All course dates with course and candidate statuses	ap_courses_view
ap_coursescalendar_venues_view	All course dates with venues with venue details	ap_coursescalendar_view
ap_coursescalendar_viabilityitems_view	Expenditure and income items for course dates	ap_courses_view
ap_coursescalendar_view	All course dates	ap_courses_view ap_coursescalendar_bookings_view ap_coursescalendar_status_view ap_coursescalendar_viabilityitems_view ap_resourcerequirements_view ap_users_coursescalendar_view ap_invoiceitems_view ap_trainerconfirmations_view
ap_creditnotes_view	All credit notes	ap_companies_view ap_users_view ap_transactions_view
ap_discounts_view	All discounts	ap_invoiceitems_view
ap_invoiceitems_view	All invoice items	ap_invoices_view
ap_invoicelogs_view	All logged actions relating to invoices	ap_invoices_view
ap_invoices_transactions_view	All transactions on invoices	ap_invoices_view
ap_invoices_view	All invoices	ap_invoiceitems_view
ap_notes_view	All notes made on objects; including resources, tasks, opportunities and users	ap_opportunities_view ap_users_view ap_companies_view
ap_opportunties_view	All CRM opportunities	ap_opportuntyitems_view
ap_opportuntyitems_view	All items from CRM opportunities	ap_opportunties_view ap_courses_view ap_coursescalendar_view
ap_resourcerequirements_view	Course and course date resource requirements	ap_courses_view ap_coursescalendar_view ap_resources_view
ap_resources_view	All resources	ap_resourcerequirements_view ap_resourcetypes_view
ap_resourcetypes_view	All resource types	ap_resources_view
ap_skilsprofiles_view	All skills profiles	ap_awards_view
ap_specialrequirements_view	Special requirements stored against users	ap_users_specialrequirements_view
ap_systemeventslog_view	Log of all audited actions in the system	ap_users_view
ap_tasks_view	All tasks	ap_users_view
ap_trainerconfirmations_view	All requests for trainer confirmation on course dates	ap_trainers_view ap_coursescalendar_view
ap_trainers_view	All trainers	ap_trainerconfirmations_view
ap_transactions_view	All transactions	ap_invoices_view
ap_users_awards_view	Users with awards	ap_awards_view ap_users_view
ap_users_coursescalendar_view	Users on course dates, i.e. candidates	ap_courses_view ap_coursescalendar_view ap_invoiceitems_view ap_users_view
ap_users_employmentdetails_view	The account/s users belong to	ap_users_view
ap_users_roles_view	The system roles users have i.e. administrator	ap_users_view
ap_users_specialrequirements_view	Users with special requirements	ap_specialrequirements_view ap_users_view
ap_users_usertypes_view	Users with Job Roles	ap_users_view
ap_users_view	All users	ap_users_awards_view ap_users_coursescalendar_view ap_users_employmentdetails_view ap_users_roles_view ap_users_specialrequirements_view ap_users_usertypes_view
ap_usertypes_view	All available Job Roles for users	ap_users_usertypes_view
ap_venues_view	All venues	ap_resourcerequirements_view
ap_vouchers_mappings_view	Vouchers mapping to users or companies for spending	ap_vouchers_view
ap_vouchers_view	All vouchers	ap_vouchers_mappings_view