Get Items from Sales Order

Frappe Framework App Development
, , , ,
1

Hello everyone,

I have simple question about How to get sales orders item from Frappe API ?. I have got response from sales order except Sales order items.

with using this request :
http://206.189.54.106/api/resource/Sales%20Order?filters={"customer_name":"Baran Karaboga"}&fields=["naming_series","customer_name","order_type","address_display","total","net_total","delivery_date","delivery_status","billing_status"]&limit_page_length=20&

i got this response :

{
"data": [
    {
        "billing_status": "Not Billed",
        "delivery_date": "2099-01-01",
        "delivery_status": "Not Delivered",
        "net_total": 100,
        "customer_name": "Baran Karaboga",
        "naming_series": "SAL-ORD-.YYYY.-",
        "order_type": "Sales",
        "address_display": "asda<br>asdas<br>asdasd<br>\nasdasd<br>48200<br>Turkey<br>\nPhone: 05555598<br>Fax: 055555313498<br>Email: test@barankaraboga.com<br>",
        "total": 100
    },
    {
        "billing_status": "Not Billed",
        "delivery_date": "2099-01-01",
        "delivery_status": "Not Delivered",
        "net_total": 100,
        "customer_name": "Baran Karaboga",
        "naming_series": "SAL-ORD-.YYYY.-",
        "order_type": "Sales",
        "address_display": "asda<br>asdas<br>asdasd<br>\nasdasd<br>48200<br>Turkey<br>\nPhone: 5555553498<br>Fax: 05555598<br>Email: test@barankaraboga.com<br>",
        "total": 100
    }
]

}

this request works fine but i want to show items field. I have tried to add items in my query but i had always error like :

File "/home/frappe/frappe-bench/env/lib/python3.5/site-packages/pymysql/protocol.py", line 220, in check_error\n err.raise_mysql_exception(self._data)\n File "/home/frappe/frappe-bench/env/lib/python3.5/site-packages/pymysql/err.py", line 109, in raise_mysql_exception\n raise errorclass(errno, errval)\npymysql.err.InternalError: (1054, "Unknown column ā€˜itemsā€™ in ā€˜field listā€™")

2

@barankaraboga

You can make custom API that.

3

@bhavikpatel7023 So there is no possibility to do that ? but items are field too, i should able to get them into do response.

4

I canā€™t help you with the API but Iā€™ll tell you that cannot directly retrieve the items in Sales Order because that items table is a separate child doctype called Sales Order Items linked to the sales order via a field called Parent (so_items.parent = sales_order.name). Youā€™ll have to query that table with correct Sales Order ID and you should be able to get the list.

5

-Did you find out how to get the child items from Sales Order?

I havenā€™t been able to get Sales Order Details, or Quotation Items, or any child table because

url/api/resource/Quotation%20Item

doesnā€™t work, but

url/api/resource/Quotation

does work, but it does not return its items

6

I tried the Quotation%20Item but it says permission parent error, anyone has tried?

7

Instead of using REST API which doesnā€™t work with child level, you can use RPC method:

below is an example

/api/method/frappe.client.get_list?doctype=Quotation Item&parent=Quotation&fields=["name", "transaction_date"]&filters={"docstatus":1}&page_length_limit=None

1 Like
8

Thanks sir! Now i know thereā€™s RPC, however, how do I filter transaction date > June 1 2022? Do you know how to write it? Thanks in advance!

10

sorry, I added ā€œtransaction_dateā€ in in the example by mistake. ā€œtransaction_dateā€ is only available in the parent table (Quotation) not the child one (Quotation Item).

If you need to apply a filter by transaction date on Quotation Item, I guess you need to build a custom API method

But, if you need to apply the filter on Quotation, you can try this:

&filters={"transaction_date":[">","2022-06-01"]}

Sorry again for my mistake

11

Ahhh, I see, so it is not actually possible to perform double filter?

example, i want to filter:

  1. brand from quotation item
  2. transaction date from quotation (parent)
12

As per your example, it is not possible but through building a custom API.