Power BI Connector for HubSpot

Build 24.0.9060

Lineitems

Retrieve the available Lineitems in HubSpot. This table supports both OAuth and PrivateAppToken authentication.

Table Specific Information

In HubSpot, line items can be thought of as a subset of products. When a product is attached to a deal, it becomes a line item. You can create line items that are unique to an individual quote, but they will not be added to your product library.

SELECT

When selecting line items, they can be filtered by columns marked below as 'Filterable'. For those columns, the supported server-side operators are =, !=, <, <=, >, >=. Further filtering is available using AND and OR combinations. A maximum of 2 OR operators are supported server-side, with each of these operators supporting up to 2 AND operators inside of them. For example: 

SELECT * FROM LineItems WHERE [Amount] > 1000 AND [Quantity] >= 10
SELECT * FROM LineItems WHERE [Product price] = 40 OR [Product description] = 'White t-shirt'

In the below server-offloading examples, "{Column}" represents any column you'd like to filter and "{Value}" represents any value you want to filter it on: 

SELECT * FROM LineItems WHERE {Column} = {Value} AND {Column} < {Value} AND {Column} > {Value}
SELECT * FROM LineItems WHERE {Column} != {Value} AND {Column} >= {Value} AND {Column} <= {Value}
SELECT * FROM LineItems WHERE ({Column} = {Value} AND {Column} = {Value}) OR ({Column} != {Value} AND {Column} >= {Value})
SELECT * FROM LineItems WHERE ({Column} != {Value} AND {Column} >= {Value} AND {Column} <= {Value}) OR ({Column} != {Value} AND {Column} >= {Value} AND {Column} <= {Value}) OR ({Column} != {Value} AND {Column} >= {Value} AND {Column} <= {Value})

INSERT


INSERT INTO LineItems (Description, [Product ID]) VALUES ('description', '33318601')

UPDATE


UPDATE LineItems SET Description = 'new description' WHERE ID = '168209183'

DELETE


DELETE FROM Deals WHERE ID = '168209183'

Columns

Name Type ReadOnly References Filterable Description
Id [KEY] Long False True

Unique identifier of the object.

Archived Bool True False

Whether the object is archived or not.

CreatedAt Datetime True False

When the object was created.

UpdatedAt Datetime True False

When the object was last updated.

Net price Decimal True True

The amount of a line item.

Create Date Datetime False True

The date the line item was created.

Description String False True

Full description of product.

Unit discount Decimal False True

The discount amount applied.

Annual contract value Decimal True True

The annual contract value _ACV_ of this product.

All accessible team IDs String True True

The team IDs_ including up the team hierarchy_ corresponding to all owner referencing properties for this object_ both default and custom.

Business units String False True

The business units this record is assigned to.

All owner ids String True True

The value of all owner referencing properties for this object_ both default and custom.

All team ids String True True

The team ids corresponding to all owner referencing properties for this object_ both default and custom.

Allow buyer selected quantity String True True

Whether buyer selected quantity is to be enabled or not for a LineItem.

Annual recurring revenue Decimal True True

The annual recurring revenue _ARR_ of this product.

Delayed billing start by days Decimal False True

Number of days billing should be delayed by. It allows the customers to start billing sometime in the future.

Delayed billing start by months Decimal False True

Number of months billing should be delayed by. It allows the customers to start billing sometime in the future.

Start billing terms String True True

The type of billing start delay. It can be a fixed date in the future_ relative delay in number of days or months.

Unit cost Decimal False True

The amount that sold goods cost the HubSpot customer.

Created by user ID Decimal True True

The user that created this object. This value is automatically set by HubSpot and may not be modified.

Object create date_time Datetime True True

The date and time at which this object was created. This value is automatically set by HubSpot and may not be modified.

Discount Percentage Decimal False True

The discount percentage applied.

External LineItem id Decimal True True

The ID of a line item.

Image URL String False True

Product images.

Last Modified Date Datetime True True

The date any property on this product was modified.

Currency String False True

Currency code for the line item.

Margin Decimal True True

The margin value of this product.

Annual contract value margin Decimal True True

The margin annual contract value of this product.

Annual recurring revenue margin Decimal True True

The margin annual recurring revenue of this product.

Monthly recurring revenue margin Decimal True True

The margin monthly recurring revenue of this product.

Total contract value margin Decimal True True

The margin total contract value of this product.

Merged object IDs String True True

The list of object IDs that have been merged into this object. This value is automatically set by HubSpot and may not be modified.

Monthly recurring revenue Decimal True True

The monthly recurring revenue _MRR_ of this product.

Record ID Decimal True True

The unique ID for this record. This value is automatically set by HubSpot and may not be modified.

Position on quote Decimal False True

The order which the line item appears on the quotes.

Pre Discount Amount Decimal True True

The pre_discount amount of a line item.

Product ID Decimal False True

ID of the product this was copied from.

Read Only Object Bool True True

Is the object read only.

End Date Date True True

Recurring billing end date for a line item.

Number of payments Decimal True True

Number of payments for the given period on a Line Item.

Term String False True

Product recurring billing duration.

Billing start date Date False True

Recurring billing start date for a line item.

Billing terms String True True

If there are fixed number payments or it goes on until cancelled.

SKU String False True

Unique product identifier.

Sync amount Decimal True True

The amount set by Ecommerce sync.

Total contract value Decimal True True

The total contract value _TCV_ of this product.

Term in months Decimal True True

The number of months in the term of this product.

Calculated Total Discount Decimal True True

Calculated total Discount for the line item taking in consideration the flat amount and discount percentage.

Unique creation key String True True

Unique property used for idempotent creates.

Updated by user ID Decimal True True

The user that last updated this object. This value is automatically set by HubSpot and may not be modified.

URL String False True

Product url.

User IDs of all notification followers String True True

The user IDs of all users that have clicked follow within the object to opt_in to getting follow notifications

User IDs of all notification unfollowers String True True

The user IDs of all object owners that have clicked unfollow within the object to opt_out of getting follow notifications

User IDs of all owners String True True

The user IDs of all owners of this object.

Variant id of the product Decimal True True

Varient id of the shopify product.

Owner Assigned Date Datetime True True

The most recent date an owner was assigned to this object. This is set automatically by HubSpot and can be used for segmentation and reporting.

Owner String False True

The owner of the object.

HubSpot Team String True True

The primary team of the owner.

Name String False True

Product name.

Unit price Decimal False True

Cost of product.

Quantity Decimal False True

How many units of a product are in this line item.

Billing frequency String False True

How often a line item with recurring billing is billed. It informs the pricing calculation for deals and quotes. Line items with one_time billing aren_t included.

Tax Decimal False True

The tax amount applied.

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