Docs
Switch to new Anvil Editor docs Switch to Classic Editor docs
You are currently viewing the new Anvil Editor Docs.
Switch to the Classic Editor Docs
You are currently viewing the Classic Editor Docs.
Switch to the new Anvil Editor Docs

You can store a reference to one table in another table, using a link column. In this example, we have a people table, and a notes table containing notes about a person. The notes table has a “Person” column, which links to a row from the people table.

# Get Jane Smith's row from the table
jane_smith = app_tables.people.get(Name="Jane Smith")

# Add a row to the 'notes' table, referencing her
n = app_tables.notes.add_row(Person=jane_smith,
                             Text="Jane is a good kid")

# Print the name of the person this note is linked to
# (prints "Jane Smith")
print(n["Person"]["Name"])

# Search by reference to a row. Eg,
# How many notes do we have about Jane?
jane_notes = app_tables.notes.search(Person=jane_smith)
print(f"There are {len(jane_notes)} notes about Jane.")

Once we have a row object representing Jane Smith’s row in the people table, we can add a new row to the notes table that refers to her, by passing that row object into add_row(). We can also search for all notes that refer to this person, by passing her row object into search().

Note that we use the len() function here - len() runs very efficiently over search iterators.

You can also create columns that link to multiple rows from another table. In this example, the “Friends” column links to multiple rows from the “People” table. We set it to a list of row objects.

jane_smith = app_tables.people.get(Name="Jane Smith")
john_smith = app_tables.people.get(Name="John Smith")
zaphod = app_tables.people.get(Name="Zaphod Beeblebrox")

zaphod["Friends"] = [jane_smith, john_smith]

You can search using a “link to multiple rows” column. If you pass a list of row objects to search(), it will return only rows that link to all of those rows. (If you specify multiple rows, the order doesn’t matter - it matches anything that links to all of them.)

friends_of_jane = app_tables.people.search(Friends=[jane_smith])

mutual_friends = app_tables.people.search(Friends=[jane_smith, john_smith])

Values in Multiple Link columns are represented as lists in Python. Lists from Simple Objects are also Python lists.

To add a new item to a Multiple Link or a Simple Object, use the += operator.

zaphod = app_tables.people.get(Name="Zaphod Beeblebrox")
ford_prefect = app_tables.people.get(Name="Ford Prefect")

zaphod["Friends"] += [ford_prefect]

Ensure the right-hand-side of the operation is a list, since the Multiple Link column behaves like a list.

This will work: zaphod["Friends"] += [ford_prefect]

This will raise an exception: zaphod["Friends"] += ford_prefect

Empty multiple link columns are None until they contain at least one element. To initialise a mulitple link column with one element, use:

zaphod["Friends"] = [ford_prefect]

To remove rows, remove the item from the list and re-assign it using the = operator.

zaphod = app_tables.people.get(Name="Zaphod Beeblebrox")
ford_prefect = app_tables.people.get(Name="Ford Prefect")

zaphod["Friends"] = [r for r in zaphod['Friends'] if r != ford_prefect]

Do you still have questions?

Our Community Forum is full of helpful information and Anvil experts.

Data Security Using Multiple Databases

On This Page: