Okay; I understand. Let me explain using a simpler version, and also come
up with SQL that would do the equivalent.
Here's a simpler version. This example shows the problem, and simply uses a
grandparent table (A), a parent table (B), and a child table (C). That is,
C is a child of B, and B is a child of A:
db.define_table('A',
Field('A_name', 'string', required=True),
format='%(A_name)s')
db.define_table('B',
Field('A_id', db.A),
Field('B_name', 'string', required=True),
format='%(B_name)s')
db.define_table('C',
Field('B_id', db.B),
Field('C_name', 'string', required=True),
format='%(C_name)s')
db.C.B_id.requires=IS_IN_DB(db, 'B.id',
lambda r : '%s' % (db.A[db.B[r.B_id].A_id].A_name)
)
If I leave the lambda out, then the drop-down values are B.id values. I can
make that be B.B_name values easily, but what I want are A.A_name values.
The lambda is fairly straightforward (although I'm obviously missing
something).
lambda r : '%s' % (db.A[db.B[r.B_id].A_id].A_name)
The 'r' is the current record, and we're looking at a C record, so it first
evaluates r.B_id, B_id being the foreign key in the C record of its parent
B record. Let's say this evaluates to 123 (for example), which would then
be id 123 in the B table. Now, replacing r.B_id with 123 we get:
lambda r : '%s' % (db.A[db.B[123].A_id].A_name)
So now it evaluates db.B[123].A_id, A_id being the foreign key in the B
record of its parent A record:
lambda r : '%s' % (db.A[db.B[123].A_id].A_name)
Let's say that evaluates to 456, which would then be id 456 in the A table.
Replacing db.B[123].A_id with 456 we get:
lambda r : '%s' % (db.A[456].A_name)
So now it evaluates db.A[456].A_name, which is what I'd like to see in the
drop-down. And yet the error I get is:
<type 'exceptions.KeyError'> 'B_id'
An equivalent SQL to get the A_name for each C id would be:
SELECT C.id, A.A_name
FROM A INNER JOIN
B ON A.id = B.A_id INNER JOIN
C ON B.id = C.B_id;
Thoughts?
Thanks.
On Wednesday, July 11, 2012 3:24:41 AM UTC-6, Johann Spies wrote:
>
> On 11 July 2012 05:27, MichaelF <[email protected]> wrote:
>
>> Didn't help. I now get the same error, this time in the function. Any
>> idea what the error message is trying to tell me?
>
>
> Unfortunately I don't have time to try and work out your logic. The error
> is telling you that the process could not find the key C1_2 in your
> database query. You have some logical proglem in your code.
>
> Try and build an SQL-query to which you can translate into a function.
>
> Regards
> Johann
>
> --
> Because experiencing your loyal love is better than life itself,
> my lips will praise you. (Psalm 63:3)
>
>
