If I have defined a parent table and a child table linked by a one-to-many relation, how can I get the row id of the last added child record to show in the parent table?
Have found the way using “rollup” (parent-child relation not relevant). Thanks!
Glad you found it. Ya… single value column.
Oh so that is what “single value” means! I was using “Rollup” actually. But thanks!
Single value > last would bring in the last item from a table or from a relation.
I have another question on “Single Value”. The configuration of Get: First, Last Random, From Start, From End, does not allow the choice of a sort order. So a table may look like the following:
Get First of Date will return 6/18/2022. Get Last of Date will return 6/17/2022. There is no way to get 6/15/2022 unless I know its precise position From Start or From End. I will have to use Rollup to get 6/15/2022. But I will still not be able to get Row-id jkmn by either Single Value or Rollup.
Any suggestion? Thanks.
So you want to find the row that contains the earliest date value, and fetch the RowID from that row, yes?
mmm, this is a tricky one. Here is one way it can be done.
- Firstly, convert all your Dates to numbers with a math column using the following formula:
Year(Date) * 10000 + Month(Date) * 100 + Day(Date). This will give you a number that looks like
- Next, use a rollup column to find the minimum value in that column
- Then, create a single relation column that matches the minimum to the math column
- Finally, use a lookup column to fetch the Row ID through that relation
One thing to note is that if you have duplicate dates, then this method will always give you the first one. But I assume that’s what you want?
@mark - this a good example of where it would be useful to be able to do a min/max rollup and have it return a column value other than the one that’s being used for the rollup.
Hi Darren, Thanks. Yes I understand your explanation. Your last paragraph is what I had in mind.
@Darren_Murphy Why wouldn’t you use the “Earliest” rollup?
In this case, the poster didn’t want the date, they wanted the RowID that was associated with the earliest date. There is no direct way to do that, hence the 4 column solution that I offered.
But if we could do a max/min rollup and return a column value other than the one that is used for the rollup, then it would be a single column solution. If you recall a while back you asked for examples of things that weren’t easy to do with Glide computed columns, this is very similar to one the examples I gave at that time.
Thank you, I get the larger point of wanting a column associated with the min/max/earliest/latest.
What I was curious about is why use a Math column when you can do the “Earliest” rollup over the date column directly.
Yeah, you could do that. But then you’d need to use that date value to create the relation and subsequent lookup. But I’ve found that using dates to create relations can be problematic, so I tend to avoid it. I find it much safer (and more reliable) to first convert the date to either a string or integer value.
If it doesn’t work with the date then that’s a bug. I’d love to see a reproduction
Ya relating a date column to another date column doesn’t always work because sometimes the dates have times and the two don’t match exactly. Much safer to convert dates to integers and relate the numbers.
mmm, although I was thinking about it last night after Mark asked the question.
In this particular case, it should work because you’re basically relating the exact same value to itself. So even if it does have a time component it should match, and if it doesn’t it’s a bug.
So yeah, @Mark, I don’t think I can come up with a breaking example for this specific use case.
That said, I’m still nervous about using dates for relations