Calculation Field Problem

[Stephen K]

I have a simple FM6 database that includes a text field "Activity" and a calculation field "Group".

I have set up a calculation based on the data in "Activity" to give a result in "Group"

eg

If(Activity="SRC","School Representation",

If(Activity="Sports Council","School Representation",

""

)) etc.

But I have a range of data in "Activity" such as

Merit - Science

Merit - English

heaps of etc.

What I want to do is to identify the text "Merit" within each record to end up with a calculation that produces the data "Academic Award" in the "Group" field because I don;t want to write a heap of If(Activity=) for each possibility.

Is there an operator I can use here?

TIA

Stephen K

[LaRetta]

Hi Stephen, you might try:

Case (

Activity = "SRC" or

Activity = "Sports Council" , "School Representation" ,

Position ( Activity , "Merit" , 1 , 1 ) , "Academic Award"

)

PatternCount() would also work but Position() is faster. And you can use OR between each Activity entry, which will shorten it a bit (so you don't have to write "School Representation" each time ...

LaRetta :wink2:

[bruceR]

I have a simple FM6 database that includes a text field "Activity" and a calculation field "Group".

I have set up a calculation based on the data in "Activity" to give a result in "Group"

eg

If(Activity="SRC","School Representation",

If(Activity="Sports Council","School Representation",

""

)) etc.

But I have a range of data in "Activity" such as

Merit - Science

Merit - English

heaps of etc.

What I want to do is to identify the text "Merit" within each record to end up with a calculation that produces the data "Academic Award" in the "Group" field because I don;t want to write a heap of If(Activity=) for each possibility.

Is there an operator I can use here?

TIA

Stephen K

You could use a related value instead, where the relationship is based on the short name/initials you have mentioned and an associated field in the related table shows the full name. Of course this is easier in FileMake 7/8 because it is so much easier to add special purpose tables instead of entire new files.

[Stephen K]

Thanks LaRetta

I'll try it...

Stephen K

[Stephen K]

Thanks BruceR

I won't go that far...its making the solution more complex...for me...

I guess what I want is a Calculation that finds a particular word without doing a Find.

Stephen K

[SlimJim]

If Merit is always the first word in Activity in those to which you want to assign "Academic Award" then you could try If(Left(Activity,5) = "Merit","Acadmic Award"