Case Function not working 100%

[lamBRETTa]

Greetings and salutations -

I built a large Case statement to calculate my total task cost. The problem is that if 'Task Unit' is not blank and the Round Priority equals either 'Priority24' or 'Priority72' the result is not calculated. Any clues? All other parts of the statement will calculate correctly. I thought it might have something to do with having a number at the end of the priority, but the calculation will work if it using hours instead of units to calculate. Also, it does seem to matter what order the statment is in. If i move all the task unit * task cost lines to the top then i lose all my other calculations. I was under the impression that if you covered every scenario you were ok, but apparently not. Any insight? Here is the statement. Thanks in advance for your help.

-Brett

Case ( jobs::Job status = "Internal" ; "0" ;

round priority = "Standard" and task unit = "" ; task hours for calculating * task cost ;

round priority = "Weekend" and task unit = "" and task cost = "225" ; task hours for calculating * task cost * 1.77777777777777778 ;

round priority = "Weekend" and task unit = "" and task cost = "140" ; task hours for calculating * task cost * 1.7857142857142 ;

round priority = "Weekend" and task unit = "" and task cost = "295" ; task hours for calculating * task cost * 1.8644 ;

round priority = "Weekend" and task unit = "" ; task hours for calculating * task cost * 2 ;

round priority = "Holiday" and task unit = "" and task cost = "225" ; task hours for calculating * task cost * 1.77777777777777778 ;

round priority = "Holiday" and task unit = "" and task cost = "140" ; task hours for calculating * task cost * 1.7857142857142 ;

round priority = "Holiday" and task unit = "" and task cost = "295" ; task hours for calculating * task cost * 1.8644 ;

round priority = "Holiday" and task unit = "" ; task hours for calculating * task cost * 2 ;

round priority = "Same day" and task unit = "" ; task hours for calculating * task cost * 1.5 ;

round priority = "Priority" and task unit = "" ; task hours for calculating * task cost * 1.25 ;

round priority = "Mercer-rush" and task unit = "" ; task hours for calculating * task cost * 1.5 ;

round priority = "Mercer-same day/Weekend" and task unit = "" ; task hours for calculating * task cost * 2 ;

round priority = "Priority24" and task unit = "" ; task hours for calculating * task cost * 1.25 ;

round priority = "Priority72" and task unit = "" ; task hours for calculating * task cost * 1.5 ;

round priority = "Same-day" and task unit = "" ; task hours for calculating * task cost * 2 ;

round priority = "Standard" and task unit ≠ "" ; task unit * task cost ;

round priority = "Weekend" and task unit ≠ "" ; task unit * task cost ;

round priority = "Holiday" and task unit ≠ "" ; task unit * task cost;

round priority = "After-Hours" and task unit ≠ "" ; task unit * task cost ;

round priority = "Same day" and task unit ≠ "" ; task unit * task cost ;

round priority = "Priority" and task unit ≠ "" ; task unit * task cost ;

round priority = "Mercer-rush" and task unit ≠ "" ; task unit * task cost ;

round priority = "Mercer-same day" and task unit ≠ "" ; task unit * task cost ;

round priority = "Priority24" and task unit ≠ "" ; task unit * task cost ;

round priority = "Priority72" and task unit ≠ "" ; task unit * task cost ;

round priority = "Same-day" and task unit ≠ "" ; task unit * task cost)

[Reed]

in a case statement, as soon as a true statement is found, the result is returned, and the rest of the function is not evaluated.

[-Queue-]

Well, cleaning it up a bit gives

If( jobs::Job status = "Internal"; 0;

If( IsEmpty(task unit);

task hours for calculating * task cost *

Case(

round priority = "Standard"; 1;

round priority = "Weekend" or round priority = "Holiday";

Case(

task cost = 225; 1.77777777777777778;

task cost = 140; 1.7857142857142;

task cost = 295; 1.8644;

2 );

round priority = "Same day" or round priority = "Mercer-rush" or round priority = "Priority72"; 1.5;

round priority = "Priority" or round priority = "Priority24"; 1.25;

round priority = "Mercer-same day/Weekend" or round priority = "Same-day"; 2

);

round priority = "Standard" or round priority = "Weekend" or round priority = "Holiday" or round priority = "After-Hours" or round priority = "Same day"; or round priority = "Priority" or round priority = "Mercer-rush"; or round priority = "Mercer-same day" or round priority = "Priority24" or round priority = "Priority72" or round priority = "Same-day"; task unit * task cost

)

)

If the last test covers all possibilities of round priority when task unit is not empty, then you can replace that entire section with

task unit * task cost

Otherwise, if only one or a few possibilities remains, you can use, for example,

round priority ≠ "This Value" and round priority ≠ "That Value"; task unit * task cost

Note that if all possibilities are not covered in the last section, then no result will be returned since there is no default specified.

