Saving data including lookup tables

Anvil Q&A
#1

Hello,
more questions here :slight_smile:
I have 2 tables: Expenses and Concepts.
Also a form: text field, drop down, save button.
The drop down is pulling data from a lookup table, in this case: Concepts.
The idea is that the user selects a Concept and inputs an amount and saves.
To do that I am doing:

 dict_new_expense = {}
dict_new_expense= self.item
anvil.server.call('new_expense',dict_new_expense )
alert("Saved ok")
self.clear_inputs()

and binding the drop down value to self.item['Concept'].

When saving I get: anvil.tables.TableError: Column 'Concept' is a Row from 'ExpensesConcepts' table - cannot set it to a string
which makes sense…but I can’t see any other option on the binding to select the reference to the Concept table.
Basically trying to do this:
https://anvil.works/docs/how-to/dropdowns-data-tables but getting this: anvil.server.InternalError: You cannot store a view row in a table column. Store the full row (not from a view) instead

Is the same as here; Linked table not showing values but the other way around.

Any idea?
Thanks

#2

There are several approaches you could take here depending on preference.

  • self.item['Concept'] is a string from a dropdown
  • you want to use the selected value to create a new row in the expenses table
  • where actually table_row['Concept'] should be a linked row not a string

server code - just an idea

@anvil.server.callable
def new_expense(dict_new_expense):
  concept_row = app_tables.concepts.get(description=dict_new_expense['Concept'])
  dict_new_expense['Concept'] = concept_row
  app_tables.expenses.add_row(**dict_new_expenses)

For me - I get this type of situation often - and I would typically not use strings in my dropdown but use tuples

In my very first server call I would store concepts as a variable in a Globals module

concepts = anvil.server.call('get_concepts')

@anvil.server.callable
def get_concepts():
  return [(c['description'], c) for c in app_tables.concepts.search()]
  # this is a list of tuples for dropdowns

For the dropdown I would then bind the items property to self.concepts
and bind the selected value to self.item['concept']

from Globals import concepts

class Form1(Form1Template):
  def __init__(self, **properties):
    self.concepts = concepts  # set concepts before the databindings
    self.init_components(**properties)

This way I can change my server code for adding an expense to be:

@anvil.server.callable
def new_expense(dict_new_expense):
  app_tables.expenses.add_row(**dict_new_expenses)

Other notes - you can’t make links between rows of tables if you have a ‘view’ of a table. This is a limitation of anvil tables, which is the error you’re seeing.


a couple of forum points:

  • try using backticks ``` to display code snippets rather than >
  • clone links for questions like this are super helpful since descriptions of code can only go so far
  • for us to really get a picture of your approach seeing the source code would be beneficial - see:
2 Likes