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.
