Price and stock in one report

[Slava]

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

[Umair Sayyed]

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 <asta...@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-fo...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Thanks and Regards,
Umair Sayyed
www.erpnext.com

[Addy]

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.

[Addy]

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.

[Slava]

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.

[Addy]

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.

[Alec Ruiz-Ramon]

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