Dear Systems
The data from Dear Systems can be imported in just a few clicks into Lokad thanks to a native integration that leverages the Dear Systems API. In this page, we detail how to make the most of this native Dear Systems integration.
-
[Setup of the Lokad/Dear Systems data connector](#setup-of-the-lokad-Dear Systems-data-connector)
Setup of the Lokad/Dear Systems data connector
The suggested process to connect your Lokad and Dear Systems accounts is the following:
- Log into your Lokad account, and clik on the Connect data source button at the bottom of the Project page
- Choose Dear Systems among the proposed integrations options
- Insert your Dear Systems credentials
The last step will redirect you to your Lokad account, where the project DearSystems data source will have been created.
Two remarks:
- Our data retrieval logic is read-only: your Dear Systems data will not be modified in any way by Lokad; in the future, if Lokad were to add write capabilities for Dear Systems, we will make it very explicit whenever you are about to change anything within Dear Systems.
- In addition, your company remains the sole owner of your data; by default, we treat this data as if an NDA was in place between your company and Lokad; if you want an actual NDA to be signed, just drop us a message.
Annex: Tables reference
Lokad retrieves extensive data from Dear Systems which include:
- The complete catalogue with the stock on hand, the stock on order, the sell price, the buy price.
- The purchase process with the purchase orders, quantity, the unit price, the delivery date, the supplier, the status etc.
- The sale process with the sale orders, quantity, the unit price, the client etc.
- The manufacturing process with the product bill of materials (BOM).
- The price lists with the variant, the price and the name of the price list.
All data are aggregated per product and per warehouse. They are pushed to your Lokad account into the following files:
- DearSystems_AdvancedPurchasePutAway.tsv
- DearSystems_AdvancedPurchaseStocks.tsv
- DearSystems_AttributeSets.tsv
- DearSystems_BOMProducts.tsv
- DearSystems_BOMServices.tsv
- DearSystems_Customers.tsv
- DearSystems_FinishedGood.tsv
- DearSystems_ProductAvailabilities.tsv
- DearSystems_ProductCategories.tsv
- DearSystems_ProductFamilies.tsv
- DearSystems_ProductFamilyAttachments.tsv
- DearSystems_ProductFamilyLines.tsv
- DearSystems_ProductMovements.tsv
- DearSystems_Products.tsv
- DearSystems_ProductSuppliers.tsv
- DearSystems_PurchaseOrderAdditionalCharges.tsv
- DearSystems_PurchaseOrderLines.tsv
- DearSystems_PurchaseOrders.tsv
- DearSystems_Purchases.tsv
- DearSystems_PurchaseStocks.tsv
- DearSystems_SaleOrderAdditionalCharges.tsv
- DearSystems_SaleOrderLines.tsv
- DearSystems_SaleOrders.tsv
- DearSystems_SaleItems.tsv
- DearSystems_Sales.tsv
- DearSystems_StockAdjustmentExisting.tsv
- DearSystems_StockAdjustmentNew.tsv
- DearSystems_StockAdjustments.tsv
- DearSystems_StockAdjustmentTransaction.tsv
- DearSystems_StockTakeExisting.tsv
- DearSystems_StockTakeNew.tsv
- DearSystems_StockTakes.tsv
- DearSystems_StockTakeTransaction.tsv
- DearSystems_StockTransferLines.tsv
- DearSystems_StockTransfers.tsv
- DearSystems_SupplierAddresses.tsv
- DearSystems_SupplierContacts.tsv
- DearSystems_Suppliers.tsv
- DearSystems_UnitsOfMeasure.tsv
On Lokad’s side, the information about purchase orders has to be extracted from 2 files (DearSystems_PurchaseOrderLines
, DearSystems_Purchases
), that about sales orders from 4 (DearSystems_SaleOrderLines.tsv
, DearSystems_SaleItems.tsv
, DearSystems_Sales.tsv
, DearSystems_SaleOrders.tsv
). See later for more details about the tables fields.
Additional information can be found at the Dear Systems’s support page.
DearSystems_AdvancedPurchase.tsv
ID
Approach
BaseCurrency
BlindReceipt
CombinedInvoiceStatus
CombinedPaymentStatus
CombinedReceivingStatus
CurrencyRate
InventoryAccount
IsServiceOnly
LastUpdateDate
Location
Note
OrderDate
OrderNumber
DearSystems_AdvancedPurchasePutAway.tsv
Name
SKU
Date
Quantity
Received
BatchSN
CardID
ExpiryDate
Location
LocationID
ProductID
PurchaseID
Status
SupplierSKU
TaskID
DearSystems_AdvancedPurchaseStocks.tsv
Name
SKU
Date
Quantity
Received
BatchSN
ExpiryDate
Location
LocationID
ProductID
PurchaseID
Status
SupplierSKU
TaskID
DearSystems_AttributeSets.tsv
ID
Name
AttrName
AttrType
AttrValues
DearSystems_BOMProducts.tsv
ID
Name
Quantity
ComponentProductID
CostPercentage
ProductCode
WastagePercent
WastageQuantity
DearSystems_BOMServices.tsv
ID
Name
Quantity
ComponentProductID
ExpenseAccount
PriceTier
DearSystems_Customers.tsv
ID
Currency
AccountReceivable
AdditionalAttribute1
AdditionalAttribute10
AdditionalAttribute2
AdditionalAttribute3
AdditionalAttribute4
AdditionalAttribute5
AdditionalAttribute6
AdditionalAttribute7
AdditionalAttribute8
AdditionalAttribute9
AttributeSet
Carrier
Comments
CreditLimit
Discount
LastModifiedOn
Location
PaymentTerm
PriceTier
RevenueAccount
Status
Tags
TaxNumber
TaxRule
DearSystems_FinishedGood.tsv
Date
Quantity
AssemblyNumber
BatchSN
ExpiryDate
Location
LocationID
Notes
ProductCode
ProductID
ProductName
Status
TaskID
UnitCost
DearSystems_ProductAvailabilities.tsv
ID
Name
SKU
StockOnHand
Allocated
Available
Barcode
Batch
Bin
ExpiryDate
Location
OnHand
OnOrder
DearSystems_ProductCategories.tsv
ID
Name
DearSystems_ProductFamilies.tsv
ID
Name
SKU
Category
Brand
AttributeSet
COGSAccount
CostingMethod
DefaultLocation
Description
DearSystems_ProductFamilyAttachments.tsv
ContentType
DownloadUrl
FileName
IDProductFamily
IDProductFamilyAttachment
DearSystems_ProductFamilyLines.tsv
Name
SKU
IDProductFamily
IDProductFamilyLine
Option1
Option2
Option3
DearSystems_ProductMovements.tsv
ID
: same field asID
in DearSystems_Products.tsv andProductID
in DearSystems_SaleOrderLines.tsvDate
Quantity
Type
: e.g. “Sale”, “Assembly”, “Purchase”Amount
BatchSN
ExpiryDate
FromTo
Location
Number
Status
TaskID
DearSystems_Products.tsv
ID
: same field asID
in DearSystems_ProductMovements.tsv andProductID
in DearSystems_SaleOrderLines.tsvName
SKU
Category
Brand
Type
AssemblyCostEstimationMethod
AssemblyInstructionURL
AttributeSet
AutoAssembly
AutoDisassembly
AverageCost
Barcode
BillOfMaterial
COGSAccount
CostingMethod
DefaultLocation
Description
DimensionsUnits
DiscountRule
DropShipMode
ExpenseAccount
Height
InventoryAccount
LastModifiedOn
Length
MinimumBeforeReorder
PickZones
PriceTierName1
: contains the name of the price tiers; it is important to know that the numbering is not the same as displayed by Dear Systems in the customer interface (i.e.PriceTier1
,PriceTier2
, etc.).PriceTierName10
PriceTierName2
PriceTierName3
PriceTierName4
PriceTierName5
PriceTierName6
PriceTierName7
PriceTierName8
PriceTierName9
PriceTierValue1
: contains the value of the associated price tier.PriceTierValue10
PriceTierValue2
PriceTierValue3
PriceTierValue4
PriceTierValue5
PriceTierValue6
PriceTierValue7
PriceTierValue8
PriceTierValue9
PurchaseTaxRule
QuantityToProduce
ReorderQuantity
RevenueAccount
SaleTaxRule
Sellable
ShortDescription
Status
StockLocator
Tags
UOM
Weight
WeightUnits
Width
DearSystems_ProductSuppliers.tsv
ID
SupplierID
DearSystems_PurchaseOrderAdditionalCharges.tsv
Quantity
Description
Discount
Price
Reference
TaskID
Tax
TaxRule
Total
DearSystems_PurchaseOrderLines.tsv
This is the basic table about purchase orders. Additional information can be imported from DearSystems_Purchases.tsv
Name
SKU
Quantity
Comment
Discount
Price
ProductID
SupplierSKU
TaskID
: same field asID
in DearSystems_Purchases.tsvTax
TaxRule
Total
DearSystems_PurchaseOrders.tsv
CombineAdditionalCharges
Memo
Status
TaskID
Tax
Total
TotalBeforeTax
DearSystems_Purchases.tsv
ID
: same field asTaskID
in DearSystems_PurchaseOrderLines.tsvSupplier
Type
OrderStatus
BaseCurrency
BlindReceipt
CombinedInvoiceStatus
CombinedPaymentStatus
CombinedReceivingStatus
CreditNoteNumber
CreditNoteStatus
InvoiceAmount
InvoiceDate
InvoiceDueDate
InvoiceNumber
InvoiceStatus
IsServiceOnly
LastUpdatedDate
OrderDate
OrderNumber
PaidAmount
RequiredBy
Status
StockReceivedStatus
SupplierCurrency
SupplierID
UnstockStatus
DearSystems_PurchaseStocks.tsv
Name
SKU
Date
Quantity
Received
BatchSN
CardID
ExpiryDate
Location
LocationID
ProductID
Status
SupplierSKU
TaskID
DearSystems_SaleOrderAdditionalCharges.tsv
Quantity
Description
Discount
Price
SaleID
Tax
TaxRule
Total
DearSystems_SaleOrderLines.tsv
This is the basic table about sale orders. Additional information can be imported from DearSystems_SaleOrders.tsv and DearSystems_SaleItems.tsv.
Name
SKU
Quantity
AverageCost
BackorderQuantity
Comment
Discount
DropShip
Price
ProductID
: same field asID
in DearSystems_Products.tsv and DearSystems_ProductMovements.tsvSaleID
: same field as in DearSystems_SaleOrders.tsv and DearSystems_SaleItems.tsvTax
TaxRule
Total
DearSystems_SaleOrders.tsv
CombineAdditionalCharges
Memo
SaleID
: same field as in DearSystems_SaleOrderLines and DearSystems_SaleItems.tsvSaleOrderNumber
: same field asOrderNumber
in DearSystems_SaleItems.tsvStatus
Tax
Total
TotalBeforeTax
DearSystems_SaleItems.tsv
Type
OrderStatus
BaseCurrency
CombinedInvoiceStatus
CombinedPackingStatus
CombinedPaymentStatus
CombinedPickingStatus
CombinedShippingStatus
CombinedTrackingNumbers
CreditNoteNumber
CreditNoteStatus
Customer
CustomerCurrency
CustomerID
CustomerReference
FulFilmentStatus
InvoiceAmount
InvoiceDate
InvoiceDueDate
InvoiceNumber
OrderDate
OrderNumber
: same field asSaleOrderNumber
in DearSystems_SaleOrders.tsvPaidAmount
QuoteStatus
SaleID
: same field as in DearSystems_SaleOrderLines.tsv and DearSystems_SaleOrders.tsvShipBy
Status
Updated
DearSystems_Sales.tsv
SaleId
BillingCountry
ShippingCountry
ShippingCompany
SalesRepresentative
DearSystems_StockAdjustmentExisting.tsv
SKU
Adjustment
Available
Barcode
BatchSN
Comments
CostingMethod
DimensionsUnits
ExpiryDate
Location
LocationID
ProductCustomField1
ProductCustomField10
ProductCustomField2
DearSystems_StockAdjustmentNew.tsv
SKU
Quantity
Barcode
BatchSN
Comments
CostingMethod
DimensionsUnits
ExpiryDate
Location
LocationID
ProductCustomField1
ProductCustomField10
ProductCustomField2
ProductCustomField3
ProductCustomField4
ProductCustomField5
ProductCustomField6
ProductCustomField7
ProductCustomField8
ProductCustomField9
ProductHeight
ProductID
ProductLength
ProductName
ProductWeight
ProductWidth
StockLocator
TaskID
Unit
UnitCost
WeightUnits
DearSystems_StockAdjustments.tsv
Account
EffectiveDate
Reference
Status
StocktakeNumber
TaskID
DearSystems_StockAdjustmentTransaction.tsv
Amount
Credit
Debit
EffectiveDate
TaskID
TransactionID
DearSystems_StockTakeExisting.tsv
SKU
Adjustment
Available
Barcode
BatchSN
Comments
CostingMethod
DimensionsUnits
ExpiryDate
Location
LocationID
ProductCustomField1
ProductCustomField10
ProductCustomField2
ProductCustomField3
ProductCustomField4
ProductCustomField5
ProductCustomField6
ProductCustomField7
ProductCustomField8
ProductCustomField9
ProductHeight
ProductID
ProductLength
ProductName
ProductWeight
ProductWidth
QuantityOnHand
StockLocator
TaskID
Unit
WeightUnits
DearSystems_StockTakeNew.tsv
SKU
Quantity
Barcode
BatchSN
Comments
CostingMethod
DimensionsUnits
ExpiryDate
Location
LocationID
ProductCustomField1
ProductCustomField10
ProductCustomField2
ProductCustomField3
ProductCustomField4
ProductCustomField5
ProductCustomField6
ProductCustomField7
ProductCustomField8
ProductCustomField9
ProductHeight
ProductID
ProductLength
ProductName
ProductWeight
ProductWidth
StockLocator
TaskID
Unit
UnitCost
WeightUnits
DearSystems_StockTakes.tsv
Account
Bins
Brands
Categories
CreatedDate
EffectiveDate
LastUpdatedBy
LastUpdatedDate
Location
LocationID
PickZones
Reference
Status
StockLocators
StocktakeNumber
Tags
TaskID
DearSystems_StockTakeTransaction.tsv
Amount
Credit
Debit
EffectiveDate
TaskID
TransactionID
DearSystems_StockTransferLines.tsv
SKU
BatchSN
Comments
DimensionsUnits
ExpiryDate
ProductCustomField1
ProductCustomField10
ProductCustomField2
ProductCustomField3
ProductCustomField4
ProductCustomField5
ProductCustomField6
ProductCustomField7
ProductCustomField8
ProductCustomField9
ProductHeight
ProductID
ProductLength
ProductName
ProductWeight
ProductWidth
QuantityAvailable
QuantityOnHand
TaskID
TransferQuantity
WeightUnits
DearSystems_StockTransfers.tsv
CompletionDate
CostDistributionType
DepartureDate
From
FromLocation
InTransitAccount
Number
Reference
Status
TaskID
To
ToLocation
DearSystems_SupplierAddresses.tsv
ID
Type
City
Country
DefaultForType
Line1
Line2
PostCode
State
DearSystems_SupplierContacts.tsv
ID
Name
Comment
Default
Email
Fax
IncludeInEmail
Phone
Website
DearSystems_Suppliers.tsv
ID
Name
Currency
AccountPayable
AdditionalAttribute1
AdditionalAttribute10
AdditionalAttribute2
AdditionalAttribute3
AdditionalAttribute4
AdditionalAttribute5
AdditionalAttribute6
AdditionalAttribute7
AdditionalAttribute8
AdditionalAttribute9
AttributeSet
Comments
Discount
LastModifiedOn
PaymentTerm
Status
TaxNumber
TaxRule
DearSystems_UnitsOfMeasure.tsv
ID
Name