I think I have it:
Do you think this is the best way of doing this??
Many thanks again!!
Controller:
def index():
form = SQLFORM(db.tr_items)
if form.process().accepted:
item_in_out = form.vars.item
amount_in_out = form.vars.amount
row = db.products_summary(product=item_in_out)
if row==None:
db.products_summary.insert(product=item_in_out,
cumulative_amount=amount_in_out)
else:
db.products_summary.update_or_insert(db.products_summary.product
==item_in_out, product=item_in_out, cumulative_amount=db(db.products_summary
.product==item_in_out).select(db.products_summary.cumulative_amount)[0][
"cumulative_amount"]+form.vars.amount)
On Tuesday, 17 November 2015 10:12:06 UTC, RedBeard wrote:
>
> 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.
