Explore the Lightspeed Retail (R-Series) API data model

See how the various Lightspeed Retail (R-Series) endpoints relate to each other, using our handy Entity Relationship Diagram

Try our interactive ERD Or, browse below

Pulling data from the Lightspeed Retail (R-Series) API is only the first part of your reporting. You also need to understand the relationships between different endpoints - something that APIs typically fail to convey.

At SyncHub, we not only sync your Lightspeed Retail (R-Series) API endpoints down into a reportable format, we also describe their relationships (e.g. in terms of foreign keys) so that you can quickly get a grasp of your data and how it fits together. This page is intended as technical documentation of these relationships.

Endpoints

Category

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Category/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CategoryID int - -
CreateTime datetime - -
TimeStamp datetime - -
ParentID int Category → CategoryID -
NodeDepth string - -
FullPathName string - -
LeftNode int - -
RightNode int - -

Contact

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Customer/

Column Type Relates to Notes
RemoteID string - -
CustomerRemoteID string Customer → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ContactID int - -
NoEmail boolean - -
NoPhone boolean - -
NoMail boolean - -
WebsiteUrl string - -
PhoneHome string - -
PhoneWork string - -
PhoneMobile string - -
PhonePager string - -
PhoneFax string - -
PrimaryEmail string - -
SecondaryEmail string - -
Address1 string - -
Address2 string - -
City string - -
State string - -
Zip string - -
Country string - -
CountryCode string - -
StateCode string - -

CreditAccount

Documentation: https://developers.lightspeedhq.com/retail/endpoints/CreditAccount/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
Description string - -
CreditAccountID long - -
Code string - -
CreditLimit decimal - -
GiftCard boolean - -
Archived boolean - -
CustomerID long Customer → CustomerID -
TimeStamp datetime - -
GiftCardUuid guid - -
Balance decimal - -

Customer

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Customer/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
CustomerID long - -
CreateTime datetime - -
TimeStamp datetime - -
Archived boolean - -
FirstName string - -
LastName string - -
Title string - -
Company string - -
Dob datetime - -
CompanyRegistrationNumber string - -
VatNumber string - -
CreditAccountID int -
  • 7 Jul 2020 Foreign Key - Refers to `CreditAccount` (not implemented).
CustomerTypeID int CustomerType → CustomerTypeID -
DiscountID int Discount → DiscountID -
TaxCategoryID int TaxCategory → TaxCategoryID -
ContactID int Contact → ContactID -

CustomerType

Documentation: https://developers.lightspeedhq.com/retail/endpoints/CustomerType/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomerTypeID int - -
DiscountID int Discount → DiscountID -
TaxCategoryID int TaxCategory → TaxCategoryID -

Discount

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Discount/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
DiscountID int - -
CreateTime datetime - -
TimeStamp datetime - -
Archived boolean - -
DiscountAmount decimal - -
DiscountPercent decimal - -
RequireCustomer boolean - -

Employee

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Employee/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
EmployeeID long - -
TimeStamp datetime - -
Archived boolean - -
FirstName string - -
LastName string - -
LockOut boolean - -
ContactID int -
  • 7 Jul 2020 Foreign Key - Refers to `Contact` (not implemented).
ClockInEmployeeHoursID int - -
EmployeeRoleID int -
  • 7 Jul 2020 Foreign Key - Refers to `EmployeeRole` (not implemented).
LimitToShopID long Shop → ShopID -
LastShopID long Shop → ShopID -
LastSaleID long Sale → SaleID -
LastRegisterID int Register → RegisterID -

InventoryCount

Documentation: https://developers.lightspeedhq.com/retail/endpoints/InventoryCount/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
InventoryCountID int - -
TimeStamp datetime - -
Archived boolean - -
ShopID long Shop → ShopID -

InventoryCountItem

Documentation: https://developers.lightspeedhq.com/retail/endpoints/InventoryCountItem/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
InventoryCountItemID int - -
TimeStamp datetime - -
InventoryCountID int InventoryCount → InventoryCountID -
ItemID long Item → ItemID -
Qty decimal - -
EmployeeID long Employee → EmployeeID -

InventoryLog