Anyway, hopefully this will make the problem more apparent to you, as well as speed up the calculation.

[lamBRETTa]

Thanks. I will definitely try that.

Yeah, i know my original was kind of long winded, but hey! I just dicovered the whole Case thing, so not bad for a beginner. Still trying to figure out some elements of your clean up. Looks much nicer though.

Ok, i get it. But why would this work and not my original? Isn't it a condensed version of mine? I really want to know why mine didn't work. Once i understand the problem it is easier to come up with a solution. I will plug it in and see what happens.

Thanks again.

Edited November 8, 2005 by Guest

[-Queue-]

It is a condensed and reorganized version. I am not sure why yours did not work, but I could not determine much by looking at it in that format. It may have involved using ≠ "" instead of IsEmpty, but I do not know for sure. Perhaps if you posted a sample file using the original calculation, with an instance of it failing, I could be of more help.

[lamBRETTa]

Well, all plugged in and working...sort of. It is missing certain instances of the hours * task cost for some reason and not picking up any of the units * task cost instances. Back to the drawing board! Sorry, i can not post a file, this stuff is very confidential. After comparing the two solutions, mine is basically the long drawn out version. I made it that way becasue i knew that Case statements now stop when they reach a True element, so i covered all the bases. I like the way you add case statements inside of case statements. I'd like it better if it worked though! Thanks for the tips, all was not in vain. You learn something new every day.

[-Queue-]

Is it possible that round priority contains more data (such as trailing spaces) than is explicitly tested within the calculation?

A posted sample need not contain any data. We can create necessary sample data. If file structure is an issue, you can delete all fields and relationships, except those necessary for the calculation field's definition, and delete all unneeded scripts.

If posting is still not possible, give me a few examples of where it fails, using just the values for each referenced variable. For example,

jobs::Job status = "External"

task unit = 3

task hours for calculating = 2

task cost = 140

round priority = "Standard"

You may find the answer for yourself while investigating the variables' values also.

[lamBRETTa]

Here it is!

I have made a file that illustrates every possible combination of the problematic case statement. The first 'task cost total' is my original case statement. The one next to it is the one you sent me. I left a lot of extra fields etc...so just ignore all that. I hope this helps to illustrate my problem. Thanks again for your tips. Good luck!

*no password is needed, just log in as admin and leave password blank.

ForTesting.zip

[-Queue-]

Ok, first of all, in the testing calc you need to move one of the closing parentheses from the end to be in between the 2 and the "; task unit * task cost" statement at the end, so that it will be similar to my calculation posted above. Secondly, and I assume this is due to something involving FM's internal decimal storage, adding quotes around 1.77777777777777778 seems to fix the other issues.

Note also that if you remove one 7 in the number instead of adding quotes, it works fine, as does using 17.7777777777777778 * .1 in its place. So that one extra decimal definitely appears to be an issue. Why does adding quotes and forcing it to be read as text work then? I have no idea.

I assume the quote solution would also help resolve the original calculation's problems, but I did not test it.

[lamBRETTa]

Strangely enough, putting quotes around all numbers that are used as multipliers seems to fix the entire problem. The only one that is NOT working is the Mercer-same day/weekend when it is units and not hours. Very weird. Hey, that's Filemaker for ya!

Thanks for all the help. I had a feeling it was something strange and simple. I just wish i understood why.

**no wonder the mercer one doesn't calculate, it isn't included in the initial case! Go figure...

Edited November 9, 2005 by Guest

[comment]

Huh? Can you isolate this issue? I have no idea where to look and at what (could it be a Pentium thing?)

[-Queue-]

See attached. There are simple Case statements, whose results are multiplied by 1 to force a zero to be returned when their values would otherwise be blank (and unexpectedly so).

I have attached a shot of my results, in case it is a Windows issue.

WhatsTheDeal.zip

WhatsTheDeal.PDF

[comment]

Mhmm... most interesting. It's a bug, of course, and I see it too (v.7 AND v.8).

Nowhere can I find a documented limit on numeric constants in a calculation (it works OK if you reference the same value from a field). Even so, the delayed effect is most peculiar.

I tried Evaluate() and SetPrecision(), but that doesn't help.

[SlimJim]

There is a similar effect if you replace the Case by an If. For example

If(var = 1; 1.11111111111111111;2)

returns blank unless var = 1 whereas

If(var = 1; "1.11111111111111111";2)

seems to work as expected.

Until FMI fix this we are all left in the air - do we have any Case or If statements tucked away somewhere which are being affected by this?

Workarounds are only of help if we know precisely under what circumstances we have to do one - e.g "if you have a constant with 17 or more decimal places put it in quote marks." As comment says removing the constant and replacing it by a variable or field does the trick

Let(num = 1.11111111111111111;

If(var = 1; num ;2))

seems to work. But we shouldn't have to do this should we.