Live Chat

We'll need to share your messages (and your email address if you're logged in) with our live chat provider, Drift. Here's their privacy policy.

If you don't want to do this, you can email us instead at contact@anvil.works.

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?
janes_notes = app_tables.notes.search(Person=jane_smith)
print("There are %d notes about Jane." % len(janes_notes))

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.

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 both 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

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]