Documentation: https://developers.lightspeedhq.com/retail/endpoints/InventoryLog/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
InventoryLogID int - -
CreateTime datetime - -
QohChange decimal - -
CostChange decimal - -
Automated boolean - -
CausedNegative boolean - -
Reason string - -
EmployeeID long Employee → EmployeeID -
ItemID long Item → ItemID -
ShopID long Shop → ShopID -
OrderID int Order → OrderID -
TransferID long InventoryTransfer → TransferID
  • 27 Sep 2023 Foreign Key - Previously referred to `Transfer' (deprecated), now refers to InventoryTransfer
SaleID long Sale → SaleID -
InventoryCountID int InventoryCount → InventoryCountID -
CustomerID long Customer → CustomerID -
VendorReturnID int -
  • 27 Sep 2023 Foreign Key - References VenderReturn (not implemented)

InventoryTransfer

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Inventory-Transfer/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
TransferID long - -
CreateTime datetime - -
TimeStamp datetime - -
Archived boolean - -
Note string - -
Status string - -
SentOn datetime - -
NeedBy datetime - -
SendingShopID long Shop → ShopID -
SentByEmployeeID long Employee → EmployeeID -
ReceivingShopID long Shop → ShopID -
CreatedByEmployeeID long Employee → EmployeeID -

InventoryTransferItem

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Inventory-Transfer-TransferItems/

Column Type Relates to Notes
RemoteID string - -
InventoryTransferRemoteID string InventoryTransfer → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
TransferID long InventoryTransfer → TransferID -
TransferItemID long - -
ItemID long Item → ItemID -
TimeStamp datetime - -
ToSend decimal - -
ToReceive decimal - -
Sent decimal - -
Received decimal - -
SentValue decimal - -
ReceivedValue decimal - -
Comment string - -

ItemShop

Column Type Relates to Notes
RemoteID string - -
ItemRemoteID string Item → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ItemShopID int - -
TimeStamp datetime - -
ItemID long Item → ItemID -
ShopID long Shop → ShopID -
Qoh int - -
Backorder int - -
ComponentQoh int - -
ComponentBackorder int - -
ReorderPoint int - -
ReorderLevel int - -
Layaways int - -
Specialorders int - -
Workorders int - -

ItemPrice

Column Type Relates to Notes
RemoteID string - -
ItemRemoteID string Item → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ItemID long Item → ItemID -
UseTypeID int -
  • 13 Jul 2020 The documentation mentions that this is the `foreign key for the pricing level for this item price`, but I could not find a separate endpoint for it.
Amount string - -
UseType string - -

ItemTag

Column Type Relates to Notes
RemoteID string - -
ItemRemoteID string Item → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ItemID long Item → ItemID -
TagName string - -

ItemCustomFieldValue

Column Type Relates to Notes
RemoteID string - -
ItemRemoteID string Item → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomFieldValueID int - -
ItemID long Item → ItemID -
CustomFieldID int ItemCustomField → CustomFieldID -
Type string -
  • 28 Apr 2021 For example, 'boolean', 'multiple_choice' etc
CustomFieldChoiceID int ItemCustomFieldChoice → CustomFieldID
  • 28 Apr 2021 Is only available if we have a multi/single choice field type
Choice string - -

Item

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Item/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
ItemID long - -
CreateTime datetime - -
TimeStamp datetime - -
SystemSku string - -
DefaultCost decimal - -
AvgCost decimal - -
Discountable boolean - -
Tax boolean - -
Archived boolean - -
ItemType string - -
Serialized boolean - -
ModelYear int - -
Upc string - -
Ean string - -
CustomSku string - -
ManufacturerSku string - -
PublishToEcom boolean - -
CategoryID int Category → CategoryID -
TaxClassID int -
  • 13 Jul 2020 Foreign key - References `TaxClass` (not implemented).
DepartmentID int -
  • 13 Jul 2020 Foreign key - References `Department` (not implemented). The endpoint either doesn't exist or could not be found in the documentation.
ItemMatrixID int ItemMatrix → ItemMatrixID -
ManufacturerID int Manufacturer → ManufacturerID -
SeasonID int -
  • 13 Jul 2020 Foreign key - References `Season` (not implemented).
DefaultVendorID int Vendor → VendorID -
Attribute1 string - -
Attribute2 string - -
Attribute3 string - -
ItemAttributeSetID int ItemAttributeSet → ItemAttributeSetID -
ItemNote string - -
ItemNoteIsPublic boolean - -
ItemNoteTimeStamp datetime - -

ItemAttributeSet

Documentation: https://developers.lightspeedhq.com/retail/endpoints/ItemAttributeSet/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
ItemAttributeSetID int - -
Archived boolean - -
AttributeName1 string - -
AttributeName2 string - -
AttributeName3 string - -
System boolean - -

ItemCustomFieldChoice

Column Type Relates to Notes
RemoteID string - -
ItemCustomFieldRemoteID string ItemCustomField → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomFieldID int ItemCustomField → CustomFieldID -
CustomFieldChoiceID int - -
Value string - -
CanBeDeleted boolean - -

ItemCustomField

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Item-CustomField/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomFieldID int - -
Archived boolean - -
Type string - -
Uom string - -
DecimalPrecision int - -

ItemMatrixPrice

Column Type Relates to Notes
RemoteID string - -
ItemMatrixRemoteID string ItemMatrix → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ItemMatrixID int ItemMatrix → ItemMatrixID -
UseTypeID int -
  • 13 Jul 2020 From APIDocs: `Foreign key for the pricing level for this item price`. No endpoint exists or its missing from the documentation
Amount string - -
UseType string - -

ItemMatrix

Documentation: https://developers.lightspeedhq.com/retail/endpoints/ItemMatrix/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
ItemMatrixID int - -
TimeStamp datetime - -
Archived boolean - -
Tax boolean - -
DefaultCost decimal - -
ItemType string - -
Serialized boolean - -
ModelYear int - -
ItemAttributeSetID string -
  • 13 Jul 2020 Foreign key - References `ItemAttributeSet` (not implemented).
ManufacturerID int Manufacturer → ManufacturerID -
CategoryID int Category → CategoryID -
DefaultVendorID int Vendor → VendorID -
TaxClassID int -
  • 13 Jul 2020 Foreign key - References `TaxClass` (not implemented).
SeasonID int -
  • 13 Jul 2020 Foreign key - References `Season` (not implemented).
DepartmentID int -
  • 13 Jul 2020 Foreign key - References `Department (not implemented). The api document is missing!

