So, I came to a solution by changing a line in the SQLFORM.factory as
so:
Field('Add', requires=IS_EMPTY_OR(IS_IN_SET(placeddict,
multiple = True)),
widget=SQLFORM.widgets.multiple.widget),
The multiple widget seems to work for this. I'd rather have
checkboxes, but unless anyone has a solution using checkboxes, this
will have to do.
On Oct 21, 1:07 pm, Nate Atkinson <[email protected]> wrote:
> Hi,
>
> I have a question on using the checkboxes widget, and the correct way
> to go about what I want to do. Here's some background:
>
> The company I work for places environmental monitoring equipment in
> the field. I recently made a small database application to keep track
> of where we have equipment. My boss has asked if I can include payment
> details in the database. I said "sure."
>
> The "business logic" goes about like this--
> Someone called a "sitefinder" finds a place where we can put some
> equipment. Every so often, the sitefinder will send an invoice to the
> head office for his sites. Head office sends the sitefinder a check.
> Usually the first check is for the value of (# of sites) * (price per
> site) * 70%. After the equipment has been on site for a month, the
> sitefinder will get another check for the additional 30%. Sometimes,
> the check will be mixed-- for example 5 new sites @ 70% and 4 old
> sites @ 30%.
>
> So, I already had a table for the sites where we have equipment. It's
> db.plads (name is for legacy reasons). There's also a table for the
> sitefinders (db.wards-- also for legacy reasons). There are a few
> other tables (status, state, etc.).
>
> I added two tables. They are db.payments and db.payment details.
>
> The models for the two new tables look like this:
>
> db.define_table('payments',
> Field('sitefinder', db.wards),
> Field('date', 'date'),
> Field('amount', 'double'),
> Field('comments', 'text'),
> format = '%(sitefinder.wardname)s, %(date)s')
>
> db.payments.sitefinder.requires = IS_IN_DB(db, db.wards.id, '%
> (wardname)s')
> db.payments.date.requires = IS_DATE()
>
> db.define_table('paymentdetails',
> Field('payment', db.payments),
> Field('plads', db.plads),
> Field('percentage', 'integer'))
>
> db.paymentdetails.payment.requires = IS_IN_DB(db, db.payments.id, '%
> (sitefinder)s, %(date)s')
> db.paymentdetails.plads.requires = IS_IN_DB(db, db.plads.id, '%
> (name)s, %(address)s')
>
> The idea is that when we pay someone the boss (or more likely, me
> doing the data entry) will create a new payment showing who we are
> paying, the date, amount we're paying, and various notes (like check
> #, etc.). Once the payment is created, we add sites to the payment
> (paymentdetails table).
>
> The most straightforward way I can think of to do this is to have a
> page showing a list of all sites from a sitefinder that are eligible
> for payment. There should be a checkbox next to each site, and box at
> the bottom to enter the percentage of payment. So, I can check 5
> sites, enter 70 in the percentage box, hit submit, and 5 rows will be
> added to the paymentdetails table containing the payment id, place id,
> and percentage of payment.
>
> Here's the code that I've written to implement this (not the
> prettiest, still a work in progress):
>
> Controller:
>
> def paymentdetails():
>
> def gettotalpercent(somesite):
> #Calculates total percentage of payments on a site
> percentset = [x.percentage for x in
> somesite.paymentdetails.select()]
> return sum(percentset)
>
> #Query database for already existing paymentdetails on this
> payment
> payment = db.payments(request.args(0))
> details = db(db.paymentdetails.payment == db.payments.id).select()
>
> #Generate the list of sites from sitefinder w/ less than 100%
> payment for checkboxes widget
> placedbysitefinder = db(db.plads.sitefinder ==
> payment.sitefinder).select()
> placedlessthan100 = filter(lambda a: gettotalpercent(a) < 100,
> placedbysitefinder)
> placeddict = {}
> for place in placedlessthan100:
> placeddict[place.id] = place.name + ', ' +
> str(gettotalpercent(place)) + '%'
>
> #create a the SQL form. Shows a checkbox widget with all sites
> from sitefinder that haven't been fully paid
> #user can check which sites to add to a payment, and input the
> percentage of payment
> addform = SQLFORM.factory(
> Field('Add', requires=IS_EMPTY_OR(IS_IN_SET(placeddict,
> zero=T('Choose One'))),
> widget=SQLFORM.widgets.checkboxes.widget),
> Field('Percentage',
> requires=IS_EMPTY_OR(IS_INT_IN_RANGE(0,101)))
> )
>
> #step through all of the checked sites and insert them into
> paymentdetails
> if addform.process().accepted:
> percentage = addform.vars.Percentage
> for place in addform.vars.Add:
> db.paymentdetails.insert(payment = payment.id, plads =
> place, percentage = percentage)
> redirect(URL('paymentdetails', args=payment.id))
>
> return dict(details = details, addform = addform)
>
> View:
>
> {{extend 'layout.html'}}
> <h1>This is the default/paymentdetails.html template</h1>
> {{=details}}
> {{=addform}}
>
> So, this kind of works, but not really. I'm running into two problems:
>
> 1) In the following section of code:
>
> for place in addform.vars.Add:
> db.paymentdetails.insert(payment = payment.id, plads =
> place, percentage = percentage)
>
> The value that gets inserted is not plads.id. Instead, it appears that
> the index of site in the checkboxes widget is inserted. So, if I check
> the first item in the checkboxes widget, 0 gets inserted instead of
> the id of the place on that row.
>
> 2) If I select multiple checkboxes, the form does not validate. It
> gives me 'value not allowed' below each checkbox.
>
> I have two questions:
>
> 1) Am I even going about this the correct way? Am I totally off base
> on how to implement records of payments?
>
> 2) If I am going about this the correct way, how can I insert the
> correct value for plads.id? How can I insert multiple values?
>
> Thanks for any help. Best regards,
>
> Nate Atkinson
