Power BI Connector for Zoho Inventory

Build 24.0.9062

Invoices

Read, Insert, Update and Delete Invoices.

Table Specific Information

Select

The 本製品 will use the Zoho Inventory API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the 本製品.

  • Id supports the '=' comparison.
  • CurrencyId supports the '=' comparison.
  • CustomerId supports the '=' comparison.
  • SalespersonId supports the '=' comparison.
  • Balance supports the '=' comparison.
  • CreatedTime supports the '=' comparison.
  • CustomerName supports the '=,LIKE' comparisons.
  • Date supports the '=,>,<,<=,>=' comparisons.
  • DueDate supports the '=,>,<,<=,>=' comparisons.
  • InvoiceNumber supports the '=' comparison.
  • ReferenceNumber supports the '=' comparison.
  • Status supports the '=' comparison.
  • TaxAmountWithheld supports the '=' comparison.
  • Total supports the '=' comparison.
  • Email supports the '=' comparison.
  • RecurringInvoiceId supports the '=' comparison.
  • ItemId supports the '=' comparison.
  • ItemName supports the '=,LIKE' comparisons.
  • ItemDescription supports the '=,LIKE' comparisons.
  • InvoiceFilter supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Invoices WHERE Id = '3350895000000089001'

SELECT * FROM Invoices WHERE InvoiceNumber = '1937623621'

SELECT * FROM Invoices WHERE ItemId = '867623621'

SELECT * FROM Invoices WHERE ItemName = 'name'

SELECT * FROM Invoices WHERE ItemDescription = 'description'

SELECT * FROM Invoices WHERE ReferenceNumber = '30089001'

SELECT * FROM Invoices WHERE CustomerName = 'Mr. First'

SELECT * FROM Invoices WHERE RecurringInvoiceId = '9272623621'

SELECT * FROM Invoices WHERE Email = '[email protected]'

SELECT * FROM Invoices WHERE Total = '1960'

SELECT * FROM Invoices WHERE Balance = '100'

SELECT * FROM Invoices WHERE Date = '2013-12-03'

SELECT * FROM Invoices WHERE DueDate = '2013-12-03'

SELECT * FROM Invoices WHERE Status = 'Paid'

SELECT * FROM Invoices WHERE CustomerId = '987123657483'

Insert

Insert can be executed by specifying the CustomerId and LineItems column. The columns that are not read-only can be inserted optionally. Following is an example of how to insert into this table.

INSERT INTO Invoices (CustomerId, LineItems) VALUES (3285934000000085043, '[{\"name\": \"I Phone\", \"description\": \"500GB, USB 2.0 interface 1400 rpm, protective hard case.\"}]')

Update

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Invoices SET Email = '[email protected]', CustomerId = '8779', LineItems = [{\"name\": \"I Phone\"}] WHERE Id = '3285934000000136008'

Delete

Delete can be executed by specifying the Id in the WHERE Clause For example:

DELETE FROM Invoices WHERE Id = '3350895000000089001'

Columns

Name Type ReadOnly References Description
Id [KEY] Long True

The ID of the invoice.

CurrencyId Long False

Currencies.Id

The id of the currency.

CustomerId Long False

Contacts.Id

ID of the customer the invoice has to be created..

SalespersonId String True

ID of the salesperson linked to invoice.

TemplateId String True

ID of the pdf template associated with the invoice..

AttachmentName String True

Name of the file attached

AchPaymentInitiated Boolean True

To check initiation of ACH Payment.

Adjustment Integer True

Adjustments made to the invoice..

AllowPartialPayments Integer True

Boolean to check if partial payments are allowed for the contact.

Balance Double True

The unpaid amount

ClientViewedTime String True

Time when client viewed the statement.

CreatedTime Datetime False

The time of creation of the invoices.

CurrencyCode String True

The currency code in which the invoice is created..

CustomerName String False

The name of the customer. Maximum length [100].

CanSendInMail String False

To check if attachment can be sent in email

Discount Float True

Discount applied to the invoice. It can be either in % or in amount.

Date Date True

Invoice date. Default date format is yyyy-mm-dd..

IsPreGst String True

Applicable for transactions that fall before july 1, 2017

GstNo String True

15 digit GST identification number of the customer.

GstTreatment String True

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

Adjustmentdesc String True

Customize the adjustment desc. E.g. Rounding off.

PaymentReminderEnabled Boolean True

Boolean to check if reminders have been enabled.

PaymentMade String True

The amount paid

PaymentOptions String True

Payment options available for payment

PricePrecision String True

The precision value on the price

IsDiscountBeforeTax Boolean True

Check if discount is exclusive of tax

DiscountType String False

Type of discount. Allowed values are entity_level,item_level.

IsInclusiveTax Boolean True

To check if discount is inclusive of tax.

InvoiceUrl String True

Url of invoice as a link.

PaymentTerms Integer True

Payment terms in days.

PaymentTermsLabel String True

Used to override the default payment terms label..

DueDate Date True

Due date of the invoices. Default date format is yyyy-mm-dd..

DueDays String True

Due days.

ExchangeRate Integer True

Exchange rate of the currency.

HasAttachment Boolean True

To check if invoice has an attachment.

InvoiceNumber String False

An unique number given to the invoice. Maximum length [100].

IsEmailed Boolean True

Boolean check to see if the mail has been sent.

IsViewedByClient Boolean True

Check if invoice is viewed by client

LastModifiedTime Datetime True

Date of last modification of the invoice.

LastPaymentDate String True

The last payment date of the invoice.

Notes String False

The notes added below expressing gratitude or for conveying some information.

LastReminderSentDate String True

The date the last email was sent.

ContactPersons String False

Contact Person listed in invoice.

Taxes Double True

List of the taxes levied.

LineItems String False

Items listed in invoice.

CustomFields String False

Custom Fields in invoice.

PaymentExpectedDate String True

The expected date of payment.

ReferenceNumber String False

The reference number of the invoice.

RemindersSent Integer True

The number of reminders sent.

SalespersonName String True

Name of the salesperson. Maximum length [200]

ShippingCharge Integer True

Shipping charges applied to the invoice. Maximum length [100].

BillingAddress Integer False

Billing address of the contact

ShippingAddress Integer False

Shipping address of the contact

Status String True

Search invoices by invoice status.

使用できる値は次のとおりです。sent, draft, overdue, paid, void, unpaid, partially_paid, viewed

SubTotal Double True

The sub total of the all items

TaxTotal Double True

The total amount of the tax levied

Terms String True

The terms added below expressing gratitude or for conveying some information.

PlaceOfSupply String False

Place where the goods/services are supplied to. (If not given, place of contact given for the contact will be taken)

TemplateName String True

Name of the invoice template used

TaxAmountWithheld Float True

The tax amount which has been withheld

Total Double True

The total amount to be paid.

WriteOffAmount Integer True

The write off amount. i.e. the amount which is not expected to be returned. Like a bad debt.

CreditsApplied Float False

The credits applied.

Email String True

Email address of an invoice.

RecurringInvoiceId String True

ID of the recurring invoice from which the invoice is created.

ItemId String False

Items.ItemId

Id of an item.

ItemName String True

Name of an item.

ItemDescription String True

Description of an item.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name Type Description
InvoiceFilter String

Filter invoices by any status or payment expected date.

使用できる値は次のとおりです。Status.All, Status.Sent, Status.Draft, Status.OverDue, Status.Paid, Status.Void, Status.Unpaid, Status.PartiallyPaid, Status.Viewed, Date.PaymentExpectedDate

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