Aug 23, 2017 09:32 AM
I’ve setup a number of bases where external people can use forms to submit things like updated contact information by submitting a form as many times as they like, selecting their sponsor company record name as a link field in the form each time.
This creates a series of attached entries to their sponsor record. I then setup the sponsor records as a series of Rollup fields on things like “email” etc that rolls up all submitted form entries.
However I only want to show people the most up-to-date entries submitted, so I made the following formula that only returns the last linked entry .
This is building off this post below:
The formula joins all values into a “;” separated array, does an ugly IF() check to make sure the array doesn’t end with “;” in order to drop any entries from linked records that are empty, identifies the last “;” separator, replaces it with “~”, and then deletes the “~” and everything before it, giving us the last record information.
REPLACE(
IF(
RIGHT(ARRAYJOIN(values,";"),1) = ";",
LEFT(
ARRAYJOIN(values,";"),
LEN(ARRAYJOIN(values,";")) - 1
),
ARRAYJOIN(values,";")
),
1,
FIND(
"~",
SUBSTITUTE(
IF(
RIGHT(ARRAYJOIN(values,";"),1) = ";",
LEFT(
ARRAYJOIN(values,";"),
LEN(ARRAYJOIN(values,";")) - 1
),
ARRAYJOIN(values,";")
),
";",
"~",
LEN(
IF(
RIGHT(ARRAYJOIN(values,";"),1) = ";",
LEFT(
ARRAYJOIN(values,";"),
LEN(ARRAYJOIN(values,";")) - 1
),
ARRAYJOIN(values,";")
)) -
LEN(
SUBSTITUTE(
IF(
RIGHT(ARRAYJOIN(values,";"),1) = ";",
LEFT(
ARRAYJOIN(values,";"), LEN(ARRAYJOIN(values,";")) - 1
),
ARRAYJOIN(values,";")
),
";",
"")
)
)
),
"")
Seems to work, as long as your data doesn’t have any “;” in already—if it does, you’ll need to choose a different intermediate array divider symbol.
Mar 11, 2019 09:08 PM
Just applied it across my base and works great saves on having loads of formulas as workarounds :grinning:
Mar 19, 2020 05:59 PM
Is there a way to make this work and keep it as a number. My variable is a $ amount and I’d like to take the last amount.
Update: Nevermind, realized i could accomplish it just by adding Value(your code)
Thanks
Mar 19, 2020 07:01 PM
Keep in mind that the formula in this topic will get the last record that was linked, not necessarily the record with the last value for a particular field.
I wrote a script to get
a value from the last record according to a user specified sort.
https://community.airtable.com/t/weekend-scripting-block-challenge-and-1-000-prize/28074/10
Oct 02, 2020 07:18 PM
I’ve been looking for such a formula for a while. Thank you very much for sharing! This is a really smart solution!
Jul 06, 2021 02:30 AM
I find this entire thread to be sexually attractive. Love!
Jul 15, 2021 06:06 AM
Please can I get your script for this?
Jul 15, 2021 06:40 AM
Sorry, it looks like the thread for the scripting contest that contained the script has been archived, so the script is no longer available for free.
Feb 15, 2022 05:59 AM
Thank you @Alex_Bernson for this formula. works really well
Nov 30, 2023 08:32 AM
Hey @Pratik_Shah
Could you share a test base with the specific rollup formula in it. Therefore we would have an example.
Thanks a lot 🙏