- Created by Alice Pearson, last modified on 01 Nov, 2021
You are viewing an old version of this page. View the current version.
Compare with Current View Page History
« Previous Version 22 Next »
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
On this page:
- No labels