Apr 16, 2013
Ron
wrote
Hi, on our invoices we have product descriptions with additional attributes. So if the product is "screw" and the attribute: Pack size is say 10, then the description being shown on the invoice is "Screw Quantity : 10" and that is great.
In a spreadsheet or database I can extract the last few characters of a field using field length and right() so for example i could use right(2) and it will give me 10
Is it possible in the report tool editor to use a function similar to "Right" so that I can extract the last few characters (Which I will then use in a calculation"
So in other works is there anyway to make "Screw Quantity : 10" just say "10"
OR
can I grab just the attribute data?
In a spreadsheet or database I can extract the last few characters of a field using field length and right() so for example i could use right(2) and it will give me 10
Is it possible in the report tool editor to use a function similar to "Right" so that I can extract the last few characters (Which I will then use in a calculation"
So in other works is there anyway to make "Screw Quantity : 10" just say "10"
OR
can I grab just the attribute data?
Reply
7 Answers
Apr 17, 2013
Dmytro Grycelyak
agent
wrote
Hello Ron,
Could you please provide us with the screen capture of the invoice, so we could check it more deeply and advise you on this regard.
Could you please provide us with the screen capture of the invoice, so we could check it more deeply and advise you on this regard.
Apr 17, 2013
Ron
wrote
Thanks for the reply
I have attached a screen shot. You will see the customer has bought 1 pack of 5 (the five is a prestashop attribute - most would use coluurs e.g red shoes, but we have them as pack sizes)
We have the invoices barcoded so that we can book out the stock from our database. As this invoice stands it would book out "1" turnbuckle. I want to take the attribute "5" and multiply by the qty sold so that we have the total number bought
Therefore if customer buys 1 pack of 5, we can book out 5, if they buy 2 packs of 5 we can book out 10, if they buy 2 packs of 10 we can book out 30 etc
Hope that makes sense
In essence how can I get at the "5" at the end of the product description
I have attached a screen shot. You will see the customer has bought 1 pack of 5 (the five is a prestashop attribute - most would use coluurs e.g red shoes, but we have them as pack sizes)
We have the invoices barcoded so that we can book out the stock from our database. As this invoice stands it would book out "1" turnbuckle. I want to take the attribute "5" and multiply by the qty sold so that we have the total number bought
Therefore if customer buys 1 pack of 5, we can book out 5, if they buy 2 packs of 5 we can book out 10, if they buy 2 packs of 10 we can book out 30 etc
Hope that makes sense
In essence how can I get at the "5" at the end of the product description
Apr 18, 2013
Dmytro Grycelyak
agent
wrote
Thank you for screen capture.
You have to retrieve the total quantity of the product in the invoice.
We assume that you understand that in order to proceed with these actions you must have at least basic MySQL skills:
1. Go to this file:
"c:\Users\%username%\AppData\Local\eMagicOne Store Manager for PrestaShop\Reports\SQL\Invoice15.sql"
*use file 13 or 14 if you have older PrestaShop website.
2. Add a line to select:
RIGHT(T2.product_name,1) * T2.product_quantity AS 'final_qty'
*don't forget about comma :-)
3. Go to this file:
"c:\Users\%username%\AppData\Local\eMagicOne Store Manager for PrestaShop\Reports\Invoice.fr3"
4. Insert a new column under MasterData1 section
5. In new Memo write Text value:
[frxInvoiceDS."final_qty"]
6. Should look something like this:
http://screencast.com/t/5Gw2x0pvZ
After that you should have additional column in Invoice that contains "final" quantity.
You have to retrieve the total quantity of the product in the invoice.
We assume that you understand that in order to proceed with these actions you must have at least basic MySQL skills:
1. Go to this file:
"c:\Users\%username%\AppData\Local\eMagicOne Store Manager for PrestaShop\Reports\SQL\Invoice15.sql"
*use file 13 or 14 if you have older PrestaShop website.
2. Add a line to select:
RIGHT(T2.product_name,1) * T2.product_quantity AS 'final_qty'
*don't forget about comma :-)
3. Go to this file:
"c:\Users\%username%\AppData\Local\eMagicOne Store Manager for PrestaShop\Reports\Invoice.fr3"
4. Insert a new column under MasterData1 section
5. In new Memo write Text value:
[frxInvoiceDS."final_qty"]
6. Should look something like this:
http://screencast.com/t/5Gw2x0pvZ
After that you should have additional column in Invoice that contains "final" quantity.
Apr 18, 2013
Ron
wrote
That is perfect
Except
I have realised a flaw in my own reasoning as soon as I saw your solution
The attribute quantity could be 10 or even a 100
So for example "M10 Bolts - Quantity : 100"
So I would need
RIGHT(T2.product_name,3) if the attribute was 100
Can I do what I can in a spreadsheet and return all the data AFTER the colon?
Ahh, and another issue, some products don't have attributes so RIGHT(T2.product_name,1) would the last letter of the description which would then cause an error because it is not numeric. So can we don something like
If there is a colon in the description take all the data after the colon (and convert to numeric?), else use "1"?
Except
I have realised a flaw in my own reasoning as soon as I saw your solution
The attribute quantity could be 10 or even a 100
So for example "M10 Bolts - Quantity : 100"
So I would need
RIGHT(T2.product_name,3) if the attribute was 100
Can I do what I can in a spreadsheet and return all the data AFTER the colon?
Ahh, and another issue, some products don't have attributes so RIGHT(T2.product_name,1) would the last letter of the description which would then cause an error because it is not numeric. So can we don something like
If there is a colon in the description take all the data after the colon (and convert to numeric?), else use "1"?
Apr 22, 2013
Dmytro Grycelyak
agent
wrote
1. Yes there is a MySQL function that does what you need. Please use this:
TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity AS 'final_qty'
2. You may also use brief MySQL help yourself:
http://screencast.com/t/K1mD1zNH
or official Oracle MySQL documentation at:
http://dev.mysql.com/doc/
TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity AS 'final_qty'
2. You may also use brief MySQL help yourself:
http://screencast.com/t/K1mD1zNH
or official Oracle MySQL documentation at:
http://dev.mysql.com/doc/
Apr 22, 2013
Ron
wrote
Brilliant, thanks for your help
I combined the trim with an If statement. By testing if TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity is true tells me if it is numeric or not. If true, I return that value, if not I keep the original quantity
IF (TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity,TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity, T2.product_quantity) AS final_qty ,
I combined the trim with an If statement. By testing if TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity is true tells me if it is numeric or not. If true, I return that value, if not I keep the original quantity
IF (TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity,TRIM(SUBSTRING_INDEX(T2.product_name,':',-1)) * T2.product_quantity, T2.product_quantity) AS final_qty ,
Apr 23, 2013
Dmytro Grycelyak
agent
wrote
Thank you for keeping us informed!