Hi Happy Rob,
If you want this to be flexible, I guess you would need something
more like this:
entrants: id, name
exams: id, descr
exam_entry: exam_id, entrant_id, total_scrore
questions: id, exam_id, question_num, question_text
options: id, question_id, option_text, score
answers: id, exam_entry_id, option_id
It is important to try not to compromise on your data structure. It
may be more difficult to display the forms and process them, but
usually you win in the long run by 'normalising' your data as much as
possible.
Best wishes, D
On May 4, 12:44 pm, Happy Rob <happy.rob.ang...@gmail.com> wrote:
> Computed fields from multiple columns
> I'm new to programming and have a question
> How can I make a computed field that adds up from another table
> depending on the contents of the first table?
> eg A multi choice exam has the results entered by formfactory.
> The answers and their scores are stored in a separate table
>
> db
> table:answers
> answers.question, answers.answer, answers.score
> 1. 3rd planet, a. mars, 0
> 1. 3rd planet, b. Earth, 10
> 1. 3rd planet, c. Pluto, 0
> 2. Best movie, a. Toy Story 3, 5
> 2. Best movie, b. Casablanka, 10
> 3. Best movie, c. Twilight, -1000
>
> db
> table:exam
> exam.id exam.name exam.score exam.question01 exam.question02
> 1 Fred Smith 20 b. Earth
> b.Casablanka
> 2 Sarah Smith 15 b. Earth
> a.Toy Story 3
>
> As can be seen, the exam score is the sum of the answer scores given.
>
> My difficulty is in the model...
>
> m = DAL ('sqlite://storage.sqlite')
> from gluon.tools import *
> crud = Crud(globals(),m)
> m.define_table('answers',
> Field('question'),
> Field('answer'),
> Field('score',int))
>
> m.define_table('exam',
> Field('name'),
>
> Field('score',compute=lambda
> r:m(m.answers.answer==r.question01).select()[0].score
>
> +m(m.answers.answer==r.question02).select()[0].score),
> Field('question01'),
> Field('question02'))
>
> It reads the score quite well if checking only one question, but if
> adding both, it returns None.
> How could I modify it to add up from multiple questions?
> You help is greatly appreciated.
