Build Custom Formula Functions

Create custom functions that can be accessed through Alteryx Designer anywhere an expression editor exists, such as the Formula tool, Multi-Row Formula tool, Filter tool, or others. Custom functions are made by:

writing an XML file, which wraps parameters into other function calls
writing 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 to 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 be equal to the minimum number required.

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

<Name>: The name of the DLL file.
<EntryPoint>: The name of exported function being used.