Managing Multiple Prices

When selling products or services with fixed standard prices, managing products and sales orders is straightforward. A common workflow is to record product standard prices in the "Products" sheet and set up Link and Load to the "Sales Order" sheet. This way, when creating an order, the price is automatically pulled in, and formulas calculate subtotals and totals, as demonstrated in this video tutorial.

However, in practice, many businesses have products or services with prices that vary depending on different situations, such as "promotional discounts at specific times", "seasonal price variations", "quantity-based pricing", "customer-specific pricing", or "customized services where each quotation must be calculated individually".

In such cases, how can you record multiple prices for the same product in the "Products" sheet so that you don’t have to enter prices manually when creating an order, but can still use Link and Load to automatically retrieve the correct price? The following are design recommendations and example scenarios for managing multiple prices.

Basic Principle

Suppose you have a product called "Funland Admission Ticket", whose price varies by day—400 from Monday to Friday, 800 on Saturday, and 600 on Sunday. In this case, the product would have three different unit prices.

If you have only a few products (for example, fewer than 10) without variations such as color or size, you can record each price as a separate product with a unique name and number in the "Products" sheet.

This method is straightforward and works well for linking the "Sales Order" sheet to the "Products" sheet, making price selection simple. However, as the number of products grows, it can become messy and difficult to manage, making it harder to track price variations and pick the right one when creating a "Sales Order".

Managing Prices with a Subtable

For more complex scenarios, it’s recommended to manage multiple prices in a Subtable under each product, such as a "Product Price List". This keeps pricing organized and makes it easy to update.

However, since we want to easily pull up the relevant price information when selecting a product in the "Sales Order" sheet, Ragic does not support using Subtable fields separately as direct Link and Load sources. It only supports loading all Subtable data at once (see this section for details).

Therefore, you need to generate a new sheet from the Subtable to create a separate "Pricelist" sheet.

Then, in the "Sales Order" sheet, set up Link and Load from the new "Pricelist" sheet. When selecting a "Product Price No.", the corresponding "price" will be automatically loaded.

In a "one product with multiple prices" setup, when creating a record in the "Sales Order" sheet, you select the "Product Price No." instead of the product itself. This ensures that the correct price is automatically loaded without manual input.

Since a product can have multiple prices, the system cannot determine which one to use just by selecting the product. For example, when choosing the "Funland Admission Ticket," it cannot know whether you want the weekday, Saturday, or Sunday price. Therefore, you must first select the corresponding price record.

This design logic is also applied in Ragic's free "Sales Order Management" template, where the "Product Sales No." in the "Sales Order" sheet represents a specific price record rather than the product itself.

Displaying Only Relevant Price Options

For example, in the "Funland Admission Ticket" case, there are three price options. If it is Monday, you wouldn’t want the "Saturday (800) " or "Sunday (600) " tickets to appear in the selection.

This can be addressed with Dynamic Filtering, which ensures that only price options meeting specific conditions or within valid periods are displayed.

Examples of Common Scenarios:

Prices Vary by Day of the Week

You can record applicable weekdays in a "Note" field (for example, Mon–Fri, Sat, Sun). Add a "Weekday" field in the "Sales Order" sheet and use the WEEKDAY() formula to reference the "Order Date" field and return the weekday number. Then add an "Order Day of the Week" field and use a Conditional Formula to convert the numeric value into text (Mon, Tue, Wed, Thu, Fri, Sat, Sun). Finally, apply Dynamic Filtering so only the price valid for that day is selectable.

Prices Vary by Specific Periods

Suppose there is another case where ticket prices remain the same within a given period but change each year. By recording the "Effective Date" and "Expiry Date" in the "Products"/"Pricelist" sheet, you can apply Dynamic Filtering so that only prices with an "Order Date" falling between those dates are displayed.

Show Only the Latest Price

You can add a "Latest Price" Checkbox field in the "Pricelist" sheet to mark the most recent price, and apply a Dynamic Filtering to display only records with this checkbox selected.

Prices Vary by Purchase Quantity

Suppose your pricing is quantity-based, where larger orders get lower prices, for example, $600 for 100 units or more, and $800 for 1,000 units or more. In this case, add a "Minimum Purchase Quantity" field to the "Pricelist" sheet, and apply Dynamic Filtering to display the correct price.

It is important to note that Dynamic Filtering only applies when selecting from the list, so the "Order Quantity" must be entered first. To ensure this, consider placing the "Order Quantity" field before the "Product Price No." field so that it is filled in first.

Alternatively, you can load the "Minimum Purchase Quantity" field into the "Sales Information" Subtable and set a Validation rule to prevent saving if the "Minimum Purchase Quantity" is greater than the "Order Quantity".

Prices for Specific Order Types

You can also add an "Order Type" field to the "Sales Order" sheet. For promotional orders, set the value to "Promotion", and note the same in the remarks or other relevant fields of the promotional price records in the "Pricelist" sheet. Then, configure the Dynamic Filtering conditions accordingly.

Standard and Discounted Prices

If your pricing varies depending on specific conditions, for example, when each product in an order can be "discounted by 100 dollars" or "when "Diamond Member" receive a 30% discount and "Gold Member" a 20% discount", it is better to handle this by adding a "Discount Rate" field in the "Sales Order" sheet and applying Conditional Formulas to calculate the final prices.

In this setup, the prices recorded in the "Products" sheet represent standard prices, while discounts are calculated directly in the order. If both conditions apply, you can combine them by recording multiple standard prices for the same product in the "Pricelist" sheet, then Linking and Loading the selected price into the "Sales Order" sheet, where the "Discount Rate" is applied to calculate the total amount.

When Pricing Is Highly Variable

If pricing fluctuates significantly and no fixed rate applies, for example, for customized services tailored to each client and quoted individually, the focus should not be on maintaining a fixed price field in the "Products" sheet or automatically loading it into quotations or orders. Instead, it should be on helping the person preparing the quotation or order easily access references and quickly calculate a reasonable price based on material costs and past quotations.