Hey dps,
Thanks for your reply. It is definitely getting closer, you're right, the
reference was a good idea, I hadn't put it in because if a tr_items, item
doesn't exist in product I wanted it to be added.
I now have:
Model:
db.define_table('products_summary',
Field('product', unique=True),
Field('cumulative_amount', default='0', type='double'))
db.define_table('tr_items',
Field('products_summary_id', 'reference products_summary',
writable=False),
Field('item'),
Field('amount', type='double'))
Controller:
def index():
form = SQLFORM(db.tr_items)
if form.process().accepted:
item_in_out = form.vars.item
amount_in_out = form.vars.amount
db.products_summary.update_or_insert(db.products_summary.product==item_in_out,
product=item_in_out,
cumulative_amount=db(db.products_summary.product).select(db.products_summary.cumulative_amount)[0]["cumulative_amount"]+form.vars.amount)
response.flash = T("Product: %s Amount: %d") % (item_in_out,
amount_in_out)
return locals()
It is getting closer, but it isn't quite right: I seem to have two
problems:
1 - When both databases are empty, and I first try to add an item I get the
following error:
<type 'exceptions.IndexError'> list index out of range
2 - The [0] for the cumulative seems to reference the very first product,
take that amount and then add the amount the user entered in tr_item and
add them - I am trying to have the amount for the same product (or create a
new product with amount 0) and then add the tr_item amount to it.
Thanks again!!
On Tuesday, 17 November 2015 00:13:42 UTC, Dave S wrote:
>
>
> On Sunday, November 15, 2015 at 11:58:01 AM UTC-8, RedBeard wrote:
>>
>> Hi,
>>
>>
> Hi. See below (inline)
>
>
>> I have only just started learning python and web2py so please bear with
>> me.
>>
>> When a user inserts an item and amount in tr_items, I am trying to have
>> the system check whether the item exists in products_summary, if it does,
>> update the amount by the amount input by the user into tr_items - if it
>> doesn't exist, the product should be added to products_summary and the
>> amount set to the amount input by the user.
>>
>> Below I have put in three variations that most make sense, but I have
>> tried a whole load of different possibilities. Any ideas on how I could go
>> about solving this problem would be great!
>>
>> Model:
>>
>> db.define_table('products_summary',
>> Field('product'),
>> Field('cumulative_amount', default='0', type='double'))
>>
>>
>> db.define_table('tr_items',
>> Field('item'),
>> Field('amount', type='double'))
>>
>>
>>
> Are products_summary.product and tr_items supposed to be the same
> identification? If so, you may want to change one to a reference to the
> other.
> See <URL:
> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Field-types>,
>
> where "reference" is one of the field types.
> More at <URL:
> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=reference+type#One-to-many-relation
> >
>
>
>
>
>>
>> Controller:
>>
>> def index():
>> form = SQLFORM(db.tr_items)
>> if form.process().accepted:
>> item_in_out = form.vars.item
>> amount_in_out = form.vars.amount
>> #1
>>
>> #db.products_summary.update_or_insert(db.products_summary.product==item_in_out,
>>
>> product=item_in_out,
>> cumulative_amount=db.products_summary.cumulative_amount+form.vars.amount)
>> #2
>>
>> #db.products_summary.update_or_insert(db.products_summary.product==item_in_out,
>>
>> product=item_in_out,
>> cumulative_amount=db(db.products_summary.product).select(db.products_summary.cumulative_amount)+form.vars.amount)
>> #3
>> db.products_summary.update_or_insert(db.products_summary.product
>> ==item_in_out, product=item_in_out, cumulative_amount=+form.vars.amount)
>> response.flash = T("Product: %s Amount: %d") % (item_in_out,
>> amount_in_out)
>> return locals()
>>
>>
>>
>> Error message #1
>>
>> <class 'sqlite3.OperationalError'> no such column:
>> products_summary.cumulative_amount
>>
>> Error message #2
>>
>> <type 'exceptions.TypeError'> unsupported operand type(s) for +: 'Rows'
>> and 'float'
>>
>>
> I believe that #2 is what you want, but you have to extract the field of
> interest from the Rows object. A Rows object is (roughly) an array of Row
> objects, and a Row object is (roughly) a dictionary of fields. Assuming
> you only get 1 match to your query, you'd want
>
> db.products_summary.update_or_insert(db.products_summary.product==
> item_in_out, product=item_in_out,
> cumulative_amount=db(db.products_summary.product).select(db.
> products_summary.cumulative_amount)[0]["cumulative_amount"]
> + form.vars.amount)
>
>
>
> Error message #3
>>
>> No error message - this overwrites cumulative amount to the amount
>> entered by the user, no memory.
>>
>
> Python doesn't have the increment assignment operator, so number 3 is
> being taken as
>
> cumulative_amount = NULL + form.vars.amount
>
>
>
>>
>>
>>
>> Thanks for any help!!
>>
>
>
> If this was of any help, you're welcome!
>
> /dps
>
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
