Query Report - Stock Ledger

pdowd · September 22, 2014, 1:05am

Hi,

I've created a basic query report as below:

SELECT

`tabStock Ledger Entry`.`posting_date`,

`tabStock Ledger Entry`.`voucher_no` as "Delivery Note:Link/Delivery Note:100",

`tabStock Ledger Entry`.`item_code` as "Item Code:Link/Item:80",

`tabStock Ledger Entry`.`batch_no`,

`tabStock Ledger Entry`.`warehouse`,

`tabStock Ledger Entry`.`actual_qty`,

`tabStock Ledger Entry`.`stock_uom`,

`tabStock Ledger Entry`.`qty_after_transaction`,

(SELECT `tabDelivery Note`.`customer_name` FROM `tabDelivery Note` WHERE `tabDelivery Note`.`naming_series` = `tabStock Ledger Entry`.`voucher_no`) AS Customer

FROM

`tabStock Ledger Entry`

WHERE

`tabStock Ledger Entry`.`voucher_type` = 'Delivery Note' AND `tabStock Ledger Entry`.`item_code` = 'NU-1'

`

This works to display most of the data perfectly fine.

I have a few questions:

1. can I make the link open in a new window? and if so how

2. the stock UOM isn't showing for some reason, it shows fine on the Stock Ledger master report, is there another field I should be using here?

3. how do I pull the Customer name (final line in the select query) Basically I want to display the name of the customer on each line. This requires getting the Customer Name from the delivery note which is linked in the query as `voucher_no`. I need this to happen on each result line.

4. how can I add a filter so that the item and voucher_type can be selected by the user? I'm assuming something has to go in the JS section? I don't want to have to add a customer report for every product!

I'll be using the method in #3 to pull in a number of other bits of linked data from customer files and delivery notes as well.

Many Thanks

Paul

–

Note:

If you are posting an issue,

ERPNext is a free and open source software and support is given on this forum by a team (https://frappe.io/webnotes). So please consider donating if you find this forum useful (https://frappe.io/buy). Even a small amount would be helpful.
We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

End of Note

—

You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/e4cc5646-1773-464d-9f46-79fedb733bd5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

pdowd · September 22, 2014, 4:49am

Got #3 myself in the end :)

other questions still apply

Example working code:

SELECT

`tabStock Ledger Entry`.`posting_date`,

`tabStock Ledger Entry`.`voucher_no` as "Delivery Note:Link/Delivery Note:90",

`tabStock Ledger Entry`.`item_code` as "Item Code:Link/Item:80",

`tabStock Ledger Entry`.`batch_no`,

`tabStock Ledger Entry`.`warehouse`,

`tabStock Ledger Entry`.`actual_qty`,

`tabStock Ledger Entry`.`stock_uom`,

`tabStock Ledger Entry`.`qty_after_transaction`,

`tabDelivery Note`.`customer_name` AS "Customer"

FROM

`tabStock Ledger Entry` INNER JOIN `tabDelivery Note` ON `tabStock Ledger Entry`.`voucher_no` = `tabDelivery Note`.`name`

WHERE

`tabStock Ledger Entry`.`voucher_type` = 'Delivery Note' AND `tabStock Ledger Entry`.`item_code` = 'NU-1'

–

Note:

If you are posting an issue,

ERPNext is a free and open source software and support is given on this forum by a team (https://frappe.io/webnotes). So please consider donating if you find this forum useful (https://frappe.io/buy). Even a small amount would be helpful.
We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

End of Note

—

You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/fc6f3959-c5f5-490d-9309-8d317dec24d8%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

rmehta · September 22, 2014, 6:51am

On 22-Sep-2014, at 6:35 am, Paul Dowd <pd...@myzealand.com> wrote:

Hi,

I've created a basic query report as below:

SELECT
`tabStock Ledger Entry`.`posting_date`,
`tabStock Ledger Entry`.`voucher_no` as "Delivery Note:Link/Delivery Note:100",
`tabStock Ledger Entry`.`item_code` as "Item Code:Link/Item:80",
`tabStock Ledger Entry`.`batch_no`,
`tabStock Ledger Entry`.`warehouse`,
`tabStock Ledger Entry`.`actual_qty`,
`tabStock Ledger Entry`.`stock_uom`,
`tabStock Ledger Entry`.`qty_after_transaction`,
(SELECT `tabDelivery Note`.`customer_name` FROM `tabDelivery Note` WHERE `tabDelivery Note`.`naming_series` = `tabStock Ledger Entry`.`voucher_no`) AS Customer

FROM
`tabStock Ledger Entry`
WHERE
`tabStock Ledger Entry`.`voucher_type` = 'Delivery Note' AND `tabStock Ledger Entry`.`item_code` = 'NU-1'
`

This works to display most of the data perfectly fine.

I have a few questions:

1. can I make the link open in a new window? and if so how

Thats not so easy - the entire "desk" is basically one page.

2. the stock UOM isn't showing for some reason, it shows fine on the Stock Ledger master report, is there another field I should be using here?

can you define the type as in:

as "Stock UOM:Link/UOM:80"

