What I’m trying to do sounds simple enough, but the AI suggestion didn’t work and reviewing the documentation and related Community posts isn’t helping. I’m very familiar with database concepts and scripting languages, but have never used Python so I’m wondering if that’s where I’m doing something wrong. anyway…
I’ve got a table named “Song List”:
@grist.UserTable
class Song_List:
Song_Name = grist.Text()
...
It’s related to a table (unfortunately) named “Performance Data (by Date)”:
@grist.UserTable
class Performance_Data_per_Date_:
Date = grist.Date()
Order = grist.Numeric()
Name = grist.Reference('Song_List')
...
I’m trying to create a column in Song List that shows the date of the most recent performance of each Song_Name. Based on my research so far, it seems like something like this should work:
But it doesn’t. The lookupOne() doesn’t seem to work. If I remove the contents of the parentheses, I get back a single record (the same one for all Song List records, of course), so it’s not the general structure or table references. What am I misunderstanding?
BTW, here’s the AI suggestion that didn’t work:
Prompt: show the latest date in records from the $Performance_Data table with the same $Song Name
song_name = $Song_Name
# Lookup all records from Performance_Data_per_Date_ with the same Song Name
records = Performance_Data_per_Date_.lookupRecords(Name=song_name)
# Sort the records by Date in descending order
sorted_records = sorted(records, key=lambda x: x.Date, reverse=True)
# Return the latest record
sorted_records[0]
This function takes a Song_List record as input and returns the latest
Performance_Data_per_Date_ record with the same Song Name. It looks
up all records from Performance_Data_per_Date_ with the same Song Name,
sorts them by Date in descending order, and returns the first record, which
will be the latest one.
Thank you! It looks like, since the target field for the query was a Reference field, the “=rec” was the magic word I needed. So, what works fine now is:
performance = Performance_Data_per_Date_.lookupOne(Name=rec, sort_by='-Date')
return performance.Date if performance else None