self.dropdown_description.items = app_tables.todo.search()
Gives an error
Exception: ‘items’ must be a list of strings or tuples
at Form1, line 30
So I guess how do I return just one column app_tables_todo.search().Description ?
Thanks
Lucas
That’s right - the items property of a DropDown must be an iterable object containing strings or 2-tuples. If they’re tuples, the first element of the tuple is the text displayed for that option in the DropDown, and the second element is what you’ll get from the selected_value property if that option is selected. (See the docs here.)
app_tables.todo.search() gives you an iterable object containing rows from the data table. Rows are a bit like dictionaries - you can access column values using square brackets, eg row['description'].
So if you have a text column in your table called 'description', you can turn a list of rows into a list of strings with a list comprehension:
self.dropdown_description.items = [row['description'] for row in app_tables.todo.search()]
Then, you can get the selected description string by reading self.dropdown_description.selected_value.
But what if you didn’t want the string? You want the dropdown box to show the description text, but you want to access the row object itself (eg so you can access other columns on that row). That’s where you use a tuple:
self.dropdown_descriptions.items = [(row['description'], row) for row in app_tables.todo.search()]
Now, self.dropdown_descriptions.selected_value will be a row from the table. So if you had another column called 'done', you could mark the selected row as done with code like this:
selected_row = self.dropdown_descriptions.selected_value
selected_row['done'] = True
Does that help explain it?
Something like this I think :
data=app_tables.todo.search()
arr=[]
for d in data:
arr.append(d['Description])
self.drop_down_1.items=arr
EDIT - Meredydd beat me to it with a much more comprehensive answer 
Thanks. This helped.
I guess my issue was I was returning list of “years” which are int so I had to convert to string first.
I have 1 more:
My next question is how do I do group by:
self.dropdown_descriptions.items = [(row['Year'], row) for row in app_tables.testcars.search()]
This returns year 2012 twice…
select year from cars group by year
Hello,
I have a table that contains year,make,models(s), Description.
2012, Ford, Mustang , FWD (front will drive)
2012, Ford, Mustang , RWD
2011, Ford, Mustang , RWD
self.dropdown_year.items = [str(row[‘Year’]) for row in app_tables.testcars.search()]
This returns year 2012 twice…
2012
2012
2011
I can see in your docs how do to order by, but I can’t find how to do group by:
select year from cars group by year
The Anvil guys can be definitive but at the moment I don’t think you can group on the data tables yet. You may either have to do it in python or move to an SQL database.
However, for your case above (and using my example) you can use a “set” :
data=app_tables.todo.search()
arr=[]
for d in data:
arr.append(d['Description])
self.drop_down_1.items=set(arr)
Not tried this but using your code this should work :
self.dropdown_year.items = set([str(row[‘Year’]) for row in app_tables.testcars.search()])
)
set works for now…but who is doing the calculation?
javascript in user browser? or webserver?
What will happen when I have 36,000 rows, and instead of returning 20 values, I get 36000, then convert that to a set? That doesn’t seem to be optimal, and therefore I would think:
a) have database do its job…aka group by
or
b)?
Thanks
Lucas
Depends where you run it. You can run the code to collect the data and create the set in a server module and return that to the front end. You would usually run all database access in a server module and call it with
data_for_dropdown = anvil.server.call("myserverfunc")
from the form (client side), then set the item data on the drop down from this return set. This way your front end remains the same and you can change how the back end gets its data invisibly.
If you are handling that amount of data and it needs sorting or grouping then I think an SQL database is the way to go (I use them all the time for everything). Anvil has the PostgreSQl library built in, and they’ll add a MySQL one (I use PyMySQL) if you ask them.
You could also run the server side code on your own hardware using the Uplink system (see links below).
The context of your example was however to fill a drop down, and I was assuming you wouldn’t want 30,000+ elements in that so the calculation in that case was being done on the front end form (browser).
Some useful links in Anvil docs :
https://anvil.works/doc/index.html#-div-id-data_tables_sql-data-tables-and-sql-div-
https://anvil.works/doc/index.html#-div-id-server_modules-server-modules-div-6
https://anvil.works/doc/index.html#-div-id-uplink-server-uplink-div-
https://anvil.works/doc/index.html#-div-id-python_modules-python-libraries-and-limitations-div-
Thanks. I’ll consider that closer to prod version.
How do I set the dropdown value to blank as a default first value?
In the init section I have this:
self.dropdown_year.items = set([str(row['Year']) for row in app_tables.testcars.search()])
How do I get set the dropdown to be blank first, so that when the user sets it to 1st value , it triggers the
self.dropdown_year_1_change event?
Currently the first value is returned, and the website does not seem to be know something was selected until i changed the value to next item then back to the first value in a selection.
a) In this section you guys indicate you need to set self.drop_down_1.selected_value=“foo”
The dropdown with above code does not start at null, but rather at the first item returned.
b) but below causes error saying "
self.dropdown_year.items = set([str(row['Year']) for row in app_tables.cars.search()])
self.dropdown_year.selected_value="Please Select Value"
Could you provide an answer on this, or pointer to docs?
Thanks
Lucas
Hello Meredydd,
I tried to follow this same process with data tables I built in my app. I have a table called anomaly_category and I have a dropdown in a form named anomaly_category_choice. There are four columns in the anomaly_category table so I tried to call the string value from the column “category.” All I get is a blank drop down and I have tried several ways to write the code. My code:
from anvil import *
import anvil.server
import tables
from tables import app_tables
import anvil.users
class Form1(Form1Template):
def init(self, **properties):
# You must call self.init_components() before doing anything else in this function
self.init_components(**properties)
# Any code you write here will run when the form opens.
def anomaly_category_choice_change (self, **event_args):
self.anomaly_category_choice.items = [(row[“category”]) for row in app_tables.anomaly_category.search()]
self.anomaly_category_choice.selected_value
My app:
https://HN7TRNTGJ52C3UJX.anvilapp.net/AP3CS2OSHWKWZS72CGI2YRXX
Can you share your app and allow cloning?
I’ll take a look at the source and see if I can see what’s happening.
Hello David…I actually just figured out the first dropdown. Now I need to get the second. I have a many-to-many table I built that defines the category and type.
I updated the code as follows:
from anvil import *
import anvil.server
import tables
from tables import app_tables
import anvil.users
class Form1(Form1Template):
def init(self, **properties):
# You must call self.init_components() before doing anything else in this function
self.init_components(**properties)
# Any code you write here will run when the form opens.
self.anomaly_category_choice.selected_value = 'External Metal Loss'
self.anomaly_category_choice.items = [(row["category"]) for row in app_tables.anomaly_category.search()]
acat = self.anomaly_category_choice.selected_value
I will save my app and allow cloning. Here is the cloning link https://anvil.works/ide#clone:HN7TRNTGJ52C3UJX=J4D3ZMN5ZB5C3VGH7IP2MOAB
You’ll need to post the cloning link (I think it’s different).
Sorry I see what you did
I’m not 100% sure what you are trying to do.
i see the one drop down but not a second.
Are you trying to retrieve another value based on the drop down selection? If so, which one?
I just updated with the extra drop down and a server module to build the set of items to be called. Not sure if I’m even close to right…
I may have to pick this up in the morning, sorry. Midnight here and school run in the morning.
If you haven;t worked it out by then I’m pretty sure i can point you in the right direction then.
But in the mean time, and apologies if I’m being a bit thick, but I’m still not sure what you are expecting as the end result.
Could you give me an example - say I select “External Metal Loss” in drop down 1, what would you expect drop down 2 to show? If you could give me actual values it would help me understand.
I think you might have slightly overcomplicated it - but get me that info and I’ll look again in the AM.
Hello David,
No worries about signing off. I am working on the Holiday over here. My goal for many of the forms I have to create is to have dependent dropdown fields. So for this arrangement we have a category and type. I have created tables category, type, and then a many-to-many table called anomaly_category_m2m_type that will allow me to normalize the responses. When a user selects a category value, the type dropdown choices will automatically change. For example, on load of the form, I have the category drop down value set to “External Metal Loss.” Looking at the table and finding the tuples that match the category “External Metal Loss” the anomaly_type_choice (dropdown) should only have the following choices:
Pitting
Pin Hole
General
Isolated
Clusters
SCC
I can give more examples from the table if that would help such as “Internal Metal Loss” would only have:
Pitting
Pin Hole
General
I would like to set this up Server side so that I can manage all of these easily and there will be many of these for several of the forms i need to create. Please let me know if that helps. I will continue to work on it from my end as well.
Many thanks,
Neal
Morning, ok fresh pair of eyes.
It looks like you might have slightly overcomplicated it - I’ll work through it shortly and check - in that you’re creating the tables as you would an SQL database. Anvil manages a lot of the links automagically without the need for a mapping table.
Gimme an hour or two and I’ll send you an example of what i mean.
Ok, here is something that might help you, though please tell me if I have misunderstood.
https://anvil.works/ide#clone:5MQ5Y4BZPOSG5NVN=2VRU5ZTRHPP6CN5NRCVDQYPV
I have created Form2 as the start up form.
It loads the categories into drop down 1
Whenever drop down 1 changes, it reloads drop down 2 - note I have only populated the two you gave in the example, the rest will be blank.
I have created a new table which is a clone of Anomaly Category called, unsurprisingly, Anomaly Category 2.
In there I have create the 1-many relationship that categories has with types.
In the server module I have created the two functions that fetch the data (I have used straight forward for loops instead of comprehensions for clarity - I am new to Python myself).
For what you were showing me, you don’t need that mapping table, as Anvil data tables does that for you in the background.
** There is no error checking ! **
Does that help any?
Hello David,
I have gone through what you did and it absolutely makes sense, unfortunately, our requirements are more complicated than that. I was only laying the foundation for the pattern which I will need to use extensively. The pattern I set with the many-to-many table is needed because there may be additional information (not yet included in the table) that is pertinent to that specific row. For example:
Category “External Metal Loss” with Type “Pitting” may have a different picture, description, or inspection method than Category “Internal Metal Loss” with Type “Pitting.”
I was hoping to be able to return all that information with one lookup once I had the base code created. I can’t find anything in the documentation how to look up or search a table with a string value. Is there some function Anvil has for that? Would I need use an ID field instead? And if an ID field is needed, I don’t see an auto-increment function on the data tables, so would I need to use an external database?
Essentially, I want to minimize the number of server calls and we were already leaning towards using a MongoDB database for the mobile side and MySQL for the asset management component. I know this is probably too much info for this item, but trying to give you background that this is much bigger than just the two drop downs. I know it’s hard to help out of context.
I gave it one more go with the many-to-many table and adapted some of your code but am still getting this error:
‘’’
External Metal Loss
tables.TableError: Column ‘category’ can only be searched with a Row from ‘Anomaly Category’ table (not a string) in table ‘Anomaly Category and Type’
‘’’
I made some updates and you can clone here if you still have some time today…
https://anvil.works/ide#clone:HN7TRNTGJ52C3UJX=J4D3ZMN5ZB5C3VGH7IP2MOAB
I’ve not got much time today but I will look over the weekend.
What instantly jumps out is you are searching on the m2m table using a string (the drop down result), but that table is just links to other tables, so a string makes no sense. You need to pass as a search criteria a row from the category table.
Personally, I would be looking at an SQL database for this (PostgreSQL & MySQL libraries are available; I use both in Anvil) because it’s something I know far better than Anvil’s data tables, and looking at how you’ve set the tables up I’m guessing you might too.
But, that said, Anvil Central (@meredydd & @daviesian) might be able to help you further with the data tables capabilities.