Price and stock in one report

Is there any report (or possibility to create such a report) that will show actual quantity and price on one screen? with a possibility to search by code and item name both? It would be a great tool for sales persons.


Slava



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

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

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

 

 

Dear Slava,

You will need to make customize report to achieve this.

Currently, Sales Person is update about actual stock of item while making Sales Order. The Actual Stock of Item in Reserve Warehouse is pulled in Sales Order. The Item Price will be fetched into Sales Order from Item mater.


Not via report, but while making Sales Order, Sales person get both the information about item.

Hope this helps.

On Tue, Apr 9, 2013 at 10:43 AM, Slava <as…@gmail.com> wrote:
Is there any report (or possibility to create such a report) that will show actual quantity and price on one screen? with a possibility to search by code and item name both? It would be a great tool for sales persons.

Slava



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

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

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









Thanks and Regards,

Umair Sayyed
www.erpnext.com

Hi Slava,

This report is possible, the only thing needed is someone with good knowledge of mysql query. Here is the hint, since I am not an expert and hence cannot give the correct code:

  • Join the 3 tables: tabBIN, tabITEM and tabITEM PRICE
  • Now you are looking for this kind of a report if I am not wrong.

<!–[if gte mso 10]>

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:8.0pt; mso-para-margin-left:0cm; line-height:107%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;}

<![endif]–>

Item Code

Item Name

Price List

WH1

WH2

WH3

A

Name of Item A

PL1

10

20

30

A

Name of Item A

PL2

10

20

30

B

Name of Item B

PL1

1

4

2

C

Name of Item C

PL1

5

6

7

Now you would notice that if you have multiple price lists for an item then the items would be shown in multiple lines. I would try and work out the code for this report, since this report would indeed be very helpful for sales people.

Let me give it a try and I would come back if I am successful.

On Tuesday, April 9, 2013 10:43:23 AM UTC+5:30, Slava wrote:
Is there any report (or possibility to create such a report) that will show actual quantity and price on one screen? with a possibility to search by code and item name both? It would be a great tool for sales persons.

Slava



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

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

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

 

 

Hi Slava,

I have created one report which can be accessed vide this link. What you would need to do:

  1. Create a query report.
  2. Copy paste this code in the query report.
  3. Change line no 10 and 11 with the actual names of the warehouse in your system, currently there is a facility to show the stock in 2 warehouses, you can add many more warehouses as well.
There is a problem with this report. The problem is:
  1. It shows one item in one line even if there are multiple price list for the item. I think some one good at joining the tables might be able to tell the real problem since the joins used are not correct.
If you could see my table where item A is listed twice since it has 2 price lists this is not happening in this report.

Maybe this helps.
On Tuesday, April 9, 2013 4:52:37 PM UTC+5:30, Addy wrote:
Hi Slava,

This report is possible, the only thing needed is someone with good knowledge of mysql query. Here is the hint, since I am not an expert and hence cannot give the correct code:

  • Join the 3 tables: tabBIN, tabITEM and tabITEM PRICE
  • Now you are looking for this kind of a report if I am not wrong.

Item Code

Item Name

Price List

WH1

WH2

WH3

A

Name of Item A

PL1

10

20

30

A

Name of Item A

PL2

10

20

30

B

Name of Item B

PL1

1

4

2

C

Name of Item C

PL1

5

6

7

Now you would notice that if you have multiple price lists for an item then the items would be shown in multiple lines. I would try and work out the code for this report, since this report would indeed be very helpful for sales people.

Let me give it a try and I would come back if I am successful.

On Tuesday, April 9, 2013 10:43:23 AM UTC+5:30, Slava wrote:
Is there any report (or possibility to create such a report) that will show actual quantity and price on one screen? with a possibility to search by code and item name both? It would be a great tool for sales persons.

Slava



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

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

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

 

 

Addy, great job! I find it really faster and more convenient rather than creating new Sales order. Yes I see, it works properly only for items with one price-list.

On Wednesday, April 10, 2013 8:43:55 AM UTC+5:30, Addy wrote:

Hi Slava,

