How to do a multicase

[Heathbo]

How do you write a calculation with multiple case function. For example, if field A = 21 then run this group of case functions. If Field A = 25 then run another group of case functions.

[LaRetta]

Heathbo, you've been on Forums long enough to know we need more information.

The idea is:

If ( A = 21 ;

Case( test ; result ; test ; result etc.) ; result )

But if more than one test in the If(), you can't use if and should instead use Case() within Case(). You can nest the If's but it gets convoluted. And if it gets to that point, I would question what you're doing. So much depends upon what you need. But I simply can't write a calc without clear criteria and purpose. Cought it up and we'll help you!!

LaRetta

[Heathbo]

Your right LaRetta. I've been here long enough to know better. I just thought there might be an easy answer that I'm overlooking.

DETAILS:

I have multiple fields. Field 1 is titled "Secondary_Type", Field 2 is titled "Secondary_Equipment", Field 3 is titled "Display".

Basically what needs to happen is,

if "Secondary_Type" says energy and "Secondary_Equipment" says solar then I need "Display" to say renewable.

if "Secondary_Type" says energy and "Secondary_Equipment" says coal then I need "Display" to say nonrenewable.

if "Secondary_Type" says energy and "Secondary_Equipment" says nuclear then I need "Display" to say limited renewable.

if "Secondary_Type" says parts and "Secondary_Equipment" says line then I need "Display" to say standard

if "Secondary_Type" says parts and "Secondary_Equipment" says circuit then I need "Display" to say customizable

if "Secondary_Type" says parts and "Secondary_Equipment" says insulation then I need "Display" to say order-able.

I hope this clears some things up.

[LaRetta]

One final question and we'll be good to go. Answer please:

If Secondary_Type is empty but Secondary_Equipment is not? Possible? What should be result?

If Secondary_Type has value but Secondary_Equipment is empty? What should be result?

If both are empty? What should be result?

[Heathbo]

If both are empty nothing happens. If one is empty nothing happens.

[LaRetta]

Try this (result is text of course)

If ( not IsEmpty ( Secondary_Type ) and not IsEmpty ( Secondary_Equipment ) ; If ( Secondary_Type = "Energy" ; Case ( Secondary_Equipment = "solar" ; "renewable" ; Secondary_Equipment = "coal" ; "nonrenewable" ; Secondary_Equipment = "nuclear" ; "limited renewable" ) ;

[color:red]If ( Secondary_Type = "Parts" ; Case ( Secondary_Equipment = "line" ; "standard" ; Secondary_Equipment = "circuit" ; "customizable" ; Secondary_Equipment = "insulation" ; "order-able" ) ) ) [color:red])

This calc will only produce results on specific matching entries you've indicated. It's too bad a Let() couldn't make it easier.

Have I missed any? Let me know and we can tweak it. [color:red]Yep, make this adjustment in red!

UPDATE: I must add something here ... I keep looking at this thinking I only have two such calcs in my entire solution. And I don't like having ANY. It seems to me that this type of 'word conversion' might be handled better using a simple lookup table. Because if you change ANY of these words, you'll be changing the calculation. And I despise hardcoding, particularly when it's comparing text data to substitute with text data. Just an observation ...

L

[Heathbo]

You are awesome. It works perfectly. Thank you so much. It seems my misunderstanding wasn't that I didn't know the proper terms, I just didn't know the proper syntax to put the terms in.

This script has to be applied to many different fields. Do you know if there is a way to substitute "Secondary_Equipment" with a variable that would look up the name of the field this script is applied to?

[LaRetta]

Script? I thought this was calculation? It's in Define Fields!?

I have no idea what you're doing. Is it a one-time data conversion of some sort - switching one set of text for another? Possibly using 8 variables, but again - I have no clear vision of what you're attempting. And without a vision my mind won't work.

But Display should be fluid, ie, either Auto-Enter (Replace) text or calculation ... so it adjusts automatically as data in other fields change. Your request is too unclear for me to grok. Sorry.

[Heathbo]

No no your right it is a calculation. I just have to apply this calculation to many different fields. With the only difference being that the field name "Secondary_Equipment" changes with each field. I was just hoping there was an easy way to look up what the fields name was and auto insert the name into the place where Secondary_Equipment was. That way I wouldn't have to go in and retype it each time, I could just copy and paste.

[LaRetta]

I just have to apply this calculation to many different fields...that way I wouldn't have to retype it each time.

I asked if this was a one-time conversion. It doesn't sound like it if you say 'each time'. I would re-think your process. Something screams WONKY here, I'm afraid.

I must go to bed. Others will help you, I'm sure.

Night night

Wait ... if you're talking about simply retyping it in your calculation box, it's easy!! Go below your calculation in the calc box and select/insert your new field name. Copy it to clipboard. Then cut it. Double-click your Secondary_Equipment field (which will just select the field) then while it's still highlighted immediately CTRL-V to paste your new field - repeat with each Secondary_Equipment field. But I would seriously re-think your process here.

Now night night for sure!!

Edited November 14, 2005 by Guest

[comment]

If there is no default result, there's no need to check for not IsEmpty().

You can use Let() to avoid multiple replacements of the field name (also les error-prone)

Let ( [

type = Secondary_Type ;

equipment = Secondary_Equipment

] ;

Case (

type = "Energy" ;

Case (

equipment = "solar" ; "renewable" ;

equipment= "coal" ; "nonrenewable" ;

equipment = "nuclear" ; "limited renewable"

) ;

type = "Parts" ;

Case (

equipment = "line" ; "standard" ;

equipment = "circuit" ; "customizable" ;

equipment = "insulation" ; "order-able"

)

)

)

With FMP8A, this could also be turned into a custom function, with type and equipment as parameters.

[LaRetta]

Much clearer!

"this could also be turned into a custom function, with type and equipment as parameters. "

THIS ROCKS!!!! :laugh2:

[Heathbo]

comment, this is awesome. This is exactly what I've been looking for. Expanding on what you said earlier, what kind of custom function and parameters are you talking about. To be honest I'm not sure what your talking about. I just started using FMP8A, so I'm not familiar with all the new stuff yet.

[comment]

It's a rather broad question. I suggest you examine the attached file (go to Define Fields and Define Custom Functions), then read more in the help.

CFdemo.fp7.zip