Jump to content
Sign in to follow this  
pehrlabel

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!

- adam

Share this post


Link to post
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 this post


Link to post
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.

thanks again, adam

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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. :laugh2:

LaRetta

Edited by Guest

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.