Sorting a calculated text field

[susan siegel]

I'm having trouble doing an alpha sort on a last name, first name field that I created as follows:

1. the original field was first name last name (no comma) (field #1)

2. using left and right functions to break down field #1, I've been able to create a new field: last name, first name. (field #2)

My problem is that I cannot do an alpha sort on the new field (#2). Is this because it's a calculation field instead of straight text field?

Nor can I go back to the "last name" only field for the sort as this is also a calculation field.

I've tried to change field #2 into a text field by creating a new field (#3)using the getastext and getas SVG functions but either that's not the solution or I'm doing it incorrectly.

I've tried to duplicate field #2 and change the copy to a text field but then the field comes up empty.

Any suggestions would be most appreciated.

Thanks

Susan

[Ender]

You can sort on calculation fields, just make sure the fields are indexed (check the Storage options). Also, in this case, make sure the result of the calc is text.

[LaRetta]

"2. using left and right functions to break down field #1, I've been able to create a new field: last name, first name. (field #2)"

Solve it once and for all. Have standard field - one each for first name and last name. With your current arrangement, Lord help you when they insert a middle initial (and so forth). And, with your theory of grabbing only the left or the right, you will break people's names such as Van Dyke.

[susan siegel]

Thanks for the quick response. I'm embarrassed to say that I should have seen that extra dialog box. It's working fine now.

Susan

[susan siegel]

You're absolutely right. Two fields is the way to go. The problem is I'm working from a list that I inherited that was in "first name last name" format.

To complicate matters, the field includes multiple names (although thankfully most have only one name) with some names in parenthesis (these have to be treated differently.) So once I get the 2 word names and middle initial names done, I still have to edit the list manually for the others -- and pick up any errors.

susan

[Lee Smith]

I couldn't agree more with LaRetta about changing these fields to individual fields. The more you can separate the data at the point of entry, the more flexibility your data will when making your reports.

Here is an old file that should convert okay, that you might find helpful. The file was contributed a long time ago by a very special lady: Lynne Bradford, and it is about the best I've seen in breaking out names.

HTH

Lee

[LaRetta]

Hi Susan,

"The problem is I'm working from a list that I inherited that was in "first name last name" format."

Of course. This is the way most FM solutions begin ... with a migration and data which is not normalized. If you solve the name-mix problem first, it will save you a LOT of time and effort. Each day you wait (and this design is used), more junk will be entered. Each day that passes, you will be less likely to remember all the locations and ways you've used these temp calcs. You will build Band-Aide upon Band-Aide; place these calcs in reports, use them in scripts and so on. Will you remember all the places? Example: If you use one of these calcs as merge field on a report, FM will NOT tell you. And your report will break when you change (and then delete the calc). DDR helps a bit but not in all cases.

I assure you ... reworking a solution is much more difficult than structuring it correctly from the start. I call it unraveling my knitting and it is NOT fun. When you unravel, you risk breaking some of your process. Instead, make each step a step forward. Data-normalization should be top priority. And client names are the most important piece in data-normalization.

LaRetta :wink2: