Skip to main content

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

  • Aug 23, 2017 4:32 PM UTC
  • 9 replies
  • 231 views

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.

This topic has been closed for replies.

9 replies

  • New Participant
  • Mar 12, 2019 4:08 AM UTC

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


  • New Participant
  • Mar 20, 2020 12:59 AM UTC

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
Forum|alt.badge.img+29
  • Brainy
  • Mar 20, 2020 2:01 AM UTC

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!


Forum|alt.badge.img+1
  • Known Participant
  • Jul 6, 2021 9:30 AM UTC

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


Forum|alt.badge.img+2
  • New Participant
  • Jul 15, 2021 1:06 PM UTC

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


Please can I get your script for this?


kuovonne
Forum|alt.badge.img+29
  • Brainy
  • Jul 15, 2021 1:40 PM UTC

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.


Forum|alt.badge.img+7
  • Inspiring
  • Feb 15, 2022 1:59 PM UTC

Thank you @Alex_Bernson for this formula. works really well


Forum|alt.badge.img+2
  • New Participant
  • Nov 30, 2023 4:32 PM UTC

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 🙏