Table: dbo.Client
Columns
NameTypeConstraintsDescription
client_KEYint (4)NOT NULLDatabase assigned primary key for this table
client_idnvarchar (22)NOT NULLUser defined identifier (unique)
descriptionnvarchar (100)NOT NULLClient Name. This is the primary display name for the client.
client_class_KEYint (4)NOT NULLKey to a Client Class record.
name_1nvarchar (100)NOT NULLClient Payroll Name. This name is mainly used for Payroll Compliance forms.
name_2nvarchar (100)NOT NULLClient DBA Name. This is the Doing Busiess As name for the client.
federal_tinnvarchar (22)NOT NULLThis field will hold the Fed Tax ID if the client is a non-individual, it will hold the taxpayer SSN for individual clients.
individual_first_namenvarchar (28)NOT NULLFirst name of the client if the client is an individual.
individual_last_namenvarchar (40)NOT NULLLast name of the client if the client is an individual.
spouse_first_namenvarchar (28)NOT NULLFirst name of the clients spouse if the client type is an individual.
spouse_last_namenvarchar (40)NOT NULLLast name of the clients spouse if the client type is an individual.
record_status_KEYint (4)NOT NULLKey to a Client Status record.
industry_codenvarchar (12)NOT NULLIndustry code.
fiscal_year_end_monthint (4)NOT NULLEnd month of the client's fiscal year end.
date_openedsmalldatetime (4)Date the client came to your firm, default system date when a new client is added.
date_leftsmalldatetime (4)Date your firm terminated the client/firm relationship.
contact_KEYint (4)NOT NULLKey to the primary Contact record for this Client.
client_id_sortablevarchar (48)NOT NULLA representation of the client_id column that sorts in natural order, meaning that digits are sorted as numbers while non-digits are sorted alphabetically. A trigger keeps this field synchronized with the content of client_id.
client_inactive_reason_KEYint (4)Reference to Client_Inactive_Reason.
inactive_explanationnvarchar (2000)NOT NULLExtra information regarding why the client is no longer active.
client_type_KEYint (4)NOT NULLForeign key to the type of client
data_origin_KEYint (4)NOT NULLThe origin of each client added to the database. For example, Data Entry or CSA Conversion.
import_source_KEYint (4)NOT NULLThe source of imported data: Examples are Client Bookkeeping Solution and QuickBooks. Can also be set to None.
allow_all_firm_staff_accessbit (1)NOT NULLTrue if all firm staff are allowed to access the client.
csa_client_had_tbs_or_gl_module_markedbit (1)NOT NULLTrue if the CSA client had either the TBS module or the GL module marked at the time the conversion to ACS took place. False if the client was not converted from CSA or if neither module was marked.
send_w2s_to_ultra_taxbit (1)NOT NULLOption to send W-2s to UltraTax through Pod REST service for staging by WebServices
client_versiontimestamp (8)NOT NULLThe version of this row. It's an aid in versioning the row.
client_force_new_version_togglebit (1)NOT NULLA bit to toggle that causes the client_version to update. Flip this bit when a subordinate tables are changed.
is_checked_outbit (1)NOT NULLFlag that will indicate if this client was checked out for in-field Workpaper audits.
is_undergoing_restorebit (1)NOT NULLThis will be 1 during a CDMU Restore, for the client being restored from the backup file. It should never be observable as 1 from outside the CDMU transaction, always visible as 0. Do not confuse with a client_type_KEY of 6 BeingRestored, which indicates that the client is the previously-existing copy of a client being restored over, and which will be discarded on successful completion of the CDMU Restore.
Primary key
NameColumnsDescription
PK_Clientclient_KEY
Unique constraints
NameColumnsDescription
UK_Client$client_idclient_id
UK_Client$client_id_sortableclient_id_sortable
UK_Client$contact_KEYcontact_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Client__Client_Class__client_class_KEYclient_class_KEYClient_Class (client_class_KEY)
FK_Client__Client_Inactive_Reason__client_inactive_reason_KEYclient_inactive_reason_KEYClient_Inactive_Reason (client_inactive_reason_KEY)
FK_Client__Client_Type__client_type_KEYclient_type_KEYClient_Type (client_type_KEY)
FK_Client__Contact__contact_KEYcontact_KEYContact (contact_KEY)
FK_Client__Data_Origin__data_origin_KEYdata_origin_KEYData_Origin (data_origin_KEY)
FK_Client__Import_Source__import_source_KEYimport_source_KEYImport_Source (import_source_KEY)
FK_Client__Record_Status__record_status_KEYrecord_status_KEYRecord_Status (record_status_KEY)
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_GL_Account_Grouping__Client__client_KEYclient_KEYGL_Account_Grouping
FK_Shortfall_Tax_Treatment_Selection__Client__client_KEYclient_KEYShortfall_Tax_Treatment_Selection
FK_Elf_File__Client__client_KEYclient_KEYElf_File
FK_Form_1094c_Aggregate_Group_Member__Client__client_KEYclient_KEYForm_1094c_Aggregate_Group_Member
FK_Report_Profile__Client__client_KEYclient_KEYReport_Profile
FK_Quick_Books_Export_Option__Client__client_KEYclient_KEYQuick_Books_Export_Option
FK_Tax_Code__Client__client_KEYclient_KEYTax_Code
FK_Client_Contact_Payroll_Administrator__Client__client_KEYclient_KEYClient_Contact_Payroll_Administrator
FK_Tax_System_Bridged_Federal_Carry_Over__Client__client_KEYclient_KEYTax_System_Bridged_Federal_Carry_Over
FK_Form_Processing_Run__Client__client_KEYclient_KEYForm_Processing_Run
FK_Client_1099_Tax_Form_Information__Client__client_KEYclient_KEYClient_1099_Tax_Form_InformationFOREIGN KEY constraint to Client.
FK_Client_Quick_Books_Online_Export_Option__Client__client_KEYclient_KEYQuick_Books_Online_Export_Option
FK_Tax_Form_Filing__Client__client_KEYclient_KEYTax_Form_FilingFOREIGN KEY constraint of client_KEY for the Tax_Form_Filing table
FK_Client_N_Client_Service_Type__Client__client_KEYclient_KEYClient_N_Client_Service_Type
FK_Client_Remote_Payroll_Setup__Client__client_KEYclient_KEYClient_Remote_Payroll_Setup
FK_Client_Bank_Reconciliation_Option__Client__client_KEYclient_KEYClient_Bank_Reconciliation_Option
FK_Quick_Books_Payee__Client__client_KEYclient_KEYQuick_Books_Payee
FK_XactPay_Client_EIN_History__Client__client_KEYclient_KEYXactPay_Client_EIN_History
FK_Bank_Feeds_Assignment_Rule__Client__client_KEYclient_KEYBank_Feeds_Assignment_Rule
FK_Ledger_Entry__Client__client_KEYclient_KEYLedger_Entry
FK_OnBalance_Import_Option__Client__client_KEYclient_KEYOnBalance_Import_Option
FK_Client_Shortcut__Client__client_KEYclient_KEYClient_Shortcut
FK_Tax_Form_Override__Client__client_KEYclient_KEYTax_Form_Override
FK_Invoice_Printing_Default_Option__Client__client_KEYclient_KEYInvoice_Printing_Default_Option
FK_Notification_Queue__Client__client_KEYclient_KEYNotification_Queue
FK_Workforce_Hub_Pending_Acknowledgement_Queue__Client__client_KEYclient_KEYWorkforce_Hub_Pending_Acknowledgement_Queue
FK_Client_Benefit__Client__client_KEYclient_KEYClient_Benefit
FK_Spreadsheet_Import_Taxing_Authority__Client__client_KEYclient_KEYSpreadsheet_Import_Taxing_Authority
FK_Numeric_GL_Account_Number_Metrics__Client__client_KEYclient_KEYNumeric_GL_Account_Number_Metrics
FK_Processed_Tax_Form_Liability__Client__client_KEYclient_KEYProcessed_Tax_Form_Liability
FK_Processed_Tax_Form__Client__client_KEYclient_KEYProcessed_Tax_Form
FK_Quick_Books_Payroll_Item__Client__client_KEYclient_KEYQuick_Books_Payroll_Item
FK_Client_Contact_Payroll_Tax_Information__Client__client_KEYclient_KEYClient_Contact_Payroll_Tax_Information
FK_Client_W2_Rendered_Document__Client__client_KEYclient_KEYClient_W2_Rendered_Document
FK_Trial_Balance_Grid__Client__client_KEYclient_KEYTrial_Balance_Grid
FK_GL_Account_Budget_Category_Name_Override__Client__client_KEYclient_KEYGL_Account_Budget_Category_Name_Override
FK_Client_1095__Client__client_KEYclient_KEYClient_1095
FK_Client_W2_Override__Client__client_KEYclient_KEYClient_W2_Override
FK_Holiday_Definition__Client__client_KEYclient_KEYHoliday_Definition
FK_Recorded_Event_Object_N_Client__Client__client_KEYclient_KEYRecorded_Event_Object_N_Client
FK_Payroll_Department__Client__client_KEYclient_KEYPayroll_Department
FK_Client_W2_State_Override__Client__client_KEYclient_KEYClient_W2_State_Override
FK_Quick_Books_Online_Integration_Client__Client__client_KEYclient_KEYQuick_Books_Online_Integration_Client
FK_GL_Fiscal_Year__Client__client_KEYclient_KEYGL_Fiscal_Year
FK_Quick_Books_Online_Import_Option__Client__client_KEYclient_KEYQuick_Books_Online_Import_Option
FK_Quick_Books_Transaction__Client__client_KEYclient_KEYQuick_Books_Transaction
FK_GL_Account_Segment__Client__client_KEYclient_KEYGL_Account_Segment
FK_Client_1099_State_Override__Client__client_KEYclient_KEYClient_1099_State_Override
FK_Positive_Pay_File__Client__client_KEYclient_KEYPositive_Pay_File
FK_Quick_Books_Import_Option__Client__client_KEYclient_KEYQuick_Books_Import_Option
FK_Enter_AR_Deposit_Option__Client__client_KEYclient_KEYEnter_AR_Deposit_Option
FK_Client_1099_Override__Client__client_KEYclient_KEYClient_1099_Override
FK_Client_1095_Override__Client__client_KEYclient_KEYClient_1095_Override
FK_Journal__Client__client_KEYclient_KEYJournal
FK_Spreadsheet_Import_Bank_Routing_Number__Client__client_KEYclient_KEYSpreadsheet_Import_Bank_Routing_Number
FK_Quick_Books_Retrieved_Transaction__Client__client_KEYclient_KEYQuick_Books_Retrieved_Transaction
FK_Spreadsheet_Import_Payment_Term__Client__client_KEYclient_KEYSpreadsheet_Import_Payment_Term
FK_Client_File_Exchange_Portal_Ems_User__Client__client_KEYclient_KEYClient_File_Exchange_Portal_Ems_User
FK_Print_Filing_Instruction__Client__client_KEYclient_KEYPrint_Filing_Instruction
FK_Client_N_Report_Folder__Client__client_KEYclient_KEYClient_N_Report_Folder
FK_XactPay_Consent__Client__client_KEYclient_KEYXactPay_Consent
FK_XactPay_Payroll_Transmission__Client__client_KEYclient_KEYXactPay_Payroll_Transmission
FK_Spreadsheet_Import_1099_Box_Combination__Client__client_KEYclient_KEYSpreadsheet_Import_1099_Box_Combination
FK_Employee_Tip_Total__Client__client_KEYclient_KEYEmployee_Tip_Total
FK_Receivable_Transaction__Client__customer__client_KEYcustomer__client_KEYReceivable_Transaction
FK_XactPay_Setup_Transmission__Client__client_KEYclient_KEYXactPay_Setup_Transmission
FK_Quick_Books_Retrieved_Account__Client__client_KEYclient_KEYQuick_Books_Retrieved_Account
FK_Client_Synchronization__Client__client_KEYclient_KEYClient_Synchronization
FK_Location__Client__client_KEYclient_KEYLocation
FK_Insurepay_Client__Client__client_KEYclient_KEYInsurepay_Client
FK_Balance_Definition__Client__client_KEYclient_KEYBalance_Definition
FK_Tax_Jurisdiction__Client__client_KEYclient_KEYTax_Jurisdiction
FK_Export_Journal_Entry_To_Csa_Information__Client__client_KEYclient_KEYExport_Journal_Entry_To_Csa_Information
FK_Client_Onvio_Payroll_Integration_Status__Client__client_KEYclient_KEYClient_Onvio_Payroll_Integration_Status
FK_Client_N_Subsidiary_Client__Client__consolidating__client_KEYconsolidating__client_KEYClient_N_Subsidiary_Client
FK_Client_N_Subsidiary_Client__Client__subsidiary__client_KEYsubsidiary__client_KEYClient_N_Subsidiary_Client
FK_Client_W2_Rendering_Queue__Client__client_KEYclient_KEYClient_W2_Rendering_Queue
FK_Quick_Books_Retrieved_Vendor__Client__client_KEYclient_KEYQuick_Books_Retrieved_Vendor
FK_Trial_Balance_Split_Screen_Grid_Option__Client__client_KEYclient_KEYTrial_Balance_Split_Screen_Grid_Option
FK_Client_Primary_Location__Client__client_KEYclient_KEYClient_Primary_Location
FK_Bank_Feeds_Transaction_Option__Client__client_KEYclient_KEYBank_Feeds_Transaction_OptionThe client key corresponding to which the bank feeds transaction options are set. It is forign-keyed to the Client
FK_Print_Job__Client__client_KEYclient_KEYPrint_Job
FK_Client_N_Staff__Client__client_KEYclient_KEYClient_N_Staff
FK_Staff_Default_Report_Option__Client__client_KEYclient_KEYStaff_Default_Report_Option
FK_Apply_Customer_Payment_Option__Client__client_KEYclient_KEYApply_Customer_Payment_Option
FK_Import_Template__Client__client_KEYclient_KEYImport_Template
FK_Custom_Value_Client__Client__client_KEYclient_KEYCustom_Value_Client
FK_Client_Access_Notification__Client__client_KEYclient_KEYClient_Access_Notification
FK_Payroll_Journal_Entry_Option__Client__client_KEYclient_KEYPayroll_Journal_Entry_Option
FK_Complete_Forms_Output_Session_Client_Detail__Client__client_KEYclient_KEYComplete_Forms_Output_Session_Client_Detail
FK_Timerack_Client_Update_Queue__Client__client_KEYclient_KEYTimerack_Client_Update_Queue
FK_Engagement_Binder__Client__client_KEYclient_KEYEngagement_Binder
FK_AP_Enter_Transaction_Option__Client__client_KEYclient_KEYAP_Enter_Transaction_Option
FK_Web_Services_Employee_Deletion_Queue__Client__client_KEYclient_KEYWeb_Services_Employee_Deletion_Queue
FK_W2_Rendering_Client_Lock__Client__client_KEYclient_KEYW2_Rendering_Client_Lock
FK_Quick_Books_Class__Client__client_KEYclient_KEYQuick_Books_Class
FK_Magnetic_Media_File_Client__Client__client_KEYclient_KEYMagnetic_Media_File_Client
FK_GL_Period_Key_Old_To_New_Map__Client__client_KEYclient_KEYGL_Period_Key_Old_To_New_Map
FK_User_Client_Preference_Grid_Column_Screen_Option__Client__client_KEYclient_KEYUser_Client_Preference_Grid_Column_Screen_Option
FK_Print_1095__Client__client_KEYclient_KEYPrint_1095
FK_Transaction_Template__Client__client_KEYclient_KEYTransaction_Template
FK_Onvio_Payroll_Integration_Queue__Client__client_KEYclient_KEYOnvio_Payroll_Integration_Queue
FK_Manage_AP_Payment_User_Preference__Client__client_KEYclient_KEYManage_AP_Payment_User_Preference
FK_Manage_AP_Payment_Grid_User_Option__Client__client_KEYclient_KEYManage_AP_Payment_Grid_User_Option
FK_Complete_Payroll_Output_Session_Client_Detail__Client__client_KEYclient_KEYComplete_Payroll_Output_Session_Client_Detail
FK_Client_Consolidation_Option__Client__client_KEYclient_KEYClient_Consolidation_Option
FK_Tax_Form_Liability_Processing_Lock__Client__client_KEYclient_KEYTax_Form_Liability_Processing_Lock
FK_Csa_Account_Group_Map_To_Classification_Code__Client__client_KEYclient_KEYCsa_Account_Group_Map_To_Classification_Code
FK_Print_1096_Selection__Client__client_KEYclient_KEYPrint_1096_Selection
FK_Quick_Books_Account__Client__client_KEYclient_KEYQuick_Books_Account
FK_Print_W3_Selection__Client__client_KEYclient_KEYPrint_W3_Selection
FK_Journal_Entry__Client__client_KEYclient_KEYJournal_Entry
FK_Engagement_Binder_Tree_Item__Client__client_KEYclient_KEYEngagement_Binder_Tree_Item
FK_GL_Account__Client__client_KEYclient_KEYGL_Account
FK_Spreadsheet_Import_Account__Client__client_KEYclient_KEYSpreadsheet_Import_Account
FK_Client_AP_Setup__Client__client_KEYclient_KEYClient_AP_Setup
FK_Client_Undistributed_GL_Account__Client__client_KEYclient_KEYClient_Undistributed_GL_Account
FK_AR_Enter_Invoice_Option__Client__client_KEYclient_KEYAR_Enter_Invoice_Option
FK_Client_GL_Setup__Client__client_KEYclient_KEYClient_GL_Setup
FK_Checkbook__Client__client_KEYclient_KEYCheckbook
FK_Client_AR_Setup__Client__client_KEYclient_KEYClient_AR_Setup
FK_AR_Item__Client__client_KEYclient_KEYAR_Item
FK_Finance_Charge_Definition__Client__client_KEYclient_KEYFinance_Charge_Definition
FK_Client_Billing__Client__bill_to_other_client__client_KEYbill_to_other_client__client_KEYClient_Billing
FK_Client_Billing__Client__client_KEYclient_KEYClient_Billing
FK_Processing_Note__Client__client_KEYclient_KEYProcessing_Note
FK_Client_Billing_Sales_Tax__Client__client_KEYclient_KEYClient_Billing_Sales_Tax
FK_AP_Enter_Transaction_List_View_Column_Option__Client__client_KEYclient_KEYAP_Enter_Transaction_List_View_Column_Option
FK_OnBalance_GL_Account__Client__client_KEYclient_KEYOnBalance_GL_Account
FK_Netclient_File_Log__Client__client_KEYclient_KEYNetclient_File_Log
FK_Client_Billing_Item__Client__client_KEYclient_KEYClient_Billing_Item
FK_OnBalance_Bank_Account__Client__client_KEYclient_KEYOnBalance_Bank_Account
FK_Client_Payroll_Information__Client__client_KEYclient_KEYClient_Payroll_Information
FK_Client_Pay_Schedule__Client__client_KEYclient_KEYClient_Pay_Schedule
FK_Manage_AP_Payment_Option__Client__client_KEYclient_KEYManage_AP_Payment_Option
FK_Spreadsheet_Import_Finance_Charge_Definition__Client__client_KEYclient_KEYSpreadsheet_Import_Finance_Charge_Definition
FK_Spreadsheet_Import_AR_Item__Client__client_KEYclient_KEYSpreadsheet_Import_AR_Item
FK_ATF_Payroll_Check_Entry_Option__Client__client_KEYclient_KEYATF_Payroll_Check_Entry_Option
FK_Customer__Client__client_KEYclient_KEYCustomer
FK_Enter_Transaction_User_Preference__Client__client_KEYclient_KEYEnter_Transaction_User_Preference
FK_AR_Customer_Payment_Option__Client__client_KEYclient_KEYAR_Customer_Payment_Option
FK_Vendor__Client__client_KEYclient_KEYVendor
FK_Spreadsheet_Import_Bank_Account__Client__client_KEYclient_KEYSpreadsheet_Import_Bank_Account
FK_Spreadsheet_Import_Vendor__Client__client_KEYclient_KEYSpreadsheet_Import_Vendor
FK_Initial_Open_Item_Bank_Transaction__Client__related__client_KEYrelated__client_KEYInitial_Open_Item_Bank_Transaction
FK_Client_Payroll_Item__Client__client_KEYclient_KEYClient_Payroll_Item
FK_Worker_Compensation__Client__client_KEYclient_KEYWorker_Compensation
FK_Spreadsheet_Import_Customer__Client__client_KEYclient_KEYSpreadsheet_Import_Customer
FK_Bank_Reconciliation_Rapid_Clear_Unmatched_Transaction__Client__impound__client_KEYimpound__client_KEYBank_Reconciliation_Rapid_Clear_Unmatched_Transaction
FK_Employee__Client__client_KEYclient_KEYEmployee
FK_Ach_Entry_Detail_Record__Client__client_KEYclient_KEYAch_Entry_Detail_Record
FK_Billable_Item__Client__client_KEYclient_KEYBillable_Item
FK_Print_W2_Selection__Client__client_KEYclient_KEYPrint_W2_Selection
FK_ATF_Payroll_Check_Entry_Option_Pay_And_Tax_Item_Definition__Client__client_KEYclient_KEYATF_Payroll_Check_Entry_Option_Pay_And_Tax_Item_Definition
FK_Payroll_Check_Payroll_Item_Import__Client__client_KEYclient_KEYPayroll_Check_Payroll_Item_Import
FK_Payroll_Check_Tax_Item_Import__Client__client_KEYclient_KEYPayroll_Check_Tax_Item_Import
FK_Print_1099_Selection__Client__client_KEYclient_KEYPrint_1099_Selection

Schema diagram