1. Abstract
Text cubes in Board applications serve as flexible data entry points, allowing users to input free text such as comments, narratives, or descriptions. While these cubes offer a simple interface for capturing data, they also come with a set of challenges due to the variety of text input formats that users may paste or type.
A common issue is the pasting of text from various sources (e.g. PDFs, Word files, Excel sheets, Notepad). These external sources can introduce hidden or special characters that may interfere with subsequent data processing. These problematic characters might not be visible to the user, but they can cause errors when the data is extracted in a flat file and then reloaded into the system or in other systems. Some of these problematic characters are: carriage returns, line feeds, and tabs.
2. Context
This solution is applicable in any scenario where it is necessary to prevent special characters in text cubes, particularly when the data is exported to flat files. In such cases, the presence of special characters can cause issues when reloading the extracted file into Board using a Data Reader or into other systems.
3. Content
Text cubes are largely used in Board applications with different scopes like:
Enter Comments and Narratives: Text cubes are often used in situations where users need to provide contextual information or explanations related to specific data points. These can be simple comments, detailed narratives, or other forms of textual input.
Edit Entity Descriptions: Text cubes are needed to manage the localization of Entity members, where the translated descriptions are collected in cubes.
This article shows how to enable the Validation rule to prevent users from entering undesired characters in the text cube.
Let’s assume that text cubes are used to allow users to edit an entity description, in this case, the common need is to:
- Avoid the usage of special characters like ENTER, TAB
- Limit the length of the text inserted by the user
Both cases cause issues when exporting those values to be re-imported in Board using a Data Reader.
In this case, the use of Validation Rules is very helpful using the function "search" to find the special character using the ASCII code.
3.1 How to write the validation rule
Following the example, two validation rules are added to validate data entry on block a:
The rule is applied on the block “a” itself.
First validation rule: “len(a)<513” is aimed to notify and block users if the text length is higher than 512 characters. This was done because the text was then reloaded as Entity description and exported to a SQL DB where the data type for the corresponding column was a “char(512)”.
Second validation rule: “IFError(Search(Char(10),a),0)=0”. This rule is using 2 functions:
- Search(Char10,a): this function searches for the ENTER character, whose ASCII Code is Char(10), in the block “a”. This function returns the number representing the position of the specified character.
- IFError: this is added to the formula because the Search function returns an error if the specified character is not found. So, in case of the character is not found the IFError is forcing the value to 0: IFError(<search>,0)
The formula is flexible and can be used to block any special character. To do so, simply use the correct ASCII code for the desired character in the search function (e.g., TAB is represented by “Char(9)”).
4. Conclusion
The use of validation rules is highly flexible and gives users full control over the data entry process. One key advantage of using validation rules, as opposed to a background dataflow for text cleaning, is that errors are immediately visible to the user on the screen. The user is prompted to correct the error before proceeding, ensuring they are fully aware of the issue. This approach prevents data entry from continuing until the problem is resolved, which helps avoid overwriting existing data.
