CustomerFirstNameFunctionAtSignInNameExample

Getting First and Last Name from a Dynamics SL Name field In a Crystal Report

Microsoft Dynamics SL ER system gives you the ability to make names, such as customer names, vendor names, employee names, etc. sort by something other than the the first character in the name field. So, for example you can create a customer named John Smith and have that name appear in list and reports, sorted by “Smith” instead of by “John”.

 Create names with alternative sort ordering

Sorting Names by something other than the first character
Most maintenance screens that have “name” fields support the use of an “@” in the name to indicate what part the name should be used for sorting.The image to right demonstrates how you would enter then name for a customer “John Smith” if you wanted their name to appear on reports and data entry as “John Smith” but you wanted their name to sort in lookup windows and on reports based on the name “Smith” instead.

Searching for names with alternative sort orders

PVAtSignInNameExampleWhen searching for records like Customers, Vendors, or Employees where the name supports the use of the “@” to define alternative sort orders, if you resort your Possible Values (PV / F3) lookup windows by the name column (Click the name column header to resort your lookup lists), you will see that the names display “appropriately” with the first name first and the last name last.

Note also that they sort by the last name. So for example in the image to the right, “John Smith” is sorted after “Tailspin Toys” and before “Scott Schute” (note, Scott’s name was NOT entered with a “@” so it’s sorting by the first name). And “John Smith” is NOT appearing down with “Jeff Smith” and “Jeff Price” (again, Jeff Smith and Jeff Price were not entered with the “@” to make then sort by last name)

Crystal functions for displaying names in reports

When you use the “@” sign in a name in the maintenance screen, what the system actually does is store that name in the database with the part of the name that comes after the “@” FIRST, then it places a “~” character after that, then it puts the part of the name that came before the “@” LAST.So it “Flips” the name. This means that if you want to display all or part of a name in something like a Crystal report, as shown to the right, you will need to do a little work with some Crystal functions.

Crystal functions Get the “First” name

To get the “first” name from a name that was created using the “@” in Dynamics SL, you’ll need to create a Crystal Formula Field. In that formula, you will need to use at least the following Crystal functions:

  • If / Then / Else
  • MID()
  • INSTR()
  • LEN()

One of the first things you’ll need to decide is, what are you going to do for a “First Name” if the original name did NOT have an “@”…. I’ve taken the easy way out in this example, and just assumed that you want the whole name in that case. Odds are that wont be “correct” for you, but I leave it to you to decided what “should” happen.

If the name was created with an “@” then the data in the field will have a “~” just before the part of the name that is supposed to be the “first name”. So, we need to use the INSTR function to find the position of that “~” character, then starting with the character from one position further in the string, get however many more characters it takes to get to the end of the name.

The image to the right demonstrates this.

The MID function will given you a part of a string starting at a given position and going for a given number of characters.  To get the starting position of the First name, we get the position of the “~” character plus 1: instr({Customer.name}, "~") + 1

To determine the number of characters that are in the “First Name” we need to subtract the number of characters up to and including the “~” from the total length of all the characters in the Name field: len({Customer.Name})-instr({Customer.name},"~"))
So when you put it all together you get the following to extract the first name from a name with an alternative sort: mid({Customer.name},
instr({Customer.name}, "~") + 1,
len({Customer.Name})-instr({Customer.name},"~"))

 Crystal functions Get the “Last” name

To get the “last” name from a name that was created using the “@” in Dynamics SL, the Crystal function you will need to create is very similar, though a little easier than the one you need for the frist name.

If the name was created with an “@” then the data in the field will have a “~” at the end of the part of the name that is supposed to be the “last name”. So, we need to use the INSTR function to find the position of that “~” character, then get all the characters from the first character to one character before the “~”.

The image to the right demonstrates this.

Getting the starting position of the last name is easy.  It’s just the first character.

To get the number of characters in the Last name, we get the position of the “~” character minus 1:

instr({Customer.name}, "~") - 1

 

 

So when you put it all together you get the following to extract the first name from a name with an alternative sort:     mid({Customer.name},
1,
instr({Customer.name}, "~") - 1)

Posted in Dynamics SL Crystal Tips Tricks and Examples, Dynamics SL Tips Tricks and examples.