ItemVendor

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Item/

Column Type Relates to Notes
RemoteID string - -
ItemRemoteID string Item → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ItemID long Item → ItemID -
ItemVendorID int -
  • 2 Nov 2023 Unique ID for the item-vendor relationship
TimeStamp datetime - -
Value string - -
Cost decimal - -
VendorID int Vendor → VendorID -

Manufacturer

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Manufacturer/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
ManufacturerID int - -
CreateTime datetime - -
TimeStamp datetime - -

Order

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Order/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OrderID int - -
TimeStamp datetime - -
Archived boolean - -
OrderedDate datetime - -
ReceivedDate datetime - -
ArrivalDate datetime -
  • 2 Jun 2021 The arrivalDate field is not present in the API response, if its value is NULL
ShipInstructions string - -
StockInstructions string - -
ShipCost decimal - -
OtherCost decimal - -
Complete boolean - -
Discount decimal - -
TotalDiscount decimal - -
TotalQuantity decimal - -
RefNum string - -
VendorID int Vendor → VendorID -
NoteID int -
  • 13 Jul 2020 Foreign key - could not find the relation this is referencing. From APIDocs: ` the foreign key for the notes for this order`.
ShopID long Shop → ShopID -

OrderCustomField

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Order-CustomField/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomFieldID int - -
Archived boolean - -
Type string - -
Uom string - -
DecimalPrecision int - -

OrderCustomFieldChoice

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Order-CustomField-CustomFieldChoice/

Column Type Relates to Notes
RemoteID string - -
OrderCustomFieldRemoteID string OrderCustomField → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomFieldID int OrderCustomField → CustomFieldID -
CustomFieldChoiceID int - -
Value string - -
CanBeDeleted boolean - -

OrderLine

