Invoices

Read, Insert, Update and Delete Invoices.

Table Specific Information

Select

The connector 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 connector.

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. The allowed values are 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

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

InvoiceFilter

String

Filter invoices by any status or payment expected date.

The allowed values are Status.All, Status.Sent, Status.Draft, Status.OverDue, Status.Paid, Status.Void, Status.Unpaid, Status.PartiallyPaid, Status.Viewed, Date.PaymentExpectedDate.

Power BI Connector for Zoho Inventory