Build Custom Formula Functions

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.

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.

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.

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.