Data Source Glossary

Search for a solution

Users

ap_users_view - This Data Source contains data about Users.

users - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the User module.

userscompanies -This Data Source contains employment data for Users, such as Employment Start Date, whether this is their Main Account, etc. (companyid is the Account that a User has an employment record with).

ap_users_usertypes_view - This Data Source contains Job Roles linked to Users.

Delegates

ap_users_coursescalendar_view - This Data Source contains data about Delegates (Users on Courses), where companyid is the Account that was the booking was made under.

userscoursescalendarattributes - This Data Source contains data regarding any Attributes a Delegate has.

userselearningpackages - This Data Source contains data about eLearning Delegates.

usersdocumentpackages - This Data Source contains data about Knowledge Document Delegates.

You can link both userselearningpackages and usersdocumentpackages mappedid field to the ap_coursescalendar_view table and its coursecalid field to add in information about the Course, and then link ap_coursescalendar_view to ap_users_coursescalendar_view using the coursecalid fields for information about the Delegate (Status, etc.).

Courses

courses - This Data Source contains data about Course Templates.

coursescompanies - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the Course Template module.

ap_courses_categories_view - This Data Source contains data to link Categories to Course Templates.

ap_coursescalendar_view - This Data Source contains data about Course Dates (excluding any Courses with no Trainers/Delegates).

coursescalendar - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the Course Date module, and the 'sessional(bit)' field to filter out any parent or child courses.

coursescalendarbookings - This Data Source contains data about Bookings. The notable fields it contains are as follows:

  • The User who created the basket (userid)

  • The User marked as the Booker for the basket (bookuserid)

  • The Opportunity that was used to make the booking (opportunityid)

  • The Account that was the booking was made by (companyid)

You can link a Delegate’s booking record to their Delegate record by linking ap_users_coursescalendar_view’s ccbookid field to coursescalendarbookings view’s field of the same name!

coursedate_statusonly_view - This Data Source contains data about Course Date Status, and can be linked to your coursescalendar data source using 'coursescalid'.

In the database and therefore Report Writer, coursescalendar is a Course Date table and courses is for Course Templates – you will need to know the difference!

Accounts

ap_companies_view - This Data Source contains data about Accounts (only with the status of 'Enabled').

companies - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the Account module.

ap_companygroupcompanies_view - This Data Source contains data to link Accounts to Account Groups.

Resources

ap_resourcerequirements_view - This Data Source contains data about Resource Requirements on a Course Date.

resourcerequirements - This Data Source contains data about Resource Requirements on Course Dates or Templates (mappedid is the ID of the Course Date or Template).

ap_resources_view - This Data Source contains data about Resources.

ap_venues_view - This Data Source contains data about Venues.

Finance

ap_invoices_view - This Data Source contains data about Invoices (excluding any Invoices with a custom Reference Prefix that isn't 'INV').

invoices  - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the Invoices module and also includes Credit Notes.

ap_invoiceitems_view - This Data Source contains data about Invoice line items (excluding any Purchases/Products).

invoiceitems - You would use this Data Source if you need all of the possible Invoice line items.

ap_transactions_view - This Data Source contains data about Transactions on Invoices.

ap_discounts_view - This Data Source contains data about Discounts.

discountmappings - This Data Source contains data about each use of a Discount.

The mappedid field will be the ID of the Booking, Delegate or Invoice, depending on whether your Discount has the type of “Course Booking”, “Delegate” or “Invoice”. Therefore, this can be linked to the coursescalendarbookings (ccbookid), userscoursescalendar (usercoursecalid) or invoices (invoiceid) tables.

ap_vouchers_view - This Data Source contains data about Vouchers.

ap_vouchers_mappings_view - This Data Source contains data about each Voucher issued to a User/Account, such as the Status, Expiry Date, Value, etc.

Awards

ap_awards_view - This Data Source contains data about Awards.

userawards - This Data Source contains data about each Award a User had, such as Expiry Date, Renewed, etc., as well as any Custom Fields in the Opportunity module.

ap_courses_awards_view - This Data Source contains data about Awards that are linked to Courses.

Opportunities

ap_opportunities_view - This Data Source contains data about Opportunities, such as Sales Stage, Source, Linked To Account, etc.

opportunities - You would use this Data Source if you need all of the above fields as well as any Custom Fields in the Opportunity module.

opportunityitems - This Data Source contains data about Opportunity Items, where you can add columns regarding Quantity, Unit Cost, etc.

opportunityitemmappings - This Data Source contains data to link Opportunity Items to the Courses they are mapped to. The mappedid field is the ID of the Course Date that the Opportunity Item is for, therefore can be linked to ap_coursescalendar_view using the coursecalid_2 field.

Notes

ap_notes_view - This Data Source contains data about Notes (mappedid is the ID of the User, Course, Opportunity, etc. that the Note has been added to).

Products & Services

products - This Data Source contains data about Products & Services.

productmappings - This Data Source contains data to link Products & Services to Course Dates or Templates.

purchases - This Data Source contains data about Purchases.

Custom Fields

The correct Data Sources to use for the different types of Custom Fields are below:

User fields - users

Account fields - companies

Course Date fields - coursescalendar

Course Template fields - coursescompanies

Delegate fields - userscoursescalendar

Invoice fields - invoices

Opportunity fields - opportunities

User Award fields - userawards