How to populate links to other tables?

Anvil Q&A
1

What I’m trying to do:
Get a list (json array) from a REST API to populate a table with links to another table.

What I’ve tried and what’s not working:

  1. From my REST API, I get a JSON like:
[{
    'Id': 1,
    'Title': 'noco1t1',
    'title3': [{
        'Id': 1,
        'Title': 'noco2t1'
        }, {
        'Id': 2,
        'Title': 'noco2t2'
    }]
}, {
    'Id': 2,
    'Title': 'noco1t2',
    'title3': []
}]
  1. Code Sample:
for c in res['list']:
    app_tables.noco1.add_row(**c)
  return res['list']

The columns are created according to their types except for the last one ‘title3’, which is a link to records from another table noco2 and is by default created as simpleObject by Anvil.
3. To simplify, I manually created the linked column on the primary table noco1 and the secondary table noco2 with records.
image
4. I run the server code to populate the primary table and link to records from secondary table.
5. Server throws: anvil.tables.TableError: Column 'title3' is a List of rows from 'noco2' table - cannot set it to a simpleObject
Because I guess, it simply wants to write the data while the column is for links, here’s a record created manually:
image

And, as the CSV export shows, the Anvil linking way is using table hidden ids and timestamps:

ID	Id	Title	title3
[241089,408021488]	1	noco1t1	#ROWS[[241092,408020939],[241092,408020985]]

It seems stretched, but let’s ask: Is there a way to “explain” Anvil that the array for this column is to be resolved as links to another table?

2

Not sure if this is the answer but it seems to be what you are looking for?

1 Like
3

Thank you @ianb !

Here’s my result, this was generated from a REST API call with a linked table:
image

Believe it or not, I didn’t find your link in the doc by myself :laughing:

Here’s the final working code if it helps someone (Python is so simple compared to JS where I come from):

  for c in res['list']: #For each c of your REST API result (a list, i.e. a JSON array)
    if isinstance(c['title3'],list): # When your JSON embed a list (here an array in title3)
      c.update({'links':[]}) # Add an empty list to your record
      for rec in c['title3']: # For each record in this list, 
        linkedrec = app_tables.noco2.get(Title=rec['Title']) # Search it into the secondary table using the value
        c['links'].append(linkedrec) # Append the result to your c['links'] list (it's not text but a`<LiveObject>`, Anvil internal way of linking tables)
    app_tables.noco1.add_row(**c) # And finally push all this in your primary table

I allow for an extra column containing the simpleObject to keep for eventual CSV export (but it’s not dynamically updating itself).

Now I’d need to generalize code and the other way around (Anvil to REST API as POST for linked records). If anyone has hints …

4

The forums are a treasure trove of clone links and old forum posts full of code snippets and other people doing similar things. (but not me, I haven’t needed to build anything RESTful with anvil. )

5

If I understand, you need two loops, the first to create all the rows, the second to create the links, because creating links on one run risks to not find a row that has not been inserted yet:

for item in res['list']:
  item['row'] = app_tables.noco1.add_row(
    {'Id': item['Id'], 'Title': item['Title']})

for item in res['list']:
  linked_rows = [app_tables.noco2.get(Title=link['Title']) 
                 for link in item['title3']]
  item['row']['title3'] = linked_rows
6

Exactly! And also the other way around (POST rows with linked records).

anvil.http.request(url="http://myapi", 
                           method="POST",
                           json=True,
                           data= app_tables.noco1.search(),
                           headers=headers)

would have been too easy :wink:

I’m getting: TypeError: Object of type 'LiveObjectProxy' is not JSON serializable

I don’t get how to transform LiveObject to JSON.
I searched "LiveObjectProxy" "JSON" site:anvil.works without luck so far.

7

Where do you get the error?
(Maybe this should be another post, this already has an answer)

8

As I understood how to GET I extended my question to POST :laughing:

The only way explained in the docs is
HTTP APIs where you rebuild the object from scratch. I’d need dynamic way of reading LiveObject: anvil.tables.Row and turn it to JSON.

The error is on the server console:
image

9

I don’t understand what fails.
From the snapshot I assume there is a print and a line that fails. Can you share the code with both, and the code that defines the variables used on both the lines?

You are now talking about HTTP endpoints, while the subject at the top of the page is about populating links in tables. Please create a new post with the new question. Adding to this is not going to help anyone in the future.

10

I had the same issue when starting with Anvil.

In this forum, you can search rows to list of dictionary, then list to Json.

Good luck!