Smartgrid: How to display value from foreign table in a table when only foreign key is in table

[Alex Glaros]

Instead of db.Word.dictionaryTypeID displaying (which is a foreign key in db.Word), I’d like a value from the foreign table to appear, i.e., DictionaryType. dictionaryName.

In the example below, when user cascades down to the Word table, it only shows db.Word.dictionaryTypeID but I’d like to add the corresponding DictionaryType. dictionaryName value to it.

def search_lang():  

   grid = SQLFORM.smartgrid(db.HumanLanguage, linked_tables=['Word','DictionaryType'], fields = [db.HumanLanguage.id, db.HumanLanguage.languageName, db.Word.id, db.Word.wordName, db.Word.definition, db.DictionaryType.dictionaryName,

       db.Word.dictionaryTypeID],

       user_signature=False)

   return dict(grid=grid)

id	Wordname	Definition	Dictionarytypeid
1	beaker	glass jar	1

Want to replace the "1" under Dictionarytypeid with value from foreign table.

Thanks, 

Alex Glaros

[Jim S]

Can you show the model code?

-Jim

[Alex Glaros]

db.define_table('HumanLanguage',Field('languageName','string'),Field('forumLocations','string'),Field('comments','string'), auth.signature)

db.HumanLanguage.languageName.requires = IS_NOT_EMPTY()

db.define_table('Word',Field('wordName','string'), Field ('definition', 'string'), Field('languageID','reference HumanLanguage'),Field('dictionaryTypeID','reference DictionaryType'),Field('wordReferenceModelID','reference WordReferenceModel'), Field('comments','string'), auth.signature)

db.Word.languageID.requires = IS_IN_DB(db, 'HumanLanguage.id', '%(languageName)s',zero=T('choose one'))

db.Word.dictionaryTypeID.requires = IS_IN_DB(db, 'DictionaryType.id', '%(dictionaryName)s',zero=T('choose one'))

db.Word.wordName.requires = IS_NOT_EMPTY()

db.Word.wordReferenceModelID.requires = IS_NULL_OR(IS_IN_DB(db, 'WordReferenceModel.id', '%(wordID)s',zero=T('choose one')))

## Uses English language as standard connector between all languages. WordID below points to the English version of the word that is the standard. The reason "Is_Null" clause is there is because the first time the word is encountered, it won't be in the English dictionary

db.define_table('WordReferenceModel',Field('wordID','reference Word'),Field('dictionaryTypeID','reference DictionaryType'), Field('picture', 'upload', default=''),Field('comments','string'), auth.signature) 

db.WordReferenceModel.wordID.requires = IS_NOT_EMPTY()

db.WordReferenceModel.wordID.requires = IS_IN_DB(db, 'Word.id', '%(wordName)s',zero=T('choose one'))

db.WordReferenceModel.dictionaryTypeID.requires = IS_IN_DB(db, 'DictionaryType.id', '%(dictionaryName)s',zero=T('choose one'))

## dictionary_type_query = (db.DictionaryType.dictionaryName=='English')

## /* need this too for wordReferenceModel */

## Dictionary type means what category of dictionary is it? Medical, computer,etc.  There is one for each language.

db.define_table('DictionaryType',Field('dictionaryName','string'),Field('comments','string'), Field('languageID','reference HumanLanguage'),

    Field('DictionaryReferenceModelID', 'reference DictionaryReferenceModel'), auth.signature)

db.DictionaryType.dictionaryName.requires = IS_NOT_EMPTY()

db.DictionaryType.languageID.requires = IS_IN_DB(db, 'HumanLanguage.id', '%(languageName)s',zero=T('choose one'))

db.DictionaryType.DictionaryReferenceModelID.requires = IS_IN_DB(db, 'DictionaryReferenceModel.id', '%(DictionaryTypeID)s',zero=T('choose one'))

## Uses English dictionary type as standard connector between all dictionary types. DictionaryType.id points to the English DictionaryType.id

