How-To Use Culture Support

Generally, Foxtrot requires you to always use American-formatted values whenever you use formulas, calculations, if-statements, etc. This is very important to be aware of as you risk incorrect results if you ignore this fact, for example, if you evaluate a date that is formatted with Danish format, Foxtrot might believe that the month is actually the day and vice versa. To make sure everything is consistent in Foxtrot, the engine expects American format, so you should set up your scripts accordingly.

Of course, not all processes deal only with American-formatted data, especially if you are not located in America. The culture support engine in Foxtrot allows you to work fluently across different culture formats. The feature makes it possible to convert data back and forth between different formats to both make it possible to utilize use formulas, calculations, if-statements, etc.

This article will explain how you enable and use the culture support engine in Foxtrot.

Getting started

First, you need to enable culture support. You can either do this directly in the project via the Project Pane --> Project, or, if you wish to have this activated by default, you can click Account in the top right corner, then Preferences --> Project --> Culture Support.

To check whether you have successfully activated culture support, you can click in the Project Pane to add a variable to see if you now have the option "Culture".

If it is not visible, please make sure to click on Project in the Project Pane and double-check that Culture Support is activated.

Understand the basics

By default, whether you have Culture Support activated or not, all variables are created with your system settings as their culture format. Therefore, the culture option on variables are, by default, set to "System". To check your system settings, you can go to Control Panel --> Region to see your date format settings and click on "Additional settings..." to also see other settings such as decimal separator.

So, based on these regional settings, let us see how Foxtrot reacts when we create variables with different culture options. First, let us create a "System" decimal variable first with comma as the decimal separator as that is our system settings.

And this is the output. Notice that the output is exactly as we would expect it to be.

Now, let us try to create a new variable but with English (UK) format.

Notice here that the output is incorrect, at least from our perspective, but that is because English (UK) format expects dot as the decimal separator.

So to correct the variable, we need to set it with proper format.

Now the two variables are correctly formatted. If you are interested in converting one variable value to a different format, you can create a new variable with the desired culture and use the Convert Variable action to perform the conversion. Here is an example. Let us translate the English (UK) decimal to a Spanish decimal. First, we create a Spanish decimal, the value will be blank as it is not set yet.

Now, go under Data in the Action List to create a Convert Variable action like this.

The output of the action will be the English (UK) decimal converted to the Spanish variable.

This is the core concept of the Culture Support. It is exactly the same concept with date values, simply more complicated as there are more different formats.

How to use the culture formulas

As an alternative to the Convert Variable action, you can also use the available culture formulas in Foxtrot. Keep this in mind during the rest of the guide as well if you find this approach easier and more convenient than using the Convert Variable action.

With this approach, you can completely avoid using additional variables, however, it might also get a bit more complex - at least, you have to be quite comfortable using formulas in Foxtrot. Consider these two variables:

Now, let us assume that we want to multiply the decimal by 3 but without creating any additional variables! We can actually do this in a single formula action with multiple layers (embedding formulas). So, first, we need to create a new formula action.

First, we need to convert our input value, the "SystemDecimal" variable, to American format. We can do that using the formula "ConvertNumCulture".

Here, we can convert the variable from the system format, in this case "DAN", to "ENG-US" format. Because we are not done with the formula, click "Hold" after setting up the formula.

Now we can make our "Multiply" formula:

The "Number 1" (the input) will be the output of the first formula:

In this case, we are multiplying the decimal by 3. Because we are not done with the formula, click "Hold" after setting up the formula:

Now, to convert the output back to system (Danish) format, we can use the same "ConvertNumCulture" formula again just the other way around. First, we select the formula to convert the format:

The input will be the "Multiply" formula that we put on hold:

Now, we can convert it from "ENG-US" back to "DAN" and save the output the "SystemOutput" variable:

And that is it! With one action, you have performed three steps:

1. Convert from "ENG-US" --> "DAN"
2. Multiply with 3
3. Convert from "DAN" --> "ENG-US"

How to work with decimals

To better understand exactly how to work properly with differently formatted decimal values, let us set up a concrete example. You can download the script at the end of the article.

In this example, the script first creates a blank Excel workbook and writes two different decimal values with two different formats.

To deal with the fact that we need to work with two different formats of decimal values, we create two different variables with the appropriate culture settings, one with English format and one with Danish format. Hereafter, we retrieve the values using the Get Value action. After retrieving the values, we need to prepare them for the calculation. As Foxtrot requires English/American format, we create a new variable to store the converted value of the Danish decimal variable and use the Convert Variable action to convert it. Now, we can create a final variable to store the result from the Calculate action.

At the end of the script as an example, we create another variable to illustrate what could be done if we needed the result of the calculation in a different format than English/American.

You may download the script at the end of the article.

How to work with dates

The general concept for dates is the same as for decimals, however, it can be a bit more complicated as dates are a more complex data type and there are many different culture formats. To better understand exactly how to work properly with differently formatted date values, let us set up a concrete example. You can download the script at the end of the article.

In this example, the script first creates a blank Excel workbook and writes three different date values with three different formats. To deal with the fact that we need to work with three different formats of date values, we create three different variables with the appropriate culture settings, one with American format, one with Danish format, and one with Spanish format.

Hereafter, we retrieve the values using the Get Value action. After retrieving the values, we need to prepare them for the calculations and formulas. As Foxtrot requires American format (IMPORTANT: For dates, English (UK) and English (US) is different!), we create two new variables to store the converted value of the Danish and Spanish date variable and use the Convert Variable action to convert them.

After converting them, as the last four steps illustrate, we make a Message action simply to show the different variable values and try to create the first of two formula examples. Here is the Message action.

The American date: [%AmericanDate]
The Danish date: [%DanishDate] --> [%DanishDateConv]
The Spanish date: [%SpanishDate] --> [%SpanishDateConv]

And here is the formula action.

And in this last part we simply illustrate the use of a different formula.

Here is the formula to add 45 days to the Danish date. Remember that the output of formulas is always American format!

Of course, if you then needed to get the output of the formula in the variable ResultDateAdd in Danish format, you could implement a Convert Variable to convert the value back to the original Danish variable called DanishDate or a new variable with Danish culture.

And important note is that even though you are not on an American formatted machine, all the default tokens in the Expression Builder is automatically translated when using formulas, however, remember that the output is always in American format, therefore, you will typically store the output in an American-formatted variable and then use the Convert Variable action to translate the value to whatever format you need.