API CRUD with native Grist object

Grist Feedback
1

In my workflows, I have started to archive records from one table in a different table, based on specific criteria.

I do this via API using Python, as such:

data = grist_WN.fetch_table('WebReg')

for record_to_archive in data:
    # Copy to WebReg_Archive
    if record_to_archive.discard == True or record_to_archive.reg == True or record_to_archive.over == True:
        grist_WN.add_records('WebReg_Archive', [
                                        {   'tweet': record_to_archive.tweet,
                                            'id': record_to_archive.id,
                                            'url': record_to_archive.url,
                                            'website': record_to_archive.website,
                                            'provider': record_to_archive.provider,
                                            'discard': record_to_archive.discard,
                                            'title': record_to_archive.title,
                                            'date': record_to_archive.date,
                                            'reg': record_to_archive.reg,
                                            'notes': record_to_archive.notes,
                                            'contact': record_to_archive.contact,
                                            'updated_by': record_to_archive.updated_by,
                                            'updated_og': record_to_archive.updated,
                                            'tz': record_to_archive.tz,
                                            'provider_og': record_to_archive.provider_og,
                                            'over': record_to_archive.over,
                                            }
                                    ])
        # Delete from WebReg
        grist_WN.delete_records('WebReg', [
                                record_to_archive.id,
                            ])

It works, but quite verbose, especially as I need to write this for quite a few tables.

What would be great is the ability to pass via API the original Grist objects (namedtuple?), instead of having to deconstruct each field, so the above could be written and done just with this:

list_WN001_archive = [record for record in grist_WN.fetch_table('WebReg') if record.discard == True or record.reg == True or record.over == True]

grist_WN.add_records('WebReg_Archive', list_WN001_archive)
grist_WN.delete_records('WebReg', list_WN001_archive)
1 Like
2

That’s a good suggestion for the API client. In the meantime, namedtuples have an _asdict method that should do what you want:

list_WN001_archive = [
  record._asdict() for record in grist_WN.fetch_table('WebReg')
  if record.discard or record.reg or record.over
]

grist_WN.add_records('WebReg_Archive', list_WN001_archive)
grist_WN.delete_records('WebReg', [record["id"] for record in list_WN001_archive])

In any case, I think you should avoid moving data between tables in this case, but rather just filter the data differently in different widgets. I suggest adding a column with the formula $discard or $reg or $over and then filtering for false in most widgets but true in a widget specifically for looking at archives. Setting up access rules to block access to archived records shouldn’t be much harder than if they were in a separate table.

1 Like
3

That looks amazing! Thanks for the clean code suggestion, will save a lot of time.

However, getting this error:
ValueError Can't save value to formula column gristHelper_Display2
I know (from you) about those hidden hidden columns that display values from Reference columns.
How can I deal with them in this scenario?

Reasons behind moving to separate table instead of just filtering:
avoiding hitting max record per table
avoiding having to filter each time

1 Like
4

That’s annoying. Here’s a nested dictionary comprehension to select the fields you want:

list_WN001_archive = [
  {
    key: value
    for key, value in record._asdict().items()
    if not key.startswith("gristHelper")
  }
  for record in grist_WN.fetch_table('WebReg')
  if record.discard or record.reg or record.over
]
2 Likes
5

Thanks for all this feedback, Nic! By the way, the row limit applies to the entire document, not individual tables. Limits - Grist Help Center

6

I missed this :confused: thought it was 100k per table, not across all tables of a document.
Is there a way to see current usage (total rows or total size) of a document with multiple tables?

This means I will need to spread data across multiple documents (instead of tables).

7

Love it! :pray:t3: Will test.