Overview

Formulas are an efficient way to manage pricing tiers for products without having to manually make changes to single products when a pricing tier changes.  Finale can assign pricing tiers to customers, or you can choose them when creating sales orders to apply them manually.  All formula driven pricing tiers will automatically update the sales order based on the formula created.  If you are using formula pricing tiers, the price will not be visible on the product details page because the formula is dependent on order data to output a value.

This tutorial assumes you are familiar with products, sales orders, and customers, as well as the fields available on each page.

Pricing Tiers and how they work

Pricing will appear in the product detail screen in the selling group. Here, custom pricing fields can be set manually which will override formula-driven fields, or left blank to default to the formula.

Pricing tiers can be created and managed by navigating to Application Settings --> Products.  

Pricing tiers can also be assigned to customers as well.

Operators and Fields Available to Use

Operators and Functions

Formulas are expressions made up of operators and functions that act on input. There are a number of functions available, of which we will be using conditional(), if(), and number().

Expressions are tied together using operators, which we cover here.

Also, when inserting values into a formula, chances are you are using "lookup". Be aware of the data type returned by the dimension you are looking up. For example, calling "lookup" to return custom product pricing actually returns a string, which needs to be converted to a number before we can use mathematical operators on it. This means you need to also use number().  For example, you would input it number(lookup(fieldtolookup)).

For more general examples of how to use these functions, expressions, and data types, take a look at these examples.

Fields Available

Pricing depends on a product and a customer in the context of an order. Therefore, fields from all three collections are available to be used in formulas.

Calculated Fields

• basePrice : The price for the product when imported into an order
• basePricePerUnit : The price for a single product when imported into an order (different if tracked by case)
• quantity
• quantityCaseEquivalents
• quantityUnits

Finale Fields

• Product Store fields
• Product fields (+ custom fields)
• Company info
• Order fields
• Customer fields (attached to order)

These fields are generally accessed using the lookup() function. **Be wary! lookup() generally returns a string not a number!** Strings **MUST** be converted to a number before math operations (multiply, add, subtract, divide...) can be performed on them by using number().

Formula Entry

Formulas can be entered in Application Settings -> Product -> Selling prices. To the right of any prices, click on the blue link (either "add formula" or "edit formula"). The window below will open.

1. Formula text box: This is where you type the formula
2. Variable select drop down: This is where you can select reference fields
3. Preview products select drop down: Here you can choose a product to text the results
4. Preview quantity text box: Enter the quantity here.  Useful when testing a formula for quantity discounts.

**Formulas have to be on one line to be correctly parsed by our parser.** We suggest preparing more complex formulas in a text editor on multiple lines for ease of reading, then deleting the line breaks before pasting.

NOTE: Not all fields are available to preview in this view! Customer fields can be used when calculating pricing for products in orders, but are not available for previewing the price. The only way to preview these customer fields is to apply the pricing level to an order and test with a customer.

Examples

Math Operations on Lookup Fields

When inserting a field using the variable select drop-down, chances are the value returned is a string. This may look like a number, but our parser can't tell the difference between `7.00` and `"7.00"`. In order to convert the values returned so we can add, subtract, multiply, or divide, we must convert these strings to numbers. Using the number() function, we can do just that.

For example, if our product Item Price was 10.00:

lookup(productItemPrice) + 5  returns  10.005

number(lookup(productItemPrice) +5  returns 15.00

If you are getting unexpected results from your formulas, checking your data types is a good first step.

Percentage Discount From Price

Given a custom pricing level "Wholesale", lets create a Wholesale price with 5% off. Since lookup() returns a string, we need to convert it into a number before multiplying it by our discount. This is why we wrap lookup() with the function number().

number(lookup("productWholesale")) * 0.95

Price Markup from Average Cost or Standard Buy Price

Here is an example with a 60% markup from the standard buy price. Like the example above, we have to convert the value we receive with the lookup() function to a number with number() before we can use it with a math operator.

number(lookup("productStdBuyPrice")) * 1.6

We can also do the same thing with the average cost for a product.

number(lookup("productAverageCost")) * 1.6

Graduated Pricing Based on Quantity Using a Step Function

We will be using the conditional() function to create a step-wise function based on the quantity of a product being sold. This function can have a variable number of arguments. The first argument is the value we are comparing to. In the function below, we compare to the boolean "true". The second and third arguments are the condition and return value, respectively. Taking advantage of the if() statement, we can compare the quantity to a number and return "true" or "false". If the result is the same as our first argument of the conditional(), then we return number(lookup("productBase")). This is the numerical value of the field named "productBase", or simply "Base" on the Product detail screen.

These pairs of arguments make up the steps of our function, with the last argument being the default value if none of the other conditions match.

Human Readable View - ideal way to create the conditional statement

conditional(true,
            if(quantity < 5, true, false), number(lookup("productBase")),
            if(quantity < 10, true, false), number(lookup("productBase")) * .95,
            if(quantity < 25, true, false), number(lookup("productBase")) * .90,
            if(quantity < 100, true, false), number(lookup("productBase")) * .85,
            number(lookup("productBase")) * .85)

Pasted without line breaks to meet Finale's formula requirements

conditional(true, if(quantity < 5, true, false), number(lookup("productBase")), if(quantity < 10, true, false), number(lookup("productBase")) * .95, if(quantity < 25, true, false), number(lookup("productBase")) * .90, if(quantity < 100, true, false), number(lookup("productBase")) * .85, number(lookup("productBase")) * .80)

Graphical representation of how the price is affected based on quantity purchased