Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

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

4543 8
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

8 Replies 8

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

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

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

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

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

Please can I get your script for this?

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.

Thank you @Alex_Bernson for this formula. works really well