<style>p { margin: 0; }span.fr-emoticon.fr-emoticon-img { background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle; } span.fr-emoticon { font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0; } blockquote { border-left: solid 2px #5e35b1; color: #5e35b1; margin-left:0; padding-left:5px;}blockquote blockquote{ border-color: #00bcd4; color: #00bcd4;}blockquote blockquote blockquote{ border-color: #43a047; color: #43a047;} table.grid{ border-collapse: collapse;} table.grid td, table.grid th { border: 1px solid #ddd;} .fr-fic.fr-dib{ display: block; margin: 5px auto;}.fr-fic.fr-dib.fr-fir{ text-align: right; margin: 5px 0 5px auto;}.fr-fic.fr-dib.fr-fil{ text-align: left; margin: 5px auto 5px 0;}.fr-fic.fr-dii{ float: none; margin: 5px auto;}.fr-fic.fr-dii.fr-fil{ float: left; margin: 5px auto;}.fr-fic.fr-dii.fr-fir{ float: right; margin: 5px auto;}img.fr-dib.fr-fir { margin-right: 0; text-align: right;}img.fr-dib.fr-fil { margin-left: 0; text-align: left;}img.fr-dib { margin: 5px auto; display: block; float: none;}img.fr-bordered { box-sizing: content-box; border: solid 5px #CCC;}img.fr-shadow { box-shadow: 10px 10px 5px 0px #cccccc;}img.fr-rounded { border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;}</style><p><strong>In this guide we will cover:</strong></p><p><strong>- Common SQL Tables and What Each Contains</strong></p><p><strong>- Example Joins Between Them</strong></p><p><br></p><p><br></p><p>This guide will cover some commonly referenced SQL tables and the information that can be pulled from each. A majority of the tables listed here are also within the following schemas.</p><p><br></p><ul><li><a href="https://usehalo.com/haloitsm/sqlschema/" target="_blank" rel="noopener noreferrer"><strong>ITSM Schema</strong></a></li><li><a href="https://usehalo.com/halopsa/sqlschema/" target="_blank" rel="noopener noreferrer"><strong>PSA Schema</strong></a></li><li><a href="https://usehalo.com/halocrm/sqlschema/" target="_blank" rel="noopener noreferrer"><strong>CRM Schema</strong></a></li></ul><p>Each table listed below is sorted by the general area you are likely to be reporting on when using them. The most used columns within each are also outlined.</p><p><br></p><p id="isPasted"><strong><span style="font-size: 14pt;">Tickets</span></strong></p><p>These are the commonly used tables to report on tickets, and are often joined together.</p><p><br></p><p><strong>Faults and Actions:</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> actions </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> actions.faultid</span><span style="color: #778899;">=</span><span style="color: #000000;">faults.faultid</span></div><p><br></p><p><strong><span style="font-size: 12pt;">Faults</span></strong></p><p>Ticket details.</p><ul><li><strong id="isPasted">Faultid</strong>: ID of the ticket.<strong id="isPasted"><br id="isPasted"></strong></li><li><strong id="isPasted">Username</strong>: Name of the end-user of the ticket.<strong id="isPasted"><br id="isPasted"></strong></li><li><strong id="isPasted">Status</strong>: ID of the status of the ticket.<strong id="isPasted"><br id="isPasted"></strong></li><li><strong id="isPasted">Dateoccured</strong>:<strong id="isPasted"> </strong>Date the ticket was logged.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">RequestType</span></strong></p><p>Ticket type.</p><ul><li><strong id="isPasted">RTid</strong>: ID of the ticket type.</li><li><strong id="isPasted">RTdesc</strong>: Name of the ticket type.</li><li><strong id="isPasted">RTdefsection</strong>: Default team.</li><li><strong id="isPasted">RTworkflowid</strong>: ID of the workflow that is started upon logging.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">FlowHeader</span></strong></p><p>Workflow details.</p><ul><li><strong>FHid</strong>: ID of the workflow.</li><li><strong>FHname</strong>: Name of the workflow.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">FlowDetail</span></strong></p><p>Workflow step details.</p><ul><li><strong>FDfhid</strong>: ID of the workflow the step is on.</li><li><strong>FDname</strong>: Name of the workflow step.</li><li><strong>FDstart</strong>: Whether the workflow step is the start step.</li><li><strong>FDend</strong>: Whether the workflow step is the end step.</li><li><strong>FDstagenumber</strong>: Workflow stage the step is within.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">TStatus</span></strong></p><p>Ticket status.</p><ul><li><strong>Tstatus</strong>: ID of the status.</li><li><strong>Tstatusdesc</strong>: Name of the status.</li><li><strong>Tshortname</strong>: Icon text/short name of the status.</li><li><strong>TstatusputonSLAhold</strong>: Whether the status puts the ticket on hold.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">Actions</span></strong></p><p>Action details.</p><ul><li><strong>Faultid</strong>: ID of the ticket.</li><li><strong>Actionnumber</strong>: Which number the action was in the sequence of the ticket.</li><li><strong>Actoutcome</strong>: Name of the action outcome.</li><li><strong>Who</strong>: Who completed the action.</li><li><strong>Whe_</strong>: When the action was done.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">Feedback</span></strong></p><p>Ticket feedback.</p><ul><li><strong>FBid</strong>: ID of the feedback.</li><li><strong>FBusername</strong>: Who gave the feedback.</li><li><strong>FBemail</strong>: Email of the user.</li><li><strong>FBscore</strong>: Score level of the feedback.</li><li><strong>FBcomment</strong>: Comment given with the feedback.</li><li><strong>FBfaultid</strong>: ID of the ticket the feedback is for.</li></ul><p><br></p><p id="isPasted"><strong><span style="font-size: 14pt;">Service Level Agreements (SLAs)</span></strong></p><p id="isPasted">These are the commonly used tables to report on service level agreements (SLAs), and both are often joined together.</p><p><br></p><p><strong>SLAHead and Policy:</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> SLAHEAD </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> Slid</span><span style="color: #778899;">=</span><span style="color: #000000;">Pslaid</span></div><p><br></p><p><strong><span style="font-size: 12pt;">SLAHead</span></strong></p><p>SLA details.</p><ul><li><strong>SLid</strong>: ID of the SLA.</li><li><strong>SLdesc</strong>: Name of the SLA.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">Policy</span></strong></p><p>SLA priority.</p><ul><li><strong>Pslaid</strong>: ID of the SLA.</li><li><strong>Ppolicy</strong>: Priority level.</li><li><strong>Pdesc</strong>: Name of the priority.</li><li id="isPasted"><strong>Presponsetime</strong>: Response target.</li><li><strong>Presponseunits</strong>: Unit of time for response target.</li><li><strong>Ptime</strong>: Resolution target.</li><li><strong>Punits</strong>: Unit of time for resolution target.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Budgets</span></strong></p><p id="isPasted">These are the commonly used tables to report on project budgets, and both are often joined together to show an overall budget overview for a project.</p><p><br></p><p><strong>FaultBudget and BudgetType:</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> BudgetType </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> btid</span><span style="color: #778899;">=</span><span style="color: #000000;">fbtbtid</span></div><p><br></p><p><strong><span style="font-size: 12pt;">FaultBudget</span></strong></p><p>Budget on projects.</p><ul><li><strong>FBTfaultid</strong>: ID of the ticket.</li><li><strong>FBThours</strong>: Hours of the budget line.</li><li><strong>FBTrate</strong>: Rate of the budget line.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">BudgetType</span></strong></p><p>Budget type details.</p><ul><li><strong>BTid</strong>: ID of the budget type.</li><li><strong>BTname</strong>: Name of the budget type.</li><li><strong>BTdefaultrate</strong>: Default rate of the budget type</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Clients/Customers</span></strong></p><p id="isPasted">These are the commonly used tables to report on customer details, as well as the sites and users associated with them. These are often joined together to report on multiple users' details within a customer.</p><p><br></p><p><strong>Site and AddressStore:</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> AddressStore </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> assiteid </span><span style="color: #778899;">=</span><span style="color: #000000;"> ssitenum</span></div><p><br></p><p><strong><span style="font-size: 12pt;">Users</span></strong></p><p>User details.</p><ul><li><strong>Uusername</strong>: Name of the user.</li><li><strong>Usite</strong>: Site the user is under.</li><li><strong>Umobile</strong>: Home mobile number.</li><li><strong>Umobile2</strong>: Work mobile number.</li><li><strong>Utelhome</strong>: Home telephone number.</li><li><strong>Uextn</strong>: Work telephone number.</li><li><strong>Uemail</strong>: User's email address.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">UserChange</span></strong></p><p>User change history.</p><ul><li><strong>UCuid</strong>: ID of the user.</li><li><strong>UCusername</strong>: Name of the user.</li><li><strong>UColdvalue</strong>: Previous value before the change.</li><li><strong>UCnewvalue</strong>: New value after the change.</li><li><strong>UCwho</strong>: Which agent/what integration made the change.</li><li><strong>UCwhen</strong>: When the change was made.</li></ul><p><br></p><p id="isPasted"><strong><span style="font-size: 12pt;">Site</span></strong></p><p>Site details.</p><ul><li><strong>Ssitenum</strong>: Site ID.</li><li><strong>Sdesc</strong>: Site name.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">AddressStore</span></strong></p><p>Site address.</p><ul><li><strong>ASline1</strong>: Address line 1</li><li><strong>ASline2</strong>: Address line 2</li><li><strong>ASline3</strong>: Address line 3</li><li><strong>ASline4</strong>: Address line 4</li><li><strong>ASline5</strong>: Postcode/ZIP code.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">Area</span></strong></p><p>Customer details.</p><ul><li><strong>Aarea</strong>: Customer ID.</li><li><strong>Aareadesc</strong>: Name of the customer.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">Tree</span></strong></p><p>Top level details.</p><ul><li><strong>Treeid</strong>: Top level ID.</li><li><strong>Treedesc</strong>: Name of the top level.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Agents</span></strong></p><p id="isPasted">This is the main table to report on agents. It is often joined with ticket based reports to show the agent's details alongside the ticket details.</p><p><br></p><p><strong>Uname and Faults:<br></strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> uname </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> uname.unum </span><span style="color: #778899;">=</span><span style="color: #000000;"> f.assignedtoint</span></div><p><br></p><p><strong><span style="font-size: 12pt;">Uname</span></strong></p><p>Agent details.</p><ul><li><strong>Unum</strong>: Agent ID.</li><li><strong>Uname</strong>: Name of agent.</li><li><strong>Usection</strong>: Default team.</li><li><strong>Usmtp</strong>: Email address.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Contracts/Agreements</span></strong></p><p id="isPasted">This is the main table to report on contracts or agreements, and are often joined when creating invoice or billing related reports.</p><p><br></p><p><strong>ContractHeader and Area:</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> contractheader </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> charea</span><span style="color: #778899;">=</span><span style="color: #000000;">aarea</span></div><p><br></p><p><strong><span style="font-size: 12pt;">ContractHeader</span></strong></p><p>Agreement/contract details.</p><ul><li><strong>CHid</strong>: Contract ID.</li><li><strong>CHarea</strong>: Customer ID.</li><li><strong>CHstartdate</strong>: Start date.</li><li><strong>CHenddate</strong>: End date.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Assets</span></strong></p><p id="isPasted">These are the commonly used tables to report on assets. The first is often joined with site details to show an overview of each site's assets.</p><p><br></p><p><strong>Device and DeviceChange</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> device </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> did</span><span style="color: #778899;">=</span><span style="color: #000000;">dcdid</span></div><p><br></p><p><strong><span style="font-size: 12pt;">Device</span></strong></p><p>Asset details.</p><ul><li><strong>Did</strong>: Asset ID.</li><li><strong>Dinvno</strong><span style="white-space:pre;" id="isPasted">: Asset number.</span></li><li><span style="white-space:pre;" id="isPasted"><strong>Dsite</strong>: Site the asset is against.</span></li><li><span style="white-space:pre;" id="isPasted"><strong>Dtype</strong>: Asset type.</span></li></ul><p><span style="white-space:pre;" id="isPasted"><br></span></p><p><strong><span style="font-size: 12pt;">DeviceChange</span></strong></p><p>Asset change history.</p><ul><li><strong>DCdevnum</strong>: Asset ID.</li><li><strong>DColdvalue</strong>: Value before the change.</li><li><strong>DCnewvalue</strong>: Value after the change.</li><li><strong>DCwho</strong>: What agent/integration made the change.</li><li><strong>DCwhen</strong>: When the change was made.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">XType</span></strong></p><p>Asset type.</p><ul><li><strong>Ttypenum</strong>: Asset type ID.</li><li><strong>Tdesc</strong>: Asset type name.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Items/Products</span></strong></p><p id="isPasted">These are the commonly used tables to report on items or products, and are often joined with quotation or revenue reports.</p><p><br></p><p id="isPasted"><strong>Item and ItemStockHistory<br></strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> item </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> isiid </span><span style="color: #778899;">=</span><span style="color: #000000;"> iid</span></div><p><br></p><p><strong><span style="font-size: 12pt;">Item</span></strong></p><p>Product details.</p><ul><li><strong>Iid</strong>: Item ID.</li><li><strong>Igeneric</strong>: Item group ID.</li><li><strong>Idesc</strong>: Item name.</li><li><strong>Ibaseprice</strong>: Sell price</li><li><strong>Icostprice</strong>: Cost.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">ItemStock</span></strong></p><p>Stock quantity details.</p><ul><li><strong>ISiid</strong>: Item ID.</li><li><strong>ISlocation</strong>: Site ID.</li><li><strong>ISdate</strong>: Stock date.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">ItemStockHistory</span></strong></p><p>Stock change log.</p><ul><li><strong>ISHiid</strong>: Item ID.</li><li><strong>ISHlocation</strong>: Site ID.</li><li><strong>ISdate</strong>: Stock date.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">Generic</span></strong></p><p>Item/asset groups.</p><ul><li><strong>Ggeneric</strong>: Group ID.</li><li><strong>Gdesc</strong>: Group name.</li><li><strong>Gshowasitem</strong>: Whether the group is an item group.</li><li><strong>Gshowasequip</strong>: Whether the group is an asset group.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Quotations</span></strong></p><p id="isPasted">These are the commonly used tables to report on quotes, and are often joined together to give an overview of current quotations.</p><p><br></p><p><strong>QuotationHeader and QuotationDetail:</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> QUOTATIONDETAIL </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> qhid</span><span style="color: #778899;">=</span><span style="color: #000000;">qdqhid</span></div><p><br></p><p><strong><span style="font-size: 12pt;">QuotationHeader</span></strong></p><p>Quotation details.</p><ul><li><strong>QHid</strong>: Quote ID.</li><li><strong>QHfaultid</strong>: Ticket ID the quote was raised from.</li><li><strong>QHstatus</strong>: Status of quote.</li><li><strong>QHuserid</strong>: User of quote.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">QuotationDetail</span></strong></p><p>Quotation line details.</p><ul><li><strong>QDid</strong>: Quote line ID.</li><li><strong>QBdesc</strong>: Item description.</li><li><strong>QDquantity</strong>: Quantity.</li><li><strong>QBprice</strong>: Sell price.</li><li><strong>QDcostprice</strong>: Cost.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Sales Orders (SOs)</span></strong></p><p id="isPasted">These are the commonly used tables to report on sales orders, and are often joined together.</p><p><br></p><p><strong><span style="font-size: 11pt;">OrderHead and OrderLine:</span></strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> orderhead </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> olid</span><span style="color: #778899;">=</span><span style="color: #000000;">ohid</span></div><p><br></p><p><strong><span style="font-size: 12pt;">OrderHead</span></strong></p><p>Sales order details.</p><ul><li><strong>OHid</strong>: SO ID.</li><li><strong>OHtitle</strong>: SO title.</li><li><strong>OHusername</strong>: User's name.</li><li><strong>OHsitenum</strong>: SIte ID.</li><li><strong>OHinvoicedate</strong>: Invoice date.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">OrderLine</span></strong></p><p>Sales order line details.</p><ul><li><strong>OLid</strong>: Line ID.</li><li><strong>OLsellingprice</strong>: Price.</li><li><strong>OLcostprice</strong>: Cost.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Purchase Orders (POs)</span></strong></p><p id="isPasted">These are the commonly used tables to report on suppliers and purchase orders, and are often joined together to give a full overview of a purchase order.</p><p><br></p><p><strong>SupplierOrderHeader and SupplierOrderHeader:</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> supplierorderdetail </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> SDSHid</span><span style="color: #778899;">=</span><span style="color: #000000;">Shid</span></div><p><br></p><p><strong><span style="font-size: 12pt;">Company</span></strong></p><p>Suppliers.</p><ul><li><strong>Cnum</strong>: Supplier ID.</li><li><strong>Cdesc</strong>: Supplier name.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">SupplierOrderHeader</span></strong></p><p>PO details.</p><ul><li><strong>SHid</strong>: PO ID.</li><li><strong>SHfaultid</strong>: Ticket ID the PO was raised from.</li><li><strong>SHporef</strong>: PO reference.</li><li><strong>SHusername</strong>: User's name.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">SupplierOrderDetail</span></strong></p><p>PO line details.</p><ul><li><strong>SDif</strong>: Line ID.</li><li><strong>SDshid</strong>: PO ID.</li><li><strong>SDdesc</strong>: Item description.</li><li><strong>SDquantity</strong>: Quantity.</li><li><strong>SDprice</strong>: Price.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Invoices</span></strong></p><p id="isPasted">These are the commonly used tables to report on invoices, and are often joined together.</p><p><br></p><p><strong>InvoiceHeader and InvoiceDetail:</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> invoicedetail </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> ihid</span><span style="color: #778899;">=</span><span style="color: #000000;">IdIHid</span></div><p><br></p><p><strong><span style="font-size: 12pt;">InvoiceHeader</span></strong></p><p>Invoice details.</p><ul><li><strong>IHid</strong>: Invoice ID.</li><li><strong>IHaarea</strong>: Customer ID.</li><li><strong>IH3rdpartyinvoicenumber</strong>: Integration invoice number.</li><li><strong>IHdatepaid</strong>: Date of payment.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">InvoiceDetail</span></strong></p><p>Invoice line details.</p><ul><li><strong>IDid</strong>: Line ID.</li><li><strong>IDihid</strong>: Invoice number.</li><li><strong>ID_itemid</strong>: Item ID.</li><li><strong>IDitem_shortdescription</strong>: Item short description.</li><li id="isPasted"><strong>IDitem_longdescription</strong>: Item long description.</li><li id="isPasted"><strong>IDunit_price</strong>: Price</li><li id="isPasted"><strong>IDnet_amount</strong>: Total excluding tax.</li><li id="isPasted"><strong>IDtax_amount</strong>: Total including tax.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">InvoicePayment</span></strong></p><p>Payment details.</p><ul><li><strong>IPid</strong>: Payment ID.</li><li><strong>IPihid</strong>: Invoice ID.</li><li><strong>IPamount</strong>: Amount paid.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Licences/Subscriptions</span></strong></p><p id="isPasted">These are the commonly used tables to report on licences and subscriptions. They are often joined together to give a full overview of licence and subscription updates and counts.</p><p><br></p><p id="isPasted"><strong>Licence and LicenceChange:</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> LicenceChange </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> lid</span><span style="color: #778899;">=</span><span style="color: #000000;">lclid</span></div><p><br></p><p><strong><span style="font-size: 12pt;">Licence</span></strong></p><p>Software licenses and subscriptions.</p><ul><li><strong>Lid</strong>: Licence ID.</li><li><strong>Ldesc</strong>: Licence name.</li><li><strong>Lcount</strong>: Licence count.</li><li><strong>Larea</strong>: Customer ID.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">LicenceChange</span></strong></p><p>Change log for licenses and subscriptions.</p><ul><li><strong>LCid</strong>: Change ID.</li><li><strong>LClid</strong>: Licence ID.</li><li><strong>LCwhen</strong>: Change date.</li><li><strong>LCwho</strong>: Who/what integration made the change.</li><li><strong>LColdvalue</strong>: Previous value.</li><li><strong>LCnewvalue</strong>: New value.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Knowledge Base</span></strong></p><p id="isPasted">These are the commonly used tables to report on the knowledge base. The first and third are often joined to show a list of articles and the FAQ list they are each contained in.</p><p><br></p><p id="isPasted"><strong>KBEntry and KBVotes:</strong></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;"><span style="color: #778899;">JOIN</span><span style="color: #000000;"> kbentry </span><span style="color: #0000ff;">on</span><span style="color: #000000;"> kbvkbid </span><span style="color: #778899;">=</span><span style="color: #000000;"> id</span></div><p><br></p><p><strong><span style="font-size: 12pt;">KBEntry</span></strong></p><p>Article details.</p><ul><li><strong>Abstract</strong>: Title of article.</li><li><strong>Description</strong>: Description of article.</li><li><strong>Resolution</strong>: Resolution of article.</li><li><strong>Datecreated</strong>: When the article was created.</li><li><strong>Whocreated</strong>: Who made the article.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">KBVotes</span></strong></p><p>Article ratings.</p><ul><li><strong>KBVkbid</strong>: Article ID.</li><li><strong>KBVvote</strong>: Rating.</li><li><strong>KBVdate</strong>: Date voted.</li><li><strong>KBVnegativecomment</strong>: Comment left if rated negative.</li></ul><p><br></p><p><strong><span style="font-size: 12pt;">FAQListHead</span></strong></p><p>FAQ list details.</p><ul><li><strong>FAQlistdesc</strong>: FAQ list name.</li></ul>
