I need a way to retrieve all orders which contains a product/orderline.
Beacuse the customer needs to se which orders and orderdetails that have been used on specific product.
Is there an API or another way to get all orders and filter by product ?
We don’t have a specific function for this purpose so it would have to be a little custom. Your best bet would probably be to use TC.GetFinalizedOrdersForCustomer to get all orders for a given customer (assuming your customers are Umbraco members and they were logged in at point of purchase) and then filter them by order lines containing the given product.
Thanks you for a quick response. I red the question again after reading your answer - and maybe I misled you a bit.
The customer is my customer 'the store manager' So he needs to see all orders and details on a product. Do you have an idea how to achieve this.
If I need to get my hands dirty in the database then I need some pointers on which tables to look at.
Ahh, sorry, I thought you meant "customer" as in someone making a purchase in the store.
In that case yea it will probably need to be custom.
I think the best bet would bet to get a list of order IDs from the database and then use the TC API to fetch the full orders.
I think a SQL statement like the following should get you a list of order IDs
SELECT TCO.[Id]
FROM [TeaCommerce_Order] as TCO
INNER JOIN [TeaCommerce_OrderLine] as TCOL ON TCOL.[OrderId] = TCO.[Id]
WHERE TCO.[StoreId] = YOUR_STORE_ID AND TCO.[DateFinalized] != NULL AND TCOL.[ProductIdentifier] = YOUR_PRODUCT_ID
Then from there, you could call the following to retrieve the orders
I made a little modification to the query, to get distinct ids and a like for productidentifier to support variants.
SELECT distinct TCO.[Id]
FROM [TeaCommerce_Order] as TCO
INNER JOIN [TeaCommerce_OrderLine] as TCOL ON TCOL.[OrderId] = TCO.[Id]
WHERE TCO.[StoreId] = 1 AND TCO.[DateFinalized] is not NULL AND TCOL.[ProductIdentifier] like '9061%'
Get orders by product
Hi,
I need a way to retrieve all orders which contains a product/orderline. Beacuse the customer needs to se which orders and orderdetails that have been used on specific product.
Is there an API or another way to get all orders and filter by product ?
/ Søren
Hi Soren,
We don’t have a specific function for this purpose so it would have to be a little custom. Your best bet would probably be to use TC.GetFinalizedOrdersForCustomer to get all orders for a given customer (assuming your customers are Umbraco members and they were logged in at point of purchase) and then filter them by order lines containing the given product.
https://docs.teacommerce.net/3.4.0/api/order/#getfinalizedordersforcustomer
Hope this helps
Hi Matt,
Thanks you for a quick response. I red the question again after reading your answer - and maybe I misled you a bit.
The customer is my customer 'the store manager' So he needs to see all orders and details on a product. Do you have an idea how to achieve this. If I need to get my hands dirty in the database then I need some pointers on which tables to look at.
/Søren
Ahh, sorry, I thought you meant "customer" as in someone making a purchase in the store.
In that case yea it will probably need to be custom.
I think the best bet would bet to get a list of order IDs from the database and then use the TC API to fetch the full orders.
I think a SQL statement like the following should get you a list of order IDs
Then from there, you could call the following to retrieve the orders
Hope this helps
Matt
Hi Matt
You're awesome - thank you so much
I made a little modification to the query, to get distinct ids and a like for productidentifier to support variants.
Cheers.
Ahh, nicely done.
Glad we could get you what you were after 👍
is working on a reply...