Roll-up Formula that provides only last / latest value to handle multiple form submissions

[Alex_Bernson]

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:

Split First and Last Names

That's a little more difficult… At heart, the formula would be something like this: REPLACE({Name},1,FIND("~",SUBSTITUTE({Name}," ","~",LEN({Name}) - LEN(SUBSTITUTE({Name}," ","")))),"") That's a little hard to suss out at first glance, so let me break it down, back to front: LEN({Name}) - LEN(SUBSTITUTE({Name}," ","")) gets the difference in length between {Name} and {Name} with all spaces stripped. This gives us the index of the last space in the name, which presumably prefaces the las…

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.

[chris_Rees]

Just applied it across my base and works great saves on having loads of formulas as workarounds :grinning:

[Phil_Lee]

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

[kuovonne]

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

[Tesoreria_APA]

I’ve been looking for such a formula for a while. Thank you very much for sharing! This is a really smart solution!

[scotty]

I find this entire thread to be sexually attractive. Love!

[David_Zisner1]

Please can I get your script for this?

[kuovonne]

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.

[Pratik_Shah]

Thank you @Alex_Bernson for this formula. works really well

[Schemelnator]

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 🙏