Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

2075 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Alex_Bernson
4 - Data Explorer
4 - Data Explorer

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.

9 Replies 9
chris_Rees
4 - Data Explorer
4 - Data Explorer

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

Phil_Lee
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Tesoreria_APA
4 - Data Explorer
4 - Data Explorer

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

scotty
6 - Interface Innovator
6 - Interface Innovator

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.

Pratik_Shah
6 - Interface Innovator
6 - Interface Innovator

Thank you @Alex_Bernson for this formula. works really well

Schemelnator
4 - Data Explorer
4 - Data Explorer

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 🙏