I am trying to get the Open value in the Table 2 under Reservation Status column. I tried a bunch of ways but I can’t get it to work.
- In Table 2, create a multiple relation that matches the StockID to the StockID in Table 1
- Use a Single Value → Last to fetch the Reservation Status via the relation.
NB. The above assumes that records will always be added in chronological order.
The issue is that it may not be in the chronological order.
This kinda worked, let me know if it makes sense:
- Multi relation in Table 2, StockID to StockID
- In table 2, a new look-up column to lookup Reservation status
- In table 2, a if-then column that checks in column in step 2 contains “Open”
Okay, if they are not guaranteed to be in chronological order, you can do it as follows:
- Multi relation in Table 2, StockID to StockID
- Rollup in Table 2, taking the latest Reservation Date via the multi relation
- Single Relation in Table 2, matching the Rollup Reservation Date to Table 1 Reservation Date
- Lookup in Table 2, taking the Reservation Status via the Single Relation
1 Like
You can create a conditional relation which will be stock id + open and the you can use a lookup
1 Like
How can i create a conditional relation?
With a template. But I don’t think that will help in this case because a single relation returns the first matching row, which means you’d be getting the earliest date, not the latest.
Yes using a template column. The template will be stock id + reservation status.
You can do match multiple here and then use a single value column and using that relation you can get the last value
They need the latest, not the last. This solution is not guaranteed to give the latest unless the records are in chronological order. And they might not be…
Okay understood!
Thanks for suggestions. The solution I outlined works for me and it’s quite elegant. I pull in all Statuses for each StockID and check if contains Open via if-then function.