Jump to content

Removing First/Last Character in Field if " "

This topic is 2243 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am merging and reorganizing roughly a decade's worth of FileMaker layouts, designed by a user without knowledge of how FileMaker works, and used by several employees over time. As a result, the formats of data entry have shifted constantly, making it difficult to effectively merge and connect data.

The problem I'm running into is that First/Last name were calculated together without a " " between the names. As a workaround, data was entered into fields with either a space at the end of First, or beginning of Last. I currently suggest that the names be edited as they're used to display related data properly, but this is inefficient and burdens the user. Often, there are spaces used for middle/last names, in the middle of the field.

As First/Last name were not always separated, I created the Calculation field [Full Name], "First + Last". In the Relationships graph, I set [Calculated Full Name] = [Imported Full Name]

I am slowly introducing myself FileMaker and data modeling/programming, and have hit a wall in how to write a script to delete the redundant spaces so that [Calculated] will equal the [Imported] value, therefore displaying portal records properly. So far as I can tell, here is the concept:

In [First]:                         In [Last]:

if (first character) = " ", delete (first character) 

If (last character) = " ", delete (last character)


Is this a viable model? How would I write this script?

If I wanted to learn the underlying process, what topic should I research?

Thank you very much, and I appreciate how supportive and knowledgable this community has been each time I have had a question!


Edited by Hoytopher
Link to comment
Share on other sites

It's difficult to understand your question.

Technically, using Trim ( Field ) will remove any leading and/or trailing spaces from Field. And Substitute ( Field ; " " ; "" ) will remove all spaces from Field.

Is this a viable model? Probably not, as names should not be used as match fields for relationships.

Note also that First + Last makes no sense with Text fields; it should be First & Last.



Edited by comment
Link to comment
Share on other sites

53 minutes ago, Hoytopher said:

I created the Calculation field [Full Name], "First + Last".

Also, if/when you wish to combine two fields in a calculation, you want to use '&' and not '+'.  

& appends two strings together: "ab" & "cd" -> "abcd".   + is numerical operator.  

So if you need to create *calculation with full name field, it would look like:  FirstName & " " & LastName.  

* This calculation, as Comment indicates, is not good choice as a match field.  For display, you can simply use merge fields as <<FirstName>> <<LastName>> and the calculation is usually created only if you need to export the full name or use it in a value list.

Edited by LaRetta
Link to comment
Share on other sites

This topic is 2243 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.