You are viewing limited content. For full access, please sign in.

Question

Question

Truncate after a special character, not decimal

Forms Version 11 Self-Hosted
Updated May 23, 2024
asked on May 23, 2024

A form needs to be auto-assigned to an employee. Most users don't know their username, but they know their agency email (example: email@cityofmadeup.com). Their username is everything before the @ sign. 

Is there a way to, in forms, drop the @cityofmadeup.com and only leave the username using a function? I don't have much JavaScript experience and don't want to try to maintain JavaScripts through Laserfiche upgrades. 

Thank you for your time.

 

0 0

Answer

SELECTED ANSWER
replied on May 23, 2024 • Show version history

Here's another option.  At least in the new designer, the field with the formula will show a formula error if the source email field is blank, or if doesn't contain the @ symbol.  Based on your needs, that error may be acceptable - but if that is a problem, then this formula should be able to get the same result while avoiding the error: 

=INDEX(SPLIT(EmailField,"@"), 1)

 

The inner SPLIT formula breaks up a string into an array, split each time it encounters the indicated character, so email@website.com becomes an array of email and website.com.  Then the outer INDEX formula selects the first item in that array of values.

Unlike the FIND formula that will generate an error if the searched character isn't found, the SPLIT formula returns a value even if the character is never found.
{BLANK} will evaluate as {BLANK}
email will evaluate as email
email@website.com will evalutate as email
email@website@com will evaluate as email

2 0

Replies

replied on May 23, 2024 • Show version history

I would have your employees enter their email address and then have a second field for their username that uses the following calculation formula:

=LEFT(EmailField,FIND("@",EmailField)-1)

Replace "EmailField" with the variable name of your email field.

3 0
SELECTED ANSWER
replied on May 23, 2024 • Show version history

Here's another option.  At least in the new designer, the field with the formula will show a formula error if the source email field is blank, or if doesn't contain the @ symbol.  Based on your needs, that error may be acceptable - but if that is a problem, then this formula should be able to get the same result while avoiding the error: 

=INDEX(SPLIT(EmailField,"@"), 1)

 

The inner SPLIT formula breaks up a string into an array, split each time it encounters the indicated character, so email@website.com becomes an array of email and website.com.  Then the outer INDEX formula selects the first item in that array of values.

Unlike the FIND formula that will generate an error if the searched character isn't found, the SPLIT formula returns a value even if the character is never found.
{BLANK} will evaluate as {BLANK}
email will evaluate as email
email@website.com will evalutate as email
email@website@com will evaluate as email

2 0
replied on May 23, 2024 • Show version history

I just needed to point out, since it won't last long, but at this exact moment my reputation here on LFAnswers is 7777 and that's just too perfect.

5 0
replied on May 23, 2024

Thank you for these solutions. I ended up using Matt's solution to reduce the possibility of errors for the formula. Although now that I re-read them, I can force them to have the @ symbol by changing it form Single Line field to an Email field.

 

All the same, I appreciate your time helping me solve this problem. 

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...