PurchaseOrders

Stores purchase orders issued to vendors for inventory or services; not accessible in free or standard editions.

Table-Specific Information

The PurchaseOrders table is only accessible when connected to a ZohoCRM Enterprise Edition account. If using a different edition, this table may not be available.

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

The following example demonstrates a basic insert operation with line item details:

-- Insert a new purchase order with a subject and product line items
INSERT INTO PurchaseOrders (Subject, VendorName_Id, PurchaseItems) VALUES (
  -- This is the value for the Subject column
  'Purchase Order',
  -- This is the value for the VendorName_Id column
  '1021046000000513014', 
  -- This is the JSON string for PurchaseItems
  '[
  {
    "Product_Name": {
      "id": "1021046000000414457"
    },
    "Quantity": 1024,
    "Discount": 99,
    "Tax": 13,
    "List_Price": 176.21,
    "Description": "test description 1"
  }
]'
)

Required fields for REST API: Subject and PurchaseItems are required. These provide the order summary and line item data needed to create a purchase order.

Required fields for BULK API: Subject, VendorName_Id and PurchaseItems are required. These provide the order summary, the vendor's identifier and line item data needed to create a purchase order.

Any field which is not read-only (ReadOnly = false in the table schema) can be inserted.

Please note that the REST API honors only the 'id' field of the 'Product_Name' aggregate in the PurchaseItems column. The BULK API does not accept any other field in this aggregate.

Delete

You must specify the Id column in the WHERE clause to delete a record. The following example demonstrates a delete operation:

-- Delete a purchase order by its unique ID
DELETE FROM PurchaseOrders WHERE Id = '3152079000000153079'

Update

You must specify the Id column in the WHERE clause to update a record. The following example demonstrates an update operation:

-- Update the description for a specific purchase order using its ID
UPDATE PurchaseOrders SET Description = 'Changed from the API.' WHERE Id = '3152079000000492001'

Required fields: Id is required. The update must target a specific record.

Any field which is not read-only (ReadOnly = false in the table schema) can be updated.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	Unique identifier of the purchase order.
RequisitionNumber	String	False	Requisition number for the purchase order.
TrackingNumber	String	False	Tracking number associated with the purchase order.
PONumber	String	False	Purchase order number.
Subject	String	False	Subject line of the purchase order.
VendorName_Id	String	False	Unique identifier of the vendor.
VendorName_Name	String	True	Name of the vendor.
ContactName_Id	String	False	Unique identifier of the contact linked to the purchase order.
ContactName_Name	String	True	Name of the contact linked to the purchase order.
PODate	Date	False	Date the purchase order was created.
DueDate	Date	False	Due date for the purchase order.
Carrier	String	False	Shipping carrier for the purchase order.
ExciseDuty	Decimal	False	Excise duty charged on the purchase order.
SalesCommission	Decimal	False	Sales commission applied to the purchase order.
Status	String	False	Current status of the purchase order.
PurchaseOrderOwner_Id	String	False	Unique identifier of the purchase order owner.
PurchaseOrderOwner_FirstName	String	True	First name of the purchase order owner.
PurchaseOrderOwner_LastName	String	True	Last name of the purchase order owner.
PurchaseOrderOwner_Email	String	True	Email address of the purchase order owner.
PurchaseOrderOwner_Name	String	True	Full name of the purchase order owner.
CreatedBy_Id	String	False	Unique identifier of the user who created the record.
CreatedBy_FirstName	String	True	First name of the user who created the record.
CreatedBy_LastName	String	True	Last name of the user who created the record.
CreatedBy_Email	String	True	Email address of the user who created the record.
CreatedBy_Name	String	True	Full name of the user who created the record.
ModifiedBy_Id	String	False	Unique identifier of the user who last modified the record.
ModifiedBy_FirstName	String	True	First name of the user who last modified the record.
ModifiedBy_LastName	String	True	Last name of the user who last modified the record.
ModifiedBy_Email	String	True	Email address of the user who last modified the record.
ModifiedBy_Name	String	True	Full name of the user who last modified the record.
CreatedTime	Datetime	False	Timestamp when the record was created.
ModifiedTime	Datetime	False	Timestamp when the record was last modified.
Tag	String	False	Tags associated with the purchase order.
BillingStreet	String	False	Billing street address.
ShippingStreet	String	False	Shipping street address.
BillingCity	String	False	Billing city.
ShippingCity	String	False	Shipping city.
BillingState	String	False	Billing state or region.
ShippingState	String	False	Shipping state or region.
BillingCode	String	False	Billing postal code.
ShippingCode	String	False	Shipping postal code.
BillingCountry	String	False	Billing country.
ShippingCountry	String	False	Shipping country.
SubTotal	String	True	Subtotal amount before discounts and taxes.
Discount	Decimal	False	Discount amount applied to the purchase order.
Tax	Decimal	False	Tax amount applied to the purchase order.
Adjustment	Decimal	False	Any adjustment amount applied.
GrandTotal	String	True	Final total amount of the purchase order after all calculations.
TermsandConditions	String	False	Terms and conditions associated with the purchase order.
Description	String	False	Additional description or notes for the purchase order.
PurchaseItems	String	False	List of items included in the purchase order.
Locked	Bool	True	Indicates whether the record is locked for editing.
LastActivityTime	Datetime	False	Timestamp of the most recent activity on the record.

Pseudo-Columns

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


Name	Type	Description
DuplicateCheckFields	String	The fields used to check for duplicate records during an upsert operation. The REST API supports multiple fields; the BULK API supports only one.
Trigger	String	Used to trigger automation rules when inserting the record into the CRM account.
CustomViewId	Long	Custom view identifier used to filter this record. Only works when the useCOQL parameter is set to false.

ODBC Driver for Zoho CRM