Formulae via API?

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!

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

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!

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

That’s perfect. Thanks so much!