Split a field contents before a word: Calculation

[chuckcou]

I have a field called [color:purple]US County.

Problem is the data has the word County in it. I need to remove that. :

{Of course I could use a find and replace, but I am inporting data and I wan't to keep the orginal import.}

So: In the field: [color:purple]US County

data:

[color:green]The Big Boy County

Riverside County

San Diego County

Imperial County

etc.

As you can see my problem I don't see how I can use a Left or Right (Word) when the field contains 2,3 or 4 words. :qwery:

Thus, I need a calcualtion that give this result:

[color:green]The Big Boy

Riverside

San Diego

Imperial

Can't figure out what to function to use? :bang:

[Lee Smith]

If the value is always going to be "County" you can use the Replace Function with the Substitute Function.

Substitute ([color:purple]US County; "County"; "")

You can do this by using the Replace in the Main Menu >> Field Format >> Replace field Contents

or a Script a Script and the Set Field and the Substitute function.

However you approach it, do it with a copy, as it can not be undone.

Lee

[ThatOneGuy]

Hi chuckcou:

... but I am inporting data and I wan't [sic] to keep the orginal import.

If I follow correctly, you'll need an additional field in your database, and you could define it as (1) a calculation resulting in text, or (2) a text field with AutoEnter capability. There are distinctions between those two field types, but the calculation argument is as Lee indicated ...

Substitute ( US County ; " County" ; "" )

... with the minor exception where the word "County" is preceded by a leading space so that the calculated result does not have a trailing space. Some folks might instead use the Trim function ... Trim ( Substitute ( US County ; "County" ; "" ) ).

If you create a true Calculation field, you won't be able to directly edit the field; perhaps that's what you're wanting. OTOH, a Text field with AutoEnter set to use a "Calculated value" would allow you to directly edit the contents of this new field. If you go this route, be sure to deactivate the "Do not replace existing value of field (if any)" option by unchecking its little box.

A final aside ... if you decided to have just one field in your database for the County, the AutoEnter field will even operate on your imports and strip out the " County" text string, yet you could still edit its contents.

Don't you just love all this hair-splitting? : Let us know how it turns out.

[Lee Smith]

Or

Trim (Substitute (US County; "County"; ""))

:joust:

[chuckcou]

[color:red]Thanks Lee Smith & ThatOneGuy I am just a beginner, but I have learned so much on this forum. It is people like you that do a great job educating. I just wish Filemaker Pro would understand that and compensate you for you time. By all the hard work you do you better their program. Personally, the least they should do is give you a free copy of the new versions.

I used Lee's first calculation without the trim and it worked fine. Not sure why, because there is a space before County. But I have changed it and add the trim. Make more since :

ThatOneGuy: You brought up something I am not familiar with and I am curious. [color:orange]"OTOH, a Text field with AutoEnter set to use a "Calculated value" would allow you to directly edit the contents of this new field. If you go this route, be sure to deactivate the "Do not replace existing value of field (if any)" option by unchecking its little box."

Explain the setup of this a just little more please. I think I know what you mean.

[Lee Smith]

Here is a quick sample of both ways of doing this.

HTH

Lee

US_County.fp7.zip

[chuckcou]

Thanks again Lee

In my situation, I need the use of the 1st example.

But I think I can see the use of the second example>auto enter.

[color:green]Example:

Customer database that looks up person city. This auto enter would give you the ability to change the data should the user move.

[color:red]{If I remember something from the manual, you just have to be careful not to do a relookup}

Well once again, I just learned something new>> auto enter. Thanks again for the extra effort.

Basic concepts I am sure, but I have to start somewhere, books, forums, etc.

:tigger:

[ThatOneGuy]

You brought up something I am not familiar with and I am curious.

Explain the setup of this a just little more please. I think I know what you mean.

As you know, fields defined in your database as a Calculation type do not permit users to edit their contents; you can "click into" them, but you can't change their contents. Much of the time, that's what we want.

AutoEnter fields can be edited, although a calculation is driving them. That hybrid quality is very useful in some circumstances. Care must be given to the calculation, of course, but with the advent of FM7 we are able to reference the field itself within its calculation ... something we longed for under all previous versions. FM7 also introduced the Let function, and when used in the calculation of an AutoEnter field, we can achieve some pretty cool stuff!

My explanation is pretty obtuse, but that's the idea. Experiment with AutoEnter fields and their settings, and I bet you'll develop an instinct for them.

... And thank you for all those complimentary remarks. I'll be sure to forward them to FMI! :wink2: