Tableau Connector for Zoho Books

Build 24.0.9060

BillDetails

List, add, update and delete details of a bill.

Table Specific Information

Select

The connector uses the Zoho Books API to process WHERE clause conditions built with the following column and operator:

  • BillId supports the the '=' and IN operators.

NOTE: BillId is required to query BillDetails.

The rest of the filter is executed client-side in the connector.

For example: 

    SELECT * FROM BillDetails WHERE BillId = '1894553000000085259'
	SELECT * FROM BillDetails WHERE BillId IN (SELECT BillId FROM Bills)
	SELECT * FROM BillDetails WHERE BillId IN ('1894553000000085259','1894553000000085260')

Insert

INSERT can be executed by specifying BillNumber, VendorId, LineItems columns. The columns that are not read-only can be inserted optionally. The following is an example of how to insert into this table. 

INSERT INTO BillLineItems#TEMP (Name, accountid, itemid) VALUES ('Cloth-Jeans', '3285934000000034001', '3285934000000104097')

INSERT INTO BillDetails (BillNumber, VendorId, lineitems) VALUES ('00011', '3285934000000104023', BillLineItems#TEMP )

INSERT can also be executed by specifying the LineItems column as a JSON array. The following is an example of how to insert using JSON array into this table. 

INSERT INTO BillDetails (BillNumber, VendorId, lineitems) VALUES ('00011', '3255827000000081003', '[{"Name":"Cloth-Jeans3", "AccountId":"3285934000000034001", "ItemId":"3285934000000104097"}]')

Update

UPDATE can be executed by specifying the BillId in the WHERE Clause. The columns that are not read-only can be updated. For example: 

INSERT INTO BillLineItems#TEMP (Name,accountid,itemid) VALUES ('Cloth-Jeans','3285934000000034001','3285934000000104097')

UPDATE BillDetails SET BillNumber = '00011', VendorId = '3285934000000104023', lineitems = 'BillLineItems#TEMP' WHERE BillId = '3350895000000089001'

UPDATE can also be executed by specifying the LineItems column as a JSON array. The following is an example of how to update using JSON array into this table. 

UPDATE BillDetails SET BillNumber = '00011', VendorId = '3285934000000104023', LineItems = '[{"Name":"Cloth-Jeans", "AccountId":"3285934000000034001", "ItemId":"3285934000000104097"}]' WHERE BillId = '3350895000000089001'

Delete

DELETE can be executed by specifying the BillId in the WHERE Clause For example: 

DELETE FROM BillDetails WHERE BillId = '3350895000000089001'

Columns

Name Type ReadOnly References SupportedOperators Description
BillId [KEY] String True

Bills.BillId

Id of a Bill.

BillNumber String False

Number of a Bill.

Adjustment Decimal False

Adjustments made to the bill.

Approvers String False

Approvers.

AdjustmentDescription String False

Description of adjustments made to the bill.

AllocatedLandedCosts String True

Allocated landed costs of bill.

ApproverId String True

Users.UserId

Id of an approver.

AttachmentName String True

Name of an attachment.

Balance Decimal True

Balance of bill.

BillingAddress String True

Billing address of a bill.

BillingAddressAttention String True

Name of a person in billing address.

BillingAddressCity String True

City of a billing address.

BillingAddressCountry String True

Country of a billing address.

BillingAddressFax String True

Fax of a billing address.

BillingAddressPhone String True

Phone of a billing address.

BillingAddressState String True

State of a billing address.

BillingAddressStreet2 String True

Street two of a billing address.

BillingAddressZip String True

Zip of a billing address.

CanAmmendTransaction Boolean True

Can ammend transaction.

CanSendInMail Boolean True

Can the file be sent in mail.

ClientViewedTime String True

Time when client viewed.

ColorCode String True

Color code.

ContactCategory String True

Category if contact.

CreatedById String True

Users.UserId

Id of a user who has created bill.

CreatedTime Datetime True

Time at which the bill was created.

CurrencyCode String True

Currency code of the customer's currency.

CurrencyId String True

Currencies.CurrencyId

Currency Id of the customer's currency.

CurrencySymbol String True

Symbol of the currency.

CurrentSubStatus String True

Current sub status of a bill.

CurrentSubStatusId String True

Current sub status Id of a bill.

CustomFields String False

Custom fields of the contact.

Documents String False

List of files to be attached to a particular transaction.

Date Date False

Bill date.

DestinationOfSupply String False

Place where the goods/services are supplied to.

Discount String True

Discount of bills.

DiscountAccountId String True

BankAccounts.AccountId

Account Id of discount.

DiscountAmount Decimal True

Amount of discount.

DiscountAppliedOnAmount Decimal True

Discount applied on amount.

DiscountSetting String True

Setting of discount.

DueByDays Integer True

Number of days the bill is due by.

DueDate Date False

Delivery date of the bill.

DueInDays String True

Number of days the bill is due in.

EntityType String True

Entity type of the bill.

ExchangeRate Decimal False

Exchange rate of the currency.

FiledInVatReturnId String True

VAT return Id of bill which was filed.

FiledInVatReturnName String True

VAT return name of bill which was filed.

FiledInVatReturnType String True

VAT return type of bill which was filed.

GstNo String False

GST number.

GstReturnDetailsReturnPeriod String True

Return period of GST.

GstReturnDetailsStatus String True

Status of GST return details.

GstTreatment String False

Choose whether the bill is GST registered/unregistered/consumer/overseas.

HasNextBill Boolean True

Check if it has next bill.

InvoiceConversionType String True

Type of invoice conversion.

IsApprovalRequired Boolean True

Check of the approval required.

IsDiscountBeforeTax Boolean True

Check if discount should be applied before tax.

IsInclusiveTax Boolean False

Check if the tax is inclusive in the bill.

IsItemLevelTaxCalc Boolean False

Check if the item leven tax is calculated.

IsLineItemInvoiced Boolean True

Check if the line item is invoiced in the bill.

IsPreGst Boolean True

Check if pre GST is applied.

IsReverseChargeApplied Boolean True

Check if reverse charge is applied.

IsTdsAmountInPercent Boolean True

Check if the TDS amount in percent.

IsTdsApplied Boolean True

Check if TDS is applied.

IsUpdateCustomer Boolean False

Check if cutomer should be updated.

IsViewedByClient Boolean True

Check if bill is viewed by client.

LastModifiedId String True

Id when bill was last modified.

LastModifiedTime Datetime True

The time of last modification of the bill.

LineItems String False

Line items of an estimate.

Notes String False

Notes of the bill.

OpenPurchaseordersCount Integer True

Count of open purchase order.

Orientation String True

Orientation of the bill.

PurchaseOrderIds String False

Purchase Order Ids.

PageHeight String True

Height of the page.

PageWidth String True

Width o the page.

PaymentExpectedDate Date True

Date when the payment is expected.

PaymentMade Decimal True

Amount paid of this bill.

PaymentTerms Integer False

Net payment term for the customer.

PaymentTermsLabel String False

Label for the paymet due details.

PricePrecision Integer True

The precision for the price.

PricebookId String False

Enter Id of the price book.

PlaceOfSupply String False

The place of supply is where a transaction is considered to have occurred for VAT purposes.

PermitNumber String False

The permit number for the bill.

RecurringBillId String False

Id of a recurring bill.

ReferenceBillId String True

Id of a reference bill.

ReferenceId String True

Id of a reference.

ReferenceNumber String False

Number of a reference.

SourceOfSupply String False

Source of supply of bill.

Status String True

Status of the bill.

SubTotal Decimal True

Sub total of bills.

SubTotalInclusiveOfTax Decimal True

Subtotal amount which are inclusive of tax.

SubmittedBy String True

Detail of the user who has submitted the bill.

SubmittedByEmail String True

Email of the user who has submitted the bill.

SubmittedByName String True

Name of the user who has submitted the bill.

SubmittedDate Date True

Bill submitted date.

SubmitterId String True

Users.UserId

Id of a submitter.

Taxes String False

Taxes.

TaxAccountId String True

BankAccounts.AccountId

Account Id of tax.

TaxTotal Decimal True

Total amount of tax.

TaxTreatment String False

VAT treatment for the Bill.

TdsAmount Decimal True

Amount of TDS.

TdsPercent String True

Percent of TDS.

TdsSection String True

Section of TDS.

TdsTaxId String True

Tax Id of TDS.

TdsTaxName String True

Tax name of TDS.

TemplateId String True

Id of a template.

TemplateName String True

Name of a template.

TemplateType String True

Type of a template.

Terms String False

Terms and Conditions apply of a bill.

Total Decimal True

Total of bills.

TrackDiscountInAccount Boolean True

Track discount in account.

UnallocatedLandedCosts String True

Costs of unlocated land.

UnprocessedPaymentAmount Decimal True

Unprocessed payment amount.

UnusedCreditsPayableAmount Decimal True

Payable amount of unused credits.

VatTreatment String False

VAT treatment for the bills.

VendorCreditsApplied Decimal True

Amount of applied vendor credits.

VendorId String False

Id of the vendor the bill has been made.

VendorName String True

Name of the vendor the bill has been made.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060