Having trouble with substitute function

[Lougee]

I'm close. I've almost got it. But there's still one glitch I haven't figured out. I'd be forever grateful to someone for clueing me in on what I'm doing wrong.

I'm tracking students' ethnicity. There are 10 codes — AI/AN (American Indian/Alaskan Native), AS (Asian American), B (African American American/Black), HA (Hispanic), W (White) and several more but these are enough to illustrate my problem. There are many students with mixed ethnicity, i.e. Hispanic and Black. I track them as M (Multiple) and have a field called EthnicCodeDetails where I list the codes for their specific backgrounds. This student would be listed as ethnicity M and the EthnicCodeDetails field would show HA, B.

Returning students' ethnic code is pre-printed on the program application using the EthnicCodeDetails field. I want to decode this to plain English so I've created a calc field called EthnicCodeDetails_SHOW. The calculation is:

If ( not isEmpty ( ethnicCodeDetails );

Substitute ( ethnicCodeDetails ; [ "I" ; "Asian Indian"] ; ["AI/AN" ; "American Indian/Alaskan Native" ] ; [ "B" ; "African American/Black"] ; [ "AS" ; "Asian American/Asian"] ; ["HA" ; "Hispanic"] ; [ "PI" ; "Pacific Islander" ] ; [ "W"; "White"]; ["O" ; "Other"]))

and it almost works. It breaks down when there's another word in the details field that begins with any of the specific codes. Take for example, a student whose ethnic details are AI/AN, W for American Indian/Alaskan Native, White. The 'show' calculation returns AAsian Indian/AN, White. The I in AI/AN is treated like another, separate code. Changing the order of the codes only changes which letters cause the glitch.

I guess I'm looking for a way to stop the codes themselves from causing the calculation to reevaluate midstream.

I hope I've explained myself clearly enough. If not, let me know.

I'm using FMP Advanced 9.0v3 on a Mac running OS 10.5.7.

Abbe Lougee

[comment]

I'd suggest you use a checkbox for the codes - it's both convenient and predictable (you can be certain that the codes will be separated by carriage returns). Then you could do something like:

Substitute ( "§¶" & ethnicCodeDetails & "¶§" ; 

[ "¶I¶" ; "¶Asian Indian¶"] ; 

[ "¶AI/AN¶" ; "¶American Indian/Alaskan Native¶" ] ; 

[ "¶B¶" ; "¶African American/Black¶"] ; 

[ "¶AS¶" ; "¶Asian American/Asian¶"] ; 

["¶HA¶" ; "¶Hispanic¶"] ; 

[ "¶PI¶" ; "¶Pacific Islander¶" ] ; 

[ "¶W¶"; "¶White¶"] ; 

[ "¶O¶" ; "¶Other¶"] ;

[ "§¶" ; "" ] ; 

[ "¶§" ; "" ] ; 

[ ¶ ; ", " ] 

)

[Lougee]

Thank you for the suggestion, but as it is, it's still not enough. I thought I'd included all the pertinent information, but I see I did leave out important elements. Using checkboxes for the standard ethnic codes would work most of the time, but I'm trying to capture very diverse information.

The EthnicCodeDetails_SHOW field is preprinted on the application of returning students. This so the student and/or parent can verify that we have included all the ethnic background information that THEY consider important. It often includes information such as "Canadian indian" or "Registered tribe member" or "Turkish." For example, one student's ethnicCode is "O" for 'other' and the details field says "Brazilian, Russian." My current calc evaluates the initial B in Brazilian as the ethnic code "B" and returns "African American/Blackrazilian, Russian." You get the idea.

The calc does what I want but does it TOO well. I need to have it not calculate the first letter of a word in the details field that also is an ethnic code itself, e.g., the "B" in Brazilian.

I hope this makes things clearer.

Abbe 8-)

Edited June 19, 2009 by Guest
Original post was not complete and in logical order.

[IdealData]

It seems you have extra conditions to evaluate, so use another IF construct in the middle of your calculation where the extra conditions apply.

Incidentally, you may find it easier to read your calculation if you split each line (like comment did) where each condition applies.

Edited June 19, 2009 by Guest

[Lee Smith]

Might be helpful to us and you for you to post a clone of your file.

Lee

[Lougee]

Oops...I always put each condition on a separate line when constructing my calculations but I rewrote, didn't copy/paste, my calc into my post. Thanks for the reminder.

I'm off to play with my calculation again using your additional 'if statement' suggestion. I'll be back with the results.

Abbe

[Lougee]

Thanks, Lee.

Definitely worth the time to create a clone of the problem if it will help someone help me.

I'm going to play around with IdealData's additional 'if condition' first. If (no pun intended) I can't make that work, I'll create a small clone of my problem and upload it.

[Fitch]

The solution comment provided should work if you add a substitution at the beginning for [",";"¶"].

If "details" is a separate field I don't see how this is part of the problem.

[Fitch]

And anyway it sounds like you're entering the data based on a printed form, so YOU control the way the data is entered.

[comment]

My calculation does exactly what your calculation does - the only difference is that it relies on a predictable input, i.e. that every code is separated by a known character. Without this you cannot control the result, because the "B" as a code is exactly the same "B" as the one in "Brazilian". It doesn't matter what method you use to indicate the codes as such, as long as it's consistent.

I'm afraid I didn't really understand your clarification. It sounds like there are - or should be - two fields to enter: one for the codes, one for additional information. The calculation could append the additional information to the results of the code substitution, if that's what's needed - but now I am merely guessing.

[Lougee]

I've fixed it. Well, actually I realized there's a very simple work around that is not going to create a bigger problem later on.

I added a space before and after the ethnicCode. Used find/replace to change current codes to the revised codes and changed the ethnicCode definitions in all affected calculations. Slick and quick. I only had to manually change about 50 out of 10,000 records.

Works like a charm and gives me exactly what I want on the printed applications.

Thanks to all for your help and I have no doubt I'll be back -- sooner rather than later, I'm afraid -- for more expert advise.