db.define_table('DictionaryReferenceModel', Field('DictionaryTypeID','reference DictionaryType'),Field('comments','string'), 

    auth.signature)

db.DictionaryReferenceModel.DictionaryTypeID.requires = IS_NOT_EMPTY()

db.define_table('Synonyms',Field('synonymName','string'),Field('wordID','reference Word'),Field('comments','string'), 

    auth.signature)

db.Synonyms.synonymName.requires = IS_NOT_EMPTY()

db.Synonyms.wordID.requires = IS_NOT_EMPTY()

db.Synonyms.wordID.requires = IS_IN_DB(db, 'Word.id', '%(Word)s',zero=T('choose one'))

db.define_table('PublicComments',Field('wordID','reference Word'),Field('comments','string'), 

    auth.signature)

db.PublicComments.comments.requires = IS_NOT_EMPTY()

db.PublicComments.wordID.requires = IS_NOT_EMPTY()

db.PublicComments.wordID.requires = IS_IN_DB(db, 'Word.id', '%(wordName)s',zero=T('choose one'))

[Jim S]

Add this line before creating your smartgrid:

db.Word.dictionaryTypeID.represent = lambda s,r: s.dictionaryName

Does that help?  It worked for the trimmed down model I made...

-Jim

[Alex Glaros]

It worked Jim

thanks so much for going through the process of writing and testing the code.

I'm new to web2py, is there any preference as to where that statement goes: controller or model?

much appreciated,

Alex

--
 
---
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 web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Javier Pepe]

Alex

Use represent for this:

http://web2py.com/books/default/chapter/29/06#Record-representation

Ej:

db.Word.dictionaryTipeID.represent = lambda id,row: db.DictionaryType(id).dictionaryName

[Jim Steil]

Depends on whether or not you want that behavior globally or not.  Remember, your model gets executed on every request.  So, if you want that behavior globally I'd put it there.  If you just need it for one instance, put it in the controller.

-Jim

[Alex Glaros]

There is a grandparent (HumanLanguage), parent (DictionaryType), child (Word), relationship.  If I go straight from the grandparent to the child, "represent" works.  

(First I click on HumanLangages, then I click on Word)

If I go from the parent to the child, I get this message: <type 'exceptions.TypeError'> <lambda>() takes exactly 2 arguments (1 given)

(First I click on HumanLangages, then I click on DictionaryType, then, I click on Word)

If I reduce the number of parms to db.Word.dictionaryTypeID.represent = lambda id: db.DictionaryType(id).dictionaryName, then it works if I go from the parent to the child, but if I go from the grandparent to the child, I get <type 'exceptions.TypeError'> <lambda>() takes exactly 1 argument (2 given).

If a join (result of a query) was allowed, it would solve the problem but I think they are not supported in smartgrid. 

Any ideas would be much appreciated,

Thanks,

Alex

[Alex Glaros]

I've added another represent  HumanLanguage "name" to represent HumanLanguage "ID" in the Word table, and it works correctly.  Same table, same everything, so I will see if there's any differences in what I'm doing.

Alex

On Friday, February 22, 2013 10:08:04 AM UTC-8, Alex Glaros wrote:

There is a grandparent (HumanLanguage), parent (DictionaryType), child (Word), relationship.  If I go straight from the grandparent to the child, "represent" works.  

(First I click on HumanLangages, then I click on Word)

If I go from the parent to the child, I get this message: <type 'exceptions.TypeError'> <lambda>() takes exactly 2 arguments (1 given)

(First I click on HumanLangages, then I click on DictionaryType, then, I click on Word)

If I reduce the number of parms to db.Word.dictionaryTypeID.represent = lambda id: db.DictionaryType(id).dictionaryName, then it works if I go from the parent to the child, but if I go from the grandparent to the child, I get <type 'exceptions.TypeError'> <lambda>() takes exactly 1 argument (2 given).

If a join (result of a query) was allowed, it would solve the problem but I think they are not supported in smartgrid. 

Any ideas would be much appreciated,

