Jump to content

case calculation with weird exceptions?


This topic is 6181 days old. Please don't post here. Open a new topic instead.

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

Link to comment
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?

Link to comment
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:

Link to comment
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

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

Link to comment
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
Link to comment
Share on other sites

This topic is 6181 days old. Please don't post here. Open a new topic instead.

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
×
×
  • Create New...

Important Information

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