REST Call to Query Purchase Order for an Specific Item

Integration
1

Hello All,

i am trying to make a REST call to retrieve all the purchase orders for a specific item. I was able to do it calling:
https://demo.erpnext.com/api/resource/Purchase%20Order?filters=[[“Purchase Order”,“item_code”,“=”,“Base Plate”]]

I am also trying to bring the Items data in the same call so I can get details as Quantity, Base_Price, etc. Tried to use:
?fields=[“fields”] without success. Also tried to access directly the items.qty…

Any idea of how I could do it?

Cheers
Ewerton

2

try ?fields=*

3

Tried this:
https://demo.erpnext.com/api/resource/Purchase%20Order?filters=[[“Purchase Order”,“item_code”,“=”,“Base Plate”]]?fields=[“*”]

No success…
raise ValueError(errmsg(“Extra data”, s, end, len(s)))
ValueError: Extra data: line 1 column 50 - line 1 column 59 (char 49 - 58)

It works when I only apply the fields parameter:
https://demo.erpnext.com/api/resource/Purchase%20Order/?fields=[“*”]

4

Try this:

https://demo.erpnext.com/api/resource/Purchase%20Order?filters=[["Purchase Order","item_code","=","Base Plate"]]&fields=["*"]
1 Like
5

OMG. Don’t believe it was this… Thanks.

cheers
Ewerton

6

Actually it worked but something is not right: if I get the details of each individual Purchase Order it brings me an “Items” node, containing all the items in that Order.

Doing it this way it brings the PO info but not the Items details?

Thanks
Cheer

7

I usually just use the https://demo.erpnext.com/api/resource/Purchase%20Order/Base Plate command to get all the details. I’m not sure if you can replicate that exactly using the filters/fields tool. In my code, I typically do the following

https://demo.erpnext.com/api/resource/Purchase%20Order?filters=[["Purchase Order","item_code","=","Base Plate"]]

[returns a list of names]
For each name found:

https://demo.erpnext.com/api/resource/Purchase%20Order/name

2 Likes
8

Thanks Ben, this is what I have implemented so far. The problem is that if I have a list of 30 items I will be running the command 30 times.

Performance wise it is not the best.

thanks
Ewerton

9

Can you explain what you’re trying to accomplish with this? You could probably write a whitelisted server side script that would give you all your results in one message (and potentially do more processing if you wanted).

10

@EwertonMoreira you are wrong!

You can determine how many rows your need, using limit_page_length eg:

https://demo.erpnext.com/api/resource/Purchase%20Order/filters=[[“Purchase Order Item”, “item_code”, “=”, “Base Plate”]]&limit_page_length=500&fields=“*”

it’ will return 500 rows, you can change this value, for any value needed, but how many rows, many time consumed in the query.

11

@max_morais_dmm you are wrong!

He’s referring to getting the data (including Purchase Order Item data) from 30 entries in one pull, which can’t be done through the command you provided.

12

@Ben_Cornwell_Mott, yes it can be done!

Using the resource apis or the old frappe.client module, both support the same behavior!

But if him is telling to “update” 30 records in one time, the old frappe.client.update, support a doclist where you can pass all documents to update!

Otherwhise, if it’s only to get the data, yes him can! How do you think that pages get 20, 100 or 500 records at one query?

Edit:

@EwertonMoreira if you only need Purchase Order Item use api/resource/Purchase Order Item/ instead of Purchase Order

13

When I run the command you suggest, I get multiple Purchase Order results, but they don’t include the table data (items). If you attempt the command on the Purchase Order Item data, you get the details of multiple Purchase Order Items but it doesn’t have the Purchase Order details (including the other items from those common purchase orders).

The pages that get 20, 100 or 500 records in one query don’t get all the table data for the records. Even trying to just get the items data using the fields command results in an error:

…
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database.py”, line 148, in sql
self._cursor.execute(query)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘tabPurchase Order.items’ in ‘field list’”)

14

@Ben_Cornwell_Mott, in fact frappe should support this using this query

https://demo.erpnext.com/api/resource/Sales%20Order?fields=["*","tabSales%20Order%20Item.*"]&filters=[["Sales%20Order%20Item",%20"item_code",%20"=",%20"12312"]]&with_child_names=1

But has a issue in the Database query, that is bad parsing the children table name!

@rmehta that’s a issue?