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.
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)
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.
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 !
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.
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
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 ^^
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.
