case calculation with weird exceptions?

[pehrlabel]

help! I import tons of records each month into my filemaker, and I’m assigning them percentages by hand, clicking the radio buttons for hundreds of records a month. i have to stop this madness. I need to make a percentage calculation field whereby:

Field A is a text field with numbers and letters. if Field A's 8th digit is 0, then my calc field = .5

if the 8th digit is NOT 0, then my calc field = .85

except for 2 exceptions:

1. if the value in this field is 676347700721, or US4G706007** then this field = .33

2. if the value in this field is 676347799220, or US4G705992**, then this field = 1

Is there a case calculation that can do all this?

thank you so much if you can think of something!

- adam

[comment]

It doesn't seem too hard, but you need to clarify:

1. What do the asterisks in "US4G706007**" mean? Is it the actual content, is it a wildcard?

2. Do you mean "8th digit" literally, as in 'skip any non-numeric characters'? Or is it just the 8th character?

[pehrlabel]

Thanks Comment!

The asterisks are wildcards.

And I meant the 8th character, sorry. Because the codes in those fields, sometimes they are letters, sometimes numbers. Digit was the wrong term.

thanks again, adam

[LaRetta]

This appears to work:

Case (

Field A = 676347700721 or Left ( Field A ; 10 ) = "US4G706007" ; .33 ;

Field A = 676347799220 or Left ( Field A ; 10 ) = "US4G705992" ; 1 ;

Middle ( Field A ; 8 ; 1 ) = 0 ; .5 ;

Middle ( Field A ; 8 ; 1 ) ≠ 0 ; .85

)

Did I catch all the rules?

LaRetta :wink2:

[LaRetta]

Ummmm, the US4G706007** ...

A wildcard would be indicated with ONE asterisk, indicating 'no matter how many following characters.' Does it matter how many characters are after the first ten? If so, my calc might break. If the total character count is set, ie, MUST be 12, it should be indicated as US4G706007## and the entire length would also need to be verified to be 12 (I think).

But then again, I can overthink on occasion. Check that syntax carefully.

L

[pehrlabel]

LaRetta you are a genius. You are going to laugh but you have saved me HOURS of work! HOURS. And now from your syntax I can try it myself if any new exceptions come up. The more of this table I can automate, the more new business I can do without worrying about having to do the accounting by hand, so let me say you are awesome.

Oh I didn't know that about wildcards, how one * goes on forever to the right, whereas the # is specific for one character only, thanks.

So I just visually compared a few thousand records, and the calculation matched what I had done by hand perfectly.

Wow.

By the way if I can continue to gush, I was reading some of your other posts about dates in filemaker, and those were really helpful.

[LaRetta]

And now from your syntax I can try it myself if any new exceptions come up.

Just remember that, since vs. 7, evaluations short circuit (stop when they hit the first true). So put your exceptions at the beginning. If the 8th character test were first, two of your exceptions could be evaluated and stop on .5. Also, the [color:blue]Middle ( Field A ; 8 ; 1 ) :notequal: 0 ; .85 isn't needed at all because it would be the default result in which all remaining numbers would fall. You could change it to simply .85.

Never a woman has been born that doesn't appreciate a gush. Fact.

LaRetta

Edited July 22, 2007 by Guest