Hi,
I'm trying to create a query report for use with multiple tables and can't seem to get it to work. I'm not extremely well versed in MySQL, and might be able to figure it out but there isn't an error console when creating a query report, it just will say "Loading Report" and never load when something is broken.
Brief background : For sale of parts, one part can fit in many engines. The different engines that a part is compatible with are stored in the website item group table, so that a customer can select their engine and see available parts on the ERPNext website.
I need a query report that allows me to type in a certain engine, and see parts that are compatible with that engine (and further filter in top bar by brand, part number, etc), along with current stock, and pricing. I can't do a script report as I don't know how, and don't have access to backend.
Here's what I've gotten done so far:
SELECT
tabWebsite Item Group.item_group as "Engine:120",
tabItem.item_code as "Part Number:Link/Item:150",
tabItem.brand as "Brand:120",
tabItem.item_group as "Item Group:Link/Item Group:120",
tabItem Price.price_list as "Price List:120",
tabItem Price.ref_rate as "Rate:Currency:120",
tabItem.actual_qty as "Actual:80",
tabItem.projected_qty as "Projected:80"
FROM
tabWebsite Item Group, tabItem, tabItem Price
WHERE
tabItem.is_purchase_item = "Yes"
It's notable that this doesn't work both with and without single quotes around the tables - but when I do a test-table, with SELECT 'tabItem'.item_code (note quotes) FROM 'tabItem', it does not work. When the quotes are removed, it works. So I don't know how to handle tables with a space in their name, as if I try to make a table with only price_list from tabItem Price, it does not work.
I've also experimented with using a UNION command but have failed there as well.
Thank you for your help and time,
Alec Ruiz-Ramon
–
Note:
If you are posting an issue,
- 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/ccd62967-6050-4046-b989-321ad701b8b3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.