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


#1

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.