Case Statements

[pubdss]

Ok,

Everyone has already helped me in the past with a Logical function but this one is more in-depth. I've been looking on the forum for a similar example but out of luck. Hopefully someone will be kind enough to spread their FMP knowledge on me again. I'm having a hard time grasping the formulas. I got a simple If() statement going, but thats about it. I need to search for some resources. Here's what I have:

Estimate 1: $$$$$

Estimate 2: $$$$$$

Estimate 3: $$$$$$$

Selected Estimate: Pop up value list window with "estimate 1", "estimate 2", "estimate 3"

Estimate Cost: Trying to pull data from estimates field corresponding to selected estimate.

To give you a better idea of what I thought it would be (i.e. how I don't understand it) I tried this for a calculation formula: Case(Selected Print Estimate = Estimate 1, Estimate 1, Selected Print Estimate = Estimate 2, Estimate 2, Selected Print Estimate = Estimate 3, Estimate 3, "")

Thanks in advance.

Greg

[Peter Fenner]

Hi Greg

The Case statement you have is correct.

You don't need the "" (ie: return nothing) at the end.

Pete

[pubdss]

Pete,

Thanks for the quick response. When I try that formula, it doesn't fill in the Estimate Cost with the figure from the selected estimate. I deleted the "" at the end.

Greg

[Lee Smith]

Here is a sample that may help you.

I think you need to use "" around the the first use of the text.

HTH

Lee

PrintEstimate.fp5.zip

[pubdss]

Hi Lee,

It's still being stubborn. Its strange, because it automatically enters in data from the second result and not the others. If I delete the Selected Print Estimate, it reverts to "0" as expected. But Only the Estimate 1 (which is the 2nd test/result) show up.

Case(Selected Print Estimate = "", 0, "Estimate 1", Estimate 1, "Estimate 2", Estimate 2, "Estimate 3", Estimate 3)

I've attached my file if it helps. Its for school project I'm working on.

Greg

EstimatingDB.fp5.zip

[Lee Smith]

oops,

I think I need a second opinion here. First, I left out the Selectted Print Estimate = in the calculation, so it should have read:

Case(

Selected Print Estimate = "Estimate 1", Estimate 1,

Selected Print Estimate = "Estimate 2", Estimate 2,

Selected Print Estimate ="Estimate 3", Estimate 3)

However, when I make the change to your file, it doesn't work, see my little file again.

Lee

PrintEstimate.fp5.zip

[Peter Fenner]

Hi Greg

Sorry, I thought you were entering the number value in Selected Print Estimate. Lee's answer above is correct.

Pete

[Fitch]

Choose(TextToNum(Selected Print Estimate), "", Estimate 1, Estimate 2, Estimate 3)

[Lee Smith]

Hi Tom,

I always forget about the Choose statement, probably because I don't know how to write one.

I was beginning to think that Greg's file was becoming corrupted, as the Case Statement I wrote worked in my little demo (2nd one), but I couldn't get it to work in Greg's file that he posted. However, when I paste your Choose Statement in, it worked great.

What I'm I missing here?

TIA

Lee

[Ugo DI LUCA]

Lee,

That is 10 minutes now that I'm looking for this bug as I switched to Choose and it worked (well not the way Tom did it, but Choose was working while Case wasn't).

I'm sure the file is corrupted or I may be really tired this evening. Your Case is fine in my opinion. At least, it looks like mine.

I'm dying to have answer.

[Lee Smith]

Hi Ugo,

Thanks for letting me know that I'm not going crazy and need to make arrangements with the local health department - YET!

Greg,

I think you need to be careful with your file. It could be in an early stage of becoming corrupt, which may or may not have something to do with the fact that it was missing the other files that it is related to, thereby causing it to faultier because of it?? What make a file become corrupt is a fuzzy area, and really no way to pin point it, but beware.

Lee

[Ugo DI LUCA]

Lee and others,

No problem with any solution above...

No corruption....

The Estimate field was number field !!!

[Lee Smith]

Huh??

[Ugo DI LUCA]

Lee,

>>Huh???

I meant the "selected print estimate", so we could also use Choose(selected print estimate, "", estimate 1, estimate 2, estimate 3)

But you should change it for a text field...

[pubdss]

Woohoo,

Choose(TextToNum(Selected Print Estimate), "", Estimate 1, Estimate 2, Estimate 3) worked.

I want to thank all of you for your help. I learned more about case solutions and choose solutions.

Greg

[Lee Smith]

Hi Ugo and Greg,

My [color:"blue"] Huh was for:

I'm not convenced that there isn't corruption hanging around the corner, otherwise why didn't the Case Statement work in his file, but did in mine?

[color:"red"] Update, I changed it to my case statement, and closed the file. I just had to restart my machine, and when I opened the file, guess what

[color:"green"] IT WORKS! More concern for you Greg, if you are having a problem with the Case Statement.

[color:"blue"] Huh continued

Where did the new field come from?

Why change the field from number to text. Wouldn't the Choose statement work either way? I was getting ready to try it when I had to restart my Mac and discovered that the Case was now working. I guess someone better knowledge of the Choose Statement can fill us in.

Lee

[Ugo DI LUCA]

Lee,

Your statement (and mine also) was :

Case(

Selected Print Estimate = "Estimate 1", Estimate 1,

Selected Print Estimate = "Estimate 2", Estimate 2,

Selected Print Estimate ="Estimate 3", Estimate 3)

But Selected Print Estimate was formated as number, thus entering " " wasn't working, while if you had entered Estimate 1 (without the quotes) it should have worked at first time....

The Choose was working either with TextToNum or without because the Selected Print Estimate is a Num field.

There is absolutely no corruption in this file, only some illogism about setting the Selected Print Estimate as a number field while its value is text.

Sorry if I wasn't clear enough. It's just that Greg wanted to play with us and our nerves, an it appears he surely did as your red, blue and green fonts show your actual feelings about this bug/not bug/not logic/working or not/num or text Case or Choose....

[Lee Smith]

No Ugo,

The file I received had the field as Text (I think), but it doesn't matter, as I changed it back and forth after I started investigating when Tom submitted the Choose Statement and he had included the TTN in it. In fact, The Case Statement only started my Mac Restart.

Therefore, I stand by the statement there might be (possible) corruption (or, my OS, and/or FileMaker could have cause it).

Lee

[Peter Fenner]

The "Selected Print estimate" in the version I downloaded was a number field. I find no corruption - Ugo is right on the nail.

Pete

[Ugo DI LUCA]

Positive/Negative energy I believe.

Everytime I'm upset, my computer does....

Well, could be some kind of corruption then.

[Fitch]

How to use a Choose:

Anytime you have a logical test of some kind that returns one of a series of numbers, especially 1,2,3 etc., is a good candidate for Choose.

It's a nice shorthand for a Case statement because there's only one test (instead of test = 1, result 1, test = 2, result 2, etc.).

The only quirk is that the first returned result is based on test=0, rather than test=1, which is why the first result in the Choose statement above is a pair of empty quotes.

Now try saying "test equals" 3 times fast.

[Lee Smith]

Hi Tom,

Thanks for the information. Now I just need to be able to recognize the opportunity when it presents itself.

Thanks Again,

Lee