Select upper-case letters from variable-length text string

[mlhoenig]

In a solution I'm developing, I need to set a "key" field; the key is two characters long, and will be unique to each of my customers and vendors. My business is small enough that I can be fairly certain of this, but it would be nice to have a calculation which can be changed if need be.

Anyway; my current criteria are these:

1) Customer/Vendor name will be one or two words; two examples are: SportCrafters and Valley Bikes

2) The key field (Company Key) should be the first two UPPER-CASE letters in the company name (SportCrafters = SC; Valley Bikes = VB)

3) Not all of the companies' names will have two or more upper-case letters, so a manual-entry option is desireable

A name like Valley Bikes is easy; not so obvious -- to me, anyway -- is how to handle names like SportCrafters.

Can this be done? :confused:

[Raybaudi]

Hi

Filter(company;"ABCDEFGHJKILMNOPQRSTUWXYZ")

will give you:

SC for SportCrafters

VB for Valley Bikes

For the Key field, as you wish sometimes to change, you need a text field with auto-enter calc.

[Ender]

In a solution I'm developing, I need to set a "key" field; the key is two characters long, and will be unique to each of my customers and vendors. My business is small enough that I can be fairly certain of this, but it would be nice to have a calculation which can be changed if need be.

Constructing primary keys from user-entered data is a bad idea. There are two main problems. First, should you add a vendor that shares the same initials, you may not realize the duplication right away, leading to incorrect data entry with on the existing vendor's related records. Second, should a vendor's name change, the ID will either no longer be derived from the name, or it will get updated with new initials, breaking existing relationships.

Instead, I recommend using auto-entered serial numbers for primary relational keys.

Edited December 29, 2005 by Guest

[mlhoenig]

Thanks, raybaudi & Ender...

I'll try the Filter first. I'm absolutely certain that, in my particular solution, I don't need to deal with more than one Vendor. I have only one supplier, and anticipate only one other if things go well in my current negotiations; this is an absolute...and they don't share initials.

The problem *could* occur if I happen to come up with two Customers sharing company initials; this isn't a problem yet, and shouldn't become one any time soon. My repeat Customers are bike shops and, at the moment, I only have a half-dozen or so who buy from me at wholesale prices; everyone else is a retail customer, and I don't need to add them to my Customer list.

Thanks again!