Are you using our SaaS platform (Baserow.io) or self-hosting Baserow?
SaaS
What do you need help with?
In the parent table “containers” I have containers from our warehouse (raw feedstock for production). These containers have input and outputs of material, so their weight / volume is changing, sometimes rises, sometimes get lower. I’m saving the data of weight measurements in a child table “weight history” including columns like the date of the measurement and the weight itself. The primary column is in this child table is any unique id (but with no helpful statement itself)
In the parent table “containers” I want to show just the last actual weight (it might be higher or lower than weights before!). So if have to filter or aggregate the linked dataset to a single row of the "weight measurement history) with the latest date and want to show that single weight in the parent table “containers”.
How can I get this single value?
So if I understand it correctly you need something like first() with reverse() or last() formula function, or a similar function on a roll up field. I don’t think we have that in Baserow but maybe some other folks can help here with some workaround
This is a perfect case for the filter() formula.
I assume you have the following Weight table. Each record has an ID, reference to a container, date and a weight:
In the Containers table, you add a rollup field that get the maximum date of the linked weights. This returns the last date the weight of the container was measured.
Finally, you add a formula field to the Containers table. The formula is avg(filter(lookup('Weights','Weight'),lookup('Weights','Date') = field('Last weight date')))
The part filter(lookup('Weights','Weight'),lookup('Weights','Date') = field('Last weight date')) looks up the Weight of each related record and compares the Date field of that record with the Last weight date field. If you only have 1 weight session a day, it returns only one number.
But to make sure it is always a single number, I wrap it with the avg() function that calculates the average weight. Just in case there are wore weights for a single day.
