Skip to main content

Build Custom Formula Functions

You can create custom functions that users can access in Designer anywhere that an Expression Editor is available in the tool configuration. To create a custom function, you...

  • Write an XML file, which wraps parameters into other function calls.

  • Write a C-style DLL.

Regardless of the method of creation, the functions are made available to Designer using an XML formula function file, which must contain the following attributes:

  • <name>: A unique name that appears in the functions list.

  • <NumParams>: The number of parameters the function accepts.

  • <Category>: The function category that displays the function. This can be a new category or an existing one.

  • <InsertText>: The boilerplate text that is inserted into the expression editor when the function is used.

  • <Description>: A description that displays when the function is hovered over.

Build Using XML

When creating a function using only XML, the file's structure follows that of the example below:

<?xml version="1.0" encoding="utf-8"?>
<FormulaAddIn>
  <Function>
    <Name>Sample_XML</Name>
    <NumParams variable="false">3</NumParams>
    <Category>Sample</Category>
    <InsertText>Sample_XML(Num1, Num2, Num3)</InsertText>
    <Description>Obtains the average of three numbers.</Description>
    <Formula>((P1+P2+P3)/3)</Formula>
  </Function>
</FormulaAddIn>

The NumParams element has an attribute variable. The attribute is set to false because the number of parameters accepted is fixed. When using only XML to create a function, the number of parameters accepted is always fixed.

The Formula element contains the formula that actually performs the work. The contents are not visible to the user. The parameters are always referenced as P1, P2, P3, and so on, regardless of the text presented in InsertText.

To add a second function to the same XML file, create another Function element within the FormulaAddIn element.

Build Using a DLL and XML

To use a DLL, write your desired function, then export it from the DLL. Assuming the exported function is defined as:

struct FormulaAddInData{
        int nVarType;           // 1 for double, 2 for wchar_t
        int isNull;             // 1 if NULL, 0 if valid
        double dVal;            // valid if nVarType==1 && isNull==0
        const wchar_t * pVal;   // valid if nVarType==2 && isNull==0
};

The exported function has the signature:

typedef long ( _stdcall * FormulaAddInPlugin)(int nNumArgs, FormulaAddInData *pArgs, FormulaAddInData *pReturnValue);

nNumArgs contains the actual number of arguments in the call.

pArgs points to an array of the actual arguments.

The function should return a 1 to signal success and a 0 to signal failure.

pReturnValue returns the computed result if the call is successful or a string error message if the call is unsuccessful. Any returned strings must be allocated with GlobalAlloc, and Alteryx Designer manages freeing the string.

With the DLL successfully set up, you need to reference the DLL from the XML file.

<?xml version="1.0" encoding="utf-8"?>
<FormulaAddIn>
  <Function>
    <Name>AddInData</Name>
    <NumParams variable="true">3</NumParams>
    <Category>Sample</Category>
    <InsertText>AddInData(VarType, isNull, double)</InsertText>
    <Description>Verifies data type before adding data.</Description>
    <Dll>
        <Name>AddInData</Name>
        <EntryPoint>FormulaAddInData</EntryPoint>
    </Dll>
  </Function>
</FormulaAddIn>

The NumParams element has an attribute variable. The attribute is set to true because the number of parameters accepted is variable. When using a variable number of parameters, the value should equal the minimum required number.

The Dll element contains two child elements that provide the needed information about the DLL.

  • <Name>: The name of the DLL file without extension.

  • <EntryPoint>: The name of the exported function being used.

  • <RunSingleThreaded>: (Optional parameter) The option to enforce single-threaded processing. The value for this option can be either False (by default) or True. You can use this parameter for cases when the function doesn’t work properly in a multi-threaded environment.

    Caution

    For better performance, it's recommended to change the function to be able to work in a multi-threaded environment instead of using this parameter.

Access Functions in Designer

To make the functions available in Alteryx Designer, go to the RuntimeData folder in your Alteryx Designer installation. Check for a folder titled \FormulaAddIn. If the folder does not exist, create it.

Sample XML in FormulaAddIn

The folder contains Sample.xml, a file that is correctly configured. Add a category, save the file, and restart Alteryx Designer to see the sample function.

C:\Program Files\Alteryx\bin\RuntimeData\FormulaAddIn

Save your XML file to the \FormulaAddIn directory. If using DLL, save the related DLL file in the same place. The standard naming convention is to name the DLL and XML files to match.

In the case of two Designer instances running simultaneously on one machine (Admin and Non-Admin), the XML file with custom formula functions should be separate for both versions via different paths to be displayed in the Designer GUI.

  • For the Non-Admin version, the path is C:\Program Files\Alteryx\bin\RuntimeData\FormulaAddIn.

  • For the Admin version, the path is C:\Alteryx\RuntimeData\FormulaAddIn.