Tableau Connector for Zoho Books

Build 24.0.9060

PurchaseOrderDetails

List, add, update and delete details of a purchase order.

Table Specific Information

Select

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

  • PurchaseorderId supports the '=' and IN operators.

NOTE: PurchaseorderId is required to query PurchaseOrderDetails.

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

For example: 

    SELECT * FROM PurchaseOrderDetails WHERE PurchaseorderId = '1894553000000087078'
	SELECT * FROM PurchaseOrderDetails WHERE PurchaseorderId IN (SELECT PurchaseorderId FROM PurchaseOrders)
	SELECT * FROM PurchaseOrderDetails WHERE PurchaseorderId IN ('1894553000000087078','1894553000000087079')

Insert

INSERT can be executed by specifying the Vendorid or 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 PurchaseorderLineItems#TEMP (Name, itemid, rate, quantity, accountid) VALUES ('Cloth-Jeans', '3285934000000104097', '1700', '1', '3285934000000034001')

INSERT INTO PurchaseorderDetails (Vendorid, lineitems) VALUES ('3285934000000104023', PurchaseorderLineItems#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 PurchaseorderDetails (VendorId, LineItems) VALUES ('3255827000000081003', '[{"Name":"Cloth-Jeans3", "ItemId":"3285934000000104097", "Rate":"1700", "Quantity":"1", "AccountId":"3285934000000034001"}]')

Update

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

INSERT INTO PurchaseorderLineItems#TEMP (Name, itemid, rate, quantity, accountid) VALUES ('Cloth-Jeans', '3285934000000104097', '1700', '1', '3285934000000034001')

UPDATE PurchaseOrderDetails SET Vendorid = '3285934000000104002', lineitems = 'PurchaseorderLineItems#Temp' WHERE PurchaseorderId = '3285934000000264005'

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 PurchaseOrderDetails SET Vendorid = '3285934000000104002', LineItems = '[{"Name":"Cloth-Jeans", "ItemId":"3285934000000104097", "Rate":"1700", "Quantity":"1", "AccountId":"3285934000000034001"}]' WHERE PurchaseorderId = '3285934000000264005'

Delete

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

DELETE FROM PurchaseOrderDetails WHERE PurchaseOrderId = '3350895000000089001'

Columns

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

PurchaseOrders.PurchaseorderId

Id of a purchase order.

PurchaseorderNumber String False

Number of purchase order.

ReferenceNumber String False

Reference number of purchase order.

Adjustment Decimal True

Adjustments made to the purchase order.

AdjustmentDescription String True

Description of adjustments made to the purchase order.

ApproverId String True

Users.UserId

Id of an approver.

ApproversList String True

List of approvers.

AttachmentName String True

Name of the attachment.

Attention String False

Name of a person in purchase order.

BilledStatus String True

Status of bill.

BillingAddressId Long False

Id of the Billing Address.

BillingAddress String True

Billing address of a purchase order.

BillingAddressAttention String True

Name of the person of bill order.

BillingAddressCity String True

City of billing address.

BillingAddressCountry String True

Country of billing address.

BillingAddressFax String True

Fax number of billing address.

BillingAddressPhone String True

Phone number of billing address.

BillingAddressState String True

State of billing address.

BillingAddressStreet2 String True

Street two of billing address.

BillingAddressZip String True

Zip code of billing address.

Bills String True

Bills.

CanMarkAsBill Boolean True

Check if purchase order can be mark as bill.

CanMarkAsUnbill Boolean True

Check if purhcase order can be mark as unbill.

CanSendInMail Boolean True

Check if purchase order can be sent in mail.

ClientViewedTime Datetime True

Last time when client viewed the purchase order.

ColorCode String True

Color code.

ContactCategory String True

Category of contacts.

CreatedById String True

Users.UserId

Contact Id who have created this purchase order.

CreatedTime Datetime True

Time at which the purchase order was created.

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

Currency symbol of the customer's currency.

CurrentSubStatus String True

Current sub status of a purchase order.

CurrentSubStatusId String True

Current sub status Id of a purchase order.

ContactPersons String False

Contact persons of a contact.

CustomFields String False

Custom Fields defined for Journal.

Date Date False

Purchase order date.

Documents String False

List of files to be attached to a particular transaction.

DeliveryAddress String True

Delivery address.

DeliveryAddress1 String True

Delivery address one.

DeliveryAddress2 String True

Delivery address two.

DeliveryAddressCity String True

City of delivery address.

DeliveryAddressCountry String True

Country of delivery address.

DeliveryAddressOrganizationAddressId String True

Id or organization address of delivery address.

DeliveryAddressPhone String True

Phone number of delivery address.

DeliveryAddressState String True

State of delivery address.

DeliveryAddressZip String True

Zip code of delivery address.

DeliveryCustomerId String False

Contacts.ContactId

Id of a customer of delivery address.

DueDate Date False

Delivery date of purchase order..

DeliveryDate Date False

Date of delivery.

DeliveryOrgAddressId String False

Delivery address Id of an organization.

Discount String False

Discount given to specific item in purchase order.

DiscountAccountId String False

BankAccounts.AccountId

Account Id of discount.

DiscountAmount Decimal True

Amount of discount.

DiscountAppliedOnAmount Double True

Discount applied on amount.

ExchangeRate Decimal False

Exchange rate of the currency.

ExpectedDeliveryDate Date True

Expected delivery date of purchased product.

HasQtyCancelled Boolean True

Check if the quantity of a purchase order has been cancelled.

IsDiscountBeforeTax Boolean False

Check if purchase order applied discount before tax.

IsDropShipment Boolean True

Check if purchase order have drop shipment.

IsEmailed Boolean True

Check if purchase order is emailed or not.

IsInclusiveTax Boolean False

Check if the purchase order is inclusive tax.

IsPreGst Boolean True

Check if purchase order includes pre GST.

IsViewedByClient Boolean True

Check if purchase order is viewed by client.

IsUpdateCustomer Boolean False

Check if customer should be updated.

LastModifiedTime Datetime True

The time of last modification of the purchase order.

Notes String False

Notes for this purchase order.

OrderStatus String True

Status of order.

Orientation String True

Orientation of the page.

PageHeight String True

Height of the page.

PageWidth String True

Width of the page.

PricePrecision Integer True

The precision for the price.

PricebookId String False

Id of the pricebook.

SalesorderId String False

SalesOrders.SalesorderId

Id of the Sales Order.

SalesOrders String True

SalesOrders.

ShipVia String False

Mode of shipping the item.

ShipViaId String True

Id of mode through which shipping was done of items.

Status String True

Status of the purchase order.

SubTotal Decimal True

Sub total of Purhcase order.

SubTotalInclusiveOfTax Decimal True

Subtotal amount which are inclusive of tax.

SubmittedBy String True

Detail of the user who has submitted the purchase order.

SubmittedDate Date True

Date of the submission.

SubmitterId String True

Users.UserId

Id of the submitter.

TaxTotal Decimal True

Total amount of tax.

Taxes String True

Taxes.

TemplateId String False

Id of the template.

TemplateName String True

Name of the template.

TemplateType String True

Type of template.

Terms String False

Terms and Conditions apply of a purchase order.

Total Decimal True

Total of purchase orders.

TotalQuantity Integer True

TotalQuantity.

VendorId String False

Id of the vendor the purchase order has been made.

VendorName String True

Name of the vendor the purchase order has been made.

GstTreatment String False

Choose whether the vendor credit is GST registered/unregistered/consumer/overseas.

VatTreatment String False

VAT treatment for the vendor credit.

TaxTreatment String False

VAT treatment for the Vendor Credit.

GstNo String False

GST number.

SourceOfSupply String False

Source of supply.

PlaceOfSupply String False

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

DestinationOfSupply String False

Place where the goods/services are supplied to.

LineItems String False

Line items of an estimate.

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