Thank you Jim, i'll keep working on it, i'm still struggling with summing
up quantity sales values for each individual item but i will figure it out.
Thanks for your help. Gratitude.
On Thursday, October 28, 2021 at 4:41:30 PM UTC+2 Jim S wrote:
> In that case, then I'd do something like this:
>
> If the product name changes, add a 'total' row showing the totals from the
> previous product name.
>
> <tbody>
> {{last_item_name=[]}}
> {{item_quantity = 0}}
> {{for sales in products:}}
> <tr>
> {{if sales.product.name not in last_item_name:}}
> {{if item_quantity > 0:}}
> </tr>
> <tr>
> <td colspan="2"> </td>
> <td>{{=item_quantity}}</td>
> {{item_quantity = 0}}
> </tr>
> <tr>
> {{pass}}
> <td>{{=sales.product.name}}</td>
> <td>{{=sales.product.Quantity}}</td>
> {{last_item_name.append(sales.product.name)}}
> {{else:}}
> <td colspan="2"> </td>
> <td>{{=sales.quantity}}</td>
> {{item_quantity += sales.product.quantity}}
> {{pass}}
> </tr>
> {{pass}}
> </tbody>
>
> Again, I think the query will need some work, adding a left join to the
> product table and setting the sort order properly. Add the left join will
> then require that you include the table name in your field references in
> your template.
>
> -Jim
>
> On Thursday, October 28, 2021 at 9:31:09 AM UTC-5 mostwanted wrote:
>
>> I apologize for that, i'm terrible at giving variables, my variable
>> confuse me at times too, *sales.product.Quantity* is the initial stock,
>> its the Quantity in the *Products *table, different from the sold
>> quantity in the *sales *table (*sales.quantity*)
>>
>> On Thursday, October 28, 2021 at 4:22:15 PM UTC+2 Jim S wrote:
>>
>>> Ok, here is what I would do in my template:
>>>
>>> <tbody>
>>> {{last_item_name=[]}}
>>> {{item_quantity = 0}}
>>> {{for sales in products:}}
>>> <tr>
>>> {{if sales.product.name not in last_item_name:}}
>>> <td>{{=sales.product.name}}</td>
>>> {{last_item_name.append(sales.product.name)}}
>>> {{else:}}
>>> <td> </td>
>>> {{pass}}
>>> <td>{{=sales.product.Quantity}}</td>
>>> {{item_quantity += sales.product.Quantity}}
>>> <td>{{=item_quantity}}</td>
>>> </tr>
>>> {{pass}}
>>> </tbody>
>>>
>>> In my controller I'd go another step further and add an orderby to
>>> ensure all products with the same name are in order and in the order of
>>> when they were purchased. But, this probably means you need a left join as
>>> well.
>>>
>>> I started coding this, but then was confused by what your columns are
>>> supposed to be. From what I gathered,
>>>
>>> Name
>>> Quantity Sold for this sale
>>> Total quantity so far
>>>
>>> But now I'm not sure I'm right
>>>
>>> What does product.Quantity represent vs sales.quantity?
>>>
>>> -Jim
>>>
>>> On Thursday, October 28, 2021 at 8:57:38 AM UTC-5 mostwanted wrote:
>>>
>>>> Hey Jim, thanks for your proposed solution,it worked as shown below, i
>>>> hope this is how you imagined it but this works now the problem I still
>>>> have &cant imagine is how I do the sum up of all of the item's purchase
>>>> quantity, how would you go about it:
>>>>
>>>> *CONTROLLER:*
>>>> products=db().select(db.sales.ALL)
>>>>
>>>> *VIEW:*
>>>> <script>
>>>> $(function() { *//Script to hide the empty rows*
>>>> $("table tr").each(function() {
>>>> var cell = $.trim($(this).find('td').text());
>>>> if (cell.length == 0){console.log('empty');
>>>> $(this).addClass('nodisplay');
>>>> }});});
>>>> </script>
>>>>
>>>> <tbody>
>>>> {{last_item_name=[]}}
>>>> {{for sales in products:}}
>>>> <tr>
>>>> {{if sales.product.name not in last_item_name:}}
>>>> <td>{{=sales.product.name}}</td>
>>>> <td>{{=sales.product.Quantity}}</td>
>>>> <td>{{=sales.quantity}}</td>
>>>>
>>>> {{last_item_name.append(sales.product.name)}}
>>>> {{else:}}
>>>> <td class="hideTd"> </td>
>>>> {{pass}}
>>>> {{pass}}
>>>> </tr>
>>>> </tbody>
>>>>
>>>> On Thursday, October 28, 2021 at 2:01:34 PM UTC+2 Jim S wrote:
>>>>
>>>>> In the solution I proposed, I would create a variable in my template
>>>>> and sum it as I looped through the records.
>>>>>
>>>>> It might help if you showed a visual example of what you're trying to
>>>>> accomplish. I may be misunderstanding the question, and if so, giving bad
>>>>> advice.
>>>>>
>>>>> -Jim
>>>>>
>>>>> On Thursday, October 28, 2021 at 4:15:49 AM UTC-5 mostwanted wrote:
>>>>>
>>>>>> I am able to group them up using *(groupby) *e.g
>>>>>> (*products=db().select(db.sales.ALL,
>>>>>> orderby=db.sales.product.name <http://db.sales.product.name>,
>>>>>> groupby=db.sales.product.name <http://db.sales.product.name>)*) but
>>>>>> now I have a problem calculating the sum of each item's sold quantities.
>>>>>>
>>>>>> On Wednesday, October 27, 2021 at 8:27:50 PM UTC+2 Jim S wrote:
>>>>>>
>>>>>>> I have done this before, but not using SQLFORM.grid
>>>>>>>
>>>>>>> In a nutshell
>>>>>>>
>>>>>>> 1. Create table tag
>>>>>>> 2. create your table header
>>>>>>> 3. create a temp variable last_item_name and set to None
>>>>>>> 4. loop through all the data you're going to display
>>>>>>> 5. in the item_name column, check if the current item name is equal
>>>>>>> to last_item_name - if it is, put blanks in that cell, if not, put the
>>>>>>> item
>>>>>>> name
>>>>>>> 6. set last_item_name = current item name
>>>>>>>
>>>>>>> Not pretty or clever, more of a brute force way to get it to work.
>>>>>>>
>>>>>>> -Jim
>>>>>>>
>>>>>>> On Wednesday, October 27, 2021 at 9:56:28 AM UTC-5 Clemens wrote:
>>>>>>>
>>>>>>>> Have a look here:
>>>>>>>>
>>>>>>>> http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#groupby-having
>>>>>>>>
>>>>>>>> Combining this with:
>>>>>>>>
>>>>>>>> http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#sum-avg-min-max-and-len
>>>>>>>>
>>>>>>>> Is it what you need?
>>>>>>>>
>>>>>>>> Best regards
>>>>>>>> Clemens
>>>>>>>>
>>>>>>>>
>>>>>>>> On Wednesday, October 27, 2021 at 4:44:11 PM UTC+2 mostwanted wrote:
>>>>>>>>
>>>>>>>>> I have a sales database table that records items sold in a store,
>>>>>>>>> an item can appear several times in the table having been sold
>>>>>>>>> several
>>>>>>>>> times or in different days. What i wanna do is display this
>>>>>>>>> information in
>>>>>>>>> an html table in a view without the item names repeating also with
>>>>>>>>> the sold
>>>>>>>>> quantity summed up for every item that appears more than once, how
>>>>>>>>> can i
>>>>>>>>> achieve this?
>>>>>>>>>
>>>>>>>>> Regards
>>>>>>>>>
>>>>>>>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/c78fb117-48dc-4a54-8fee-77544430780bn%40googlegroups.com.
