Taking first letter from field A and combining with field B to create Field C

[rwoolf]

Is there a way to take the first letter from a FIRST_NAME field and combine it with the LAST_NAME field to create a new USER_NAME field?  If so, how? I have been searching for instructions on how to do this and cannot find anything. Any suggestions?

[Mathilda]

18 hours ago, rwoolf said:

Is there a way to take the first letter from a FIRST_NAME field and combine it with the LAST_NAME field to create a new USER_NAME field?  If so, how? I have been searching for instructions on how to do this and cannot find anything. Any suggestions?

Hi,

it can be done using sql substing query in the formula field: SUBSTRING ([@field:First_name] ,1 , 1)  + SUBSTRING ([@field:Last_name] ,1, 1)

However, in case of further using username field in authentication, this is not the best option since you can using only unique field as a username.

Therefore, you may insert the following script in the footer of your submission form.

<SCRIPT LANGUAGE="JavaScript">

function concatenate()
{
var position1 = document.getElementById("InsertRecordFirst_name").value;
var position2 = document.getElementById("InsertRecordLast_name").value;


document.getElementById("InsertRecordUsername").value = position1.substring(0, 1) + position2.substring(0, 1);

}

document.getElementById("caspioform").onsubmit=concatenate;
</SCRIPT>

 

[rwoolf]

Thanks. I will give that a try.

[Mathilda]

1 minute ago, rwoolf said:

Thanks. I will give that a try.

you're welcome  don't forget to insert your field names instead of mine.

I modified script from this post

[GoCubbies]

Another option is to use a formula field, and the formula below:

Substring([@field:First_Name],1,1) + [@field:Last_Name]

This will then work even if you import data, and avoids any JS needed for the form.  

[rwoolf]

On 7/11/2017 at 8:13 AM, Mathilda said:

 

It can be done using sql substing query in the formula field: SUBSTRING ([@field:First_name] ,1 , 1)  + SUBSTRING ([@field:Last_name] ,1, 1)

 

The above formula works if I want the first initial of both the first AND last names. However, how do I keep the FULL last name?

For example:   First_Name = Bob

                           Last_Name - Smith

                           User_name = BSmith

[rwoolf]

Never mind. The answer became blatantly obvious once I thought about it.

        SUBSTRING ([@field:First_name] ,1 , 1)  + [@field:Last_name])

[NailDyanC]

Hello, just to add to this post.  You might find this link useful for Function Reference for SQL Statements.

https://howto.caspio.com/function-reference/

[autonumber]

Hello! Just to update this post, if you have FULLNAME field and you want to get the initials for First and Last Name, you can use this formula - 

Left([@field:Name],1) + Substring([@field:Name],Charindex(' ',[@field:Name],0),2)