How would I change this row if City was a linked Row to another table, with the row in that table = self.location_city
` "City": self.location_city,`First you need to get the linked row. Let’s just say you have 2 Tables A and B. And A has a column ‘City’ which is linked to Table B. Then here’s how you can do it
table_b_row=app_tables.b.get(some_column=some_value)
table_a_row=app_tables.a.get(City=table_b_row)
Then you can easily change ‘table_a_row’
table_a_row['Column']=value
#Or Use the update function
table_a_row.update(Column=value)
1 Like
Thanks! I messed up and set the row to a text row, when I needed a linked row. So now have to fix it
1 Like
Actually I need a different code, here is the situation
I have a column in trips called City that is populated with a text field for the city
I have a column in trips called LocationCity that is currently empty
I have a row in locationdetail that has the City in it and I need to update the table trips LocationCity column with the correct Linked row.
I tried this, with get_all_trips just giving me a list of all trips that have LocationCity = None:-
get_list = anvil.server.call('get_all_trips')
for x in get_list:
app_tables.mytrips.update['LocationDetailCity']['LocationCity']='City'
I get an error, so Im doing something wrong
AttributeError: 'LiveObjectProxy' object has no attribute 'update'
You are using the update function wrong here.
From what I understood, you have a column in locationdetail row which links to the trips row. Assuming that this is what you meant, this piece of code should work fine :
First of all, you need to get the locationdetail row
location_detail_row=app_tables.locationdetail.get(my_column=value)
trips_row=location_detail['Linked Row Column'] #Replace this with the name of your column
trips_row['LocationCity']=value
1 Like
Hi, sorry I dont think I am explaining myself very well.
Here is a screenshot of my trips table
.
Here is a screenshot of my locationdetail table
I belatedly created the LocationCity column, instead I put the column City which is a text row, instead of a linked row.
I want to reference Column City in trips to physically update the column rows in LocationCity also in trips but since LocationCity is a linkedrow, its something like this
in pseudo code
find all the records in tips where LocationCity = none
for each row find the matching record where trips ‘City’ = locationdetail ‘LocartionDetailCity’
update the column LocationCity in trips, with the correct linkedrow in locationdetail
First off, all the code you’re talking about should be in the server, not the client. You’re not doing anything that requires user input, you’re just doing some data conversion.
Let me reorganize your algorithm to be a bit more Pythonic:
find all the records in tips where LocationCity = none
for each record:
find matching record in locationdetail where LocationDetailCity=record's City
set record's LocationCity to that matching record
You already have the code for the first part. Inside the for loop, you have an app_tables get and a column value change. Don’t try to combine those into a single statement, do them as separate lines.
As a side note, you probably want to add the country text as part of your get on the locationdetail table. If you only use the city name and you have the same city name in two or more countries, it’ll break the get.
yes you are correct of course, I only put it on the client side since I attached it to a button for ease of execution. I only need it the once so will delete it once Ive done the update. Can you put an AND statement in relation to the Country and City,
find matching record in locationdetail where LocationDetailCountry = records country AND LocationDetailCity=record's City
You can specify as many field restrictions on an app_tables get as you like. They all must be True for the row to match.
Would this work?
@anvil.server.callable
def fix_my_crappy_error():
ct =app_tables.mytrips.search(LocationCity=None)
city = (ct['City'])
ld = app_tables.locationdetail.search(LocationDetailCity='city')
for x in ct:
app_tables.trips.update(ct['LocationCity'], ld)
Edit: No it doesn’t - see error below, how do I change this city = (ct['City'])
TypeError: Indexing with [] is not supported on this anvil.tables.SearchIterator
Go back to the algorithm. The first line is your ct =app_tables.mytrips.search(LocationCity=None), and that looks fine.
city = (ct['City']) is nonsensical, which is what the error is telling you. The search function returns a search iterator (e.g. a list of rows), and you’re trying to index into it as if it’s a row. Just remove that line entirely.
The find matching record in locationdetail is what you’re trying to do with ld = app_tables.locationdetail.search(LocationDetailCity='city'), but there are issues with that line. You’re using search instead of get (search gets a list of rows, get gets a single row). You also don’t use the country there either, which you need to guarantee you’re only getting a single row.
And, it isn’t inside the for loop, so you don’t actually have a city to search for. Inside the for loop, x['City'] will give you the city for the current trip, and x['Country'] will give you the country.
app_tables.trips.update(ct['LocationCity'], ld) seems to be an attempt to update the column with the linked row. But that is not how you update column values. The docs cover the syntax for updating a column value: Anvil Docs | Using Data Tables from Python
Or you can look up in this thread at @divyeshlakhotia 's example that shows how to update a column value.
oh dear! So about 1/10 on that. Its midnight here now, so will sleep on it and hopefully it will all be clearer in the morning! Thank you for taking the time to answer, it is truly appreciated.
I have gotten further with this query, but am stuck on one part
Here is the latest effort
@anvil.server.callable
def update_my_crappy_error():
query = app_tables.mytrips.search()
for each in query:
ct =app_tables.mytrips.search(LocationCity=None)
for x in ct:
city = x['City']
city = str(city)
country = x['Country']
print(city,country)
trips_row = app_tables.mytrips.get(Country=country,City=city)
location_detail_row = app_tables.locationdetail.get(LocationDetailCountry=country,LocationDetailCity=city)
print(location_detail_row)
trips_row=location_detail_row['LocationDetailCity']
print(trips_row)
trips_row['LocationCity']=location_detail_row
And here is the error
Sydney Australia
anvil.tables.TableError: More than one row matched this query
It’s on this line
trips_row = app_tables.mytrips.get(Country=country,City=city)
The query is working where there are records that have only one match, but not if there are more than one matched rows.
How would I amend this row to stop this error
You don’t need that line at all. You’re putting in way more code than your original pseudo code said you needed. You also have an outer for loop that isn’t needed at all.
Your code should end up being the same number of lines as the Pythonic algorithm. You have the first two of those lines already:
And the third you also have (side note, you don’t need to break out the city and country into variables, but if want to, that’s fine, too):
The final line you almost have (your trip row at this point is named x, not trips_row):
Where you’re going wrong is all the other bits you put in between those lines.
2 Likes
Thanks so much for this, its all sorted now. It really was a lot simpler than I thought! I think my biggest problem is that all my query knowledge is SQL/table joins and using WHERE AND SET this to =this etc as operators. I’ve found getting my head around this hard.
1 Like