3. how do I pull the Customer name (final line in the select query) Basically I want to display the name of the customer on each line. This requires getting the Customer Name from the delivery note which is linked in the query as `voucher_no`. I need this to happen on each result line.

you go this :)

4. how can I add a filter so that the item and voucher_type can be selected by the user? I'm assuming something has to go in the JS section? I don't want to have to add a customer report for every product!

Check this:

https://github.com/frappe/erpnext/blob/develop/erpnext/accounts/report/gross_profit/gross_profit.js

I'll be using the method in #3 to pull in a number of other bits of linked data from customer files and delivery notes as well.

Many Thanks

Paul

–

Note:

If you are posting an issue,

ERPNext is a free and open source software and support is given on this forum by a team (https://frappe.io/webnotes). So please consider donating if you find this forum useful (https://frappe.io/buy). Even a small amount would be helpful.

We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.

Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email

For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

End of Note

—

You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/e4cc5646-1773-464d-9f46-79fedb733bd5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

–

Please be kind and don't send large attachments
For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/5B766C6E-E1DB-4D8E-BDA9-79674C8B14B6%40gmail.com.

For more options, visit https://groups.google.com/d/optout.

pdowd · September 23, 2014, 4:28am

Hi Rushabh

On Monday, 22 September 2014 18:51:59 UTC+12, rushabh wrote:

On 22-Sep-2014, at 6:35 am, Paul Dowd <pd...@myzealand.com> wrote:

Hi,

I've created a basic query report as below:

SELECT
`tabStock Ledger Entry`.`posting_date`,
`tabStock Ledger Entry`.`voucher_no` as "Delivery Note:Link/Delivery Note:100",
`tabStock Ledger Entry`.`item_code` as "Item Code:Link/Item:80",
`tabStock Ledger Entry`.`batch_no`,
`tabStock Ledger Entry`.`warehouse`,
`tabStock Ledger Entry`.`actual_qty`,
`tabStock Ledger Entry`.`stock_uom`,
`tabStock Ledger Entry`.`qty_after_transaction`,
(SELECT `tabDelivery Note`.`customer_name` FROM `tabDelivery Note` WHERE `tabDelivery Note`.`naming_series` = `tabStock Ledger Entry`.`voucher_no`) AS Customer

FROM
`tabStock Ledger Entry`
WHERE
`tabStock Ledger Entry`.`voucher_type` = 'Delivery Note' AND `tabStock Ledger Entry`.`item_code` = 'NU-1'
`

This works to display most of the data perfectly fine.

I have a few questions:

1. can I make the link open in a new window? and if so how

Thats not so easy - the entire "desk" is basically one page.

2. the stock UOM isn't showing for some reason, it shows fine on the Stock Ledger master report, is there another field I should be using here?

can you define the type as in:

as "Stock UOM:Link/UOM:80"

made no change, it's strange because it is bringing in some UOMs, just not all, and the ones it is bringing in are wrong. It's showing Nos instead of Cans.

3. how do I pull the Customer name (final line in the select query) Basically I want to display the name of the customer on each line. This requires getting the Customer Name from the delivery note which is linked in the query as `voucher_no`. I need this to happen on each result line.

you go this :)

4. how can I add a filter so that the item and voucher_type can be selected by the user? I'm assuming something has to go in the JS section? I don't want to have to add a customer report for every product!

Check this:

https://github.com/frappe/erpnext/blob/develop/erpnext/accounts/report/gross_profit/gross_profit.js

didn't do anything, I added the following under "Javascript":

frappe.query_reports["Stock Movement Test"] = {

"filters": [

{

"fieldname":"item_code",

"label": __("Item"),

"fieldtype": "Link",

"options": "Item",

"default": frappe.defaults.get_user_default("item_code")

},

{

"fieldname":"from_date",

"label": __("From Date"),

"fieldtype": "Date",

"default": frappe.defaults.get_user_default("year_start_date")

},

{

"fieldname":"to_date",

"label": __("To Date"),

"fieldtype": "Date",

"default": frappe.defaults.get_user_default("year_end_date")

},

]

}

I'll be using the method in #3 to pull in a number of other bits of linked data from customer files and delivery notes as well.

Many Thanks

Paul

–

Note:

If you are posting an issue,

ERPNext is a free and open source software and support is given on this forum by a team (https://frappe.io/webnotes). So please consider donating if you find this forum useful (https://frappe.io/buy). Even a small amount would be helpful.

We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.

Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email

For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

End of Note

—

You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/e4cc5646-1773-464d-9f46-79fedb733bd5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

–

Please be kind and don't send large attachments
For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/37679f92-66bb-4fbf-adba-e896b9664be5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

pdowd · September 23, 2014, 4:31am

#2 made no difference, I'm using:

`tabStock Ledger Entry`.`stock_uom` as "Stock UOM:Link/UOM:80",

#4 - I added the following under "Javascript" in the query report page and got no change in the report, no select fields or anything

frappe.query_reports["Stock Movement Test"] = {

"filters": [

{

"fieldname":"item_code",

"label": __("Item"),

"fieldtype": "Link",

"options": "Item",

"default": frappe.defaults.get_user_default("item_code")

},

{

"fieldname":"from_date",

"label": __("From Date"),

"fieldtype": "Date",

"default": frappe.defaults.get_user_default("year_start_date")

},

{

"fieldname":"to_date",

"label": __("To Date"),

"fieldtype": "Date",

"default": frappe.defaults.get_user_default("year_end_date")

},

]

}

thanks

Paul

–

Please be kind and don't send large attachments
For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/6f679c77-71f2-4144-9aef-cf48f38312ab%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

rmehta · September 23, 2014, 8:26am

Can you share the link to your repo?

On 23-Sep-2014, at 10:01 am, Paul Dowd <pd...@myzealand.com> wrote:

#2 made no difference, I'm using:

`tabStock Ledger Entry`.`stock_uom` as "Stock UOM:Link/UOM:80",

#4 - I added the following under "Javascript" in the query report page and got no change in the report, no select fields or anything

frappe.query_reports["Stock Movement Test"] = {
"filters": [
{
"fieldname":"item_code",
"label": __("Item"),
"fieldtype": "Link",
"options": "Item",
"default": frappe.defaults.get_user_default("item_code")
},
{
"fieldname":"from_date",
"label": __("From Date"),
"fieldtype": "Date",
"default": frappe.defaults.get_user_default("year_start_date")
},
{
"fieldname":"to_date",
"label": __("To Date"),
"fieldtype": "Date",
"default": frappe.defaults.get_user_default("year_end_date")
},
]
}

thanks

Paul

–

Please be kind and don’t send large attachments

For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/6f679c77-71f2-4144-9aef-cf48f38312ab%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

–

Please be kind and don't send large attachments
For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/3044D0B1-8F43-4424-854F-62F9D1A4F043%40gmail.com.

For more options, visit https://groups.google.com/d/optout.

pdowd · September 24, 2014, 9:19pm

Hi Rushabh,

I'm assuming you mean a github repository, in which case I don't have one I'm afraid. I'm making these code changes in the "Query" type report generator when logged in as Administrator.

I have a support package and you have my access details so please login with them to check. The JS side doesn't seem to change anything which is strange since I thought it would at least do something (like break it) if it was incorrect.

The company is Nutriacare.

Thanks

Paul

On Tuesday, 23 September 2014 20:26:17 UTC+12, rushabh wrote:

Can you share the link to your repo?

On 23-Sep-2014, at 10:01 am, Paul Dowd <pd...@myzealand.com> wrote:

#2 made no difference, I'm using:

`tabStock Ledger Entry`.`stock_uom` as "Stock UOM:Link/UOM:80",

#4 - I added the following under "Javascript" in the query report page and got no change in the report, no select fields or anything

frappe.query_reports["Stock Movement Test"] = {
"filters": [
{
"fieldname":"item_code",
"label": __("Item"),
"fieldtype": "Link",
"options": "Item",
"default": frappe.defaults.get_user_default("item_code")
},
{
"fieldname":"from_date",
"label": __("From Date"),
"fieldtype": "Date",
"default": frappe.defaults.get_user_default("year_start_date")
},
{
"fieldname":"to_date",
"label": __("To Date"),
"fieldtype": "Date",
"default": frappe.defaults.get_user_default("year_end_date")
},
]
}

thanks

Paul

–

Please be kind and don’t send large attachments

For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/6f679c77-71f2-4144-9aef-cf48f38312ab%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

–

Please be kind and don't send large attachments
For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/a808952d-5d7e-4164-8e07-47481548f51b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

pdowd · September 24, 2014, 10:48pm

This is a screenshot of the report builder

–

Please be kind and don't send large attachments
For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/e59dc607-f980-41f9-8055-25c2e0601d29%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

rmehta · September 26, 2014, 5:43am

Can you check if you see an error in your Javascript Console? Could be a syntax error in your JS code.

On 25-Sep-2014, at 4:18 am, Paul Dowd <pd...@myzealand.com> wrote:

This is a screenshot of the report builder

–

Please be kind and don’t send large attachments

For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/e59dc607-f980-41f9-8055-25c2e0601d29%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

<Stock Movement Test.png>

–

Please be kind and don't send large attachments
For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/EDDB4035-028C-45C6-B60F-50ED55159CD0%40gmail.com.

For more options, visit https://groups.google.com/d/optout.

pdowd · September 30, 2014, 2:42am

Now it seems that the fields are coming up, but I don't get any effect when I try and use them.

On Friday, 26 September 2014 17:43:19 UTC+12, rushabh wrote:

Can you check if you see an error in your Javascript Console? Could be a syntax error in your JS code.

On 25-Sep-2014, at 4:18 am, Paul Dowd <pd...@myzealand.com> wrote:

This is a screenshot of the report builder

–

Please be kind and don’t send large attachments

For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/e59dc607-f980-41f9-8055-25c2e0601d29%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

<Stock Movement Test.png>

–

Please be kind and don't send large attachments
For issues, features requests use https://github.com/frappe/erpnext/issues

—

You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/17348719-70cb-4ac1-b53d-317c4af79680%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.