Thanks,

Alex

On Friday, February 22, 2013 5:24:48 AM UTC-8, Jim S wrote:

[Jim Steil]

Sorry, I'm kinda lost in what you're trying to do.  Can you show it in code or screen shots?

-Jim

On Fri, Feb 22, 2013 at 12:08 PM, Alex Glaros <alexg...@gmail.com> wrote:

There is a grandparent (HumanLanguage), parent (DictionaryType), child (Word), relationship.  If I go straight from the grandparent to the child, "represent" works.  

(First I click on HumanLangages, then I click on Word)

If I go from the parent to the child, I get this message: <type 'exceptions.TypeError'> <lambda>() takes exactly 2 arguments (1 given)

(First I click on HumanLangages, then I click on DictionaryType, then, I click on Word)

If I reduce the number of parms to db.Word.dictionaryTypeID.represent = lambda id: db.DictionaryType(id).dictionaryName, then it works if I go from the parent to the child, but if I go from the grandparent to the child, I get <type 'exceptions.TypeError'> <lambda>() takes exactly 1 argument (2 given).

If a join was allowed, it would solve the problem but I think they are not supported in smartgrid. 

Any ideas would be much appreciated,

Thanks,

Alex

On Friday, February 22, 2013 5:24:48 AM UTC-8, Jim S wrote:

[Alex Glaros]

thanks for offering to take a look.  

Screen shots here: https://docs.google.com/document/d/1e517YRN8FK08vqsVWmp1UuZ0LLevCNlkrhWyCkBQ7uQ/edit?usp=sharing

Alex

[Jim Steil]

Ouch, I'm going to have to defer to to someone who understands smartgrid better than I do.  You've got me stumped on this one.

[Alex Glaros]

thanks for giving it a shot but depending upon whether I use quotes or not, either the data column disappears from the web page or I get errors

On Fri, Feb 22, 2013 at 1:47 PM, Kyle Flanagan <kylefl...@gmail.com> wrote:

Have you tried adding a format to your tables and defining your tables using objects instead of 'reference...'?

I'm no web2py expert by any means, but I had a similar situation and when I changed the reference for my foreign id to db.other_table instead of 'reference other_table,' the format defined for that table was used instead of the id.

Something like this:

db.define_table('HumanLanguage',
    Field('languageName','string'),
    Field('forumLocations','string'),

    Field('comments','string'),

    format = '%(languageName)s'), # define a format
    auth.signature)
db.HumanLanguage.languageName.requires = IS_NOT_EMPTY()

##
## Define DictionaryReferenceModel and WordReferenceModel above here
## DictionaryReferenceModel, WordReferenceModel should have a format
##

## Dictionary type means what category of dictionary is it? Medical, computer,etc.  There is one for each language.
db.define_table('DictionaryType',

    Field('dictionaryName','string'),

    Field('comments','string'),

    Field('languageID',db.HumanLanguage ), # changed to object
    Field('DictionaryReferenceModelID', db.DictionaryReferenceModel), # changed to object
    format = '%(dictionaryName)s', # define format

    auth.signature)
db.DictionaryType.dictionaryName.requires = IS_NOT_EMPTY()
db.DictionaryType.languageID.requires = IS_IN_DB(db, 'HumanLanguage.id', '%(languageName)s',zero=T('choose one'))
db.DictionaryType.DictionaryReferenceModelID.requires = IS_IN_DB(db, 'DictionaryReferenceModel.id', '%(DictionaryTypeID)s',zero=T('choose one'))

db.define_table('Word',

Field('wordName','string'),
Field ('definition','string'),

Field('languageID', db.HumanLanguage ), # changed to object
Field('dictionaryTypeID',db.DictionaryType), # changed to object
Field('wordReferenceModelID',db.WordReferenceModel), # changed to object

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/va56WuVvbqI/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Jim S]

Just to be clear, he isn't asking just to remove quotes, but to specify your reference columns as:

db.table_name

instead of

'reference table_name'

