Displaying database information without repeating details

[mostwanted]

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

[Clemens]

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

[Jim S]

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

[mostwanted]

I am able to group them up using (groupby) e.g (products=db().select(db.sales.ALL, orderby=db.sales.product.name, groupby=db.sales.product.name)) but now I have a problem calculating the sum of each item's sold quantities.

[Massimiliano]

You need to create an alias for your field.

Look here:

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=count%28%29#sum-avg-min-max-and-len

Massimiliano

--
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+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/313a1e44-7044-4cd9-b603-d0659c73b04dn%40googlegroups.com.

[Jim S]

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

[mostwanted]

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>

[Jim S]

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>&nbsp;</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

[mostwanted]

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)

[Jim S]

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">&nbsp;</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">&nbsp;</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

[mostwanted]

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.