Jump to content

Sorting a calculated text field


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

Recommended Posts

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

Link to comment
Share on other sites

"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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

This topic is 5598 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
 Share

×
×
  • Create New...

Important Information

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