Documentation: https://developers.lightspeedhq.com/retail/endpoints/OrderLine/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OrderLineID int - -
TimeStamp datetime - -
Quantity decimal - -
Price decimal - -
OriginalPrice decimal - -
CheckedIn string - -
NumReceived decimal - -
Total decimal - -
OrderID int Order → OrderID -
ItemID long Item → ItemID -

OrderShipmentItem

Documentation: https://developers.lightspeedhq.com/retail/endpoints/ShipmentItem/

Column Type Relates to Notes
RemoteID string - -
OrderShipmentRemoteID string OrderShipment → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OrderShipmentItemID int - -
OrderShipmentID int OrderShipment → OrderShipmentID -
CreateTime datetime - -
TimeStamp datetime - -
QtyReceived decimal - -
VendorCost decimal - -
Cost decimal - -
TotalVendorCost decimal - -
TotalCost decimal - -
CurrencyCode string - -
VendorCurrencyCode string - -
VendorCurrencyRate decimal - -
ItemID long Item → ItemID -
ItemVendorID string -
  • 6 Jan 2024 Despite its name, the itemVendorID column refers to the itemVendor.value column - not an ID column

OrderShipment

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Shipment/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OrderShipmentID int - -
CreateTime datetime - -
TimeStamp datetime - -
OrderID int Order → OrderID -
SequenceNumber int - -
TotalQtyReceived decimal - -
TotalVendorCost decimal - -
TotalCost decimal - -
CurrencyCode string - -
VendorCurrencyCode string - -
VendorCurrencyRate decimal - -
ShipmentPackingRefNum string - -
EmployeeID long Employee → EmployeeID -
ReceptionDate datetime - -
ShippingVendorCost decimal - -
ShippingCost decimal - -
Status string - -

Quote

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Quote/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
QuoteID int - -
IssueDate datetime - -
Archived boolean - -
Notes string - -
EmployeeID long Employee → EmployeeID -
SaleID long Sale → SaleID -

Register

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Register/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
RegisterID int - -
Archived boolean - -
Open boolean - -
OpenTime datetime - -
OpenEmployeeID long Employee → EmployeeID -
ShopID long Shop → ShopID -
CcTerminalID string -
  • 15 Jul 2021 We have observed this value coming down as integer and Guid values

Sale

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Sale/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
SaleID long - -
CreateTime datetime - -
UpdateTime datetime - -
Archived boolean - -
TimeStamp datetime - -
DiscountPercent decimal - -
Completed boolean - -
Voided boolean - -
EnablePromotions boolean - -
IsTaxInclusive boolean - -
CompleteTime datetime - -
ReferenceNumber string - -
ReferenceNumberSource string - -
Tax1Rate decimal - -
Tax2Rate decimal - -
Change decimal - -
ReceiptPreference string - -
DisplayableSubtotal decimal - -
TicketNumber string - -
CalcDiscount decimal - -
CalcTotal decimal - -
CalcSubtotal decimal - -
CalcTaxable decimal - -
CalcNonTaxable decimal - -
CalcAvgCost decimal - -
CalcFIFOCost decimal - -
CalcTax1 decimal - -
CalcTax2 decimal - -
CalcPayments decimal - -
Total decimal - -
TotalDue decimal - -
DisplayableTotal decimal - -
Balance decimal - -
CustomerID long Customer → CustomerID -
DiscountID int Discount → DiscountID -
EmployeeID long Employee → EmployeeID -
QuoteID int -
  • 13 Jul 2020 Foreign key - References `Quote` (not implemented).
RegisterID int Register → RegisterID -
ShipToID int -
  • 13 Jul 2020 Foreign key - References `ShipTo` (not implemented).
ShopID long Shop → ShopID -
TaxCategoryID int TaxCategory → TaxCategoryID -
TaxTotal decimal - -

SaleLine

Documentation: https://developers.lightspeedhq.com/retail/endpoints/SaleLine/

