How to pull last/newest related record?

Ask for Help
1

I have this table with several records. Each record is a RISK.

Each of these risks can have several analysis done… like each semester you analyse that risk to see if it got worse or better based on external and internal situations.

Now, at the main risk table, i want to pull the “current” situation of the risk, so I can order by the ones that need more attention.

The current situation is that LAST analysis done for that risk. So, how can I have a field picking up the newest/last analysis done to THAT risk specifically?

Thanks.

2

Hey there!

I modified our prior example to include this: Community #1545

I created two examples for two different ways to do this. You can do this using Row ID or by using a Date column if you have one.

Row ID is a unique ID that is created when a new record is created. This will likely work fine for you but keep in mind that if you add a new record between existing records, that new record will be the ‘newest’ risk assessment. This would only cause a problem if you are going back to enter a prior one that may have been missed. As you see in the screenshot below, I added a new row between Row 1 and Row 3. Because ID 4 was next to be assigned, this row has ID 4 and therefore is the newest record so the Risk Degree for this record is returned at the top.

image
image1580×727 53.7 KB

As long as you are entering the Risk data in order, then you won’t have any problems using Row ID but if you have a Date column, I would recommend using that as the date will always determine what is most recent :smiley: I’ll include both formulas below.

If using Row ID, the formula would be:

(Risk_Probability.lookupRecords().Risk_Degree_Text)[-1]

This makes a list of all records in the Risk Probability table then stores the value found in the ‘Risk Degree - Text’ column for each of those records. [-1] finds the last record in the list. Keep in mind that this list is created based on Row ID so the record with the highest ID will be returned, as you saw in the screenshot above.

If using Date, the formula would be:

max_date = MAX(Risk_Probability.lookupRecords().Date)
return Risk_Probability.lookupRecords(Date=max_date).Risk_Degree_Text

First, we use LookupRecords to make a list of all records in the Risk Probability table. We pull the ‘Date’ value for each of those records using Dot Notation. MAX() finds the highest/most recent date in that list. we assign this date to the variable max_date.

Next, we use LookupRecords again to find the record in the Risk Probability table where the value in the Date column matches the date we found as our most recent date in the prior statement. Then, we use dot notation to return the value from the ‘Risk Degree Text’ column.

I hope this helps! Please let me know if you have any questions.

Thanks,
Natalie

1 Like
3

I have a somewhat different way. Assuming that in your case, one table is called RISK, and the other RISK_SITUATIONS, and that RISK_SITUATIONS has a Reference column Risk (linking it to the associated RISK record), and a column Date that determines the order, your formula would be:

situations = list(RISK_SITUATIONS.lookupRecords(Risk=$id, sort_by="Date"))
return situations[-1] if situations else None

Somewhat similar examples appear in our documentation – in the “Dig Deeper” section of Reference Columns Guide (specifically in the Last Milestone field of the Tutorial Solution doc), and in the advanced section of the Lightweight CRM tutorial (click on the heading to expand it). Neither is quite what you are asking, but could be a helpful reference for other similar situations.

2 Likes
4

Thanks Dmitry. I was not able to try to implement that before. I tried some minutes ago but I am doing something wrong.
As I usually translate my table names and fields to English, to make questions more easy to understand, here are the originals

RISK = Novo_Risco
image

SITUATIONS = Analise_Risco
image

As can be seen here, just as you said, Analise_Risco has a field for DATE (Data Análise) and a field that references the Novo Risco column (RiscoFK)

image
image1096×141 11.5 KB

Got this error however
image

5

I also got a Syntax Error when trying to use Natalie’s formula

=max( Analise_Risco.lookupRecords().Data_Analise) return Analise_Risco.lookupRecords ( Data_Analise=max_date ).Grau_de_Risco

Natalia, when writing the formula, Grist returns me a Syntax Error as soon as I close the parenthesis after the first lookuprecords()

6

Hi!

For your second-to-last email, with KeyError: 'Novo_Risco', it looks like the issue is that Novo_Risco is the name of a table, but it should be the name of the column in the Analise_Risco which is a Reference column pointing to the current record (maybe RiscoFK?)

Also, instead of sort_by="Data Análise", use the Python identifier of the column, i.e. sort_by="Data_Analise".

For the latest issue, Natalie’s formula was:

max_date = MAX(Risk_Probability.lookupRecords().Date)
return Risk_Probability.lookupRecords(Date=max_date).Risk_Degree_Text

Note that max_date is part of the formula (defines a local variable used in the next line). In your version, it looks like the formula starts with the equals sign, which may be what the problem is. The line starting with return should be on its own line; you can use Shift + Enter to insert newlines while typing in formulas.

7

Thanks. I thought that “max_date” before the equal was just Natalie telling that the formula calculated the Max Date. I am used to EXCEL and you never have a formula starting with something BEFORE the equal sign.

Anyway, Natalie’s formula is returning me the last Risk Analysis for all risks, instead of the last Risk Analysis of EACH risk.

max_date = MAX(Analise_Risco.lookupRecords().Data_Analise)
return Analise_Risco.lookupRecords(Data_Analise=max_date).Grau_de_Risco

image

The table Riscos has several Risks. Each Risk has several periodic analysis, to access the current situation of that Risk, after action plans have been implemented to mitigate them.

8

For your second-to-last email, with KeyError: 'Novo_Risco' , it looks like the issue is that Novo_Risco is the name of a table, but it should be the name of the column in the Analise_Risco which is a Reference column pointing to the current record (maybe RiscoFK ?)

Also, instead of sort_by="Data Análise" , use the Python identifier of the column, i.e. sort_by="Data_Analise" .

Here is the code for that table

