last updated: 2024-09-06
Norce Commerce Query examples
Examples based on best practices
_Set testdata
Set PG - norce-open-demo
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/$metadata
Metadata
All endpoints for fetching metadata information.
Can also use the reference portal https://docs.norce.io/api-reference/#norce-commerce-query
Application Metadata
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/$metadata
Application ServiceRoot
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application
Core Metadata
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Core/$metadata
Core ServiceRoot
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Core
Customers Metadata
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Customers/$metadata
Customers ServiceRoot
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Customers
Orders Metadata
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders/$metadata
Orders ServiceRoot
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders
Products Metadata
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Products/$metadata
Products ServiceRoot
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Products
Reporting Metadata
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Reporting/$metadata
Reporting ServiceRoot
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Reporting
Shopping Metadata
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Shopping/$metadata
Shopping ServiceRoot
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Shopping
Products and pricing
Examples when working with products, pricing, availability etc.
Product information to Norce Commerce
Read more about product integration here: https://docs.norce.io/productintegration/#product-information-to-norce-commerce
There might be required to lookup information using Query, here are some examples.
Lookup ManufacturerCodes
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ClientManufacturers?$select=ManufacturerId,ManufacturerCode,Name
Lookup Category Codes
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/Categories?$select=Id,Code,DefaultName
Get Flags
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ProductFlags?$select=Id,TypeId,GroupId,DefaultName,DefaultDescription,IsManuallyEditable,Code&$expand=Cultures($select=CultureCode,Name,Description),Group($select=Id,DefaultName,DefaultDescription;$expand=Cultures($select=CultureCode,Name,Description))
Get Parametrics
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/Parametrics?$select=Id,DefaultName,HasListValues,HasMultipleValues,IsVariantType,IsRequired,IsHidden ,Code,IsReadOnly
Get ParametricListValues
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ParametricListValues?$select=Id,ParametricId,DefaultVarcharValue,DefaultDescription,Code
Get ParametricMultipleValues
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ParametricMultipleValues?$select=Id,ParametricId,DefaultValue,DefaultDescription,Code
Product information from Norce Commerce
Listen to skuchanged notification in Norce Commerce Event. Fetch ProductSku´s from Norce Commerce Query.
See examples here.
Lookup for SkuChangedNotification
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Products/ProductSkus('{{lookupPartNo}}')?$expand=PriceLists($select=PriceListId,QtyBreak,CurrencyId,PriceSale,CostPurchase,PriceStandard,PriceRecommended,ChosenSupplierId),Product($select=ManufacturerId,ManufacturerPartNo;$expand=SupplierSkus($select=PartNo,SupplierId))&$select=PartNo,StatusId,ErpName,EanCode
Small Export
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Products/ProductSkus?$expand=Product($select=DefaultName,ImageKey,DefaultUniqueUrlName;$expand=Variant($select=DefaultName,ImageKey,DefaultUniqueUrlName))&$select=PartNo,StatusId
ParameterValuesFromProductSkus
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Products/ProductSkus('{{lookupPartNo}}')?$expand=Product($expand=Parametrics($select=DefaultVarcharValue,DefaultHtmlValue,IntegerValue,DecimalValue,BooleanValue,DateValue,ListId,NormalizedValue,IsVariantUnique),ParametricMultiple($select=MultipleId);$select=Id)&$select=PartNo,ProductId,ErpName
FlagsFromProductSkus
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Products/ProductSkus('{{lookupPartNo}}')?$expand=Product($select=Id;$expand=Flags($select=FlagId,IsActive))&$select=PartNo,ProductId,ErpName
Price information from and to Norce [Storm]
Read more about the price integrations here: https://docs.norce.io/priceintegration/#price-information-to-norce-commerce and here: https://docs.norce.io/priceintegration/#price-information-from-norce-commerce
Or if prices should be fetched and sent to other system, listen to SkuChangedNotification and lookup codes and other information with quesry (if needed).
Lookup Pricelists
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ClientPriceLists?$select=PriceListId,Name,Agreement
Lookup Prices from Products
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Products/ProductSkuPriceLists?$filter=PartNo eq '{{lookupPartNo}}' and PriceListId eq {{lookupPricelist}} and IsActive eq true&$select=PartNo,PriceListId,QtyBreak,CurrencyId,PriceSale,CostPurchase,CostUnit
Availability information to Norce Commerce
Read about availability integration here: https://docs.norce.io/onhandintegration/#availability-information-to-norce-commerce
However there might be required to lookup information using Query, here are some examples.
Lookup Warehouse and Location codes
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ClientWarehouses?$expand=Locations($select=LocationId,Code,Name,IsStandard)&$select=WarehouseId,Code,Name,IsStandard
Price lists to Norce [Storm]
Read about price list integration here: https://docs.norce.io/productintegration/#price-lists-to-norce-commerce
However there might be required to lookup information using Query, here are some examples.
Lookup Pricelists
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ClientPriceLists?$select=PriceListId,Name,Agreement
Lookup Warehouse and Location codes
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ClientWarehouses?$expand=Locations($select=LocationId,Code,Name,IsStandard)&$select=WarehouseId,Code,Name,IsStandard
Images to Norce Commerce
Read about Image integration here: https://docs.norce.io/productintegration/#images-and-files-to-norce-commerce
However there might be required to lookup information using Query, here are some examples.
Lookup file types
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Core/FileTypes?$select=Id,Extension,MimeType
Competitor information to Norce Commerce
Read about competitor integration here: https://docs.norce.io/productintegration/#competitor-information-to-norce-commerce
However there might be required to lookup information using Query, here are some examples.
Lookup Salesarea
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Core/SalesAreas?$select=Id,DefaultName,DefaultCurrencyId,DefaultVatCodeId
Suppliers
Examples when working with supplier products, supplier stock, costs and supplier content etc.
Supplier catalog to Norce Commerce
Read about supplier integration here: https://docs.norce.io/supplierintegration/#automatically-integrate-a-supplier-catalog-to-norce-commerce
However there might be required to lookup information using Query, here are some examples.
Lookup Suppliers
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ClientSuppliers?$select=SupplierId,SupplierCode,Name,IsActive
Lookup file types
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Core/FileTypes?$select=Id,Extension,MimeType,IsActive
Lookup Supplier pricelist codes (not implemented)
Method: GET
URL: ``
Lookup Supplier warehouse codes
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ClientSuppliers?$expand=Warehouses($select=WarehouseId,Name,StandardLeadTimeDayCount),WarehouseLocations($select=WarehouseId,LocationId,Name)&$select=SupplierId,SupplierCode,Name
Supplier information from Norce Commerce
Read about supplier integration here: https://docs.norce.io/supplierintegration/#get-supplier-information-from-norce-commerce
Here is examples on how to fetch supplier information from Norce.
Primary supplier information
For orders with products that needs to be purchased from supplier, this lookup for every order row will give the information needed about the best supplier to by from at the moment.
Lookup additional supplier sku info
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Products/SupplierProductSkus?$filter=PartNo eq '{{lookupSupplierPartNo}}' and SupplierId eq {{lookupSupplierId}}&$expand=PriceLists($expand=PriceList($select=Id,DefaultName,Code);$select=SupplierId,SupplierPriceListId,QtyBreak,CurrencyId,CostPurchase,CostPurchaseLastChecked;$filter=SupplierPriceListId eq {{lookupSupplierPriceListId}}),OnHands($select=WarehouseId,LocationId,OnHandValue,OnHandLastChecked)&$select=PartNo,SupplierId,Name
Lookup chosen supplier from partno
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Products/ProductSkus('{{lookupPartNo}}')?$expand=PriceLists($filter=PriceListId eq {{lookupPricelist}};$select=QtyBreak,PriceSale,CostPurchase,CostUnit,ChosenSupplierId,ChosenSupplierPartNo,ChosenSupplierPriceListId,ChosenSupplierQtyBreak)&$select=PartNo,ErpName
Lookup Supplier information
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Application/ClientSuppliers?$expand=Warehouses($select=WarehouseId,Name,StandardLeadTimeDayCount),WarehouseLocations($select=WarehouseId,LocationId,Name),Infos($select=TypeId,Value)&$select=SupplierId,SupplierCode,Name
Shopping
Examples when working with baskets, customers, companies, etc.
Order events
Listen to order received event or any other order event. Fetch order or basket data from Norce Commerce Query.
See examples here.
Lookup Order information
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders/Orders?$filter=OrderNo eq '{{lookupOrderNo}}'&$expand=Items($select=Id,LineNumber,ParentLineNumber,PartNo,ProductName,QtyOrdered,VatRate,PriceSale,PriceStandard,DiscountPercentage,DiscountAmount,LineAmount;$expand=Infos($select=TypeId,Value)),Infos($select=TypeId,Value),Payer($select=FirstName,LastName,EmailAddress,CellPhoneNumber,CompanyName,CareOf,AddressLine1,AddressLine2,ZipCode,City,Region,CountryId,ShippingPhoneNumber,Gln),Buyer($select=FirstName,LastName,EmailAddress,CellPhoneNumber,CompanyName,CareOf,AddressLine1,AddressLine2,ZipCode,City,Region,CountryId,ShippingPhoneNumber,Gln),ShipTo($select=FirstName,LastName,EmailAddress,CellPhoneNumber,CompanyName,CareOf,AddressLine1,AddressLine2,ZipCode,City,Region,CountryId,ShippingPhoneNumber,Gln),DropPoint($select=FirstName,LastName,EmailAddress,CellPhoneNumber,CompanyName,CareOf,AddressLine1,AddressLine2,ZipCode,City,Region,CountryId,ShippingPhoneNumber,Gln),Quotation($select=Id;$expand=Infos($select=TypeId,Value),Items($select=LineNumber,PartNo,ProductNameWhenCreated;$expand=Infos($select=TypeId,Value))) &$select=Id,ApplicationId,QuotationId,OrderNo,OrderDate,BuyerCompanyCode,BuyerCustomerCode,PayerCompanyCode,PayerCustomerCode,StatusId,Source,DeliveryMode,PaymentTerms,CurrencyId,FreightCost,DeliveryAttention,DeliveryCareOf,DeliveryAddressLine1,DeliveryAddressLine2,DeliveryZipCode,DeliveryCity,DeliveryRegion,DeliveryCountryId,DeliveryMark,InvoiceMark,PaymentMethodId,DeliveryMethodId,DropPointCode
Lookup Basket information
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders/Quotations?$filter=Id eq {{lookupBasketId}}&$expand=Infos($select=TypeId,Value),Items($select=LineNumber,PartNo,ProductNameWhenCreated,Qty,PriceSale,PriceListId,VatCode,PromotionIdSeed,ParentLineNumber,Comment,TotalPriceSaleExVat,TotalPriceSaleIncVat,RequirementPromotionIdSeed;$expand=Infos($select=TypeId,Value)),Addresses($select=ClientCustomerAddressId,ClientCompanyAddressId,TypeId,CareOf,AddressLine1,AddressLine2,ZipCode,City,Region,CountryId,CompanyName,FirstName,LastName,CellPhoneNumber,Email,ShippingPhoneNumber,Gln)&$select=Id,ApplicationId,StatusId,Comment,PaymentMethodId,DeliveryMethodId,PayerFirstName,PayerMiddleName,PayerLastName,PayerCellPhoneNumber,PayerEmail,CurrencyId,DiscountCode,BuyerFirstName,BuyerMiddleName,BuyerLastName,BuyerCellPhoneNumber,BuyerEmail,CustomerOrderReference,PickupCode,DeliveryMethodCode
Customer lookups
When importing customers some lookups may be required.
See examples here.
Lookup Customer information
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Customers/Customers({{lookupCustomerId}})?$expand=Companies($select=IsPrimary;$expand=Company($select=CompanyCode,Name)),Addresses($select=TypeId,CareOf,AddressLine1,AddressLine2,ZipCode,City,Region,CountryId,ShippingPhoneNumber),Orders($select=ApplicationId,QuotationId,OrderNo,OrderDate,StatusId),Infos($select=TypeId,Value),Flags($select=FlagId,IsActive)&$select=Id,CustomerCode,EmailAddress,FirstName,MiddleName,LastName,PhoneNumber,CellPhoneNumber,OldCustomerNo,ReplacementCustomerId,ApplicationId
Company lookups
When importing companies some lookups may be required.
See examples here.
Lookup Company information
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Customers/Companies({{lookupCompanyId}})?$expand=Customers($select=Department,OrganizationTitle;$expand=Customer($select=Id,CustomerCode,EmailAddress,FirstName,MiddleName,LastName,PhoneNumber,CellPhoneNumber)),Infos($select=TypeId,Value),Addresses($select=TypeId,CareOf,AddressLine1,AddressLine2,ZipCode,City,Region,CountryId,Gln,ShippingPhoneNumber),Flags($select=FlagId,IsActive),PriceLists($select=PriceListId,IsExclusive)&$select=Id,CompanyCode,TypeId,OrganizationNo,Name
Order Lookups
Som different ways to look up order and basket information
GetOrders (from a date)
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders/Orders?$select=ApplicationId,QuotationId,OrderNo,OrderDate,StatusId&$expand=Quotation($select=DiscountCode)&$filter=OrderDate gt 2024-04-01Z
GetOrder (by quotationId)
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders/Orders?$select=ApplicationId,QuotationId,OrderNo,OrderDate&$filter=QuotationId%20eq%20{{lookupBasketId}}&$expand=Items($select=LineNumberString,PartNo,ProductName,QtyOrdered,PriceSale)
GetQuotations from status id
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders/Quotations?$filter=StatusId eq 8 or StatusId eq 10&$select=Id,ApplicationId,StatusId,BuyerFirstName,BuyerLastName,Created
Customer
Customers
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Customers/Customers?$expand=Addresses($select=ZipCode,City,Region,CountryId),Orders($select=ApplicationId,OrderNo,OrderDate,StatusId),Infos($select=TypeId,Value) &$select=Id,CustomerCode,OldCustomerNo,ReplacementCustomerId,ApplicationId&$filter=Created gt 2024-01-01T00:00:00.00Z
Company
Fetch company discounts
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Customers/Companies?$expand=Discounts($select=ValueCode,DiscountPct,PriceListTypeIdSeed)&$select=CompanyCode,Name
Orders
GetInvoice
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders/Invoices?$expand=Items($select=LineNumber,PartNo,ProductName,Quantity,CurrencyId,VatRate,CostUnit,PriceSale,DiscountPercentage,DiscountAmount,LineAmount,LineNumberString,PriceUnit;$expand=OrderItem($select=OrderId,LineNumber)),Address($select=TypeId,Name,Attention,CareOf,Line1,Line2,ZipCode,City,Region,CountryId,EmailAddress,PhoneNumber,ShippingPhoneNumber,Gln)&$select=Id,InvoiceNo,TypeId,ReferenceOrderId,DeliveryDate,InvoiceDate,InvoiceDueDate,CurrencyId,SumLineDiscount,FreightCost,InvoiceFee,CostValue,SalesBalance,InvoiceAmount,InvoiceRoundOff,Vat,InvoiceName,InvoiceAttention,InvoiceCareOf,InvoiceAddressLine1,InvoiceAddressLine2,InvoiceZipCode,InvoiceCity,InvoiceRegion,InvoiceCountryId,NoteText,LastInvoiceForOrder,ReferenceNoPsp,PaymentMethodId,DeliveryMethodId,IsDebit,CreditedInvoiceId,Comment&$filter=InvoiceNo eq '{{lookupInvoiceNo}}'
GetInvoices
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders/Invoices?$expand=Items($select=LineNumber,PartNo,ProductName,Quantity,LineAmount;$expand=OrderItem($select=OrderId,LineNumber))&$select=Id,InvoiceNo,ReferenceOrderId,InvoiceDate,InvoiceAmount&$filter=InvoiceDate gt 2024-04-01T00:00:00.00Z
GetDeliveryNote
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders/DeliveryNotes?$filter=NoteNo eq '{{lookupDNoteNo}}'&$expand=Items($select=LineNumber,PartNo,ProductName,QtyOrdered,Quantity,QtyBackOrdered,NoteText,Department,OrderItemId)&$select=Id,NoteNo,TypeId,ReferenceOrderId,DeliveryName,DeliveryAttention,DeliveryCareOf,DeliveryAddressLine1,DeliveryAddressLine2,DeliveryZipCode,DeliveryCity,DeliveryRegion,DeliveryCountryId,DeliveryDate,DeliveryTerms,DeliveryMode,Source,DeliveryMark,NoteText,ShippingNo
GetOrders
Method: GET
URL: https://{{apihost}}/commerce/query/2.0/Orders/Orders?$select=Id,OrderNo,OrderDate,BuyerCompanyCode&$filter=OrderDate gt 2024-04-01T00:00:00.00Z&$expand=Items($select=LineNumber,PartNo,ProductName,QtyOrdered,LineAmount)