Tables and Columns

Usage:

With the introduction of Advanced Queries, it has become necessary to provide a more robust Export feature. This option provides the ability to create a template for many data sources and the desired columns to export. The data sources expand to many more tables to pull different types of data from. Here is a list with all tables and Columns

Navigation: Tools > Advanced Query or

Click on Advanced Query Builder icon in Main menu

Table:

Table	Column	Column Name
Calls	Calls	Alert AlertTime CallID CallNumber CaseID CaseNumber CompleteDate CompletedBy CreateDate Creator CustomerID Date Deleted DeleteDate DeletedBy Department Description EmailID Flagged IsCompleted Status Type UserID
	CallNotes	CallID Date Note UserID
	Cases (The following Column Names will appear in different tables. We will list them only here and will make a link to the list) Cases - excludes Adjustments, memos, and Finance Charges.	Age APISource AppliedPayment AppliedWriteOff Carrier CarrierServiceType CaseID CaseNumber Catalog CreateDate CreatedBy CreditDebitReason Currency CurrencyRate CustomerID DateIn DeliveryID DeliveryZone DigitalScanner Discount DiscountRate DiscountReason DoctorName DueDate HoldDate HoldReason HoldStatus InDispute InvoiceDate InvoiceDateTryIn InvoicedBy IsAdjustment IsDebitMemo IsFC IsOnCOD LabName LastLocation LastPaymentDate LocalDelivery MedicaidAuth MedicaidID ModifiedBy ModifyDate Mold MultiplePans NoBundle NoShippingCharge NPINumber NumberOfPieces PanNumber PatientApptDate PatientFirst PatientFullName PatientLast PONumber PracticeDoctorID PreselectedTeam ProductionLab ReceivedBy Recurring ReferenceID Remake RemakeCaseID RemakeDiscount RemakeDiscountRate RemakeReason RemoteCaseNumber Route Rush RXNumber SalesDiscount SalesPerson Sex Shade ShipDate ShipmentDate ShippingCharge ShippingLab StatementPeriod Status SubmissionDate SubmittedBy Surcharge TeethFDI TeethUSA TotalCharge TotalTax TrackingNumber TryInDate TryInReason Type UnAppliedCredit UnpaidAmount VolumeDiscount Warranty WarrantyExpDate WasPrepaid WebAccepted Weight
	Customers (The following Column Names will appear in different tables. We will list them only here and will make a link to the list)	AccountID AccountNumber Active Address1 Address2 AGDNumber AlertDaily ApplyFC ApplyFCGlobal AutoPay AutoPayMonthEnd BillAddress1 BillAddress2 BillCity BillContact BillCountry BillingAccount BillPhone BillState BillZipCode Cell ChargeMDET City Class CODAmount CollectionDate ContactMethod Country County CreateDate CreatedBy CreditHoldDate CreditLimit CSRAdvisor Currency CurrentBalance CustomerFullName CustomerID DateOfCOD DateOfFirstCase DateOfLastCase Dear DentalGroup DoNotAllowCaseEntry Email EmailInvoicesDaily ExcludeARReports Fax FCPercent FirstName GraduateDate GraduateSchool InCollection Intercompany IsOnCOD LabCustomer LabName LastName LastPaymentAmount LastPaymentDate LicenseExpDate LicenseNumber Modifiedby ModifyDate NeverOnCOD NoCollectionLetter NoMailer NoStatement NPINumber OfficeHours OfficePhone OnCreditHold OtherPhone PastDue120 PastDue150 PastDue180 PastDue30 PastDue60 PastDue90 PastDueOver90 PerformanceOverAll PracticeName Prefix PrePay Prospect ReceivableMethod ReferredBy ResaleNumber SalesCommission SalesPerson Score SendEmailCCPayment SendMethod SetCasesToRush Source SourceName Specialty StarterKitDate State StatementClass TechnicalAdvisor Territory ThisPeriodCharges ThisPeriodCredits ThisPeriodPayments ThisPeriodWriteOffs Title ToothChartType TotalBalance TotalDueNow TotalPastDue Type UnAppliedCredits UnAppliedPayments VDEnabled VDEndDate VDID VDStartDate WebAccessDisabled WebLastAccess WebURL WebUsername ZipCode
CaseAuditTrail	CaseAuditTrail	CaseID CreateDate CreatedBy Description ID Type
CaseAuditTrail	Cases	Please see here the full list of Cases Columns
CaseProducts	CaseProducts	AdditionalDiscount AdditionalDiscountRate AdditionalDiscountReason CaseID CreateDate CreatedBy Discount DiscountRate DiscountReason IsBridge LabName LotNumber MetalFinish Notes PanNumber ProductID ProductionLab ProductionMethod Prosthesis Quantity Remake RemakeDiscount RemakeDiscountRate RemakeReason SalesDiscount Shade Surcharge SurchargeReason TaskLotNumbers Taxable TaxCategory TaxDepartment TaxGroup TaxType TeethNumbers TeethNumbers2 TotalCharge Translucency UnitPrice Warranty WarrantyExpDate
	Cases	Please see here the full list of Cases Columns
	CaseTasks	AdHoc AssignedTo AssignmentSource AssignToTeam CaseID CompleteDate CompletedBy CreateDate CreatedBy Department DueDate Duration FlatRate IsPerformedByEquipment LotNumber LSDueDate LSDurationMinutes LSLockAssignment LSScheduledDate ModifiedBy ModifyDate OnlyAssignTaskOnDueDate PayRate ProductionLab QCDate QCStatus QCTask Quantity StartNextDay Task UnitValue VitalToInvoice VitalToScan
	CaseTasksHistory	CaseID CaseProductID CompleteDate CompletedBy CreateDate CreatedBy FlatRate PayRate ProductionLab QCEmployeeID QCRating QCTask Quantity Rejected RejectReason Task UnitValue
	Products	AllowChangeDesc AllowChangePrice AllowCommission AllowDiscount AllowRemakeDiscount AllowVD AllowWarranty ApplyAllTaxes ApplyExciseTax Category DDXProcedureCode Department Description Discontinued DontAllowInvIfNoQty GLAccount GLAccountAsset GLAccountCOGS Group IsTool LXPEdit LXPTrack MaxQty Metal MfgPartNumber MinQty ModifiedBy ModifyDate PrefVendor PrintNotesOnInvoice PrintNotesOnWO ProductID ProductionLab Prosthesis PurchaseCost QBInventorySite QBType ReorderPoint RequiredDays RushFee RXCategory ShippingProduct StudyModel SubGroup Taxable TaxCategory TaxDepartment TaxGroup TaxType Type UnitPrice UnitValue WebDescription WebDisplay
Cases
CatalogProducts	CatalogProducts - excludes inactive products.
CMCampaignCustomers
CMCampaignProducts
CMCampaigns
CustomerCreditCards
CustomerPreferences
Customers
DailySales
Departments
Discounts
Employees
LabCustomerPrices
LabCustomerSettings
PracticeDoctors
Preferences
PreferenceValues
ProductionTasks
Products
ProductTasks
Reasons
Remakes
Routes
SalesData
SalesSummary
SalesYearMonth
TechnicianTasks
ToolLoanProducts