Nested IF's and CASE's

[Kurt Hansen]

I have a calculated field that is based on the following. Now I want to add this:
 

        If ( LABEL = "Eloquence" ;
                Case (
                    ANTAL I ALBUM = 1 ; "79,50" ;
                    ANTAL I ALBUM = 2 ; "149,50" ;
                    ANTAL I ALBUM = 3 ; "219,50" ;
                    ANTAL I ALBUM = 4 ; "219,50" ;
                "Mangler kalk");

Where and how do I put it in? I have tried almost everything, but I keep getting error messages.

--- Current definition ---

If ( GENRE = "Klassisk" or GENRE = "Jul" or GENRE = "Jazz" or GENRE = "Pop / Rock" or GENRE = "Soundtrack" or GENRE = "Traditional" or GENRE = "Tale" or GENRE = "Film";
        If ( LABEL = "Brilliant" ;
                Case (
                NORMALPRIS = 34 ; "49,50" ;
                VARENUMMER = 94137 ; "169,50" ;
               "Mangler kalk");
        Case (
        Left ( VARENUMMER  ; 5  ) = "GRAM," ; "70,00" ;
              VARENUMMER =  "CCS BOX 6414"; "349,50" ;
              VARENUMMER =  "AV 2313"; "799,50" ;
        NORMALPRIS = 11 ; "29,50" ;
        "Faktor?")
    ) ;
"---" )

[comment]

You are making a mistake here. Prices should be looked up, not calculated.

To do it the way you have now, you need to use Case() instead of If(). I guess something like:

If ( 
GENRE = "Klassisk" or GENRE = "Jul" or GENRE = "Jazz" or GENRE = "Pop / Rock" or GENRE = "Soundtrack" or GENRE = "Traditional" or GENRE = "Tale" or GENRE = "Film";
	Case ( 
		LABEL = "Brilliant" ;
			Case (
				NORMALPRIS = 34 ; "49,50" ;
				VARENUMMER = 94137 ; "169,50" ;
				"Mangler kalk"
				) ;			
		LABEL = "Eloquence" ;
			Case (
				ANTAL I ALBUM = 1 ; "79,50" ;
				ANTAL I ALBUM = 2 ; "149,50" ;
				ANTAL I ALBUM = 3 ; "219,50" ;
				ANTAL I ALBUM = 4 ; "219,50" ;
				"Mangler kalk"
				) ;
		Case (
			Left ( VARENUMMER  ; 5  ) = "GRAM," ; "70,00" ;
			VARENUMMER =  "CCS BOX 6414"; "349,50" ;
			VARENUMMER =  "AV 2313"; "799,50" ;
			NORMALPRIS = 11 ; "29,50" ;
			"Faktor?"
			) 
		) 
;

	"---" 
) 

But this is a dead end street. Tomorrow you will change your prices and it will ruin your existing records.

Edited July 26, 2016 by comment

[Kurt Hansen]

Thanks, @comment. I works so far, but how do I change "calculation" into "lookup"? Please see the attached screen dump.

On 7/26/2016 at 1:56 AM, comment said:

You are making a mistake here. Prices should be looked up, not calculated.

To do it the way you have now, you need to use Case() instead of If(). I guess something like:

If ( 
GENRE = "Klassisk" or GENRE = "Jul" or GENRE = "Jazz" or GENRE = "Pop / Rock" or GENRE = "Soundtrack" or GENRE = "Traditional" or GENRE = "Tale" or GENRE = "Film";
	Case ( 
		LABEL = "Brilliant" ;
			Case (
				NORMALPRIS = 34 ; "49,50" ;
				VARENUMMER = 94137 ; "169,50" ;
				"Mangler kalk"
				) ;			
		LABEL = "Eloquence" ;
			Case (
				ANTAL I ALBUM = 1 ; "79,50" ;
				ANTAL I ALBUM = 2 ; "149,50" ;
				ANTAL I ALBUM = 3 ; "219,50" ;
				ANTAL I ALBUM = 4 ; "219,50" ;
				"Mangler kalk"
				) ;
		Case (
			Left ( VARENUMMER  ; 5  ) = "GRAM," ; "70,00" ;
			VARENUMMER =  "CCS BOX 6414"; "349,50" ;
			VARENUMMER =  "AV 2313"; "799,50" ;
			NORMALPRIS = 11 ; "29,50" ;
			"Faktor?"
			) 
		) 
;

	"---" 
) 

But this is a dead end street. Tomorrow you will change your prices and it will ruin your existing records.

The overall IF (Genre = "Klassisk") etc. is not relevant any more. To me it's obvious to delete line 1 and 2, but where does the IF statement stop - What do I delete / change at the end?

[comment]

12 minutes ago, Kurt Hansen said:

how do I change "calculation" into "lookup"?

It's hard to answer in detail without knowing the details. In general, you would have a table of Prices, where each individual price would be an individual record. And you would have a relationship to this table matching on all fields the influence the price.

Variations on the method are possible - for example, in some (rare) cases you could have several prices in the same record, and use an auto-entered calculated value to lookup the correct one.

I am afraid your screenshot does not tell me anything relevant in this context. I would however point out that numbered fields are almost always a structural flaw.

[Kurt Hansen]

Hmmm, Consultant.

FM is - ceteris paribus - a very small part of my overall work and therefore I have never had free resources to put me firmly into the more advanced use of FM.

I have everything collected in two tables. The first is our entire portfolio of products (with many fields); the other is a modification of the former, where I specify variations of how the data should be uploaded to the web shop database.

Edited July 29, 2016 by Kurt Hansen

[comment]

I am afraid you greatly overestimate my psychic powers. The only thing I understood from your description is that the price is not being calculated for the purposes of recording a sale (e.g. generating an invoice). So there is probably nothing wrong with it being a calculation.

However, this still holds true: the numerical values that make up a price are data; and data should be stored in records and fields, not hard-coded in a calculation formula.

[Kurt Hansen]

5 hours ago, Kurt Hansen said:

The overall IF (Genre = "Klassisk" etc.) is not relevant any more. To me it's obvious to delete line 1 and 2, but where does the IF statement stop - What do I delete / change at the end?

Is it possible to make at reply to this?

Edited July 29, 2016 by Kurt Hansen

[comment]

5 hours ago, Kurt Hansen said:

The overall IF (Genre = "Klassisk") etc. is not relevant any more. To me it's obvious to delete line 1 and 2, but where does the IF statement stop - What do I delete / change at the end?

I believe you need to remove the default result and the closing parenthesis - IOW, the last 3 lines.

[Kurt Hansen]

Yap, @comment. It works.

I have repeatedly benefited from your patient help and I am very grateful. Thank you very much :-)