Sorting in the Data Editor

This might be similar to my ranking request, but is there anyway to sort the data in the editor prior to running relations/lookups?

Use case: I want to form a relation between two sheets (a home screen with a list of items and log screen when those items are checked in/out via form button), but I want the lookup value to be the most recent record from the log sheet. Currently, the lookup value is the top most record in the sheet, which is the oldest as the form button adds records to the bottom of the sheet.

Again, I can do this with a vlookup with a sorted range, but was hoping to achieve this without formulas as the lag in the app is confusing my users after submitting the form since they don’t see the changes right away.

Gonna go with a crazy combination again.

  • First create a multiple relation from Item sheet to Log sheet using item.
  • Create a Rollup column in Item sheet to get the max date from the log from related item.
  • Create a template column in Item sheet to join item and date.
  • Create a template column in Log sheet to join item and date.
  • Create a relation column in Item sheet to join both template columns
  • Create a lookup column against relation

Wow that worked. I figured the rollup/date would do it somehow…I was missing the match multiple that you stated in step 1. Hats off to you, sir!

1 Like

Hello, I would like to use kind of the same idea but going beyond a simple item :

Keeping the idea of items and logs, I would like my multiple relation from 1 item to X logs but sorted by log date (and not by creation date, which is the sort by default).

Does it sound possible ?

(I am indeed, talking about sorting the multiple relation inside the data editor)

Thanks !

What’s the difference between log date and creation date?

I would like the multiple relation array itself to be sorted by date :slight_smile:

The concept of sorting a relation doesn’t really make sense. A relation is just a link between one row and one (or many) other rows. It doesn’t contain any data, so there is nothing to sort.

It’s only when you use a relation either in a list component, or as the source of another column such as a lookup or joined list that you have data that you might want to sort. And how you would achieve that sorting depends on how the relation is being used.

So, how are you using your relation?


See below use case :

I have one table : “month” with one multiple relation to another table : “days”.
Each day has a date, and other values (let’s say weather / temperature etc …) I want to use in month (to compute statistics, which is why I need it sorted by date)
My user can create a day let’s say : 07/10/2022 and then 09/10/2022 and last 08/10/2022.

In my multiple relation in the month table, the days array will come in the order it’s been created : 07-09-08. And thus, the data pulled my lookup, in the same “bad” order, every time I do a lookup.

Instead of sorting it every time I want to use data pulled from the relation, it would be better to sort the relation itself depending on one of its field (its date).

Is it clearer ? Sorry it’s not been clearer before
Thanks !

Not possible. Best bet is to sort it every time.

:wink: @Robert_Petitto … everything is possible…

Can you explain some more? How are you using the lookup column? How are you calculating statistics? Are you parsing apart the array and filling columns in a certain order? There are glide columns such as the Sort Array column, and I believe someone has create javascript that will sort an array based on another value, but it would help to understand what exactly you are trying to do.

1 Like

Yes, that was me.

And that’s where I was possibly heading with my earlier response, but I was just trying to understand the use case first, before suggesting it.

sigh … this endless stream of XY questions does my head in sometimes. I think I need a holiday :frowning:

1 Like

So, you create a lookup column through that relation that returns an array of dates, which you then want to sort, yes?

You can sort that array using the Array Sort plugin column. Although, depending on what you’re subsequently doing with the array, you might have some formatting issues to deal with.

Again, this makes no sense.
Imagine that a relation is a bridge between two tables, just like a bridge over a river. That’s not a perfect analogy, but it’s close. The relation itself doesn’t contain any data, it just provides a path for data to travel from one place to another. In the same way that a bridge over a river provides a path for vehicles to get from one side to the other.

So when you ask me “how do I sort a relation?”, what I hear is “how do I sort a bridge?”, and my brain explodes :exploding_head: :wink:

Thank you both for your answers and sorry it’'s not clearer. But in my developper mind, it totally makes sens to sort objects (which is what is the multiple relation : an array of objects linked to another) based on one field.

So, examples of how I use the lookup columns :

  • Each day has a temperature : I want to know if the temperature has risen from the previous day or not. (So I need the days to be sorted to compute it)

  • Each day has got moon information (String). I want to know on which day (the number : 1st, 5th …) of the month, the moon is full. (for this, I also need to sort the days).
    This JS code would work if the relation itself was sorted by date (“jour pic” being the full moon):

const days = p1.split(",");
var index = 0;
for(var i = 0; i< days.length; i++){
if(days[i].trim()==="Jour pic")
index = i;
return index === 0 ? "" : index + 1;

And many others …

Indeed, I can sort it everytime I do a lookup with some custom JS code, having like a dictionnary : [Date : Info] (template column) I would do a lookup from month to days and then sort it with JS… But not ideal ! It would be much better to have the relation itself sorted ^^

No you don’t need the days to be sorted at all. Try this:

  • In the table that contains your data, create a math column using the following formula:
Year(Date) * 10000
+ Month(Date) * 100
+ Day(Date)

  • This will give you an integer that represents the date in YYYYMMDD format, eg. 20221008 for today
  • Now create a similar column, except substitute “Now” for Date, and subtract 1 from it:
Year(Now) * 10000
+ Month(Now) * 100
+ Day(Now)
- 1

  • This will give you a similar integer, representing yesterdays date
  • Now create a single relation between the two columns.
  • Finally, use a lookup column through that relation to obtain yesterdays temperature.

Again, you don’t need to sort anything.
You can use a slight variation of the above method. Assuming that your data contains dates spanning multiple months, then you’ll need a template column that concatenates the YearMonth and the moon phase that you need.

  • The math column should use a slightly modified formula:
Year(Date) * 100
+ Month(Date)
  • This will give an integer representing the Year & Month in the format YYYYMM, eg 202210
  • Use that in a template, concatenating it with your moon phase
  • Create a single relation that matches the two templates
  • Use a lookup column to fetch the Date of the moon phase

Once again, no, it isn’t.
When you examine a relation column in the data editor it might look like it contains data, but that’s deceiving. It doesn’t. It’s just a link.
When you create a lookup column via a multiple relation then sure, the result is an array.
But the array exists in the lookup column, not in the relation.
If you were to use a joined list column that operates on a multiple relation, then the result would not be an array, but a string.
So once more, trying to “sort” a relation is a meaningless exercise, as there is nothing to sort.