Same Member for both Input and Calculated value?

You might be wondering why someone would want to combine a calculated value and an input value within the same data member. While this isn’t a common request in every implementation, it’s something I’ve encountered and addressed in my past experience.

To explain this, let’s explore a simple driver-based calculation. While this particular example isn’t something I’ve personally worked on, the aim of this blog post is to demonstrate just how straightforward it is to build such a solution using a Groovy rule executed with the ‘Run Before Save’ option.

Here is the design of my form:

The rows of the form contain three members:

• Units Sold
• Unit Price
• Store Revenue

You might be thinking that Store Revenue should be a read-only field because it’s usually calculated. However, that’s not the approach here. The core idea is to allow users to directly adjust the calculated Store Revenue, essentially overriding the initial system-generated value.

Typically, Store Revenue is calculated by multiplying Units Sold by Unit Price. But what if a user wants to target a specific, different Store Revenue figure?

From my perspective, there are two common ways to handle this:

• Reverse Calculation: The user adjusts Units Sold or Unit Price until the calculated Store Revenue reaches their desired higher number. This requires them to manipulate the input fields.
• Override Member: A separate input field, like “Override Store Revenue,” is created. If the user enters a value here, it takes precedence over the system’s calculation.

What if we let users directly input their desired Store Revenue into the calculated field itself, and then the system reverse-calculates the corresponding Units Sold and Unit Price?

This approach offers two significant advantages:

• Data Integrity: It ensures that the system always maintains accurate Units Sold and Unit Price figures, as they are derived directly from the user’s desired Store Revenue.
• User Flexibility: It provides users with the freedom to experiment directly with the Store Revenue figure on the form, allowing them to easily achieve their target numbers without having to manually adjust the underlying components.

Diving Into the Demo

Case 1: Inputting All Three Member Data Simultaneously

It’s quite common to have our data coming from an Excel spreadsheet and then want to transfer all of it directly into a form. This process simply involves using the familiar copy-and-paste method.

When you paste multiple data cells into the form at once, the Groovy rule is designed to recognize that all three fields are being updated simultaneously. Because of this, it temporarily holds off on calculating the “Store Revenue” member right away. This allows all your pasted data to settle in without immediate, potentially conflicting, calculations.

Here is my Excel file, and I will now directly paste these values into the form

With all data successfully saved, what happens if the input is incorrect? lets demonstrate the outcome when I submit an incorrect Store Revenue value from an Excel sheet.

The Store Revenue data for July is incorrect, while the figures for other months are accurate.

The system prevents the figures from being saved and clearly indicates the specific Year and Month where the data is incorrect and requires an update. The best part is that, since it’s configured to ‘Run Before Save’, it simply retains all of your inputs. This allows you to easily manually adjust the July figures and then save the data again.

Manually Updating July Revenue:

Case 2: Updating Two Members Data Simultaneously and Calculating Non Edited Member

This section outlines how calculations occur when any two members on the form are edited. The system is designed to intelligently determine which value to derive based on your inputs:

• If Units Sold and Unit Price are updated, the rule will calculate Store Revenue.
• If Unit Price and Store Revenue are updated, the system will calculate Units Sold.
• If Units Sold and Store Revenue are updated, the system will calculate Unit Price.

Essentially, when you change any two of these fields, the third related field is automatically calculated to maintain consistency.

Using the same form and fields, we can perform three different calculations depending on the context.

Case 3: Handling Single Member Edits

Case 2, where a non-edited member is always calculated, is quite straightforward. However, a more complex situation arises when only one member is edited. The main question then becomes: which calculation should the system perform?

For instance, if only Units Sold is edited, should the system calculate Unit Price or Store Revenue?

To address this, I am using “lock” operation to inform the system which member should not be calculated. Users can simply choose which member’s data they wish to keep unchanged, allowing the system to calculate the remaining one.

Here’s how this flexible approach works:

• User Control with Locking: Users can select which member’s data they intend to lock, signifying that its value should remain constant.
• Dynamic Calculation: The system then understands that the remaining unlocked member should be the one calculated based on the single edit.
• Example: If a user edits Units Sold and locks Unit Price, the Unit Price will remain unchanged. The system will then calculate Store Revenue based on the updated Units Sold and the locked Unit Price.

Here, the data for the locked members is preserved, while the other member is then calculated

And…

The ‘Run Before Save’ rule is particularly valuable as it preserves user-inputted data even if an error occurs, ensuring you won’t lose edited information. Furthermore, the Grid provides remarkable flexibility to dynamically adapt calculations based on various scenarios. It’s truly impressive how exploring the DataCell of a Grid can unlock so many possibilities. Rest assured, this isn’t an overly complex calculation, likely involving just a few If Conditions with checks for edited and locked properties.

While you might not implement these exact methods in your application, I hope you found this information insightful