Dropdown populated by database

Anvil Q&A
1

I have a form where I’m taking input to add to a row in my database. The database has three fields, one of which is a linked field to another database. I need a drop down from the linked database to offer to the user for this field. However, I’m struggling with how to get the drop down populated with the rows that they can choose from. After studying the articles I found in the forum, I now have set up what you see below. However, I’m getting a “SyntaxError: bad input” error.

Here’s the scenario:
Database name = fy
Field user should see is: fyear
I’m assuming the value would be the unique identifier for the selected row. Is that right?

self.drop_down_1.items = set([str(row[‘fyear’]) for row in app_tables.fy.search()]

Any ideas on why this is throwing an error? TIA.

2

Quick first look - you’re missing a closing bracket (count the opening & closing parentheses for the set & str functions)

3

Hi there,

Anvil deals with databases a little differently than you might be used to. You don’t have to think about the unique identifier of a row (I mean, you can get it, with row.get_id(), but you don’t need to). Instead, in Anvil you deal with row objects. They’re a bit like dictionaries - you can do:

print row['name']

But you can also do this to update a column:

row['name'] = "Athelene"
# Also works: row.update(name="Athelene")

When you get (or set) the value of a link column, you don’t get the unique ID of a row - you get the row object itself. So, if you had the table structure you described in your previous post, you could do:

for row in app_tables.coa.search():
  print "Category: %s; Year: %d" % (row['category_name'], row['fy']['fyear'])

Note that because the “fy” column in your coa table is a link to another table, when we do row['fy'] we get row object back – which means we can immediately look up the 'fyear' column in that row.

Anyway, all of this is to say that, if you want to select a table row from a DropDown, 95% of the time this is what you want:

self.drop_down_1.items = [(str(row['fyear']), row) for row in app_tables.fy.search()]

That will make a DropDown with an item for every row in your fy table. Each item is labelled with the year (the stringified value of the 'fyear' column), but when you select, an item, the DropDown’s selected_value property will refer to the row object itself.

This means you can then do:

print "Adding new record to FY %d" % self.drop_down_1.selected_value['fyear']

app_tables.coa.add_row(category='whatever',
                       category_type='X',
                       fy=self.drop_down_1.selected_value)

See that self.drop_down_1.selected_value is a row object from the fy table (so we can do things like looking up columns on it). You use that row object to fill in the 'fy' column in the new coa row - that’s how you create a link between tables in Anvil.

I hope that clarifies how table linking works in Anvil!

2 Likes
4

AGHH! I hate when it’s something so stupid! Thank you.

1 Like
5

Meredydd, thank you so much for that explanation! That will really help me with lots of things down the road. As David pointed out above, I missed a paren (grr!) and when I fixed it, it worked. But with your explanation I much better understand WHY this works. Great help. Thank you so much.

6

OK, back again. I have the drop down loading as expected. However, when I follow the next steps to add the row, I am getting another error. I used your example and my code reads:

app_tables.coa.add_row(category_name=self.addcat.text,category_type=rb,fy=self.drop_down_1.selected_value)

I’m getting this error: tables.TableError: Column ‘fy’ is a Row from ‘fy’ table - cannot set it to a string

This makes some sense to me since we set the dropdown as a string above. But I’m not sure how to get the add_row piece to see it as a row again. Is there a process I need to use to convert it back?

7

This is what my post above was about. You don’t want to put the string into the fy= parameter there - you want to put a row object. The easiest way to do this is to configure the DropDown so that selected_value is a row object:

Anyway, all of this is to say that, if you want to select a table row from a DropDown, 95% of the time this is what you want:

self.drop_down_1.items = [(str(row['fyear']), row) for row in app_tables.fy.search()]

That will make a DropDown with an item for every row in your fy table. Each item is labelled with the year (the stringified value of the ‘fyear’ column), but when you select, an item, the DropDown’s selected_value property will refer to the row object itself.

8

Working. Thanks again. I’m going to beat this! I appreciate the help.

2 Likes