Most recent unique child records list.

[jairod]

I have a child table with DateTime stamp like the following:

It gets updated by a webhook everytime there is an update.

I´d like to show a list of the most recent child records which are not repeated at the parent view.

Any help will be greatly appreciated

[Matheus_Souza]

I think you should try the "TOP" or "MAXROW" expressions for that.

https://support.google.com/appsheet/answer/10107704?hl=en

https://support.google.com/appsheet/answer/10107920?hl=en&ref_topic=10104782

[SkrOYC]

---

@jairod wrote:

I´d like to show a list of the most recent child records which are not repeated at the parent view.

---

You want:

1. Most recent child records
2. Unique values
3. On the parent Detail View

?

[jairod]

I confirm the 3 points are correct.

[SkrOYC]

Ok, easy enough.

But! What's the criteria to say that certain record is repeated? After that, I can help you make a slice and a List column (aka [Related whatever])

[jairod]

A repeated record is all the columns repeated except the DateTime.

[SkrOYC · Accepted solution]

Ok, that's a weird one.

I suggest you to add a new column called something like [pseudo_id] to make things easier. This new column would have all of the values from the columns you need to check to make something like a "Computed key". I also suggest to remove spaces, so:

SUBSTITUTE(
 CONCATENATE(
  [Column_1],
  [Column_2],
  [Column_3],
  [Column_4],
  [Column_n],
 ),
 " ",
 ""
)

 Then, your slice could have something like this on the row filter condition (please test and come back):

[DateTimeColumn]
=
MAX(
 SELECT(
  YourTableName[DateTimeColumn],
  [_THISROW-1].[pseudo_id]=[pseudo_id]
 )
)

[jairod]

I was able to list the most recent items by using the following formula in a filter:

---

DateTimeColumn]
=
MAX(
 SELECT(
  YourTableName[DateTimeColumn],
  [_THISROW-1].[pseudo_id]=[pseudo_id]
 )
)​

However the concatenate formula in the virtual column of the source table did not allow for the filter to work properly. Anyone´s guess why is as good as mine.
And it did work using a concatenate formula directly in the source spreadsheet table.

[SkrOYC]

---

@jairod wrote:

the concatenate formula in the virtual column of the source table did not allow for the filter to work properly.

---

Can you explain that a little bit more accurate?

[jairod]

I checked my formulas and was missing the

SUBSTITUTE(

Before concatenate.

I fixed that and it works.