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.
Table of contents
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
IDApproachBaseCurrencyBlindReceiptCombinedInvoiceStatusCombinedPaymentStatusCombinedReceivingStatusCurrencyRateInventoryAccountIsServiceOnlyLastUpdateDateLocationNoteOrderDateOrderNumber
DearSystems_AdvancedPurchasePutAway.tsv
NameSKUDateQuantityReceivedBatchSNCardIDExpiryDateLocationLocationIDProductIDPurchaseIDStatusSupplierSKUTaskID
DearSystems_AdvancedPurchaseStocks.tsv
NameSKUDateQuantityReceivedBatchSNExpiryDateLocationLocationIDProductIDPurchaseIDStatusSupplierSKUTaskID
DearSystems_AttributeSets.tsv
IDNameAttrNameAttrTypeAttrValues
DearSystems_BOMProducts.tsv
IDNameQuantityComponentProductIDCostPercentageProductCodeWastagePercentWastageQuantity
DearSystems_BOMServices.tsv
IDNameQuantityComponentProductIDExpenseAccountPriceTier
DearSystems_Customers.tsv
IDCurrencyAccountReceivableAdditionalAttribute1AdditionalAttribute10AdditionalAttribute2AdditionalAttribute3AdditionalAttribute4AdditionalAttribute5AdditionalAttribute6AdditionalAttribute7AdditionalAttribute8AdditionalAttribute9AttributeSetCarrierCommentsCreditLimitDiscountLastModifiedOnLocationPaymentTermPriceTierRevenueAccountStatusTagsTaxNumberTaxRule
DearSystems_FinishedGood.tsv
DateQuantityAssemblyNumberBatchSNExpiryDateLocationLocationIDNotesProductCodeProductIDProductNameStatusTaskIDUnitCost
DearSystems_ProductAvailabilities.tsv
IDNameSKUStockOnHandAllocatedAvailableBarcodeBatchBinExpiryDateLocationOnHandOnOrder
DearSystems_ProductCategories.tsv
IDName
DearSystems_ProductFamilies.tsv
IDNameSKUCategoryBrandAttributeSetCOGSAccountCostingMethodDefaultLocationDescription
DearSystems_ProductFamilyAttachments.tsv
ContentTypeDownloadUrlFileNameIDProductFamilyIDProductFamilyAttachment
DearSystems_ProductFamilyLines.tsv
NameSKUIDProductFamilyIDProductFamilyLineOption1Option2Option3
DearSystems_ProductMovements.tsv
ID: same field asIDin DearSystems_Products.tsv andProductIDin DearSystems_SaleOrderLines.tsvDateQuantityType: e.g. “Sale”, “Assembly”, “Purchase”AmountBatchSNExpiryDateFromToLocationNumberStatusTaskID
DearSystems_Products.tsv
ID: same field asIDin DearSystems_ProductMovements.tsv andProductIDin DearSystems_SaleOrderLines.tsvNameSKUCategoryBrandTypeAssemblyCostEstimationMethodAssemblyInstructionURLAttributeSetAutoAssemblyAutoDisassemblyAverageCostBarcodeBillOfMaterialCOGSAccountCostingMethodDefaultLocationDescriptionDimensionsUnitsDiscountRuleDropShipModeExpenseAccountHeightInventoryAccountLastModifiedOnLengthMinimumBeforeReorderPickZonesPriceTierName1: 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.).PriceTierName10PriceTierName2PriceTierName3PriceTierName4PriceTierName5PriceTierName6PriceTierName7PriceTierName8PriceTierName9PriceTierValue1: contains the value of the associated price tier.PriceTierValue10PriceTierValue2PriceTierValue3PriceTierValue4PriceTierValue5PriceTierValue6PriceTierValue7PriceTierValue8PriceTierValue9PurchaseTaxRuleQuantityToProduceReorderQuantityRevenueAccountSaleTaxRuleSellableShortDescriptionStatusStockLocatorTagsUOMWeightWeightUnitsWidth
DearSystems_ProductSuppliers.tsv
IDSupplierID
DearSystems_PurchaseOrderAdditionalCharges.tsv
QuantityDescriptionDiscountPriceReferenceTaskIDTaxTaxRuleTotal
DearSystems_PurchaseOrderLines.tsv
This is the basic table about purchase orders. Additional information can be imported from DearSystems_Purchases.tsv
NameSKUQuantityCommentDiscountPriceProductIDSupplierSKUTaskID: same field asIDin DearSystems_Purchases.tsvTaxTaxRuleTotal
DearSystems_PurchaseOrders.tsv
CombineAdditionalChargesMemoStatusTaskIDTaxTotalTotalBeforeTax
DearSystems_Purchases.tsv
ID: same field asTaskIDin DearSystems_PurchaseOrderLines.tsvSupplierTypeOrderStatusBaseCurrencyBlindReceiptCombinedInvoiceStatusCombinedPaymentStatusCombinedReceivingStatusCreditNoteNumberCreditNoteStatusInvoiceAmountInvoiceDateInvoiceDueDateInvoiceNumberInvoiceStatusIsServiceOnlyLastUpdatedDateOrderDateOrderNumberPaidAmountRequiredByStatusStockReceivedStatusSupplierCurrencySupplierIDUnstockStatus
DearSystems_PurchaseStocks.tsv
NameSKUDateQuantityReceivedBatchSNCardIDExpiryDateLocationLocationIDProductIDStatusSupplierSKUTaskID
DearSystems_SaleOrderAdditionalCharges.tsv
QuantityDescriptionDiscountPriceSaleIDTaxTaxRuleTotal
DearSystems_SaleOrderLines.tsv
This is the basic table about sale orders. Additional information can be imported from DearSystems_SaleOrders.tsv and DearSystems_SaleItems.tsv.
NameSKUQuantityAverageCostBackorderQuantityCommentDiscountDropShipPriceProductID: same field asIDin DearSystems_Products.tsv and DearSystems_ProductMovements.tsvSaleID: same field as in DearSystems_SaleOrders.tsv and DearSystems_SaleItems.tsvTaxTaxRuleTotal
DearSystems_SaleOrders.tsv
CombineAdditionalChargesMemoSaleID: same field as in DearSystems_SaleOrderLines and DearSystems_SaleItems.tsvSaleOrderNumber: same field asOrderNumberin DearSystems_SaleItems.tsvStatusTaxTotalTotalBeforeTax
DearSystems_SaleItems.tsv
TypeOrderStatusBaseCurrencyCombinedInvoiceStatusCombinedPackingStatusCombinedPaymentStatusCombinedPickingStatusCombinedShippingStatusCombinedTrackingNumbersCreditNoteNumberCreditNoteStatusCustomerCustomerCurrencyCustomerIDCustomerReferenceFulFilmentStatusInvoiceAmountInvoiceDateInvoiceDueDateInvoiceNumberOrderDateOrderNumber: same field asSaleOrderNumberin DearSystems_SaleOrders.tsvPaidAmountQuoteStatusSaleID: same field as in DearSystems_SaleOrderLines.tsv and DearSystems_SaleOrders.tsvShipByStatusUpdated
DearSystems_Sales.tsv
SaleIdBillingCountryShippingCountryShippingCompanySalesRepresentative
DearSystems_StockAdjustmentExisting.tsv
SKUAdjustmentAvailableBarcodeBatchSNCommentsCostingMethodDimensionsUnitsExpiryDateLocationLocationIDProductCustomField1ProductCustomField10ProductCustomField2
DearSystems_StockAdjustmentNew.tsv
SKUQuantityBarcodeBatchSNCommentsCostingMethodDimensionsUnitsExpiryDateLocationLocationIDProductCustomField1ProductCustomField10ProductCustomField2ProductCustomField3ProductCustomField4ProductCustomField5ProductCustomField6ProductCustomField7ProductCustomField8ProductCustomField9ProductHeightProductIDProductLengthProductNameProductWeightProductWidthStockLocatorTaskIDUnitUnitCostWeightUnits
DearSystems_StockAdjustments.tsv
AccountEffectiveDateReferenceStatusStocktakeNumberTaskID
DearSystems_StockAdjustmentTransaction.tsv
AmountCreditDebitEffectiveDateTaskIDTransactionID
DearSystems_StockTakeExisting.tsv
SKUAdjustmentAvailableBarcodeBatchSNCommentsCostingMethodDimensionsUnitsExpiryDateLocationLocationIDProductCustomField1ProductCustomField10ProductCustomField2ProductCustomField3ProductCustomField4ProductCustomField5ProductCustomField6ProductCustomField7ProductCustomField8ProductCustomField9ProductHeightProductIDProductLengthProductNameProductWeightProductWidthQuantityOnHandStockLocatorTaskIDUnitWeightUnits
DearSystems_StockTakeNew.tsv
SKUQuantityBarcodeBatchSNCommentsCostingMethodDimensionsUnitsExpiryDateLocationLocationIDProductCustomField1ProductCustomField10ProductCustomField2ProductCustomField3ProductCustomField4ProductCustomField5ProductCustomField6ProductCustomField7ProductCustomField8ProductCustomField9ProductHeightProductIDProductLengthProductNameProductWeightProductWidthStockLocatorTaskIDUnitUnitCostWeightUnits
DearSystems_StockTakes.tsv
AccountBinsBrandsCategoriesCreatedDateEffectiveDateLastUpdatedByLastUpdatedDateLocationLocationIDPickZonesReferenceStatusStockLocatorsStocktakeNumberTagsTaskID
DearSystems_StockTakeTransaction.tsv
AmountCreditDebitEffectiveDateTaskIDTransactionID
DearSystems_StockTransferLines.tsv
SKUBatchSNCommentsDimensionsUnitsExpiryDateProductCustomField1ProductCustomField10ProductCustomField2ProductCustomField3ProductCustomField4ProductCustomField5ProductCustomField6ProductCustomField7ProductCustomField8ProductCustomField9ProductHeightProductIDProductLengthProductNameProductWeightProductWidthQuantityAvailableQuantityOnHandTaskIDTransferQuantityWeightUnits
DearSystems_StockTransfers.tsv
CompletionDateCostDistributionTypeDepartureDateFromFromLocationInTransitAccountNumberReferenceStatusTaskIDToToLocation
DearSystems_SupplierAddresses.tsv
IDTypeCityCountryDefaultForTypeLine1Line2PostCodeState
DearSystems_SupplierContacts.tsv
IDNameCommentDefaultEmailFaxIncludeInEmailPhoneWebsite
DearSystems_Suppliers.tsv
IDNameCurrencyAccountPayableAdditionalAttribute1AdditionalAttribute10AdditionalAttribute2AdditionalAttribute3AdditionalAttribute4AdditionalAttribute5AdditionalAttribute6AdditionalAttribute7AdditionalAttribute8AdditionalAttribute9AttributeSetCommentsDiscountLastModifiedOnPaymentTermStatusTaxNumberTaxRule
DearSystems_UnitsOfMeasure.tsv
IDName