Simple lookup not so simple?

Ask for Help
1

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:

song_name = $Song_Name
latest = Performance_Data_per_Date_.lookupOne(Name=song_name, sort_by="-Date" )
return latest.Date

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?

2

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.
3

Not sure of your specific table layout but i created a simple doc with 2 tables:
https://docs.getgrist.com/6nqepyxsaunr/Untitled-document?utm_id=share-doc

you can see formula grist ai created to display the latest for a reference column.

hope that helps.

1 Like
4

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
1 Like