Calculation Format

[Anuviel]

Is there a way to format the calculation in the calculation field when you write it? Like different colors and or visual alerts which could be useful in long calculations.

Also what are the most common things that you do when writing the calculation - new rows for each line or certain order and so?

Any tips would be appreciated - Here is an example of one of my long calculations:

Case (Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_I ; Settings::eBay_Insertion_Fees_Tier_I_Amount ;Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_II_A and Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_II_B ; Settings::eBay_Insertion_Fees_Tier_II_Amount ; Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_III_A and Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_III_B;Settings::eBay_Insertion_Fees_Tier_III_Amount ;Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_IV_A and Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_IV_B;Settings::eBay_Insertion_Fees_Tier_IV_Amount;Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_V_A and Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_V_B;Settings::eBay_Insertion_Fees_Tier_V_Amount;Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_VI_A and Ebay_Sales_Price ≤Settings::eBay_Insertion_Fees_Tier_VI_B;Settings::eBay_Insertion_Fees_Tier_VI_Amount;Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_VII;Settings::eBay_Insertion_Fees_Tier_VII_Amount; 0 )

In the above calculation is it advisable to hit enter after every ; section so it will be easier to read as it is now gives me a headache when I have to change or look it up.

Thank you.,

[comment]

No coloring is possible in the formula box.

I think your calc would be more readable (hence more manageable) if written this way:

Case (



Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_I ; 

Settings::eBay_Insertion_Fees_Tier_I_Amount ; 



Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_II_A 

and 

Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_II_B ; 

Settings::eBay_Insertion_Fees_Tier_II_Amount ; 



Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_III_A 

and 

Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_III_B ; 

Settings::eBay_Insertion_Fees_Tier _III_Amount ; 



Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_IV_A 

and 

Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_IV_B ; 

Settings: eBay_Insertion_Fees_Tier_ IV_Amount ; 



Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_V_A 

and 

Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_V_B ; 

Settings::e Bay_Insertion_Fees_Tier_V _Amount ; 



Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_VI_A 

and 

Ebay_Sales_Price ≤ Settings::eBay_Insertion _Fees_Tier_VI_B ; 

Settings: :eBay_Insertion_Fees_Tier _VI_Amount ; 



Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_VII ; 

Settings::e Bay_Insertion_Fees_Tier_V II_Amount ; 



0 )

I haven't looked much at the actual content, but it seems you haven't accounted for the fact that Case() returns the result of the FIRST test that returns true; therefore testing for both 'lower AND higher' is usually not required.

Finally, why don't you use a lookup instead?

[David Jondreau]

I like to put each Case statement (its test and result) on its own line, sometimes with empty lines above and below. Whatever makes it the most clear for you.

I'd think about wrapping a Let statement around this whole thing and defining most the fields as variables.

//For example:

Let([

Price = Ebay_Sales_Price ;

Tier1 = Settings::eBay_Insertion_ Fees_Tier_I ;

Tier1Fees = Settings::eBay_Insertion_ Fees_Tier_I_Amount;

Tier2A = Settings::eBay_Insertion_ Fees_Tier_II_A;

//etc

];

//Then your case would be

Case(

Price =< Tier1; Tier1Fees;

//and so on

))

You can build on that by using the Text Formatting functions (check the help file). And if you're doing a lot of one color a quick custom function might be useful, like:

Red = TextColor( fieldName ; RGB(255;0;0))

PS Oh, you want to color the calculation itself? Misread that. You can't. But you can use "//" to comment on your calc.

Edited June 27, 2007 by Guest
PS

[Anuviel]

Thanks, that helps. I will probably arrange it as shown by Comment.

I haven't looked much at the actual content, but it seems you haven't accounted for the fact that Case() returns the result of the FIRST test that returns true; therefore testing for both 'lower AND higher' is usually not required.

Finally, why don't you use a lookup instead?

It is a calculation that will return eBay insertion fees.

I used AND in the case because the fee is based on the selling price.

Example:

If the selling price is between 0.01 and 10.00 the cost will be 1.00, if the selling price is between 10.01 and 20.00 the cost will be 2.00

That is why I used AND in the case statement as it is possible to have more then one true statement if I do not use AND - or am I missing something?

Thank you.,

[comment]

Try this with n as input field:

Case (

n < 10 ; "0 to 10" ;

n < 20 ; "10 to 20" ;

n < 30 ; "20 to 30" ;

"more than 30" )

[Anuviel]

Thanks., that works - I was not aware of the fact that it returns/works in that way. Good to know.

Thanks.,

[comment]

I'd still suggest you read up on lookups, esp. the 'copy the next lower' option.

[Anuviel]

Will do, eager to learn, I always follow up on all tips.

Thanks.,

[Søren Dyhr]

You can format your calc, by this tool:

http://www.aptworks.com/tools/index.html

Only there is a problem here, with the indents of spaces now and then if you copy and paste from what is posted in this forum, which confuses the algorithm. Read the instructions on Debi's page.

--sd