Codat 在该页面上
Codat is a data sharing and AI insights platform that helps financial institutions build deeper connections with their business customers.
Codat integration replicates data using the Codat API .
See the
Schema section for a list of objects available for replication.
特性快照 对这个连接器进行高级查看，包括有用的链接和支持的特性。
特性/功能 支持详情 支持的 Qlik Talend Data Integration 项目 仅 Replication 项目 。不支持数据管道项目。 目标更新方法
复制任务：
在数据湖任务中登陆数据：
管理元数据
无需手动生成元数据。 模式演变
只支持更改列数据类型 操作。 复制 LOB 列（NCLOB、CLOB 和 BLOB） 不支持 已计划 CDC 必填字段。这就是目标与源的更改保持同步的方式。
有关复制任务，请参阅计划任务
有关湖登陆任务，请参阅为湖登陆任务调度 CDC
通知 部分支持
设置操作更改通知
监视 仅 CDC，因为满负载与此连接器无关。
监视单个数据任务
JSON 列有效载荷的自动解密 不支持不会自动在目标上删除源数据集中的 JSON 列有效负载。
Preparing for authentication To access your data, you need to authenticate the connection with your account credentials.
信息注释 请确保您使用的帐户对要获取的表具有读取权限。
Creating the connection 有关更多信息，请参阅连接到 SaaS 应用程序
填写所需的连接属性。 在连接名称 中提供连接的名称。
选择打开连接元数据 以定义创建连接时的元数据。
单击创建 。
Connection settings Setting Description Data gateway
如果您的用例需要，请选择一个 数据移动网关 。
信息注释 此字段不适用于 Qlik Talend Cloud Starter 订阅，因为它不支持 数据移动网关 。如果您有另一个订阅层并且不想使用 数据移动网关 ，请选择无 。
有关 数据移动网关 的优势和需要它的用例的信息，请参阅Qlik 数据网关 - 数据移动
Start Date 按格式 MM/DD/YYYY 输入必须将数据从源复制到目标的日期。
API Key API key. Use Codat UAT environment Select this check box to connect your UAT environment.
信息注释 Schemas and naming conventions can change from version to version. It is recommended to verify your version before continuing.
The schema and info displayed below is for version 1.
Depending on your destination, table and column names may not appear as they are outlined below.
accounts
The accounts table contains info about the accounts in your Codat instance. In Codat, an account is a category used to record accounting transactions for a business.
accounts table foreign keys Join accounts with on bank_accounts accounts.name = bank_accounts.accountName balance_sheets accounts.id = balance_sheets.reports.assets.accountId accounts.id = balance_sheets.reports.equity.accountId accounts.id = balance_sheets.reports.liabilities.accountId profit_and_loss accounts.id = profit_and_loss.reports.costOfSales.accountId accounts.id = profit_and_loss.reports.expenses.accountId accounts.id = profit_and_loss.reports.income.accountId accounts.id = profit_and_loss.reports.otherExpenses.accountId accounts.id = profit_and_loss.reports.otherIncome.accountId bills accounts.id = bills.lineItems.accountRef.id items accounts.id = items.billItem.accountRef.id accounts.id = items.invoiceItem.accountRef.id journal_entries accounts.id = journal_entries.journalLines.accountRef.id bank_statement_lines accounts.name = bank_statement_lines.accountName
accounts table schema
companyId
STRING
currency
STRING
currentBalance
NUMBER
description
STRING
fullyQualifiedCategory
STRING
fullyQualifiedName
STRING
id
STRING
isBankAccount
BOOLEAN
modifiedDate
STRING
name
STRING
nominalCode
STRING
sourceModifiedDate
STRING
status
STRING
type
STRING
balance_sheets
The balance_sheets table contains info about the balance sheets in your Codat instance. A balance sheet is a snapshot at a point in time of a company’s accounts.
balance_sheets table foreign keys Join balance_sheets with on accounts balance_sheets.reports.assets.accountId = accounts.id balance_sheets.reports.equity.accountId = accounts.id balance_sheets.reports.liabilities.accountId = accounts.id profit_and_loss balance_sheets.reports.assets.accountId = profit_and_loss.reports.costOfSales.accountId balance_sheets.reports.equity.accountId = profit_and_loss.reports.costOfSales.accountId balance_sheets.reports.liabilities.accountId = profit_and_loss.reports.costOfSales.accountId balance_sheets.reports.assets.accountId = profit_and_loss.reports.expenses.accountId balance_sheets.reports.equity.accountId = profit_and_loss.reports.expenses.accountId balance_sheets.reports.liabilities.accountId = profit_and_loss.reports.expenses.accountId balance_sheets.reports.assets.accountId = profit_and_loss.reports.income.accountId balance_sheets.reports.equity.accountId = profit_and_loss.reports.income.accountId balance_sheets.reports.liabilities.accountId = profit_and_loss.reports.income.accountId balance_sheets.reports.assets.accountId = profit_and_loss.reports.otherExpenses.accountId balance_sheets.reports.equity.accountId = profit_and_loss.reports.otherExpenses.accountId balance_sheets.reports.liabilities.accountId = profit_and_loss.reports.otherExpenses.accountId balance_sheets.reports.assets.accountId = profit_and_loss.reports.otherIncome.accountId balance_sheets.reports.equity.accountId = profit_and_loss.reports.otherIncome.accountId balance_sheets.reports.liabilities.accountId = profit_and_loss.reports.otherIncome.accountId balance_sheets.companyId = profit_and_loss.companyId bills balance_sheets.reports.assets.accountId = bills.lineItems.accountRef.id balance_sheets.reports.equity.accountId = bills.lineItems.accountRef.id balance_sheets.reports.liabilities.accountId = bills.lineItems.accountRef.id balance_sheets.companyId = bills.companyId items balance_sheets.reports.assets.accountId = items.billItem.accountRef.id balance_sheets.reports.equity.accountId = items.billItem.accountRef.id balance_sheets.reports.liabilities.accountId = items.billItem.accountRef.id balance_sheets.reports.assets.accountId = items.invoiceItem.accountRef.id balance_sheets.reports.equity.accountId = items.invoiceItem.accountRef.id balance_sheets.reports.liabilities.accountId = items.invoiceItem.accountRef.id balance_sheets.companyId = items.companyId journal_entries balance_sheets.reports.assets.accountId = journal_entries.journalLines.accountRef.id balance_sheets.reports.equity.accountId = journal_entries.journalLines.accountRef.id balance_sheets.reports.liabilities.accountId = journal_entries.journalLines.accountRef.id balance_sheets.companyId = journal_entries.companyId companies balance_sheets.companyId = companies.id bank_statements balance_sheets.companyId = bank_statements.companyId bank_statement_lines balance_sheets.companyId = bank_statement_lines.companyId bill_payments balance_sheets.companyId = bill_payments.supplierRef.companyId balance_sheets.companyId = bill_payments.companyId company_info balance_sheets.companyId = company_info.companyId customers balance_sheets.companyId = customers.companyId invoices balance_sheets.companyId = invoices.companyId payments balance_sheets.companyId = payments.companyId suppliers balance_sheets.companyId = suppliers.companyId connections balance_sheets.companyId = connections.companyId events balance_sheets.companyId = events.companyId tax_rates balance_sheets.companyId = tax_rates.companyId bank_account_transactions balance_sheets.companyId = bank_account_transactions.companyId bank_accounts balance_sheets.companyId = bank_accounts.companyId
balance_sheets table schema
companyId
STRING
currency
STRING
mostRecentAvailableMonth
STRING
reports
ARRAY
This is a nested data structure:
assets: ARRAY, which is also a nested data structure.
accountId: STRING name: STRING value: NUMBER date: DATE-TIME equity: ARRAY, which is also a nested data structure.
accountId: STRING name: STRING value: NUMBER liabilities: ARRAY, which is also a nested data structure.
accountId: STRING name: STRING value: NUMBER netAssets: NUMBER
status
OBJECT, STRING
This is a nested data structure.
bank_account_transactions
The bank_account_transactions table contains information about transactions for a specified company and bank account in your Codat account.
bank_account_transactions table foreign keys Join bank_account_transactions with on bank_accounts bank_account_transactions.bankAccountId = bank_accounts.id bank_account_transactions.companyId = bank_accounts.companyId companies bank_account_transactions.companyId = companies.id balance_sheets bank_account_transactions.companyId = balance_sheets.companyId bank_statements bank_account_transactions.companyId = bank_statements.companyId bank_statement_lines bank_account_transactions.companyId = bank_statement_lines.companyId bill_payments bank_account_transactions.companyId = bill_payments.supplierRef.companyId bank_account_transactions.companyId = bill_payments.companyId bills bank_account_transactions.companyId = bills.companyId company_info bank_account_transactions.companyId = company_info.companyId customers bank_account_transactions.companyId = customers.companyId invoices bank_account_transactions.companyId = invoices.companyId payments bank_account_transactions.companyId = payments.companyId profit_and_loss bank_account_transactions.companyId = profit_and_loss.companyId suppliers bank_account_transactions.companyId = suppliers.companyId connections bank_account_transactions.companyId = connections.companyId events bank_account_transactions.companyId = events.companyId items bank_account_transactions.companyId = items.companyId journal_entries bank_account_transactions.companyId = journal_entries.companyId tax_rates bank_account_transactions.companyId = tax_rates.companyId
bank_account_transactions table schema
_transactionIndex
INTEGER
amount
NUMBER
balance
NUMBER
bankAccountId
STRING
companyId
STRING
date
STRING
description
STRING
modifiedDate
STRING
reconciled
BOOLEAN
sourceModifiedDate
STRING
transactionType
STRING
bank_accounts
The bank_accounts table contains informaiton about bank accounts for a given company in your Codat account.
bank_accounts table foreign keys Join bank_accounts with on accounts bank_accounts.accountName = accounts.name bank_statement_lines bank_accounts.accountName = bank_statement_lines.accountName bank_accounts.companyId = bank_statement_lines.companyId bank_account_transactions bank_accounts.id = bank_account_transactions.bankAccountId bank_accounts.companyId = bank_account_transactions.companyId companies bank_accounts.companyId = companies.id balance_sheets bank_accounts.companyId = balance_sheets.companyId bank_statements bank_accounts.companyId = bank_statements.companyId bill_payments bank_accounts.companyId = bill_payments.supplierRef.companyId bank_accounts.companyId = bill_payments.companyId bills bank_accounts.companyId = bills.companyId company_info bank_accounts.companyId = company_info.companyId customers bank_accounts.companyId = customers.companyId invoices bank_accounts.companyId = invoices.companyId payments bank_accounts.companyId = payments.companyId profit_and_loss bank_accounts.companyId = profit_and_loss.companyId suppliers bank_accounts.companyId = suppliers.companyId connections bank_accounts.companyId = connections.companyId bank_accounts.connectionId = connections.id events bank_accounts.companyId = events.companyId items bank_accounts.companyId = items.companyId journal_entries bank_accounts.companyId = journal_entries.companyId tax_rates bank_accounts.companyId = tax_rates.companyId
bank_accounts table schema
accountName
STRING
accountNumber
STRING
availableBalance
NUMBER
balance
NUMBER
companyId
STRING
connectionId
STRING
currency
STRING
fromDate
STRING
iban
STRING
id
STRING
modifiedDate
STRING
overdraftLimit
NUMBER
sortCode
STRING
sourceModifiedDate
STRING
toDate
STRING
bank_statement_lines
The bank_statement_lines table contains info about lines included in bank statements.
bank_statement_lines table foreign keys Join bank_statement_lines with on accounts bank_statement_lines.accountName = accounts.name bank_accounts bank_statement_lines.accountName = bank_accounts.accountName bank_statement_lines.companyId = bank_accounts.companyId companies bank_statement_lines.companyId = companies.id balance_sheets bank_statement_lines.companyId = balance_sheets.companyId bank_statements bank_statement_lines.companyId = bank_statements.companyId bill_payments bank_statement_lines.companyId = bill_payments.supplierRef.companyId bank_statement_lines.companyId = bill_payments.companyId bills bank_statement_lines.companyId = bills.companyId company_info bank_statement_lines.companyId = company_info.companyId customers bank_statement_lines.companyId = customers.companyId invoices bank_statement_lines.companyId = invoices.companyId payments bank_statement_lines.companyId = payments.companyId profit_and_loss bank_statement_lines.companyId = profit_and_loss.companyId suppliers bank_statement_lines.companyId = suppliers.companyId connections bank_statement_lines.companyId = connections.companyId events bank_statement_lines.companyId = events.companyId items bank_statement_lines.companyId = items.companyId journal_entries bank_statement_lines.companyId = journal_entries.companyId tax_rates bank_statement_lines.companyId = tax_rates.companyId bank_account_transactions bank_statement_lines.companyId = bank_account_transactions.companyId
bank_statement_lines table schema
_lineIndex
INTEGER
accountName
STRING
amount
NUMBER
balance
NUMBER
companyId
STRING
date
DATE-TIME
description
STRING
id
STRING
reconciled
BOOLEAN
bank_statements
The bank_statements table contains info about bank statement report data for a company over a time period.
bank_statements table foreign keys Join bank_statements with on companies bank_statements.companyId = companies.id balance_sheets bank_statements.companyId = balance_sheets.companyId bank_statement_lines bank_statements.companyId = bank_statement_lines.companyId bill_payments bank_statements.companyId = bill_payments.supplierRef.companyId bank_statements.companyId = bill_payments.companyId bills bank_statements.companyId = bills.companyId company_info bank_statements.companyId = company_info.companyId customers bank_statements.companyId = customers.companyId invoices bank_statements.companyId = invoices.companyId payments bank_statements.companyId = payments.companyId profit_and_loss bank_statements.companyId = profit_and_loss.companyId suppliers bank_statements.companyId = suppliers.companyId connections bank_statements.companyId = connections.companyId events bank_statements.companyId = events.companyId items bank_statements.companyId = items.companyId journal_entries bank_statements.companyId = journal_entries.companyId tax_rates bank_statements.companyId = tax_rates.companyId bank_account_transactions bank_statements.companyId = bank_account_transactions.companyId bank_accounts bank_statements.companyId = bank_accounts.companyId
bank_statements table schema
accountName
STRING
accountNumber
STRING
availableBalance
STRING, NUMBER
balance
STRING, NUMBER
companyId
STRING
currency
STRING
fromDate
DATE-TIME
iban
STRING
id
STRING
sortCode
STRING
toDate
DATE-TIME
bill_payments
The bill_payments table contains information about company bill payments in your Codat account.
bill_payments table foreign keys Join bill_payments with on companies bill_payments.supplierRef.companyId = companies.id bill_payments.companyId = companies.id balance_sheets bill_payments.supplierRef.companyId = balance_sheets.companyId bill_payments.companyId = balance_sheets.companyId bank_statements bill_payments.supplierRef.companyId = bank_statements.companyId bill_payments.companyId = bank_statements.companyId bank_statement_lines bill_payments.supplierRef.companyId = bank_statement_lines.companyId bill_payments.companyId = bank_statement_lines.companyId bills bill_payments.supplierRef.companyId = bills.companyId bill_payments.companyId = bills.companyId bill_payments.supplierRef.id = bills.supplierRef company_info bill_payments.supplierRef.companyId = company_info.companyId bill_payments.companyId = company_info.companyId customers bill_payments.supplierRef.companyId = customers.companyId bill_payments.companyId = customers.companyId invoices bill_payments.supplierRef.companyId = invoices.companyId bill_payments.companyId = invoices.companyId payments bill_payments.supplierRef.companyId = payments.companyId bill_payments.companyId = payments.companyId profit_and_loss bill_payments.supplierRef.companyId = profit_and_loss.companyId bill_payments.companyId = profit_and_loss.companyId suppliers bill_payments.supplierRef.companyId = suppliers.companyId bill_payments.companyId = suppliers.companyId connections bill_payments.supplierRef.companyId = connections.companyId bill_payments.companyId = connections.companyId events bill_payments.supplierRef.companyId = events.companyId bill_payments.companyId = events.companyId items bill_payments.supplierRef.companyId = items.companyId bill_payments.companyId = items.companyId journal_entries bill_payments.supplierRef.companyId = journal_entries.companyId bill_payments.companyId = journal_entries.companyId tax_rates bill_payments.supplierRef.companyId = tax_rates.companyId bill_payments.companyId = tax_rates.companyId bank_account_transactions bill_payments.supplierRef.companyId = bank_account_transactions.companyId bill_payments.companyId = bank_account_transactions.companyId bank_accounts bill_payments.supplierRef.companyId = bank_accounts.companyId bill_payments.companyId = bank_accounts.companyId
bill_payments table schema
companyId
STRING
currency
STRING
currencyRate
NUMBER
date
STRING
id
STRING
lines
ARRAY
This is a nested data structure:
amount: NUMBER links: ARRAY, which is also a nested data structure.
modifiedDate
STRING
note
STRING
sourceModifiedDate
STRING
supplierRef
OBJECT
This is a nested data structure:
addresses: ARRAY, which is also a nested data structure.
city: STRING country: STRING line1: STRING line2: STRING postalCode: STRING region: STRING type: STRING companyId: STRING contactName: STRING emailAddress: STRING id: STRING phone: STRING registrationNumber: STRING status: STRING supplierName: STRING taxNumber: STRING
totalAmount
NUMBER
bills
The bills table contains info about the bills in your Codat account. A bill is an itemized record of goods or services purchased from a supplier .
bills table foreign keys Join bills with on accounts bills.lineItems.accountRef.id = accounts.id balance_sheets bills.lineItems.accountRef.id = balance_sheets.reports.assets.accountId bills.lineItems.accountRef.id = balance_sheets.reports.equity.accountId bills.lineItems.accountRef.id = balance_sheets.reports.liabilities.accountId bills.companyId = balance_sheets.companyId profit_and_loss bills.lineItems.accountRef.id = profit_and_loss.reports.costOfSales.accountId bills.lineItems.accountRef.id = profit_and_loss.reports.expenses.accountId bills.lineItems.accountRef.id = profit_and_loss.reports.income.accountId bills.lineItems.accountRef.id = profit_and_loss.reports.otherExpenses.accountId bills.lineItems.accountRef.id = profit_and_loss.reports.otherIncome.accountId bills.companyId = profit_and_loss.companyId items bills.lineItems.accountRef.id = items.billItem.accountRef.id bills.lineItems.accountRef.id = items.invoiceItem.accountRef.id bills.companyId = items.companyId bills.lineItems.itemRef.id = items.id bills.lineItems.taxRateRef.id = items.billItem.taxRateRef.id bills.lineItems.taxRateRef.id = items.invoiceItem.taxRateRef.id journal_entries bills.lineItems.accountRef.id = journal_entries.journalLines.accountRef.id bills.companyId = journal_entries.companyId companies bills.companyId = companies.id bank_statements bills.companyId = bank_statements.companyId bank_statement_lines bills.companyId = bank_statement_lines.companyId bill_payments bills.companyId = bill_payments.supplierRef.companyId bills.companyId = bill_payments.companyId bills.supplierRef = bill_payments.supplierRef.id company_info bills.companyId = company_info.companyId customers bills.companyId = customers.companyId invoices bills.companyId = invoices.companyId payments bills.companyId = payments.companyId suppliers bills.companyId = suppliers.companyId bills.supplierRef = suppliers.id connections bills.companyId = connections.companyId events bills.companyId = events.companyId tax_rates bills.companyId = tax_rates.companyId bills.lineItems.taxRateRef.id = tax_rates.id bank_account_transactions bills.companyId = bank_account_transactions.companyId bank_accounts bills.companyId = bank_accounts.companyId
bills table schema
amountDue
NUMBER
companyId
STRING
currency
STRING
dueDate
DATE-TIME
id
STRING
issueDate
DATE-TIME
lineItems
ARRAY
This is a nested data structure:
accountRef: OBJECT, which is also a nested data structure.
description: STRING discountAmount: NUMBER discountPercentage: NUMBER itemRef: OBJECT, which is also a nested data structure.
quantity: NUMBER subTotal: NUMBER taxAmount: NUMBER taxRateRef: OBJECT, which is also a nested data structure.
totalAmount: NUMBER unitAmount: NUMBER
reference
STRING
status
STRING
subTotal
NUMBER
supplierRef
OBJECT
This is a nested data structure:
addresses: ARRAY, which is also a nested data structure.
city: STRING country: STRING line1: STRING line2: STRING postalCode: STRING region: STRING type: STRING companyId: STRING contactName: STRING emailAddress: STRING id: STRING phone: STRING registrationNumber: STRING status: STRING supplierName: STRING taxNumber
taxAmount
NUMBER
totalAmount
NUMBER
companies
The companies table contains info about the companies in your Codat instance. A company represents a business who can share the connection to their financial data sources.
companies table foreign keys companies table schema
id
STRING
lastSync
DATE-TIME
name
STRING
platform
STRING
redirect
STRING
status
STRING
company_info
The company_info table contains company info. In Codat, company info includes information about a linked company such as address, phone number and company registration.
company_info table foreign keys Join company_info with on companies company_info.companyId = companies.id balance_sheets company_info.companyId = balance_sheets.companyId bank_statements company_info.companyId = bank_statements.companyId bank_statement_lines company_info.companyId = bank_statement_lines.companyId bill_payments company_info.companyId = bill_payments.supplierRef.companyId company_info.companyId = bill_payments.companyId bills company_info.companyId = bills.companyId customers company_info.companyId = customers.companyId invoices company_info.companyId = invoices.companyId payments company_info.companyId = payments.companyId profit_and_loss company_info.companyId = profit_and_loss.companyId suppliers company_info.companyId = suppliers.companyId connections company_info.companyId = connections.companyId events company_info.companyId = events.companyId items company_info.companyId = items.companyId journal_entries company_info.companyId = journal_entries.companyId tax_rates company_info.companyId = tax_rates.companyId bank_account_transactions company_info.companyId = bank_account_transactions.companyId bank_accounts company_info.companyId = bank_accounts.companyId
company_info table schema
accountingPlatformRef
STRING
addresses
ARRAY
This is a nested data structure:
city: STRING country: STRING line1: STRING line2: STRING postalCode: STRING region: STRING type: STRING
baseCurrency
STRING
companyId
STRING
companyLegalName
STRING
companyName
STRING
createdDate
STRING
financialYearStartDate
DATE-TIME
phoneNumbers
ARRAY
This is a nested data structure:
number: STRING type: STRING
registrationNumber
STRING
taxNumber
STRING
webLinks
ARRAY
This is a nested data structure:
connections
The connections table contains information about company connections in your Codat account.
Replication Method
Full Table
Primary Keys
companyId
id
Useful links
Codat API method
connections table foreign keys Join connections with on companies connections.companyId = companies.id balance_sheets connections.companyId = balance_sheets.companyId bank_statements connections.companyId = bank_statements.companyId bank_statement_lines connections.companyId = bank_statement_lines.companyId bill_payments connections.companyId = bill_payments.supplierRef.companyId connections.companyId = bill_payments.companyId bills connections.companyId = bills.companyId company_info connections.companyId = company_info.companyId customers connections.companyId = customers.companyId invoices connections.companyId = invoices.companyId payments connections.companyId = payments.companyId profit_and_loss connections.companyId = profit_and_loss.companyId suppliers connections.companyId = suppliers.companyId events connections.companyId = events.companyId items connections.companyId = items.companyId journal_entries connections.companyId = journal_entries.companyId tax_rates connections.companyId = tax_rates.companyId bank_account_transactions connections.companyId = bank_account_transactions.companyId bank_accounts connections.companyId = bank_accounts.companyId connections.id = bank_accounts.connectionId
connections table schema
companyId
STRING
id
STRING
integrationId
STRING
linkUrl
STRING
platformName
STRING
sourceId
STRING
status
STRING
credit_notes
The credit_notes table contains info about the credit notes in your Codat instance.
credit_notes table foreign keys Join credit_notes with on payments credit_notes.id = payments.lines.links.id credit_notes.customerRef.id = payments.customerRef.id customers credit_notes.customerRef.id = customers.id
credit_notes table schema
companyId
STRING
creditNoteNumber
STRING
currency
STRING
customerRef
OBJECT
This is a nested data structure:
companyName: STRING id: STRING
id
STRING
issueDate
DATE-TIME
paymentAllocations
ARRAY
This is a nested data structure.
remainingCredit
NUMBER
status
STRING
totalAmount
NUMBER
customers
The customers table contains info about the customers in your Codat instance. A customer is a person or organisation that buys goods or services.
customers table foreign keys Join customers with on companies customers.companyId = companies.id balance_sheets customers.companyId = balance_sheets.companyId bank_statements customers.companyId = bank_statements.companyId bank_statement_lines customers.companyId = bank_statement_lines.companyId bill_payments customers.companyId = bill_payments.supplierRef.companyId customers.companyId = bill_payments.companyId bills customers.companyId = bills.companyId company_info customers.companyId = company_info.companyId invoices customers.companyId = invoices.companyId payments customers.companyId = payments.companyId customers.id = payments.customerRef.id profit_and_loss customers.companyId = profit_and_loss.companyId suppliers customers.companyId = suppliers.companyId connections customers.companyId = connections.companyId events customers.companyId = events.companyId items customers.companyId = items.companyId journal_entries customers.companyId = journal_entries.companyId tax_rates customers.companyId = tax_rates.companyId bank_account_transactions customers.companyId = bank_account_transactions.companyId bank_accounts customers.companyId = bank_accounts.companyId credit_notes customers.id = credit_notes.customerRef.id
customers table schema
addresses
ARRAY
This is a nested data structure:
city: STRING country: STRING line1: STRING line2: STRING postalCode: STRING region: STRING type: STRING
companyId
STRING
contactName
STRING
contacts
ARRAY
This is a nested data structure:
address: OBJECT, which is also a nested data structure.
city: STRING country: STRING line1: STRING line2: STRING postalCode: STRING region: STRING type: STRING email: STRING modifiedDate: DATE-TIME name: STRING phone: ARRAY, which is also a nested data structure.
number: STRING type: STRING status: STRING
customerName
STRING
defaultCurrency
STRING
emailAddress
STRING
id
STRING
modifiedDate
DATE-TIME
phone
STRING
registrationNumber
STRING
status
STRING
taxNumber
STRING
events
The events table contains information about a given company’s events in your Codat account.
Replication Method
Full Table
Primary Keys
companyId
eventTimeUtc
Useful links
Codat API method
events table foreign keys events table schema
companyId
STRING
description
STRING
eventTimeUtc
STRING
type
STRING
invoices
The invoices table contains info about the invoices in your Codat instance. An invoice is an itemized record of goods or services sold to a customer .
invoices table foreign keys Join invoices with on companies invoices.companyId = companies.id balance_sheets invoices.companyId = balance_sheets.companyId bank_statements invoices.companyId = bank_statements.companyId bank_statement_lines invoices.companyId = bank_statement_lines.companyId bill_payments invoices.companyId = bill_payments.supplierRef.companyId invoices.companyId = bill_payments.companyId bills invoices.companyId = bills.companyId company_info invoices.companyId = company_info.companyId customers invoices.companyId = customers.companyId payments invoices.companyId = payments.companyId invoices.id = payments.lines.links.id profit_and_loss invoices.companyId = profit_and_loss.companyId suppliers invoices.companyId = suppliers.companyId connections invoices.companyId = connections.companyId events invoices.companyId = events.companyId items invoices.companyId = items.companyId journal_entries invoices.companyId = journal_entries.companyId tax_rates invoices.companyId = tax_rates.companyId bank_account_transactions invoices.companyId = bank_account_transactions.companyId bank_accounts invoices.companyId = bank_accounts.companyId
invoices table schema
amountDue
NUMBER
companyId
STRING
currency
STRING
customerRef
OBJECT
This is a nested data structure:
companyName: STRING id: STRING
dueDate
DATE-TIME
id
STRING
invoiceNumber
STRING
issueDate
DATE-TIME
lineItems
ARRAY
This is a nested data structure:
description: STRING discountAmount: NUMBER quantity: NUMBER subTotal: NUMBER taxAmount: NUMBER totalAmount: NUMBER unitAmount: NUMBER
modifiedDate
DATE-TIME
paidOnDate
DATE-TIME
paymentAllocations
ARRAY
This is a nested data structure.
status
STRING
subTotal
NUMBER
totalAmount
NUMBER
totalDiscount
NUMBER
totalTaxAmount
NUMBER
items
The items table contains information about items for a given company in your Codat account.
items table foreign keys Join items with on accounts items.billItem.accountRef.id = accounts.id items.invoiceItem.accountRef.id = accounts.id balance_sheets items.billItem.accountRef.id = balance_sheets.reports.assets.accountId items.invoiceItem.accountRef.id = balance_sheets.reports.assets.accountId items.billItem.accountRef.id = balance_sheets.reports.equity.accountId items.invoiceItem.accountRef.id = balance_sheets.reports.equity.accountId items.billItem.accountRef.id = balance_sheets.reports.liabilities.accountId items.invoiceItem.accountRef.id = balance_sheets.reports.liabilities.accountId items.companyId = balance_sheets.companyId profit_and_loss items.billItem.accountRef.id = profit_and_loss.reports.costOfSales.accountId items.invoiceItem.accountRef.id = profit_and_loss.reports.costOfSales.accountId items.billItem.accountRef.id = profit_and_loss.reports.expenses.accountId items.invoiceItem.accountRef.id = profit_and_loss.reports.expenses.accountId items.billItem.accountRef.id = profit_and_loss.reports.income.accountId items.invoiceItem.accountRef.id = profit_and_loss.reports.income.accountId items.billItem.accountRef.id = profit_and_loss.reports.otherExpenses.accountId items.invoiceItem.accountRef.id = profit_and_loss.reports.otherExpenses.accountId items.billItem.accountRef.id = profit_and_loss.reports.otherIncome.accountId items.invoiceItem.accountRef.id = profit_and_loss.reports.otherIncome.accountId items.companyId = profit_and_loss.companyId bills items.billItem.accountRef.id = bills.lineItems.accountRef.id items.invoiceItem.accountRef.id = bills.lineItems.accountRef.id items.companyId = bills.companyId items.id = bills.lineItems.itemRef.id items.billItem.taxRateRef.id = bills.lineItems.taxRateRef.id items.invoiceItem.taxRateRef.id = bills.lineItems.taxRateRef.id journal_entries items.billItem.accountRef.id = journal_entries.journalLines.accountRef.id items.invoiceItem.accountRef.id = journal_entries.journalLines.accountRef.id items.companyId = journal_entries.companyId companies items.companyId = companies.id bank_statements items.companyId = bank_statements.companyId bank_statement_lines items.companyId = bank_statement_lines.companyId bill_payments items.companyId = bill_payments.supplierRef.companyId items.companyId = bill_payments.companyId company_info items.companyId = company_info.companyId customers items.companyId = customers.companyId invoices items.companyId = invoices.companyId payments items.companyId = payments.companyId suppliers items.companyId = suppliers.companyId connections items.companyId = connections.companyId events items.companyId = events.companyId tax_rates items.companyId = tax_rates.companyId items.billItem.taxRateRef.id = tax_rates.id items.invoiceItem.taxRateRef.id = tax_rates.id bank_account_transactions items.companyId = bank_account_transactions.companyId bank_accounts items.companyId = bank_accounts.companyId
items table schema
billItem
OBJECT
This is a nested data structure:
accountRef: OBJECT, which is also a nested data structure.
description: STRING taxRateRef: OBJECT, which is also a nested data structure.
unitPrice: NUMBER
code
STRING
companyId
STRING
id
STRING
invoiceItem
OBJECT
This is a nested data structure:
accountRef: OBJECT, which is also a nested data structure.
description: STRING taxRateRef: OBJECT, which is also a nested data structure.
unitPrice: NUMBER
isBillItem
BOOLEAN
isInvoiceItem
BOOLEAN
itemStatus
STRING
modifiedDate
STRING
name
STRING
sourceModifiedDate
STRING
journal_entries
The journal_entries table contains information about journal entries for a given company in your Codat account.
journal_entries table foreign keys Join journal_entries with on accounts journal_entries.journalLines.accountRef.id = accounts.id balance_sheets journal_entries.journalLines.accountRef.id = balance_sheets.reports.assets.accountId journal_entries.journalLines.accountRef.id = balance_sheets.reports.equity.accountId journal_entries.journalLines.accountRef.id = balance_sheets.reports.liabilities.accountId journal_entries.companyId = balance_sheets.companyId profit_and_loss journal_entries.journalLines.accountRef.id = profit_and_loss.reports.costOfSales.accountId journal_entries.journalLines.accountRef.id = profit_and_loss.reports.expenses.accountId journal_entries.journalLines.accountRef.id = profit_and_loss.reports.income.accountId journal_entries.journalLines.accountRef.id = profit_and_loss.reports.otherExpenses.accountId journal_entries.journalLines.accountRef.id = profit_and_loss.reports.otherIncome.accountId journal_entries.companyId = profit_and_loss.companyId bills journal_entries.journalLines.accountRef.id = bills.lineItems.accountRef.id journal_entries.companyId = bills.companyId items journal_entries.journalLines.accountRef.id = items.billItem.accountRef.id journal_entries.journalLines.accountRef.id = items.invoiceItem.accountRef.id journal_entries.companyId = items.companyId companies journal_entries.companyId = companies.id bank_statements journal_entries.companyId = bank_statements.companyId bank_statement_lines journal_entries.companyId = bank_statement_lines.companyId bill_payments journal_entries.companyId = bill_payments.supplierRef.companyId journal_entries.companyId = bill_payments.companyId company_info journal_entries.companyId = company_info.companyId customers journal_entries.companyId = customers.companyId invoices journal_entries.companyId = invoices.companyId payments journal_entries.companyId = payments.companyId suppliers journal_entries.companyId = suppliers.companyId connections journal_entries.companyId = connections.companyId events journal_entries.companyId = events.companyId tax_rates journal_entries.companyId = tax_rates.companyId bank_account_transactions journal_entries.companyId = bank_account_transactions.companyId bank_accounts journal_entries.companyId = bank_accounts.companyId
journal_entries table schema
companyId
STRING
createdOn
STRING
id
STRING
journalLines
ARRAY
This is a nested data structure:
accountRef: OBJECT, which is also a nested data structure.
currency: STRING description: STRING grossAmount: NUMBER id: STRING netAmount: NUMBER taxAmount: NUMBER
modifiedDate
STRING
postedOn
STRING
sourceModifiedDate
STRING
payments
The payments table contains info about the payments, or Accounts Receivable transactions, in your Codat instance. This includes details about invoices and credit notes.
payments table foreign keys Join payments with on companies payments.companyId = companies.id balance_sheets payments.companyId = balance_sheets.companyId bank_statements payments.companyId = bank_statements.companyId bank_statement_lines payments.companyId = bank_statement_lines.companyId bill_payments payments.companyId = bill_payments.supplierRef.companyId payments.companyId = bill_payments.companyId bills payments.companyId = bills.companyId company_info payments.companyId = company_info.companyId customers payments.companyId = customers.companyId payments.customerRef.id = customers.id invoices payments.companyId = invoices.companyId payments.lines.links.id = invoices.id profit_and_loss payments.companyId = profit_and_loss.companyId suppliers payments.companyId = suppliers.companyId connections payments.companyId = connections.companyId events payments.companyId = events.companyId items payments.companyId = items.companyId journal_entries payments.companyId = journal_entries.companyId tax_rates payments.companyId = tax_rates.companyId bank_account_transactions payments.companyId = bank_account_transactions.companyId bank_accounts payments.companyId = bank_accounts.companyId credit_notes payments.lines.links.id = credit_notes.id payments.customerRef.id = credit_notes.customerRef.id
payments table schema
companyId
STRING
currency
STRING
customerRef
OBJECT
This is a nested data structure:
companyName: STRING id: STRING
date
DATE-TIME
id
STRING
lines
ARRAY
This is a nested data structure:
amount: NUMBER links: ARRAY, which is also a nested data structure.
note
STRING
totalAmount
NUMBER
profit_and_loss
The profit_and_loss table contains profit and loss report data for a company over a time period.
profit_and_loss table foreign keys Join profit_and_loss with on accounts profit_and_loss.reports.costOfSales.accountId = accounts.id profit_and_loss.reports.expenses.accountId = accounts.id profit_and_loss.reports.income.accountId = accounts.id profit_and_loss.reports.otherExpenses.accountId = accounts.id profit_and_loss.reports.otherIncome.accountId = accounts.id balance_sheets profit_and_loss.reports.costOfSales.accountId = balance_sheets.reports.assets.accountId profit_and_loss.reports.expenses.accountId = balance_sheets.reports.assets.accountId profit_and_loss.reports.income.accountId = balance_sheets.reports.assets.accountId profit_and_loss.reports.otherExpenses.accountId = balance_sheets.reports.assets.accountId profit_and_loss.reports.otherIncome.accountId = balance_sheets.reports.assets.accountId profit_and_loss.reports.costOfSales.accountId = balance_sheets.reports.equity.accountId profit_and_loss.reports.expenses.accountId = balance_sheets.reports.equity.accountId profit_and_loss.reports.income.accountId = balance_sheets.reports.equity.accountId profit_and_loss.reports.otherExpenses.accountId = balance_sheets.reports.equity.accountId profit_and_loss.reports.otherIncome.accountId = balance_sheets.reports.equity.accountId profit_and_loss.reports.costOfSales.accountId = balance_sheets.reports.liabilities.accountId profit_and_loss.reports.expenses.accountId = balance_sheets.reports.liabilities.accountId profit_and_loss.reports.income.accountId = balance_sheets.reports.liabilities.accountId profit_and_loss.reports.otherExpenses.accountId = balance_sheets.reports.liabilities.accountId profit_and_loss.reports.otherIncome.accountId = balance_sheets.reports.liabilities.accountId profit_and_loss.companyId = balance_sheets.companyId bills profit_and_loss.reports.costOfSales.accountId = bills.lineItems.accountRef.id profit_and_loss.reports.expenses.accountId = bills.lineItems.accountRef.id profit_and_loss.reports.income.accountId = bills.lineItems.accountRef.id profit_and_loss.reports.otherExpenses.accountId = bills.lineItems.accountRef.id profit_and_loss.reports.otherIncome.accountId = bills.lineItems.accountRef.id profit_and_loss.companyId = bills.companyId items profit_and_loss.reports.costOfSales.accountId = items.billItem.accountRef.id profit_and_loss.reports.expenses.accountId = items.billItem.accountRef.id profit_and_loss.reports.income.accountId = items.billItem.accountRef.id profit_and_loss.reports.otherExpenses.accountId = items.billItem.accountRef.id profit_and_loss.reports.otherIncome.accountId = items.billItem.accountRef.id profit_and_loss.reports.costOfSales.accountId = items.invoiceItem.accountRef.id profit_and_loss.reports.expenses.accountId = items.invoiceItem.accountRef.id profit_and_loss.reports.income.accountId = items.invoiceItem.accountRef.id profit_and_loss.reports.otherExpenses.accountId = items.invoiceItem.accountRef.id profit_and_loss.reports.otherIncome.accountId = items.invoiceItem.accountRef.id profit_and_loss.companyId = items.companyId journal_entries profit_and_loss.reports.costOfSales.accountId = journal_entries.journalLines.accountRef.id profit_and_loss.reports.expenses.accountId = journal_entries.journalLines.accountRef.id profit_and_loss.reports.income.accountId = journal_entries.journalLines.accountRef.id profit_and_loss.reports.otherExpenses.accountId = journal_entries.journalLines.accountRef.id profit_and_loss.reports.otherIncome.accountId = journal_entries.journalLines.accountRef.id profit_and_loss.companyId = journal_entries.companyId companies profit_and_loss.companyId = companies.id bank_statements profit_and_loss.companyId = bank_statements.companyId bank_statement_lines profit_and_loss.companyId = bank_statement_lines.companyId bill_payments profit_and_loss.companyId = bill_payments.supplierRef.companyId profit_and_loss.companyId = bill_payments.companyId company_info profit_and_loss.companyId = company_info.companyId customers profit_and_loss.companyId = customers.companyId invoices profit_and_loss.companyId = invoices.companyId payments profit_and_loss.companyId = payments.companyId suppliers profit_and_loss.companyId = suppliers.companyId connections profit_and_loss.companyId = connections.companyId events profit_and_loss.companyId = events.companyId tax_rates profit_and_loss.companyId = tax_rates.companyId bank_account_transactions profit_and_loss.companyId = bank_account_transactions.companyId bank_accounts profit_and_loss.companyId = bank_accounts.companyId
profit_and_loss table schema
companyId
STRING
currency
STRING
mostRecentAvailableMonth
STRING
reportBasis
STRING
reports
ARRAY
This is a nested data structure:
costOfSales: ARRAY, which is also a nested data structure.
accountId: STRING name: STRING value: NUMBER expenses: ARRAY, which is also a nested data structure.
accountId: STRING name: STRING value: NUMBER fromDate: STRING grossProfit: NUMBER income: ARRAY, which is also a nested data structure.
accountId: STRING name: STRING value: NUMBER netOperatingProfit: NUMBER netOtherIncome: NUMBER netProfit: NUMBER otherExpenses: ARRAY, which is also a nested data structure.
accountId: STRING name: STRING value: NUMBER otherIncome: ARRAY, which is also a nested data structure.
accountId: STRING name: STRING value: NUMBER toDate: STRING
suppliers
The suppliers table contains info about the suppliers in your Codat instance. A supplier is a person or organization that provides a product or service.
suppliers table foreign keys Join suppliers with on companies suppliers.companyId = companies.id balance_sheets suppliers.companyId = balance_sheets.companyId bank_statements suppliers.companyId = bank_statements.companyId bank_statement_lines suppliers.companyId = bank_statement_lines.companyId bill_payments suppliers.companyId = bill_payments.supplierRef.companyId suppliers.companyId = bill_payments.companyId bills suppliers.companyId = bills.companyId suppliers.id = bills.supplierRef company_info suppliers.companyId = company_info.companyId customers suppliers.companyId = customers.companyId invoices suppliers.companyId = invoices.companyId payments suppliers.companyId = payments.companyId profit_and_loss suppliers.companyId = profit_and_loss.companyId connections suppliers.companyId = connections.companyId events suppliers.companyId = events.companyId items suppliers.companyId = items.companyId journal_entries suppliers.companyId = journal_entries.companyId tax_rates suppliers.companyId = tax_rates.companyId bank_account_transactions suppliers.companyId = bank_account_transactions.companyId bank_accounts suppliers.companyId = bank_accounts.companyId
suppliers table schema
addresses
ARRAY
This is a nested data structure:
city: STRING country: STRING line1: STRING line2: STRING postalCode: STRING region: STRING type: STRING
companyId
STRING
contactName
STRING
emailAddress
STRING
id
STRING
phone
STRING
registrationNumber
STRING
status
STRING
supplierName
STRING
taxNumber
STRING
tax_rates
The tax_rates table contains information about tax rates for a given company in your Codat account.
tax_rates table foreign keys Join tax_rates with on companies tax_rates.companyId = companies.id balance_sheets tax_rates.companyId = balance_sheets.companyId bank_statements tax_rates.companyId = bank_statements.companyId bank_statement_lines tax_rates.companyId = bank_statement_lines.companyId bill_payments tax_rates.companyId = bill_payments.supplierRef.companyId tax_rates.companyId = bill_payments.companyId bills tax_rates.companyId = bills.companyId tax_rates.id = bills.lineItems.taxRateRef.id company_info tax_rates.companyId = company_info.companyId customers tax_rates.companyId = customers.companyId invoices tax_rates.companyId = invoices.companyId payments tax_rates.companyId = payments.companyId profit_and_loss tax_rates.companyId = profit_and_loss.companyId suppliers tax_rates.companyId = suppliers.companyId connections tax_rates.companyId = connections.companyId events tax_rates.companyId = events.companyId items tax_rates.companyId = items.companyId tax_rates.id = items.billItem.taxRateRef.id tax_rates.id = items.invoiceItem.taxRateRef.id journal_entries tax_rates.companyId = journal_entries.companyId bank_account_transactions tax_rates.companyId = bank_account_transactions.companyId bank_accounts tax_rates.companyId = bank_accounts.companyId
tax_rates table schema
code
STRING
companyId
STRING
components
ARRAY
This is a nested data structure:
isCompound: BOOLEAN name: STRING rate: NUMBER
effectiveTaxRate
NUMBER
id
STRING
modifiedDate
STRING
name
STRING
sourceModifiedDate
STRING
totalTaxRate
NUMBER