-Jim

[Alex Glaros]

web2py doesn't seem to accept that format on my windows version.  I get

<type 'exceptions.AttributeError'> 'DAL' object has no attribute 'DictionaryType'

The error below doesn't come up when there is no data:

<type 'exceptions.TypeError'> <lambda>() takes exactly 2 arguments (1 given)

The represent attribute works if there are two files in play instead of three

I created a simplified version and changed every field name in case there was some reserved word problem but it didn't work

What I will do is write something similar from scratch with different table and field names.

Thanks,

Alex

[Jim Steil]

Can you paste the table definition that choked on you?

-Jim

On Sat, Feb 23, 2013 at 1:34 PM, Alex Glaros <alexg...@gmail.com> wrote:

web2py doesn't seem to accept that format on my windows version.  I get

<type 'exceptions.AttributeError'> 'DAL' object has no attribute 'DictionaryType'

The error below doesn't come up when there is no data:

<type 'exceptions.TypeError'> <lambda>() takes exactly 2 arguments (1 given)

The represent attribute works if there are two files in play instead of three

I created a simplified version and changed every field name in case there was some reserved word problem but it didn't work

What I will do is write something similar from scratch with different table and field names.

Thanks,

Alex

[Alex Glaros]

db.define_table('Word',

Field('wordName','string'), 

Field ('definition', 'string'), 

Field('languageID','reference HumanLanguage'),

Field('dictionaryTypeID',db.DictionaryType),

[Jim Steil]

Does it occur after the definition of db.DictionaryType in your model file?

Sorry for the dumb questions, just going over mistakes I've made in the past...

-Jim

On Sat, Feb 23, 2013 at 2:09 PM, Alex Glaros <alexg...@gmail.com> wrote:

db.define_table('Word',

Field('wordName','string'), 

Field ('definition', 'string'), 

Field('languageID','reference HumanLanguage'),

Field('dictionaryTypeID',db.DictionaryType),

Field('wordReferenceModelID','reference WordReferenceModel'), 

Field('comments','string'), auth.signature)

[Alex Glaros]

I changed the order as you suggested and no longer get this error:

<type 'exceptions.AttributeError'> 'DAL' object has no attribute 'DictionaryType'

thanks for that tip

But the error below sill exists

<type 'exceptions.TypeError'> <lambda>() takes exactly 2 arguments (1 given)

thanks,

Alex

[Jim Steil]

Can you show the line of code where this is happening?

Jim

[Alex Glaros]

I don't know how to see the lines of internal code; would be interested in learning.  

I have uploaded the bundled app and data to: http://gov-ideas.com/techdictionary/   If anyone is interested they can download and run it on windows.

Have also listed the error log here: https://docs.google.com/document/d/1e517YRN8FK08vqsVWmp1UuZ0LLevCNlkrhWyCkBQ7uQ/edit?usp=sharing

thanks, 

Alex

[Jim Steil]

Does that mean it is happening in your model in the db.define_table somewhere?  If so, can you post what you have there?

-Jim

[Alex Glaros]

Unsure what you mean.  There's no error messages when creating the model or controller.  The app (see screen shots) works without errors for some situations, but when you click in the wrong order, the error will appear if there is data in the file.  No error if there is no data in the file. 

[Alex Glaros]

thanks for working through this Kyle

I made the changes but I don't see what would make "Medical" instead of "1" appear without the "represent" clause.

It now runs without errors but displays "1" instead of "Medical"

Did any lines besides the added "format" lines change, and the deleted #16 and 17?

Where is the functionality that substitutes dictionaryName for dictionaryTypeID?

thanks,

Alex

On Monday, February 25, 2013 7:00:22 AM UTC-8, Kyle Flanagan wrote:

Alex,

I downloaded your app and ran it. If I understand you correctly, you're trying to get "Medical" to show up instead of "1" when you navigate to Humanlanguages -> English -> Dictionarytypes -> Words?

I achieved this by editing your db.py to the following:

##...

## Dictionary type means what category of dictionary is it? Medical, computer,etc.  There is one for each language.
db.define_table('DictionaryType',
    Field('dictionaryName','string'),

    Field('comments','string'), 

    Field('languageID','reference HumanLanguage'),

    Field('DictionaryReferenceModelID', 'reference DictionaryReferenceModel'), 

    auth.signature, 
    format='%(dictionaryName)s') # added format tag here

db.DictionaryType.dictionaryName.requires = IS_NOT_EMPTY()

db.DictionaryType.languageID.requires = IS_IN_DB(db, 'HumanLanguage.id', '%(languageName)s',zero=T('choose one'))

db.DictionaryType.DictionaryReferenceModelID.requires = IS_IN_DB(db, 'DictionaryReferenceModel.id', '%(DictionaryTypeID)s',zero=T('choose one'))

##...

..and I removed lines 16 and 17 from your default.py controller (the db.Word.dictionaryTypeID.represent statements). 

Try that and see if that was what you were going for.

[Mark]

The grandparent, parent, child relationships maybe too complex for smartgrid, or maybe a bug in smartgrid? Try to use the IF statement to differentiate the relationships:

if 'DictionaryType.languageID' in request.args:

    db.Word.dictionaryTypeID.represent = lambda id: db.DictionaryType(id).dictionaryName

else:

    db.Word.dictionaryTypeID.represent = lambda s,r: s.dictionaryName

On Friday, February 22, 2013 1:08:04 PM UTC-5, Alex Glaros wrote:

There is a grandparent (HumanLanguage), parent (DictionaryType), child (Word), relationship.  If I go straight from the grandparent to the child, "represent" works.  

(First I click on HumanLangages, then I click on Word)

If I go from the parent to the child, I get this message: <type 'exceptions.TypeError'> <lambda>() takes exactly 2 arguments (1 given)

(First I click on HumanLangages, then I click on DictionaryType, then, I click on Word)

If I reduce the number of parms to db.Word.dictionaryTypeID.represent = lambda id: db.DictionaryType(id).dictionaryName, then it works if I go from the parent to the child, but if I go from the grandparent to the child, I get <type 'exceptions.TypeError'> <lambda>() takes exactly 1 argument (2 given).

If a join (result of a query) was allowed, it would solve the problem but I think they are not supported in smartgrid. 

Any ideas would be much appreciated,

Thanks,

Alex

On Friday, February 22, 2013 5:24:48 AM UTC-8, Jim S wrote:

[Alex Glaros]

It worked!

thanks Mark and everyone that looked at this.

Alex Glaros

[Alex Glaros]

Kyle,

How do I install your packed up?

I downloaded it and selected it from my download directory in the below GUI, and also tried getting the URL location, but both said "Invalid application name" error

thanks,

Alex

Upload and install packed application

Application name:	Invalid application name
Upload a package:
Or Get from URL:
(can be a git repo)	Overwrite installed app

On Monday, February 25, 2013 9:28:11 AM UTC-8, Kyle Flanagan wrote:

Those were the only changes that I remember. I've packed up the changes I made to your app and attached them, just in case there's something I don't remember changing. I've also attached a screenshot of where I see "Medical" to make sure we're on the same page about what you're trying to accomplish.

As for where the functionality lies, I'm not exactly certain. I'm very new to web2py and I just happened to notice that using the format argument allowed id fields to be displayed according to another corresponding field in the database.

Attachments:
Packed app: http://dl.dropbox.com/u/169215/web2py.app.TechDictionary.w2p

Screen shot: http://dl.dropbox.com/u/169215/TechDictionarySS.PNG

[steve van christie]

please fill with whatever name you want 

Application name: testdict

Upload a package: (path where you downloaded from dropbox file : web2py.app.TechDictionary.w2p)

click install

[Alex Glaros]

thanks, the install worked, and thanks Kyle, the word "medical" appeared correctly.  I don't know which line drives that, but will re-read the code.

much appreciated,

Alex