Column Type Relates to Notes
RemoteID string - -
SaleRemoteID string Sale → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
SaleLineID long - -
CreateTime datetime - -
TimeStamp datetime - -
UnitQuantity decimal - -
UnitPrice decimal - -
NormalUnitPrice decimal - -
DiscountAmount decimal - -
DiscountPercent decimal - -
AvgCost decimal - -
FifoCost decimal - -
Tax boolean - -
Tax1Rate decimal - -
Tax2Rate decimal - -
IsLayaway boolean - -
IsWorkorder boolean - -
IsSpecialOrder boolean - -
DisplayableSubtotal decimal - -
DisplayableUnitPrice decimal - -
CalcLineDiscount decimal - -
CalcTransactionDiscount decimal - -
CalcTotal decimal - -
CalcSubtotal decimal - -
CalcTax1 decimal - -
CalcTax2 decimal - -
TaxClassID int -
  • 13 Jul 2020 Foreign key - References `TaxClass` (not implemented).
CustomerID long Customer → CustomerID -
DiscountID int Discount → DiscountID -
EmployeeID long Employee → EmployeeID -
ItemID long Item → ItemID -
NoteID int -
  • 13 Jul 2020 Foreign key - could not find the relation this is referencing. From APIDocs: `the foreign ID for the Note object associated with this line. Only one Note object can be attached to each line. Notes will print on the receipt.`.
ParentSaleLineID long SaleLine → SaleLineID -
ShopID long Shop → ShopID -
TaxCategoryID int TaxCategory → TaxCategoryID -
SaleID long Sale → SaleID
  • 19 Feb 2024 Some lines are created (e.g. for special orders) before a related SaleID has been created, so this can sometimes manifest as zero

SalePayment

Documentation: https://developers.lightspeedhq.com/retail/endpoints/SalePayment/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
SalePaymentID long - -
CreateTime datetime - -
Archived boolean - -
Amount decimal - -
RemoteReference string - -
PaymentID string - -
SaleID long Sale → SaleID -
PaymentTypeID int -
  • 13 Jul 2020 Foreign key - References `PaymentType` (not implemented).
CcChargeID int -
  • 13 Jul 2020 Foreign Key - References `CCCharge` not implemented).
RefPaymentID long SalePayment → SalePaymentID -
RegisterID int Register → RegisterID -
EmployeeID long Employee → EmployeeID -
CreditAccountID int -
  • 13 Jul 2020 Foreign key - References `CreditAccount` (not implemented).

