Power BI Connector for Zoho Books

Build 24.0.9062

InvoiceDetails

List, add, update and delete details of an invoice.

Table Specific Information

Select

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

  • InvoiceId supports the '=' and IN operators.

NOTE: InvoiceId is required to query InvoiceDetails.

The rest of the filter is executed client-side in the 本製品. For example: 

SELECT * FROM InvoiceDetails WHERE InvoiceId = '1864543000000078539'
SELECT * FROM InvoiceDetails WHERE InvoiceId IN (SELECT InvoiceId FROM Invoices)
SELECT * FROM InvoiceDetails WHERE InvoiceId IN ('1864543000000078539','1864543000000078540')

Insert

INSERT can be executed by specifying the CustomerId and LineItems columns, inserting columns that are not read-only if desired. For example: 

INSERT INTO InvoiceLineItems#TEMP (Name, itemid) VALUES ('Cloth-Jeans12', '3285934000000104097')

INSERT INTO InvoiceDetails (Customerid, lineitems) VALUES ('3285934000000104002', InvoiceLineItems#Temp)

INSERT can also be executed by specifying the LineItems column as either a JSON array or a temporary table: 

INSERT INTO InvoiceDetails (CustomerId, LineItems) VALUES ('3255827000000081003', '[{"Name":"Cloth-Jeans3", "ItemId":"3285934000000104097"}]')

INSERT INTO InvoiceDetails (Customerid, lineitems) VALUES ('3285934000000104002', InvoiceLineItems#Temp)

Update

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

INSERT INTO InvoiceLineItems#TEMP (Name, itemid) VALUES ('Cloth-Jeans2', '3285934000000104097')

UPDATE InvoiceDetails SET Customerid = '3285934000000104002', lineitems = 'InvoiceLineItems#Temp'  WHERE InvoiceId = '3285934000000264005'

UPDATE can also be executed by specifying the LineItems column as a JSON array. For example: 

UPDATE InvoiceDetails SET CustomerId = '3285934000000085043', LineItems = '[{"Name":"Cloth-Jeans", "ItemId":"3285934000000104097"}]' WHERE InvoiceId = '3285934000000264005'

Delete

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

DELETE FROM InvoicesDetails WHERE InvoiceId = '3285934000000264005'

Columns

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

Invoices.InvoiceId

Id of an invoice.

InvoiceNumber String False

Number of an invoice.

InvoiceUrl String True

URL of an invoice.

AchPaymentInitiated Boolean True

Check if the Automated Clearing House payment is initiated.

AchSupported Boolean True

Check if Automated Clearing House is supported.

Adjustment Decimal False

Adjustments made to the invoice.

AdjustmentDescription String False

Description of adjustments made to the invoice.

AllowPartialPayments Boolean False

Check if invoice can allow partial payments.

ApproverId String True

Users.UserId

Id of an approver.

ApproversList String True

List of Id of an approver.

AttachmentName String True

Name of the attachment.

Balance Decimal True

The unpaid amount.

BcyAdjustment Decimal True

Adjustment of base currency.

BcyDiscountTotal Decimal True

Total discount applied in base currency.

BcyShippingCharge Decimal True

Shipping charge applied in base currency.

BcySubTotal Decimal True

Sub total of base currency.

BcyTaxTotal Decimal True

Tax total of base currency.

BcyTotal Decimal True

Total Base Currency.

BillingAddress String True

Billing address of an invoice.

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 number 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 code of a billing address.

ContactPersons String False

Contact persons of a contact.

CanSendInMail Boolean True

Check if invoice can be send in mail.

CanSendInvoiceSms Boolean True

Check if invoice can be send in SMS.

ClientViewedTime String True

Last time when client viewed the invoice.

ColorCode String True

Color code of invoice.

ComputationType String True

Computation type of invoice.

ContactCreditLimit Decimal True

Credit limit for a customer of invoice.

ContactCustomerBalance Decimal True

Balance for a customer of invoice.

ContactIsCreditLimitMigrationCompleted Boolean True

ContactIsCreditLimitMigrationCompleted.

ContactUnusedCustomerCredits Decimal True

Unused credits of customer of invoice.

ContactCategory String True

Category of a contact.

ContactPersonsDetails String True

Details of a contact person.

CreatedById String True

Users.UserId

Id of a user who has created invoice.

CreatedDate Date True

Date at which the invoice was created.

CreatedTime Datetime True

Time at which the invoice was created.

CreditsApplied Decimal True

Applied credits for invoice.

CustomFields String False

Custom fields of the contact.

CurrencyCode String True

Currency code of the customer's currency.

CurrencyId String False

Currencies.CurrencyId

Currency Id of the customer's currency.

CurrencySymbol String True

Symbol of currency.

CurrentSubStatus String True

Current sub status of an invoice .

CurrentSubStatusId String True

Current sub status Id of an invoice .

CustomerDefaultBillingAddress String True

Customer default billing address of an invoice.

CustomerDefaultBillingAddressCity String True

City of a billing address.

CustomerDefaultBillingAddressCountry String True

Country of a billing address.

CustomerDefaultBillingAddressFax String True

Fax of a billing address.

CustomerDefaultBillingAddressPhone String True

Phone number of a billing address.

CustomerDefaultBillingAddressState String True

State of a billing address.

CustomerDefaultBillingAddressStateCode String True

State code of a customer default billing address.

CustomerDefaultBillingAddressStreet2 String True

Street two of a customer default billing address.

CustomerDefaultBillingAddressZip String True

ZIP code of a customer default billing address.

CustomerId String False

Contacts.ContactId

Id of the customer or vendor.

CustomerName String False

Name of the customer or vendor.

Date Date False

Date of an invoice.

DebitNotes String False

Debit notes of an invoice.

DeliveryChallans String False

Delivery challans of an invoice.

Discount String False

Discount given to specific item in invoice.

DiscountAppliedOnAmount Decimal True

Discount applied on amount.

DiscountPercent Decimal True

Percent of discount applied.

DiscountTotal Decimal True

Total amount get on discount.

DiscountType String False

Type of discount.

DueDate Date False

Delivery date of the invoice.

EcommOperatorGstNo String True

GST number of the ecommerce operator.

EcommOperatorId String True

Id of the ecommerce operator.

EcommOperatorName String True

Name of the ecommerce operator.

EstimateId String True

Estimates.EstimateId

Id of an estimate.

Ewaybills String True

Electronic way bills of the invoice.

ExchangeRate Decimal False

Exchange rate of the currency.

IncludesPackageTrackingInfo Boolean True

IncludesPackageTrackingInfo.

InprocessTransactionPresent Boolean True

InprocessTransactionPresent.

InvoiceSource String True

InvoiceSource.

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.

GstTreatment String False

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

HasNextInvoice Boolean True

Check if it has next invoice.

IsAutobillEnabled Boolean True

Check if the autobill is enabled for this invoice.

IsClientReviewSettingsEnabled Boolean True

Check if the client review settings is enabled or not.

IsDiscountBeforeTax Boolean False

Check if the invoice is discounted before tax.

IsEmailed Boolean True

Check if the invoice can be emailed.

IsEwayBillRequired Boolean True

Check if the eway bill is required.

IsInclusiveTax Boolean False

Check if the invoice is inclusive tax.

IsPreGst Boolean True

Check if pre GST is applied.

IsTaxable Boolean True

Check if invoice is taxable.

IsViewedByClient Boolean True

Check if the invoice is viewed by client.

InvoicedEstimateId Boolean False

Id of the invoice from which the invoice is created.

LastModifiedById String True

Users.UserId

Id of the user last modified.

LastModifiedTime Datetime True

The time of last modification of the invoice.

LastPaymentDate Date True

Date when last payment was made.

LastReminderSentDate Date True

Date when last reminder was sent for an invoice.

LineItems String False

Line Items.

MerchantGstNo String True

GST number of a merchant.

MerchantId String True

Id of a merchant.

MerchantName String True

Name of a merchant.

NextReminderDateFormatted String True

NextReminderDateFormatted.

NoOfCopies Integer True

Total number of copies for invoice.

Notes String False

Notes for this invoice.

Orientation String True

Orientation of the page.

PageHeight String True

Height of the page.

PageWidth String True

Width of the page.

PaymentDiscount Decimal True

Discount applied on payment.

PaymentExpectedDate Date True

Expected date of payment.

PaymentMade Decimal True

Total amount of payment made.

PaymentOptionsPaymentGateways String False

PaymentOptionsPaymentGateways.

PaymentReminderEnabled Boolean True

Check if the payment reminder is enabled for the invoice.

PaymentTerms Integer False

Net payment term for the customer.

PaymentTermsLabel String False

Label for the paymet due details.

PlaceOfSupply String False

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

PricePrecision Integer True

The precision for the price.

QRCodeIsQREnabled Boolean True

QRCodeIsQREnabled.

QRCodeQRDescription String True

QRCodeQRDescription.

QRCodeQRSource String True

QRCodeQRSource.

QRCodeQRValue Integer True

QRCodeQRValue.

ReaderOfflinePaymentInitiated Boolean True

Check if the payment for offline reader is initiated.

ReasonForDebitNote String True

Description of having the debit note.

RecurringInvoiceId String False

RecurringInvoices.RecurringInvoiceId

Id of a recurring invoice.

ReferenceNumber String False

Reference number of invoice.

RemindersSent Integer True

Total number of reminders sent for this invoice.

ReverseChargeTaxTotal Decimal True

Total amount of reverse charge tax.

RoundoffValue Decimal True

Rounding off the values to precise number.

SalesorderId String True

SalesOrders.SalesorderId

Id of a sales order.

SalesorderItemId String False

SalesOrderLineItems.LineItemId

Id of the tax.

SalespersonId String False

Id of a sales person.

SalespersonName String False

Name of a sales person.

ScheduleTime String True

Time scheduled for the invoice.

ShipmentCharges String True

Charges deducted for shipment.

ShippingAddress String True

Shipment Address.

ShippingAddressAttention String True

Name of a person of shipping address.

ShippingAddressCity String True

City of a shipping address.

ShippingAddressCountry String True

Country of a shipping address.

ShippingAddressFax String True

Fax of a shipping address.

ShippingAddressPhone String True

Phone number of a shipping address.

ShippingAddressState String True

State of a shipping address.

ShippingAddressStreet2 String True

Street two details of a shipping address.

ShippingAddressZip String True

Zip code of a shipping address.

ShippingCharge Decimal False

Shipping charge of invoice.

ShippingBills String True

Shipping bills of invoice.

ShowNoOfCopies Boolean True

Check if the invoice can show number of copies.

Status String True

Status of the invoice.

StopReminderUntilPaymentExpectedDate Boolean True

Check if reminder can be stopped untill the payment expected date.

SubTotal Decimal True

Sub total of the invoice.

SubTotalInclusiveOfTax Decimal True

Subtotal amount which are inclusive of tax.

SubmittedBy String True

Detail of the user who has submitted the invoice.

SubmittedDate Date True

Date when invoice was submitted.

SubmitterId String True

Users.UserId

Id of the invoice submitter.

TaxAmountWithheld Decimal True

Amount withheld for tax.

TaxRegNo String True

Registration number of tax.

TaxSpecification String True

Working of tax when specifying special tax options and tax methods for earnings codes.

TaxTotal Decimal True

Total number ot tax applied in the invoice.

TaxTreatment String False

VAT treatment for the Invoice.

TemplateId String False

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

Total Decimal True

Total of invoices.

TransactionRoundingType String True

Type of round off used for transaction.

Type String True

Types of invoice.

UnusedRetainerPayments Decimal True

Payment of the invoice which is unused.

VatTreatment String False

VAT treatment for the bills.

WriteOffAmount Decimal True

Amount to be write off.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Send Boolean

Send the invoice to the contact person(s) associated with the invoice. Allowed values true and false.

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