Skip to main content

Avoid circular dependency with formulas

Question​

> I have two characteristics (special length and special stroke). Both are dependent on each other and could be calculated using a formula. When I enter the formulas in both characteristics, I get an error message. &gt > Example: The value of the special stroke is calculated by entering "special length - 180". Conversely, the special length is "special stroke + 180". However, it should remain open whether the user enters a stroke or a length.

Answer​

For circular dependencies (A is calculated from B, B is calculated from A), you must provide the user with a decision. The user chooses which value he wants to enter - the other characteristic is then calculated.

Solution approach​

  1. Create selection characteristic: "Would you like to enter special length or special stroke?" with the values "Special length" and "Special stroke"

  2. Hide input fields: Depending on the selection, the input field should be hidden per action rule that is not required

  3. Create calculated field: A hidden berechnetes Merkmal "Actual special length" with the following formula:

variable("Möchten Sie Sonderlänge oder Sonderhub eingeben?").value == 1 ? Sonderlänge : Sonderhub + 180

Create attribute for the selection​

For the formula to work, you must create aa attribute (value property) for the selection:

  1. Open the Values of the characteristic "Do you want to enter special length or special stroke?"
  2. Click on "New column "
  3. Name the column "value " and select "Number " as the type
  4. Click on "Create "
  5. Assign a 1 for the value "Special length" and a 0 for "Special stroke"
Understanding formula syntax
  • variable("...") - Characteristics with spaces in the name are packed into the variable() function
  • Merkmal.Attribut - The variable("...").value notation is used to access the "value" attribute of the selection
  • a ? b : c - The ternary operator: If a is true, b is delivered, otherwise c

Result​

  • If the user selects "Special length", they enter the length directly → the "Actual special length" field accepts the value
  • If the user selects "Special stroke", they enter the stroke → the "Actual special length" field automatically calculates Sonderhub + 180

Further information​

  • Conditional calculations
  • Ternary operator in formulas
  • Hidden calculated fields