Is it possible to output the previous values through the formula or automation?
Here’s a simple example of what I’d like to get:
Please help me, I can’t find a solution.
You can see better that way:
Each entity has no relation to other entities in the same db unless explicitly linked, so there is no definition of ‘the previous value’.
However , assuming I understand your example, you could use auto linking to connect entities together (based on matching name) in an n:n relation. Then you can use a formula to find which of the related items has the latest date earlier than the current entity, and find its price.
Edit: this answer was written before the second clarifying image was provided
1 Like
By the way, what should happen with the fifth row? It has the same name and date as row 1, so which came first?
Similarly, 6 and 2.
Perhaps you mean you only want to match if the name AND shop number is the same. If so, include this criterion in your auto relation
1 Like
But different shops.
Two criteria are important - the “Date” and the “Shop” number.
Ok, I didn’t know about the shops, but the principle is the same: match items based on rules, and then use a formula on the resultant collection to get the previous item’s value.
You do need to consider what should happen if there are multiple items with the same product, shop number and date. Which one should be thought of as being earlier and which should be later?
1 Like
I’ll try to implement it now
based on the date
@Chr1sG Did you mean like this DDD.Sort(Date).First().Price ?
Is it possible to select an element of the array? I can select not the first element but the second?
DDD.Sort(Date).[0].Price
DDD.Sort(Date).[1].Price
DDD.Sort(Date).[2].Price
DDD.Sort(Date).Second().Price - doesn’t work
1 Like
The auto-relation (which you’ve done correctly) will link to any entity with the same name and same shop, so for any entity, this will include future dated items and also itself.
So the formula you need will be something like this:
DDD.Filter(Date < [This Data].Date).Sort(Date).Last().Price
1 Like
Also
but I wrote