# case calculation with weird exceptions?

## Recommended Posts

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!

##### Share on other sites

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?

##### Share on other sites

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.

##### Share on other sites

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:

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

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 by Guest

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...