hi, i wannna create report and have the queries from tables, the result is not expected. e.g. def product(): from_date = request.vars.from_date to_date = request.vars.to_date query_purchase = (db.purchase_header.purchase_date >= from_date) & (db.purchase_header.purchase_date <= to_date) query_sale = (db.sale_header.sale_date >= from_date) & (db.sale_header.sale_date <= to_date) purchase_result = 0 sale_result = 0
for row_purchase_header in db(query_purchase).select(): sum_quantity_purchase = db.purchase_detail.quantity.sum() query_purchase_detail = (row_purchase_header.id == db.purchase_detail.purchase_no) & (db.purchase_detail.product == db.product.id) purchase_result = db(query_purchase_detail).select(sum_quantity_purchase).first()[sum_quantity_purchase] if db(query_purchase).select() else 0 # for row_purchase_detail in db(db.purchase_detail.purchase_no.belongs(query_purchase) ).select(): # for row_purchase_header in db(query_purchase).select(): # sum_quantity_purchase = db.purchase_detail.quantity.sum() # # query_purchase_detail = db.product.id.belongs(row_purchase_detail.product == db.product.id) # query_purchase_detail = db.product.id.belongs((row_purchase_header.id == db.purchase_detail.purchase_no) & (db.purchase_detail.product == db.product.id) ) # query_purchase_detail = (row_purchase_header.id == db.purchase_detail.purchase_no) & (db.purchase_detail.product == db.product.id) # # purchase_result = db(query_purchase_detail)._select(sum_quantity_purchase).first()[sum_quantity_purchase] if db(query_purchase).select() else 0 for row_sale_header in db(query_sale).select(): sum_quantity_sale = db.sale_detail.quantity.sum() query_sale_detail = (row_sale_header.id == db.sale_detail.sale_no) & (db.sale_detail.product == db.product.id) sale_result = db(query_sale_detail).select(sum_quantity_sale).first()[sum_quantity_sale] if db(query_sale).select() else 0 product_links=[dict(header=T('In'), body=lambda row: DIV(purchase_result) ), dict(header=T('Out'), body=lambda row: DIV(sale_result) ), dict(header=T('Begining'), body=lambda row: DIV(row.quantity + sale_result - purchase_result) ) ] #query = (db.product.id == row_purchase_detail.product) & (db.product.id == row_sale_detail.product) #query = ((db.purchase_header.purchase_date >= from_date) & (db.purchase_header.purchase_date <= to_date) & (db.sale_header.sale_date >= from_date) & (db.sale_header.sale_date <= to_date) & (db.purchase_header.id == db.purchase_detail.purchase_no) & (db.sale_header.id == db.sale_detail.sale_no) & (db.purchase_detail.product == db.product.id) & (db.sale_detail.product == db.product.id) ) grid = SQLFORM.grid(db.product) return locals() the result is the value is all same for In (taken from purchase detail for all product) and out (taken from sale detail for all product) and the begining value calculation is wrong. i know i have the mistake on above code but can not figure it where. any hints how to achieve it? thanks and best regards, stifan -- 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. For more options, visit https://groups.google.com/groups/opt_out.