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

Question

Question

I need a formula to split {/_currentuser_display} into 2-3 parts

Laserfiche Forms Version 10
Updated June 4, 2021
asked on June 3, 2021 • Show version history

Our display names come in as either First Name Last Name or First Name Middle Initial Last Name. I am not in a position to change this, I just need to work with it.

 

Examples

Kermit Frog

Kermit T Frog

 

For our forms we need to split it out to

Last Name: Frog

First Name: Kermit or Kermit T (Middle initial stays with the first name)

 

The formulas I currently have works fine when there is no middle initial but when it exists I get back

Last Name: T Frog

First Name: Kermit

 

 The formulas I am using now (Sting_To_Manipulate = {/_currentuser_display}  )

Last Name: =RIGHT(Sting_To_Manipulate, LEN(Sting_To_Manipulate) - FIND(" ",Sting_To_Manipulate,1))

First Name: =LEFT(Sting_To_Manipulate, FIND(" ",Sting_To_Manipulate,1))

 

I have been fighting with this for a couple hours and thought I would see if someone has already solved this issue on forms.

 

EDIT: sometimes Middle Name is not just an initial it can be the actual middle name.

0 0

Answer

SELECTED ANSWER
replied on June 4, 2021

If you want to stick with formulas on the form side, I found that searching for Excel formulas is the best way to figure out how to do something. So courtesy of this post, you can get the last name using

TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))

where A1 is whatever field has the full value.

Then I think you should be able to get the First +Middle by substituting the last name with nothing in the full value.

1 0
View 3 previous replies
replied on June 4, 2021

Nice thanks. I do enjoy JavaScript but I really do like having multiple ways to do something. Then figure out what works best for current case I'm working on. And awesome hint on using Excel formulas to figure it out!

0 0
replied on June 4, 2021

Sadly that only removed the spaces in the name to I got back "KermitTFrog", but now I will go down the excel rabbit hole and see what I find.

0 0
replied on June 4, 2021 • Show version history

this worked to get the last name, I would not have found it or given it a thought if you had not suggested using Excel formulas!

=RIGHT(Sting_To_Manipulate,LEN(Sting_To_Manipulate)-FIND("*",SUBSTITUTE(Sting_To_Manipulate," ","*",LEN(Sting_To_Manipulate)-LEN(SUBSTITUTE(Sting_To_Manipulate," ","")))))

String_To_Manipulate is field I'm testing this out on.

1 0
replied on June 4, 2021

Here is the formula using the suggestion on SUBSTITUTE

=TRIM(SUBSTITUTE(Sting_To_Manipulate, Get_Last_Word, "") )

Get_Last_Word is the last name

1 0
replied on June 4, 2021

Both Forms formulas and Workflow's Token Calculator activity are based on Excel formulas, so most of the time, somebody already wrote whatever I'm trying to do for Excel. I usually just search for it first and throw Excel in as a keyword.

1 0
replied on June 4, 2021 • Show version history

After playing around with and trying to break the formulas above (let's face it some parents do odd things to their children's names and then some people marry others that have and this can cause odd names ... I know a couple that they shared the same first name, middle initial and their last names were just different spellings of the 'same' name')

What I found is the above breaks if the Last name is also somewhere in the first/middle combo. I came up with this instead for getting the First/Middle name combo and so far I have not been able to break it even typing in Dumbledore's name (Albus Percival Wulfric Brian Dumbledore ) ;)

=TRIM(LEFT(Sting_To_Manipulate, LEN(Sting_To_Manipulate) - LEN(Get_Last_Word) ))

 

1 0

Replies

replied on June 3, 2021 • Show version history

Genny,

If you are confident that it will be either one of the combinations 'First MI Last', or 'First Last' then one option would be to apply regular expressions to the name to parse out the first and last names. 

A possible regex to get the last name will be \w+\s+\w+\s+(\w+)|\w+\s+(\w+) and a possible regex to get the first name and MI or first name is (\w+\s+\w+)\s+\w+|(\w+)\s+\w+

Those two expressions will pull out 'Frog' from 'Kermit Frog' and 'Kermit T Frog' for the last name, and 'Kermit' or Kermit T' for the first name.

2 0
View 1 previous reply
replied on June 3, 2021

I'd go for [\w\-] instead of \w to account for hyphenated first or last names.

1 0
replied on June 4, 2021 • Show version history

Would I just put those in the regex field of the field or would I use that in the Javascript behind? I have only used regex once in laserfiche and that was for an entered/typed in field and not for one the system is populating with a value.

0 0
replied on June 4, 2021

I think I'm kind of on my way I was able to grab the last name with the following code. However I have yet to figure out where to put the [\w\-]  as was suggested above. 

 

I am also not confident it's not the best bit of code on my part.

 

 

  var myValue = $('#q1 input').val();/* value to parse */ 

  var myRe =/\w+\s+\w+\s+(\w+)|\w+\s+(\w+)/;
  var myArray = myRe.exec(myValue);
  $('#q5 input').val(myArray[1]) /* last name field */

 

0 0
replied on June 4, 2021

Changing line 3 to below to the following does allow for hyphenated names

var myRe =/[\w\-]+\s+[\w\-]+\s+([\w\-]+)|[\w\-]+\s+([\w\-]+)/;

 

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

Sign in to reply to this post.

Details
Related Posts
Loading ...