Could Let function help this?

[skearton]

I admit I still don't have a grasp of the Let function, even after looking at some examples and explanations...

 

I usually stick to the Case function but now I'm encountering a messy calculation and wondering is someone could show me how they would translate it into a Let function or if that is the way to handle it?

 

This is the current calculation for my field  c_LineItemPrice

 

Case (

 

not IsEmpty ( OrderFeeClientPrice ) ; OrderFeeClientPrice ;

 

Quantity ≥ 1 and Quantity < STQtyBreak1 ; STpriceDefault ;

 

Quantity ≥ STQtyBreak1  and Quantity  <  STQtyBreak2 ; STpriceQtyBreak1 ;

 

Quantity ≥ STQtyBreak2  and Quantity < STQtyBreak3 ; STpriceQtyBreak2 ;

 

Quantity ≥ STQtyBreak3  and Quantity < STQtyBreak4 ; STpriceQtyBreak3 ;

 

Quantity ≥STQtyBreak4   ; STpriceQtyBreak4 ;

 

STpriceDefault )

 

This works great if there are values in the STQtyPriceBreak1, STQtyPriceBreak2...4 fields but if there is only one STpriceDefault, and no qty break prices given then the value is 0 if my Quantity is 2 or higher.

[Rick Whitelaw]

The Let function is a way of declaring variables for the life of a calculation. I consider that there are three types of variables: global, local and those declared by the Let function. It's a great shorthand. Using Let can shorten a complex calculation from a few paragraphs to a few lines, but imho that's all it is.

[doughemi]

Try

 

 

Case (

not IsEmpty ( OrderFeeClientPrice ) ; OrderFeeClientPrice ;//case0

Quantity=0;0;//case1

Quantity < STQtyBreak1 ; STpriceDefault ;//case2

Quantity  <  STQtyBreak2 ; STpriceQtyBreak1 ;//case3

Quantity < STQtyBreak3 ; STpriceQtyBreak2 ;//case4

Quantity < STQtyBreak4 ; STpriceQtyBreak3 ;//case5

STQtyBreak4 > 0   ; STpriceQtyBreak4 ;//case6

STpriceDefault )

 

The case statement evaluates conditions until it finds a true condition; everything after that is ignored.  That made your Quantity ≥ STQtyBreak{n} statements redundant until you got to the Quantity ≥ STQtyBreak4 one.   Since STQtyBreak4 is empty, then this statement is true, and the case statement returns an empty value, which you have probably set FM to display as 0.

 

Let's look at the Case statement in my example, and for convenience, let's assume that the break counts are 10x the label; i.e. STQtyBreak1 is 10, and so on.

Case0 is your contract price case.  

Case1 takes care of a zero quantity ordered (which might be considered redundant, but prevents the function from evaluating all the cases and finding them false.)

If your quantity is 33, then case1, case2, case3 and case4 are false, but case5 is true, so the function returns STpriceQtyBreak3.

If your quantity is any value but no quantity breaks are set, then the function drops past all the quantity breakpoints, and fails case 6 as well, so the default value is returned.

[comment]

Case (

not IsEmpty ( OrderFeeClientPrice ) ; OrderFeeClientPrice ;//case0

Quantity=0;0;//case1

Quantity < STQtyBreak1 ; STpriceDefault ;//case2

Quantity  <  STQtyBreak2 ; STpriceQtyBreak1 ;//case3

Quantity < STQtyBreak3 ; STpriceQtyBreak2 ;//case4

Quantity < STQtyBreak4 ; STpriceQtyBreak3 ;//case5

STQtyBreak4 > 0   ; STpriceQtyBreak4 ;//case6

STpriceDefault )

 

I believe it would be simpler to reverse the order, i.e. start by testing for:

 

Quantity ≥ STQtyBreak4

 

and move down until you hit the default price as the default result.

 

 

 

II'm encountering a messy calculation and wondering is someone could show me how they would translate it into a Let function or if that is the way to handle it?

 

I don't think that's the best way to handle it. You should have a Prices table, where each Price is a record, with fields for ProductID, FromQty and Price. Then lookup the applicable price from there using a relationship.

[IdealData]

The Let function can speed up calcs in some instances - particularly with any Get functions.

 

Say, you are using the Get(CurrentDate) as a comparator in a Case statement. If there were 10 instances of Get(CurrentDate) then (as I have read) each instance needs to be evaluated when each line in the Case statement is evaluated - therefore, possibly upto 10 evaluations of Get(CurrentDate).

 

With a Let function and declaring a variable ($currentdate = Get(CurrentDate)) and now use $currentdate as the comparator then only 1 evaluation is ever made.

[skearton]

Thank you all for taking time to explain! It works great!