Formulae via API?

API & Programming
1

Hi, is it possible to:

  • Update a text field so as to change it to a formula field?
    • (Or does one need to create a field as a formula field from its inception?)
  • Define the formula for a field using textual notation such as Entity.Field rather than e.g. [field_id field_id]

…via the API?

Thanks!

2

In principle, yes, it is possible (although it is not an approved method, so could break/be deprecated without warning!)

Not via the API. The formula is defined using a query language that utilises field IDs, parameters, operators, etc… There’s no user-friendly way of doing it :frowning:

1 Like
3

Thanks! Any chance you could share an example? This isn’t in the docs. I understand it would be an unsupported method/command/approach that might break; that’s fine with me.

I figured that was the case, but I thought it couldn’t hurt to ask. Thanks!

4

Something like the following code will convert a text field to a formula field (that has text as the return type)

const fibery = context.getService('fibery');

await fibery.executeSingleCommand({
    "command": "fibery.schema/batch", "args": {
        "commands": [{
            "command": "schema.field/set-meta",
            "args": {
                "holder-type": "SPACENAME/DBNAME",
                "name": "SPACENAME/TextField",
                "key": "formula/formula?",
                "value": "true",
            }
        },
        {
            "command": "schema.field/set-meta",
            "args": {
                "holder-type": "SPACENAME/DBNAME",
                "name": "SPACENAME/TextField",
                "key": "fibery/readonly?",
                "value": "true"
            }
        },
        {
            "command": "schema.field/set-meta",
            "args": {
                "holder-type": "SPACENAME/DBNAME",
                "name": "SPACENAME/TextField",
                "key": "formula/formula",
                "value": {
                    expression: [
                        'q/if',
                        [
                            '=',
                            [ '370a66a8-686b-4955-8527-31b5f9e779c4' ],
                            '$formulaParam1'
                        ],
                        '$formulaParam2',
                        '$formulaParam3'
                    ],
                    params: {
                        '$formulaParam1': 'hello',
                        '$formulaParam2': 'open',
                        '$formulaParam3': 'closed'
                    }
                }
            }
        }]
    }
})

In this example, the formula expression results in the following:
If(Name = "hello","open","closed")

2 Likes
5

That’s perfect. Thanks so much!

6

@ChrisG thanks for providing this example API request! :pray: However, I am getting the following error from the Fibery API:

Modification of “formula/formula” field meta for “SPACENAME/Name” field of “SPACENAME/DBNAME” database demands ‘setup-formula’ claim.

I was not able to find any documentation regarding the “setup-formula” claim, has anybody an idea what this is about? :upside_down_face:

Thanks,
Ben

Here is the full API response:

{
  "success": false,
  "result": {
    "name": "commands.error/schema-field-set-formula-meta-failed",
    "message": "Modification of \"formula/formula\" field meta for \"SPACENAME/Name\" field of \"SPACENAME/DBNAME\" database demands 'setup-formula' claim.",
    "data": {
      "top": {
        "error/name": "commands.error/schema-field-set-formula-meta-failed",
        "holder-type": "SPACENAME/DBNAME",
        "field": "SPACENAME/Name",
        "key": "formula/formula"
      },
      "cause": {
        "error/name": "commands.error/schema-field-set-formula-meta-failed",
        "holder-type": "SPACENAME/DBNAME",
        "field": "SPACENAME/Name",
        "key": "formula/formula"
      },
      "holder-type": "SPACENAME/DBNAME",
      "field": "SPACENAME/Name",
      "key": "formula/formula"
    }
  }
}