The FinJinni database is an open SQL database that can be accessed from any application which supports Microsoft SQL Server, not only Excel. Other applications such as Crystal Reports, MS Access, and MS Power BI can be used.
There are both data tables and views in the database, organized in a layered structure. It is advised to use the highest level of data views when possible, as these have been customized specifically for access from reporting applications. The lower-level tables are optimized primarily for loading of data, but can be used when needed.
The FinJinni database contains these types of tables and views:
- Base data tables, which contain the information loaded directly from QuickBooks. These generally use the QuickBooks entity and transaction names in plural, e.g. “Accounts: “Customers”, “Bills”. For transactions, there is both a transaction table and a line items table to contain the line item details. For example, “Bills” and “BillLineItems”.
- System “master” data tables, which combine information either added to the base data tables or combined from multiple tables. The most ubiquitous is “mGeneralLedger”, which is a combined and comprehensive general ledger created from the QuickBooks data tables.
- Data views, which both combine data from multiple tables for presentation purposes, provide a consistent naming convention, and in some cases provide appropriate filters for ease of selection. For example, there is a vGeneralLedgerBase view which provides the base view of the general ledger. The ‘vGeneralLedger” view is a refinement of this that presents accrual reporting transactions and the “vGeneralLedgerCash” view is a refinement that presents cash-basis reporting transactions.
Using “vGeneralLedgerBase” directly can result in duplicate entries, where there is one record for an accrual transaction and another version of the record for a cash-basis report. In most cases, using the refined views is more appropriate.
There are additional views for P&L and Balance Sheet. The difference between these and the GL views is that accounts are filtered as appropriate.
- Entity views, which provides views of QuickBooks entities in a format more readily joined to the data views. In some cases, there are two views for an entity, one named singular and one plural. The singular name is intended for most joins, the plural name is intended for separate queries. The only significant difference is in column-naming. For example, use the “vCustomer” view to join with “vGeneralLedger” in order to obtain additional customer fields. The “vCustomers” view is similar, but will have duplicate column names when joined.
When joining these views to the general ledger view, use the ID field and join it to “entity_ID” in the GL views.
There are also a number of system maintenance tables used to maintain system integrity and consistency, plus tables that provide logging of any issues that are encountered. These usually begin with a “z” or “m” and are not intended to be accessed directly.
A note about consolidating multiple companies
The company “nickname”, aka “extract name”, defined in FinJinni setup is contained in all data tables and views. This is used to partition the data in order to keep each company separate. For data tables, there is a “sourceid” field that contains this value. For views, the field is usually named “Company_ID” in transactions or “entity_COID” in the entity views.
Tables and Views
The complete list of tables and views follows:
Entity Tables
| Table Name | Description |
| Accounts | Accounts |
| Classes | Classes |
| CompanyInfo | Company Information, one row per company in the database. |
| CustomDataExts | Custom data extensions that are loaded from external files by FinJinni. |
| Customers | Customers |
| CustomerMsgs | Customer Messages |
| CustomerTypes | Customer Types |
| Departments | Departments or Locations. Used by QB Online only. |
| Employees | Employees |
| Entities | Combined entity table, used for general name lookup |
| EntityDataExts | Custom field values for entities, as defined in QuickBooks |
| HostInfo | Information describing the QB product, desktop only. |
| InventoryItems | Inventory items from Advanced Inventory |
| InventorySites | Inventory sites from Advanced Inventory |
| Items | Product and Service Items |
| PaymentMethods | Payment methods list |
| Preferences | Company preferences, one row per company in the database. |
| PriceLevels | Price Levels list |
| PriceLevelPerItem | Price Level detail by item |
| SalesReps | Sales reps. |
| SalesTaxAgencies | Tax agencies, used by QB Online only |
| SalesTaxCodes | Tax code list |
| SalesTaxRates | Tax rates, used by QB Online only |
| ShipMethods | Shipping methods |
| Terms | Payment terms list |
| Vendors | Vendors |
| VendorTypes | Vendor type list |
Transaction Tables
| Bills | QuickBooks transactions |
| BillLineItems | Transaction line items |
| BillLinkedTxns | Transaction links |
| BillPaymentChecks | QuickBooks transactions |
| BillPaymentCheckLineItems | Transaction line items |
| BillPaymentCheckAllocations | Cash-basis allocations calculated by FinJinni |
| BillPaymentCreditCards | QuickBooks transactions |
| BillPaymentCreditCardLineItems | Transaction line items |
| BillPaymentCreditAllocations | Cash-basis allocations calculated by FinJinni |
| BillPaymentOtherItems | Payments on bills other than checks or credit cards |
| BillPaymentOtherAllocations | Cash-basis allocations calculated by FinJinni |
| BillPayments | QuickBooks transactions, online only |
| BillPaymentLineItems | Transaction line items, online only |
| Budget | QuickBooks budget data, for budgets by account |
| BudgetByClass | QuickBooks budget data, for budgets by class |
| Checks | QuickBooks transactions |
| CheckLineItems | Transaction line items |
| CheckLinkedTxns | Transaction links |
| Cogs | QuickBooks cost-of-goods transaction postings |
| CreditCardCharges | QuickBooks transactions |
| CreditCardChargeLineItems | Transaction line items |
| CreditCardCredits | QuickBooks transactions |
| CreditCardCreditLineItems | Transaction line items |
| CreditMemos | QuickBooks transactions |
| CreditMemoLineItems | Transaction line items |
| CreditMemoLinkedTxns | Transaction links |
| CreditMemoOtherAllocations | Cash-basis allocations calculated by FinJinni |
| DailyBalances | Daily trial-balances from QuickBooks and calculated values from FinJinni |
| DailyBalancesCash | Daily trial-balances from QuickBooks and calculated values from FinJinni |
| Deposits | QuickBooks transactions |
| DepositLineItems | Transaction line items |
| Estimates | QuickBooks transactions |
| EstimateLineItems | Transaction line items |
| InventoryAdjustments | QuickBooks transactions |
| InventoryAdjustmentLineItems | Transaction line items |
| Invoices | QuickBooks transactions |
| InvoiceLineItems | Transaction line items |
| InvoicePaymentAllocations | Cash-basis allocations calculated by FinJinni |
| ItemReceipts | QuickBooks transactions |
| ItemReceiptLineItems | Transaction line items |
| JournalEntries | QuickBooks transactions |
| JournalEntryLineItems | Transaction line items |
| Payroll | QuickBooks payroll transaction postings |
| PayrollExpense | QuickBooks payroll expense transaction postings by customer |
| Purchases | QuickBooks purchase/expense transactions, online only |
| PurchaseLineItems | Transaction line items, online only |
| PurchaseOrders | QuickBooks transactions |
| PurchaseOrderLineItems | Transaction line items |
| PurchaseOrderLinkedTxns | Transaction links |
| ReceivedPayments | QuickBooks transactions |
| ReceivedPaymentLineItems | Transaction line items |
| ReceivedPaymentAllocations | Cash-basis allocations calculated by FinJinni |
| RefundReceipts | QuickBooks transactions |
| RefundReceiptLineItems | Transaction line items |
| SalesOrders | QuickBooks transactions |
| SalesOrderLineItems | Transaction line items |
| SalesReceipts | QuickBooks transactions |
| SalesReceiptLineItems | Transaction line items |
| SalesTaxPayments | QuickBooks transactions |
| TimeTracking | Time activity |
| Transactions | General transaction listing |
| TransactionsQBO | QuickBooks online transaction listing |
| TransactionsQBOCash | QuickBooks online transaction listing, cash basis |
| TransDataExts | Transaction data extensions |
| Transfers | QuickBooks transactions |
| TransfersQBO | QuickBooks online transactions |
| VendorCredits | QuickBooks transactions |
| VendorCreditLineItems | Transaction line items |
| VendorCreditLinkedTxns | Transaction links |
| VendorCreditOtherItems | Vendor credit related transactions used for calculated cash allocations |
| VendorCreditOtherAllocations | Cash-basis allocations calculated by FinJinni |
FinJinni Master Tables
| mGeneralLedger | Contains the comprehensive general ledger. All posting transactions and their line items are recorded in this table. |
| mPurchaseOrders | Contains a ledger for purchase orders (non-posting) |
| mSalesOrders | Contains a ledger for estimates and sales orders (non-posting) |
| mAccounts | Contains the chart of accounts organized as a hierarchy, used for selection and special reporting purposes. The data in this table is displayed under the Query Builder filters, for example. |
| mClasses | Class list organized as a hierarchy. |
| mCompanies | Company list organized as a hierarchy. |
| mCustomers | Customer list organized as a hierarchy. |
| mDepartments | Department list organized as a hierarchy. |
| mEmployees | Employee list organized as a hierarchy. |
| mItems | Item list organized as a hierarchy. |
| mSalesReps | Sales Rep. list organized as a hierarchy. |
| mVendors | Vendor list organized as a hierarchy. |
FinJinni Data Views
| vGeneralLedgerBase | Base view for comprehensive General Ledger. Caution: Contains both accrual and cash versions of transactions. |
| vGeneralLedger | General Ledger for accrual reporting |
| vGeneralLedgerCash | General Ledger for cash reporting |
| vGeneralLedgerFull | General Ledger with custom, job, item and vendor fields included. Note: Using this can be convenient but individual joins to vGeneralLedger are more efficient. |
| vTransInfo | Additional transaction information that can be joined to the GL views. Contains fields such as memo, address, payment method, and balance remaining that are not contained in the GL. |
| vBalanceSheetBase | Base view for Balance Sheet reporting. Restricts the GL view to balance-sheet accounts. |
| vBalanceSheet | Accrual-basis view for Balance Sheet reporting. |
| vBalanceSheetCash | Cash-basis view for Balance Sheet reporting. |
| vBalSheetNetIncomeBase | Used to include net income on the balance sheet. |
| vBalSheetNetIncome | Used to include net income on the balance sheet. |
| vBalSheetNetIncomeCash | Used to include net income on the balance sheet. |
| vPLByAccountBase | Base view for P&L reporting. Restricts the GL view to P&L accounts. |
| vPLByAccount | Accrual-basis view for P&L reporting. |
| vPLByAccountCash | Cash-basis view for P&L reporting. |
| vARAgeBy30 | Used for AR aging reports |
| vBalSheetNetIncomeEx | A modified version of the net income view used for trial-balance activity reports. |
| vBalSheetRetEarnEx | A modified version of the balance sheet view used for trial-balance activity reports. |
| vBudget | View of the budget table |
| vBudgetClass | View of the budget-by-class table |
| vPayRoll | View of the payroll data table used for payroll/expense reporting |
| vSales | View of the GL specific to sales transactions and includes additional data from those transactions. |
| vSalesOrders | View of the Sales-Order Ledger used for estimates and non-posting sales-orders. |
| zSalesInfo | Additional sales transaction information, included in vSales. |
| zSalesOrderInfo | Additional sales-order transaction information, included in vSalesOrders. |
| vPurchases | View of the GL specific to purchase transactions and includes additional data from those transactions. |
| vPurchaseOrders | View of the Purchase-Order Ledger used for non-posting purchase-orders. |
| zPurchaseInfo | Additional purchase transaction information, included in vPurchases. |
| zPurchaseOrderInfo | Additional purchase-order transaction information, included in vPurchaseOrders. |
| vCustomFieldCustJob | Custom fields for jobs |
| vCustomFieldCustomer | Custom fields for customers |
| vCustomFieldEmployee | Custom fields for employees |
| vCustomFieldItem | Custom fields for items |
| vCustomFieldSalesRep | Custom fields for sales reps |
| vCustomFieldTrans | Custom fields for transactions |
| vCustomFieldVendor | Custom fields for vendors |
| vAccounts | View of accounts, general-purpose |
| vClasses | View of classes, general-purpose |
| vCustomer | View of customers, used for joins |
| vCustomers | View of customers, general-purpose |
| vEmployee | View of employees, used for joins |
| vEmployees | View of employees, general-purpose |
| vCustJob | View of jobs, used for joins |
| vItem | View of items, used for joins |
| vItems | View of items, general-purpose |
| vLocation | View of departments/locations, used for joins |
| vSalesRep | View of sales reps, used for joins |
| vSalesReps | View of sales reps, general-purpose |
| vVendor | View of vendors, used for joins |
| vVendors | View of vendors, general-purpose |