Shop

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Shop/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
TimeZone string - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
ShopID long - -
TimeStamp datetime - -
Archived boolean - -
ServiceRate decimal - -
TaxLabor boolean - -
LabelTitle string - -
LabelMsrp boolean - -
CompanyRegistrationNumber string - -
VatNumber string - -
ZebraBrowserPrint boolean - -
GatewayConfigID string - -
ContactID int -
  • 13 Jul 2020 Foreign key - APIDocs point to `Customer/Contact-fields
TaxCategoryID int TaxCategory → TaxCategoryID -
ReceiptSetupID int -
  • 13 Jul 2020 Foreign key - APIDocs are missing or the endpoint doesn't exist. Form APIDocs: `the foreign key for the receipt setup, text at the top and bottom of the receipt etc.`
CcGatewayID int -
  • 13 Jul 2020 Foreign key - APIDocs are missing or the endpoint doesn't exist. From APIDocs: `the foreign key for the credit card gateway setup for this shop.`
PriceLevelID int -
  • 13 Jul 2020 Foreign key - References `PriceLevel` (not implemented).

SpecialOrder

Documentation: https://developers.lightspeedhq.com/retail/endpoints/SpecialOrder/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
SpecialOrderID int - -
TimeStamp datetime - -
UnitQuantity decimal - -
Contacted boolean - -
Completed boolean - -
Status string - -
CustomerID long Customer → CustomerID -
ShopID long Shop → ShopID -
SaleLineID long SaleLine → SaleLineID -
OrderLineID int OrderLine → OrderLineID -
TransferItemID int - -

Tag

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Tag/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
TagID long - -

TaxCategory

Documentation: https://developers.lightspeedhq.com/retail/endpoints/TaxCategory/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
TaxCategoryID int - -
TimeStamp datetime - -
IsTaxInclusive boolean - -
Tax1Name string - -
Tax2Name string - -
Tax1Rate decimal - -
Tax2Rate decimal - -

Vendor

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Vendor/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
VendorID int - -
TimeStamp datetime - -
Archived boolean - -
AccountNumber string - -
PriceLevel string - -
UpdatePrice boolean - -
UpdateCost boolean - -
UpdateDescription boolean - -
ShareSellThrough boolean - -
ContactID int Contact → ContactID -

Workorder

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Workorder/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
WorkorderID int - -
TimeStamp datetime - -
Archived boolean - -
TimeIn datetime - -
EtaOut datetime - -
Note string - -
Warranty boolean - -
Tax boolean - -
HookIn string - -
HookOut string - -
SaveParts boolean - -
AssignEmployeeToAll boolean - -
CustomerID long Customer → CustomerID -
DiscountID int -
  • 23 Nov 2022 Foreign Key - Refers to `Discount` (not implemented).
SerializedID int -
  • 23 Nov 2022 Foreign Key - Refers to `Serialized` (not implemented).
ShopID long Shop → ShopID -
SaleID long Sale → SaleID -
SaleLineID long SaleLine → SaleLineID -
WorkorderStatusID int WorkorderStatus → WorkorderStatusID -

WorkorderItem

Documentation: https://developers.lightspeedhq.com/retail/endpoints/WorkorderItem/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
WorkorderItemID int - -
TimeStamp datetime - -
Approved boolean - -
UnitPrice decimal - -
UnitQuantity int - -
Warranty boolean - -
Tax boolean - -
IsSpecialOrder boolean - -
Note string - -
WorkorderID int Workorder → WorkorderID -
EmployeeID long Employee → EmployeeID -
SaleLineID long SaleLine → SaleLineID -
SaleID long Sale → SaleID -
ItemID long Item → ItemID -
DiscountID int Discount → DiscountID -

WorkorderLine

Documentation: https://developers.lightspeedhq.com/retail/endpoints/WorkorderLine/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
WorkorderLineID int - -
TimeStamp datetime - -
Note string - -
Hours int - -
Minutes int - -
UnitPriceOverride decimal - -
UnitQuantity int - -
UnitCost decimal - -
Done boolean - -
Approved boolean - -
Warranty boolean - -
Tax boolean - -
WorkorderID int Workorder → WorkorderID -
EmployeeID long Employee → EmployeeID -
SaleLineID long SaleLine → SaleLineID -
SaleID long Sale → SaleID -
ItemID long Item → ItemID -
DiscountID int Discount → DiscountID -
TaxClassID int -
  • 23 Nov 2022 Foreign Key - Refers to `TaxClass` (not implemented).

WorkorderStatus

Documentation: https://developers.lightspeedhq.com/retail/endpoints/WorkorderLine/

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
WorkorderStatusID int - -
SortOrder string - -
HtmlColor string - -
SystemValue string - -

CreditAccountContact

Column Type Relates to Notes
RemoteID string - -
CreditAccountRemoteID string CreditAccount → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
CreditAccountID long CreditAccount → CreditAccountID -
Iterator int - -
TimeStamp datetime - -
NoEmail boolean - -
NoPhone boolean - -
NoMail boolean - -
WebsiteUrl string - -
PhoneHome string - -
PhoneWork string - -
PhoneMobile string - -
PhonePager string - -
PhoneFax string - -
PrimaryEmail string - -
SecondaryEmail string - -
Address1 string - -
Address2 string - -
City string - -
State string - -
Zip string - -
Country string - -
CountryCode string - -
StateCode string - -

OrderCustomFieldValue

Column Type Relates to Notes
RemoteID string - -
OrderRemoteID string Order → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomFieldValueID int - -
OrderID int Order → OrderID -
CustomFieldID int OrderCustomField → CustomFieldID -
Type string - -
CustomFieldChoiceID int ItemCustomFieldChoice → CustomFieldID -
Choice string - -

SaleNote

Documentation: https://developers.lightspeedhq.com/retail/tutorials/salenotes/

Column Type Relates to Notes
RemoteID string - -
SaleRemoteID string Sale → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
NoteID long - -
SaleID long Sale → SaleID -
Note string - -
NoteType string - -

Need more?

SyncHub lives and breathes APIs and data. Keep exploring using the links below.

Connect your own Lightspeed R-Series data Querying Lightspeed R-Series using regular SQL