Applies to:

• Velixo Classic
• Velixo NX
• Every ERP

TABLE OF CONTENTS

• Overview
• Velixo-style filtering
  • Add multiple entities with a semi-colon ( ; ) or comma ( , )
  • Using a range ( START:END )
  • Wildcard operator ( ? )
  • Everything operator ( * )
  • Subtractor operator ( - )
  • Subtracting multiple entities
  • Intersection operator ( ^ )
• Regular expression-based filtering
• "like" / "not-like" filtering

Overview

This article provides an overview of techniques you can use inside Velixo formulas to filter your queries. Below, we provide the syntax, examples and additional considerations for three methods of filtering you can use with Velixo.

Velixo-style filtering

When creating functions to query data, you can use parameters which include ranges, use wildcards, exclude certain values, etc.

There are several methods that can help you do that:

• creating a list - separating values with a semi-column ( ; ) or comma ( , )

• using a range of values ( START:END )

• using a wildcard ( ? ) - e.g., all accounts starting with 4: 4????

• including all values for the parameter ( * )

• subtracting specific values ( - )

• filtering a range using intersection ( ^ )

This article explores each of these methods and provides a usage example for each.

The following function parameters support the filtering techniques listed below:

• Account  
• AccountClass  
• AccountGroup  
• Branch  
• Budget  
• BudgetId  
• Company  
• CostCode  
• CostType  
• Dimensions
• DimensionValue  
• EstimateId  
• ForecastRevision  
• InventoryItem  
• Ledger  
• Project  
• Range  
• Subaccount  
• Task  
• UserDefinedDimensions  

Add multiple entities with a semi-colon ( ; ) or comma ( , )

To include multiple entities in the parameters of a function, you can create a list of values and use a semi-colon to separate each one.  The use of commas is also supported but not recommended since it is also the thousands separator in many locales.

In this example, the three accounts to be included are separated by a semicolon.

Example: =ACCOUNTENDINGBALANCE("Demo", ACTUAL,,"10100;10200;10300",,,"09-2019")

Using a range ( START:END )

To include all entities of a range, use a colon to denote that the values are the starting and ending values of the range.

Example: 

In this example, the range is defined as starting with 10100 and ending with 11500.

=ACCOUNTENDINGBALANCE("Demo", ACTUAL,,"10100:11500",,,"09-2019")

Example: 

You can also combine lists and ranges. In this example, we are retrieving the ending balance for accounts 10100 through 11300 and also including account 10600

=ACCOUNTENDINGBALANCE("Demo", "ACTUAL",, "10100:11300;10600",,,"09-2019")

Example:

The techniques can be applied to other parameters. Here you can see a combined total of each Account Class from AP through COGS:

=ACCOUNTENDINGBALANCE("Demo","ACTUAL","AP:COGS",,,,"12-2023")

Wildcard operator ( ? )

To add all entities that match a pattern, use a question mark  ?  in the parameter of a function, to denote "any character". This operator can be used at any point in the pattern, such as "10???", "??999", and "1???0".

Example: 

In this example, three question marks follow the first two digits of the account number. The result will include only those accounts that begin with 10.

=ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"10???",,,"09-2019")

Example: 

In this example, the two question marks at the beginning of the account number will result in including only those accounts that end with 999.

=ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"??999",,,"09-2019")

Example:  

In this example, the question mark follows the first two letters of some of our expense account classes, giving us the same total as we would get if we were to list each account class balance and add them all up.

=ACCOUNTENDINGBALANCE("Demo","ACTUAL","EX?",,,,$D$1)

Difference between Velixo NX and Velixo Classic

There is a slight difference between Velixo Classis and Velixo NX when using the  ?  operator.

By popular request, Velixo NX has been updated to only return data where the  ?  symbol represents at least 1 character (while Velixo Classic returns data where the  ?  symbol represents 0 or more characters). 

For example, if we consider the following list of subaccounts:

Velixo Classic will consider that the ACU subaccount fits the ACU? filter, where Velixo NX will not (since it requires at least a single character to be represented by the  ? )

Classic

NX

Workaround:

The recommended filter to use with both Velixo Classic and Velixo NX is ACU;ACU? to return all values starting with ACU:

Everything operator ( * )

