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.
|
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) |
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 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 |
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 |