Hi Anthony, here is the minimal app, and I did reproduce the problem and narrowed where it broke. In view_invoices() action, it checks if customer id args is provided, if so it will take it in for invoice_query = db.invoice.customer==customer_id, if not invoice_query = db.invoice.customer != None (pls let me know if there is a better way to define the query for selecting all invoice). Below code works when I specify the customer id in the URL eg. http://localhost:8000/tests/default/view_invoices/2, now when i click on the linked_tables 'Items', it will redirect to http://localhost:8000/tests/default/view_invoices/2/invoice/item.invoice/1. Now, if I don't specify the customer id in URL eg. http://localhost:8000/tests/default/view_invoices/, i still got the list of invoices, but when I clicked on the linked_tables 'Items' for any particular invoice, it will redirect to http://localhost:8000/tests/default/view_invoices/invoice/item.invoice/1, error raised - ValueError: invalid literal for long() with base 10: 'invoice'. Any further pointer is much appreciated.
Another question related to it is - how does web2py construct the URL for redirect when linked_tables is used? eg. http://localhost:8000/tests/default/view_invoices/2/invoice/item.invoice/1. The blue part is what I typed in, the red is provided by the linked_tables. In db.py: db.define_table('customer', Field('company_name'), format='%(company_name)s', ) auth.settings.extra_fields['auth_user']= [ Field('customer', 'reference customer', label=T('Customer'), notnull=True, required=True, requires=IS_IN_DB(db, db.customer.id, '%(company_name)s')), Field('mobile', requires=IS_NOT_EMPTY()) ] In db_accounting.py: db.define_table('invoice', Field('customer', 'reference customer'), Field('project_name'), Field('amount', 'double'), auth.signature) # in db_accounting.py of consumer accounting app db.define_table('item', Field('invoice', 'reference invoice', writable=False), Field('description'), Field('unit_price', 'double'), Field('quantity', 'integer'), auth.signature) In default.py: def view_invoices(): customer_id = request.args(0) if customer_id: invoice_query=db.invoice.customer==customer_id else: invoice_query=db.invoice.customer!=None constraints=dict(invoice=invoice_query) invoice_grid = SQLFORM.smartgrid(db.invoice, args=request.args[:1], constraints=constraints, linked_tables=dict(invoice=['item'], item=[])) return locals() p.s. Dave, i think Anthony is right, i don't think we need to specify an ID for the linked table according to the documentation, but I appreciate you are chipping in your idea. On Friday, March 31, 2017 at 11:58:06 PM UTC+8, Anthony wrote: > > It's not quite clear without seeing more code. I suggest you pack and > attach a *minimal *app that reproduces the problem. > > Anthony > > On Thursday, March 30, 2017 at 5:57:09 PM UTC-4, Rudy wrote: >> >> Thanks Anthony for your explanation of request.args use with smartgrid, I >> tried your suggestion, but had the same error. I think i made a mistake in >> the constraints in my earlier post (according to the documentation, >> constraints is a dict of tablename:query), here is what i have changed. >> >> invoice_grid = SQLFORM.smartgrid(db.invoice, args=request.args[:1], >> constraints=dict(invoice=invoice_query), >> linked_tables=dict(invoice=['item'], item=[])) >> >> Noted that i could select a set of invoices based on the customer_id I >> passed in through request.args(0), the only thing it broke was when I >> clicked on the linked_tables "Items" with error Query Not Supported: >> invalid literal for long() with base 10: 'item'. >> >> I have been stuck here for a few days, any further help is much >> appreciated. >> >> On Thursday, March 30, 2017 at 12:33:07 PM UTC+8, Rudy wrote: >>> >>> Hi there, >>> >>> I am very new to web2py, i posted 1 question here a week ago, but never >>> got reply (if anyone sees my post, please let me know if I missed >>> anything), I am trying luck with another question now. >>> I have a customer table, an invoice table and an item table. I >>> simplified them below: >>> >>> db.define_table('customer', Field('company_name', >>> requires=IS_NOT_EMPTY()), auth.signature, format='%(company_name)s') >>> db.define_table('invoice', Field('customer', 'reference >>> customer'), Field('project_name', requires=IS_NOT_EMPTY())) >>> db.define_table('item', Field('invoice', 'reference invoice', >>> writable=False), Field('description'), Field('unit_price', 'double')) >>> >>> When I used smartgrid without specifying the constraints below, the >>> linked_tables 'Items' worked correctly (i.e. i clicked Items link, it >>> showed me all the items belongs to that particular invoice), but obviously >>> i could not select a particular set of invoices based on a customer id. >>> when I added the constraints, i could select the particular invoices based >>> on the given customer id, but the linked_tables 'Items' broke with this >>> error message - Query Not Supported: invalid literal for long() with >>> base 10: 'item'. The URL got redirected from >>> http://localhost:8000/connect28/accounting/view_invoices/invoice/item.invoice/10?_signature=0653b123ba6f4b43b82c48d49516af9c772b1496 >>> >>> to http://localhost:8000/connect28/accounting/view_invoices/item. Any >>> help and pointers is much appreciated. >>> >>> def view_invoices(): >>> customer_id = request.args(0) >>> if customer_id: >>> invoice_query=db.invoice.customer==customer_id >>> else: >>> invoice_query=db.invoice.customer!=None >>> constraints=dict(invoice=invoice_query) >>> db.item.invoice.writable=False #avoid invoice # from any particular >>> row in item table get overwritten >>> invoice_grid = SQLFORM.smartgrid(db.invoice, constraints=constraints) >>> return locals() >>> >> -- 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/d/optout.