To add all available entities in the parameter of a function, use an asterisk ( * ).

In the example below, the asterisk specifies that every branch will be included. This is effectively the same as leaving the parameter empty.

Example: =ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"10100",,"*","09-2019")

Let's say that you want to calculate the balance of an account for every branch except a few branches. We can't use the  ?  wildcard for our branch (unless the names all our branch identifiers are of the same length), and it's tedious to write out a long list of the accounts. By using the  *  operator, we can easily include or exclude the branches we want.

Example: =ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"10100",,"*;-ONEBRANCH","09-2019")

Subtractor operator ( - )

We can use the Subtractor operator with the Account, Subaccount, or Branch parameters to exclude certain values from a range in these GL/Accounting functions:

• ACCOUNTTOTALDEBITS
• ACCOUNTTOTALCREDITS
• ACCOUNTTURNOVER
• ACCOUNTBEGINNINGBALANCE
• ACCOUNTENDINGBALANCE

This operator can also be put to use in the following parameters found in many of the Project Module Functions:

• AccountGroup
• CostCode
• Project
• InventoryItem
• Branch

Subtracting multiple entities

Often, it is inefficient to list all the branches to include. It is possible to use the Subtractor operator to subtract many entities from a range.

In this example, you can use the Subtractor to exclude the SERVEAST, SERVWEST, and PRODRETAIL branches:

Example: =ACCOUNTENDINGBALANCE("Demo", "ACTUAL",,"49300",,"*;-SERVEAST;-SERVWEST;-PRODRETAIL","12-2019")

Another example where this would be useful is in a project report where we want to calculate expenses and exclude specific item codes, as with *;-TRAVEL.

Intersection operator ( ^ )

Sometimes we want to use a range for our parameter, but really only want to include certain values that match a pattern from that range. To accomplish this, we can specify an intersection with the  ^  operator.

For information on using this, please see: Using the Intersection operator ( ^ ).

Regular expression-based filtering

Intended to allow for exact filtering, this mode is toggled whenever the expression follows this structure: /PATTERN/modifiers. This mode fully supports the regular expression syntax, including wildcards (e.g. .), quantifiers (+, *, etc.), character classes (e.g. \w or \d), lookaheads, lookbehinds, start/end anchors (^/$) etc.

To learn about regular expression syntax and constructing regular expressions, refer to external resources like the MDN guide on this topic.

Here are some considerations worth bearing in mind when using regular expressions with Velixo functions:

• Regular expressions do not bind to the start and the end of a string by default. If you do not want to find the phrase pattern, but just the beginning pat, your pattern should be /^pat$/ (and not /pat/).
• You can also use regular expressions to find strings that DO NOT match a particular pattern. For instance, to find non-empty strings that do not contain the phrase PATTERN, you can use the regular expression /^(?!.*PATTERN).+$/.
• Modifiers can help you narrow down your search further. They can toggle case sensitivity ( i ), multiline search ( m ), or use of Unicode characters ( u ).
• Regular expression-based search does not mix with Velixo filtering techniques described in the previous section. To use multiple values, use the regex syntax /(value1|value2|value3)/, not /regex1/;/regex2/.
• If you wish not to include spaces in your search, you need to add the \s* in a position where you want the spaces to be ignored. For instance, the expression /\s*pattern\s*/ will ignore spaces before and after the string pattern.

In its current capacity, this type of filtering does not support subtraction. To work around this limitation, we recommend creating separate functions and subtracting their results.

"like" / "not-like" filtering

Velixo also offers a simplified way of filtering phrases that match or do not match a particular pattern using the following syntax:

• like - returns results that match the string that follows. For instance, like canada will return all results that match the string canada.
• not-like - returns results that do not match the string that follows. For instance, not-like canada will return all results excluding the ones that match the string canada.

This mode supports two wildcard characters:

•  %  - represents zero or more characters. For instance, the filter like can will not match the string canada, but will match the string can; while like can% will match both can and canada.
•  _  - represents a single character. For instance, the filter like branch_ will match branch1 and branch2, but will not match branch or branch11.

This mode is case-insensitive only. We recommend using regular expression filtering for queries where case-sensitivity is desired.

This mode does not allow multiple values. In such cases, use Velixo-style filtering or regular expressions.