0

Formulas and Calculations

How can I implement a unique "logical" key for a table? For example, I have an Ingredient table whose logical key is IngredientName. How can I ensure there is only one row with a given IngredientName?
5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Given two tables: Recipe and RecipeIngredient, I am trying to modify all rows in RecipeIngredient for a given IngredientName whenever the Recipe changes. I started by setting up a trigger with loop following the examples in your manual. I started the trigger with the following line of code:

    For p in (select RecipeIngredient where IngredientName=”Carrots”)

    I got the error: Unexpected symbol column 55

    First, am I one the right track to do what I want? Second, what is the syntax error?
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    Try this formula in the "onChange" trigger of the table:
    –––
    let myText := IngredientName;
    IngredientName := if cnt((select Ingredient)[(IngredientName = myText)]._id) > 1 then
    "Duplicate Value"
    else
    myText
    –––

    Birger - Ninox Support
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    Yes, you are on the right track. My guess is that the error is about "curly" quotation marks. You can turn these off globally (on a Mac) at:
    Apple Menu -> System Preferences -> Keyboard -> Text -> "Use smart quotes and dashes"

    Birger - Ninox Support
    Like
  • Just so I understand, what is the ._id for? Is that standard for getting the id? In your code could it as well be ".anotherColumn"?
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    That's right. "_id" ist the internal name of the ID field (In Germany that field is called Nr)

    Birger - Ninox Support
    Like
Like Follow
  • 5 yrs agoLast active
  • 5Replies
  • 3162Views