FIELD RULE

[Barry Shadduck]

Hello,
Looking for assistance with a FIELD RULE problem.
Trying to create a FIRST-NAME, and LAST-NAME field from
a NAME field that has information stored such as:
example 1:"SMITH, BOB" and example 2:"WORKORDER".

I have the following field rule to extract last name:
IF(CONTAINS(NAME,","),LEFT(NAME,AT(",",NAME,1)-1),NAME)
This works fine as long as NAME is in the example 1 format.
Does not work for the example 2 format. The error is value out of range.

[Tom Cone Jr]

RE: FIELD RULE

Barry,

is your ultimate goal to have separate first name and last name fields in your table? If so, check out the possibiiity of defining an update operation to parse the existing data. The genie there will help you break the field down into its constituent parts. This will obviously be easier for the field values which contain a delimiter, such as your comma.

If you succeed in updating all the first and last name fields based on current values in your table, you probably won't need field rules to split future field values. You could just put the separate fields on the data input form directly.

-- tom

[Barry Shadduck]

RE: FIELD RULE

My ultimate goal was to conduct a one time import into
Alpha 5, process the data , and be done.

There may be a work-around with an update operation, but I want to know why the farily basic line of code does not work in the field rule. Can you admit that it is an A5 bug?

By the way I found a way to parse the names using Monarch against the source data field.

Thanks Tom.

[Tom Cone Jr]

RE: FIELD RULE

Barry, I see what you mean.

Try:

if(AT(",",Name)>0,left(Name,at(",",Name,1)-1), Name)


I think possibly Contains() is expecting a search argument that contains words, and not just single char strings, but this is a guess. The above sequence, using just the AT() function works fine here.

If you're defining a field rule, and then re-evaluating the field rules only once, in order to populate the first name and last name fields, I'm glad it's working for you. In my own work here I'd be inclined to define and run a saved update operation instead. This would leave the Name field free of the overhead associated with the field rule approach which will continue to be felt through the life of the table. But... if it works for you doing it as you describe, that's really all that counts.

-- tom

[JerryBrightbill]

RE: FIELD RULE

Barry,

Whether this is a bug of not is a matter of interpretation. The failure is in the contains() function. It will always return True, which creates a problem if at() returns 0 since left(value,0) makes no sense and will generate an error. If you read the manuals, contains() searches a character string for a character word. A punctuation mark is not a word. Actually, contains() works as expected.

The correct function which does work is the *any() function. This does find a punctuation mrk like a comma. This does work

IF(*any(NAME,","),LEFT(NAME,AT(",",NAME,1)-1),NAME)

There is a difference between a character word and a character expression.

Jerry

[JerryBrightbill]

RE: FIELD RULE

Tom,

You type faster than I do.

Jerry

[Barry Shadduck]

RE: FIELD RULE

Thanks for the tip, I'll try it some day when I have more time. I may be hard headed but I call it a bug, when
CONTAINS returns only .T. and not .F. It is at least a very annoying inconsitency. In my mind my code should have worked. I should not have to try this, and try that, and try something else to get things to work.

[Barry Shadduck]

RE: FIELD RULE

The manuals for 4.5 are not hardly worth reading, but
I can tell you that CONTAINS WILL FIND A COMMA IN A STRING.

The problem was the fact that through the inconsistent nature of this animal (A5) CONTAINS fails to return the
False condition.

Thanks

[JerryBrightbill]

RE: FIELD RULE

Barry,

If you use a wrod or part of a word in contains, it does work

name = "mr smith"

contains(name,"smith")

will return .T.

contains(name,"sm*")

will return .T.

contains(name,"mr")

will return .T.

contains(name,"joe")

will return .F.


Jerry