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 May 9, 20169 yr by Hoytopher

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 May 9, 20169 yr by comment

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 May 9, 20169 yr by LaRetta