I have created one report which can be accessed vide this link. What you would need to do:
  1. Create a query report.
  2. Copy paste this code in the query report.
  3. Change line no 10 and 11 with the actual names of the warehouse in your system, currently there is a facility to show the stock in 2 warehouses, you can add many more warehouses as well.
There is a problem with this report. The problem is:
  1. It shows one item in one line even if there are multiple price list for the item. I think some one good at joining the tables might be able to tell the real problem since the joins used are not correct.
If you could see my table where item A is listed twice since it has 2 price lists this is not happening in this report.

Maybe this helps.
On Tuesday, April 9, 2013 4:52:37 PM UTC+5:30, Addy wrote:
Hi Slava,

This report is possible, the only thing needed is someone with good knowledge of mysql query. Here is the hint, since I am not an expert and hence cannot give the correct code:

  • Join the 3 tables: tabBIN, tabITEM and tabITEM PRICE
  • Now you are looking for this kind of a report if I am not wrong.

Item Code

Item Name

Price List

WH1

WH2

WH3

A

Name of Item A

PL1

10

20

30

A

Name of Item A

PL2

10

20

30

B

Name of Item B

PL1

1

4

2

C

Name of Item C

PL1

5

6

7

Now you would notice that if you have multiple price lists for an item then the items would be shown in multiple lines. I would try and work out the code for this report, since this report would indeed be very helpful for sales people.

Let me give it a try and I would come back if I am successful.

On Tuesday, April 9, 2013 10:43:23 AM UTC+5:30, Slava wrote:
Is there any report (or possibility to create such a report) that will show actual quantity and price on one screen? with a possibility to search by code and item name both? It would be a great tool for sales persons.

Slava



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

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

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

 

 

I know it is possible to show items with 2 prices lists twice but I guess I am not so good at mysql joining of tables since my understanding is limited in that regards. Hopefully some one could come up with a correction in the join.

On Wednesday, April 10, 2013 10:44:29 AM UTC+5:30, Slava wrote:

Addy, great job! I find it really faster and more convenient rather than creating new Sales order. Yes I see, it works properly only for items with one price-list.

On Wednesday, April 10, 2013 8:43:55 AM UTC+5:30, Addy wrote:
Hi Slava,

I have created one report which can be accessed vide this link. What you would need to do:
  1. Create a query report.
  2. Copy paste this code in the query report.
  3. Change line no 10 and 11 with the actual names of the warehouse in your system, currently there is a facility to show the stock in 2 warehouses, you can add many more warehouses as well.
There is a problem with this report. The problem is:
  1. It shows one item in one line even if there are multiple price list for the item. I think some one good at joining the tables might be able to tell the real problem since the joins used are not correct.
If you could see my table where item A is listed twice since it has 2 price lists this is not happening in this report.

Maybe this helps.
On Tuesday, April 9, 2013 4:52:37 PM UTC+5:30, Addy wrote:
Hi Slava,

This report is possible, the only thing needed is someone with good knowledge of mysql query. Here is the hint, since I am not an expert and hence cannot give the correct code:

  • Join the 3 tables: tabBIN, tabITEM and tabITEM PRICE
  • Now you are looking for this kind of a report if I am not wrong.

Item Code

Item Name

Price List

WH1

WH2

WH3

A

Name of Item A

PL1

10

20

30

A

Name of Item A

PL2

10

20

30

B

Name of Item B

PL1

1

4

2

C

Name of Item C

PL1

5

6

7

Now you would notice that if you have multiple price lists for an item then the items would be shown in multiple lines. I would try and work out the code for this report, since this report would indeed be very helpful for sales people.

Let me give it a try and I would come back if I am successful.

On Tuesday, April 9, 2013 10:43:23 AM UTC+5:30, Slava wrote:
Is there any report (or possibility to create such a report) that will show actual quantity and price on one screen? with a possibility to search by code and item name both? It would be a great tool for sales persons.

Slava



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

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

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

 

 

To bring this back from 10 months ago....is this query report still applicable? I tried to add it and the report gets stuck on the "loading report...." screen. 

Thanks,
Alec



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

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

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/b9d8444f-66d0-425d-838c-e60ffd9d1181%40googlegroups.com.

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