substitution

[jockm]

I have imported records from another application into Filemaker pro 7. I have one field called "Category code" in which from zero to seven letters (a,b,c,d,e,f,g) appear, which correspond to category codes in the old application. I created a new calculation field called "CATEGORY" which is supposed to look at the category field and put in English words corresponding to the category code(s) (if any). So far I have not figured out how to do this.

[LaRetta]

I would ask a few questions:

1) How many codes are there?

2) Will these code names ever change in their wording

3) Will new codes ever be added?

I would suggest probably that you use another table called Codes. It would contain two fields:

Code

Description

Manually type in the code letter and description on each code. Then relate this table to your table containing the imported codes, joining on the Code and using =. Then whenever you wish to see the description of the code, simply place the Codes::Description field directly on your layout.

If there are only a few codes and they won't ever change, you can use a Case() calculation (result of text) similar to:

Case (

Code = "A" ; "Approved" ;

Code = "P" ; "Pending" ;

Code = "C" ; "Cancelled )

However, usually you will find that you will have to change this calculation and it is best to use another table which can be easily manipulated and changed as your business needs change AND it can be changed in Browse mode without opening up your calculation each time.

Edited April 15, 2009 by Guest
Added Case example

[comment]

I have one field called "Category code" in which from zero to seven letters (a,b,c,d,e,f,g) appear

How are these letters separated - if at all?

[jockm]

Lets say there are seven codes: a,b,c,d,e,f,and g. Zero to 7 of these letter codes can appear in the "category code" field.

For instance, lets say "bcg" is there. That corresponds to "EU, 2009, and Send Flyer".

So I want bcg replaced with "EU, 2009, Send Flyer".

I dont want to do this manually to 1800 records!

I want it calculated, either in the old "Category Code" field, or some new field that is a calculated field.

Moreover, once the calculations are made, I need to be able to modify that Category field, if at all possible.

Is this actually possible with Filemaker Pro 7?

[jockm]

Maybe it will help understand the problem by saying that in the original application the seven codes were actually seven checkboxes and they had English language labels assigned to them. In importing the database into Filemaker pro, the checkbox labels were replaced with the a...g labels that the original application used to refer to its seven checkboxes.

Now that the data is in Filemaker Pro I need to do searches referring to the original English label words, so I want them back!

[comment]

Could you answer my question? You seem to be saying that there are no separators between these letters, but it would be nice to have an explicit answer.

[jockm]

Correct, there is no separation between the letter codes.

[comment]

Try this:

Step 1:

Define a calculation field (result is Text) =

Substitute ( Category code & ¶ ; 

[ "a" ; "§1¶" ] ;

[ "b" ; "§2¶" ] ;

[ "c" ; "§3¶" ] ;

[ "d" ; "§4¶" ] ;

[ "e" ; "§5¶" ] ;

[ "f" ; "§6¶" ] ;

[ "g" ; "§7¶" ] ;



[ "§1" ; "Alpha" ] ;

[ "§2" ; "Bravo" ] ;

[ "§3" ; "Charlie" ] ;

[ "§4" ; "Delta" ] ;

[ "§5" ; "Echo" ] ;

[ "§6" ; "Foxtrot" ] ;

[ "§7" ; "Golf" ] ;

[ "¶¶" ; "" ]

)

Use your real labels instead of "Alpha", "Bravo", etc.

Step 2:

Define a value list using custom values (your labels).

Step 3:

Place the calculation field on the layout and format it to display as checkbox set, using the value list defined earlier.

Step 4:

Change the calculation field to a Text field.

Edited April 15, 2009 by Guest

[LaRetta]

I dont want to do this manually to 1800 records!

I never said you'd need to! See attached. You create the 7 codes - that's all. Then when you ever need to change the wording on the codes or add new codes, do so in the codes table.

I still would prefer using another table instead of hard-coding it in a calculation, Comment. I know it means another calculation overall (because the codes are together) but it is more flexible without requiring that a Developer open field definitions to manipulate it.

The attached will allow up to 15 codes in the cSplitCode field. If you add more codes than that then you'll have to modify the calculation. But the wording on the codes is completely adjustable in browse mode.

Codes.zip

[jockm]

Thanks,I will try to figure out how to do this. But can you tell me: how do I type in these unicode symbols that you show surrounding the 1 on my Macintosh keyboard? One of them is a carriage return, isnt it? And that will end the line if I type it. So confused.

§1¶

[comment]

I still would prefer using another table instead of hard-coding it in a calculation, Comment. I know it means another calculation overall

No, actually it would be even simpler.

Codes2.fp7.zip

[comment]

Why don't you copy and paste?

[LaRetta]

No, actually it would be even simpler.

Indeed! I kept thinking there was a shorter way using a value list (which wouldn't require the extra calc) but I couldn't quite nail it. :smile2:

[LaRetta]

But wait ... if the display of the result still needs to be a single line, the second calculation WOULD be required for display as: EU, 2009, Send Flyer. Your cSplit calc (I don't believe) would be any faster than the concatenation of mine ... just personal opinion of course. :wink2:

Edited April 15, 2009 by Guest
Added sentence

[jockm]

Great! It is *almost* working.

In the example I first cited, to simplify it, I used a,b,c,d,e,f, and g as my category codes.

Actually the codes are somewhat more complex. They happen to be g,i,N,R, f, and A.

And i occurs by itself sometimes and sometimes preceded by g: ("gi")

Therefore I wrote the blow calculation code, which works fine if "i" is by itself, but it does not work for the combination "gi".

What do I need to do now to get it to work for different combinations of these letter codes?

Substitute ( Category Code & ¶ ;

[ "i" ; "§1¶" ] ;

[ "N" ; "§2¶" ] ;

[ "A" ; "§3¶" ] ;

[ "f" ; "§4¶" ] ;

[ "R" ; "§5¶" ] ;

[ "gi" ; "§6¶" ] ;

[ "§1" ; "Send Brochure" ] ;

[ "§2" ; "Europe" ] ;

[ "§3" ; "On KK list" ] ;

[ "§4" ; "Facilitator" ] ;

[ "§5" ; "Vipassana" ] ;

[ "§6" ; "Send Brochure" ] ;

[ "¶¶" ; "" ]

)

My value list has only five items: Send Brochure, Europe, On KK list, Facilitator and Vipassana. However, even if I add a duplicate "Send Brochure" as the sixth item, this does not help, furthermore it puts up a duplicate check box.

[comment]

if the display of the result still needs to be a single line

I don't see such need. IIUC, this is a one-time conversion to Filemaker - and the "Filemaker way" to assign multiple categories to a record is either a checkbox field or a related table.

[comment]

The order of substitutions matters: do "gi" before you do "g" or "i".

[LaRetta]

I was going by the OPs requirement when they said, "So I want bcg replaced with "EU, 2009, Send Flyer". "

I agree that this type of translation is not preferred. My point had to do more with hard-coding the descriptions instead of using another table anyway. :wink2:

[jockm]

Brilliant! Its working! AND MANY THANKS FOR YOUR HELP. Made my day.

jockm

[comment]

I was going by the OPs requirement when they said, "So I want bcg replaced with "EU, 2009, Send Flyer". "

I didn't take that literally.

My point had to do more with hard-coding the descriptions instead of using another table anyway.

I don't think hard-coding the values for a one-time conversion is a bad thing. The real question is do you use (from now on) a table of values or a custom value list - and I cannot say that one is necessarily better than other.