@grist.UserTable
class Analise_Risco:
Probabilidade = grist.Reference(‘Riscos_Probabilidade’)
Consequencia = grist.Reference(‘Riscos_Impactos’)
Meses_p_Proxima_Analise = grist.Int()
Tratamento = grist.Choice()
Data_Analise = grist.Date()
RiscoFK = grist.Reference(‘Novo_Risco’)

def _default_Analista(rec, table, value, user):
return user.Name
Analista = grist.Text()

def ProbNum(rec, table):
return rec.Probabilidade.Num

def ImpactNum(rec, table):
return rec.Consequencia.Num

def CalcRisk(rec, table):
return rec.ProbNum*rec.ImpactNum

def Grau_de_Risco(rec, table):
return Riscos_Grau.lookupOne(Grau=rec.CalcRisk).GraudeRisco

def RiscoFK_IDRisco(rec, table):
return rec.RiscoFK.IDRisco

def Data_Proxima_Analise(rec, table):
return DATEADD(rec.Data_Analise,0,rec.Meses_p_Proxima_Analise)

So I think the references were corrected now, but now I get a new error

EDIT: I noticed now that when translating the formula, I used the name of the table “Analise de Risco” replacing your Risk_Situation… but then in the second line where you wrote situation (which was meant to be a COLUMN) by the name of the table too. Will correct that and will create an edit 2 if it works

EDIT 2 : hmmm… still did not work. In the second line, I replaced the table name by the column… error changed but it’s saying the column name is not defined. I am deleting the outdated error image I posted when writing this post
image

9

Let me try to understand the formula. This is more important than copying and pasting.

list( Analise_Risco.lookupRecords( RiscoFK=$id, sort_by=“Data_Analise”))
Ok, so I am listing the records in the table AnaliseRisco WHERE RiscoFK (the referencing column) matches the ID of the record in the main risk table. While i don´t have an ID column, I suppose Grist creates one by default anyway, right?
Then it sorts that list based on the date on the column Data_Analise (yes, it’s confusing that portuguese for DATE is DATA, which in English means another thing*)

My biggest problem I think comes with the second part
return Grau_de_Risco[-1] if Grau_de_Risco else None

ok, so the formula will check that list and return the column Grau de Risco[-1] (whatever that means) IF the column Grau de Risco, else none.

Have no idea what the formula is doing here.

.
.
.
.
.

*The etimology from Latin is the same, from the Latin verb “dare” (to give). Which also exists in Portuguese as “dar”

data(por)/date(eng) comes from Latin because letters started with “data Romae”… meaning “given in Rome”, before the day of the writing.

data(eng)/dados(por) ALSO comes from the Latin verb “dare”. It basically means “given information”.

It’s interesting that altough we don´t usually THINK about it, in romance languages lots of things still make sense. As I said, the word for DATA in Portuguese is DADOS. And “DADOS”, a noun, literally also mean GIVEN.
The data was given to him - Os dados foram dados para ele

lol, I guess etimology might be a little off-topic in a software forum.

10

Excellent point that it’s better to understand the formula. The difficulty here is in the jump from Excel to Python. In Python, you can use variables for intermediate values, which is very helpful, but is an unknown concept in Excel world.

You understood the first line exactly right. It produces a list. You need the last value from this list. In Grist / Python, you assign this list to a temporary variable, with a name you pick yourself. To avoid confusion, let’s pick a name that’s different from tables and columns, say risk_list:

risk_list = list( Analise_Risco.lookupRecords( RiscoFK=$id, sort_by="Data_Analise"))

The equal sign is assignment. Then the next line can use this variable (whose value is a list):

return risk_list[-1] if risk_list else None

Here, [-1] is Python syntax for getting the last element of the list (i.e. one with the latest date, since the list was sorted by date).

I hope this helps. By the way, if you are interested in learning more Python, I can recommend https://futurecoder.io/ – a nice self-paced Python tutorial / course (and built by a Grist engineer!)

OK, all this advanced stuff out of the way, I’m happy to say that Grist just recently acquired a simpler way to accomplish what you need:

Analise_Risco.lookupOne(RiscoFK=$id, sort_by="-Data_Analise")

That’s it, just one line. Note lookupOne in place of lookupRecords, and the minus in front of Data_Analise. The minus tells Grist to sort in descending order; and the lookupOne to return the first matching record. So there is no more need to construct a list and extract the last element from it.

11

AHHHHH!!!

The fact that first word was a VARIABLE being assigned completely escaped me, and that’s why I miundestood BOTH yours and Natalie’s formula.

I fixed Natalie formula but only now I noticed in YOUR formula I also did not set the list to a variable. Your formula also starts with the =

And only now I understood where that “situations” was coming from. I was thinking it was either a table or a column, but it’s the variable that you set in the first line!!!

12

Thanks Dmitry.

However, there is still something to work out. Both the new 1 line formula, as the old, are not bringing me the value I need

The difference is that the new formula shows me that [0] for all Risks, while the old formula brought a value ONLY for the single Risk that had an analysis (other ones were blank)

New Formula
image

old Formula
image

It seems the formula is only bringing up the ID of the last record. While I need to return the text in the text in the Grau de Risco column

image
image1113×189 11.2 KB

However, Natalie’s formula did bring the text for the correct field. So I guess I need to somehow use her formula, in a new field, using YOUR formula as source.

EDIT: well, it didn´t work. But at least no ERROR message. An improvement lol
image

13

Try this:

Analise_Risco.lookupOne(RiscoFK=$id, sort_by="-Data_Analise").Graus_de_Risco

In general, lookupOne and lookupRecords return references to records, but you can use the dot notation to get particular fields from them.

1 Like
14

Awesome! It worked flawlessly.

1 Like