¶ Field Validation
This module contains shared objects and functionality with other modules we are currently developing. For use (and on premise compilation) of Compliance Field Security is required for compiling and use of, both as objects and in the license. The module comes with no additional costs and is by default attached to any Compliance Field Security installation on Dynamics 365 Business Central.
Default Microsoft Dynamics 365 Business Central offers no functionality to enforce business rules and enhance data quality in the system. When creating new items, customers and suppliers, it is important that fields such as booking groups, valuation method and payment conditions are correctly filled in. Managing master data yourself can be very time consuming, but for a correct processing of (internal) orders it is important that the master data is correct.
With the extension Compliance Field Validation the quality of your internal information processing will be enhanced. For every table (default, partner or customization) you can determine requirements of field values entered. Depending on the setting, the user will receive a warning or an error.
To be able to use the Field Validation extension, it is required you register the module
Here you can find how to do this.
¶ Create a Field Validation with the wizard
Search for Field Validation Setup Wizard choose it from the list and click on “Next”.
Choose a table in which you want to validate the fields. You can click on the drop down arrow to search for a table, or you can type the Table ID, or you can type the name of the table and a list containing that name will become visible to choose from.
Add the fields that are mandatory to be filled in, by clicking on the AssistEdit button of the Field No. on the first line. A list with all fields in the table opens.
Choose the field you want to validate and confirm with “OK”
You can also insert a Table Relation or Related Field with corresponding error message.
! Note :
The Field Validation is inactive until the Starting date contains a date in the past or today or if the field End Date contains a date in the past. (!)
The user who set up this Field Validation should log out and then in again of Business Central to activate this Field Validation.
This applies also to the user(s) for which this Field Validation was set up.
¶ Manually create a new Field Validation
Search for Field Validations List
Click on “New”
Under the “General” tab (header) you will find the following fields :
No.: If you have an automatic no. series setup, generally this number will be created automatically when you choose a table to set a validation for. Otherwise, fill in a number manually.
Description : Add a Description for this validation e.g. Item validation.
Table ID : Select a table to set a validation for.
Validation Type : Choose if you want the Validation type to be a Warning or an Error.
- Warning: a message will appear when the validation has not been met, but the system will not hold.
- Error: a message will appear when the validation has not been met and you will not be able / allowed to proceed until the error has been corrected.
Init Values : Here you can setup what field value(s) should be filled (or kept empty) when a new document is made regarding that table validation.
E.g. to have an item card automatically set to blocked when a new card is created.
Conditions : The condition(s) that have to be met when the validation has to be triggered.
E.g. the validation has to be executed, when the card is being unblocked or when a document is released.
Action after Validation : When the record does not meet the requirements set up in the lines of the validation, this action can be executed.
E.g. when validation has been triggered, but the field values are not according the requirements, the card will be set to blocked again. Note: this only works in combination with validation type = Warning.
Toggle “Validate Default Dimensions" : When switched to On, the global default dimension setup for this table will be validated. You can view / setup the default dimensions setup with the action “Table Default Dimensions Setup” in the ribbon.
Starting Date : choose the starting date for the field validation. If left blank, the field validation will not work.
Show more : clicking on “Show more” will show more fields in the header :
Valid for Permission Set ID : Optionally, here you can setup a permission set ID for which the field validation is valid. If filled in, the field validation is only valid for users with that specific permission set. If you leave the field empty, the validation applies to all users regardless of the permission sets.
Ending Date : Optionally, you can fill in a ending date if you want this field validation to be valid for only a period of time
Setup Field Validation rules in Tab “Lines” :
In the field validation lines you can set up your business rules:
Validation Type:
- Mandatory : field cannot be empty
- Filter : apply a filter on a field, e.g. a decimal field that should have a value >100
- Length : length of a field, e.g. a phone number must be 10 digits
- Related No. of records : can be used on a related table, e.g. a customer must have at least one valid sales price
- Related Field : compare related fields to each other, e.g. on a sales order the Customer Posting Group must be the same as on the customer card
- Regular Expression : can be used with RegEx commands, anything you can do with RegEx can be applied.
- Calculated : this type allows you to validate by System Variable or a calculated filter based on the logged in user.
Field No. : Choose the field(s) that need validation by clicking the AssistEdit button on the Field No. A list with all fields in the table, set in the header, opens. Alternatively you can input the field number.
Field Caption : This field will be filled in automatically after you have chosen a Field No.
Table Relation No. : When you want to set up a validation using a table relation, you can set that table relation here by using the AssistEdit button.
Validation Value : Set the value to which the validation has to be made.
Calculated filter : To set a validation according a calculated filter, choose one here. When clicking on the AssistEdit button, a number of pre loaded variables will be visible. Below you can find a more detailed explanation of working with Calculated filters.
E.g. SALESPERSONNO where you can set the filter on the salespeople/purchase person.
Regular Expression : Click on the AssistEdit button to use a RegEx to which a field has to be set. A few RegEx have been pre loaded with the installation.
E.g. MAIL will set how to compose an email. Below you can find a more detailed explanation of working with Regular Expression filters.
Conditions Related Table: When you have chosen to validate with a Table relation, you can set condition(s) on the related table. (use the AssistEdit button)
Notification : Specify a custom Error or Warning message which will appear when the validation is not met
Table Relation Validation Type: With the Validation Type “Related Field”, you can set the relation between fields of the table relation.
Types are :
- Equal
- Not Equal
- Greater than
- Lower than
- Greater or equal than
- Lower or equal than
Related Field No. : Choose the field of the related table in relation to the field chosen in “Field No.”
Field Caption : Will be filled in automatically after the “Related Field No.” has been chosen
! Note :
The Field Validation is inactive until the Starting date contains a date in the past or today or if the field End Date contains a date in the past.
The user who set up the Field Validation should log out and then in again of Business Central to activate the Field Validation.
This applies also to the user(s) for which the Field Validation was set up.
Options in the top bar :
Manage : If you have already set up some Field Validations and you click on “Manage” when a validations is highlighted/selected, the following will become visible : “Edit”(to edit the highlighted validation), “View”(to view the highlighted validation) and “Delete”(to remove the highlighted validation).
Check table : When an active validation has been highlighted/selected, using this option will calculate the outcome of the validation and show it in a separate view (“Field Validations Check Overview). In this view you can opt to solve the issues by selecting one and using the “Open related page” in the top bar of the view.
This will open the page regarding the issue, giving you the opportunity to resolve it.
When you close that page, you will be returned to the Field Validations Check Overview.
You can choose “Calculate” in the top bar of this view, to refresh the overview of pages where the validation would give an error/warning.
At this time we are developing the possibility to recalculate the overview automatically after the “related page” was opened and the issue was solved.
- Field Validation Filter : If required, you can also opt to create an overview of an other Field Validation setup by using the AssistEdit button. After chosen an other setup, you can use the button “Calculate” in the top bar of this view to recalculate the view to the results of that Field Validation.
! Note : results will only be visible if the Field Validation is active (has a startdate of today or in the past, and no enddate in the past)
Comments : usually you use this option to fill in who and when the Field Validation was set up and to keep track of the changes made in the Field Validation. (version history)
Companies : in stead of setting up a Field Validation for all Companies, you can opt to setup a Field Validation for a specific Company by selecting the specific Company with this option.
Change Log Entries : overview of changes made after initial first setup of the Field Validation selected/highlighted.
¶ Field Validation with Regular Expressions
Since version 1.2.0.20201027 you can use the validation type Regular Expression. A Regular Expression is a string of characters that define a search pattern for (in this case) input validation. From the Field Validation settings, you can load standard data (Regular Expressions) to use in Field Validations, or you can set your own validations on the Regular Expressions page.
If you want to use a Regular Expression, you must perform the following steps:
- In the validation rule, select the validation type Regular Expression
- Select the field you want to validate
- Select the validation you need from the Regular Expression field.
In this example it has been chosen to use the Regular Expression “MAIL” for the Email field. This ensures that the mail address must exactly match the Validation value.
For example, if you go to a Customer card and you enter an incorrect email address that does not comply with the Regular Expression, you will get a warning, as shown below.
¶ Dynamics 365 Copilot Regular Expressions Generator
Designing regular expressions can be a complicated and time consuming process.
To help you, we have developed a Copilot to make this much easier.
This feature will be available from Business Center version W1 24.0 and up.
When you want to add a Regular Expressions, you can use the “Suggest with Copilot” button.
This will open a pop up where you first need to provide a Prompt. The pop up already advises you of how to assemble a prompt. Make sure your prompt has the proper format and examples. You can also make use of the Prompt guide (button next to the button “Generate”) where you also have the option to view a Demo Video of the use of this Copilot feature.
As an example for a prompt : Validate that an email address is in default mail format and consists either the domain 2-control.nl or 2-controlware.com and that it contains firstname.lastname before the @. Example: john.doe@2-control.nl or john.doe@2-controlware.com
To make sure a regex will be presented which will avoid any wrong values can be entered, it is best to be as specific as possible when composing your prompt.
After you have completed your prompt, click on “Generate” and Copilot will generate a proposal for a regular expression.
Suggested Code : proposed code for the regular expression
Expression : expression proposed by Copilot for the prompt given
Description : description of the regular expression as a result of the prompt given
Test Value : to test if the result is correct.
- When the value is correct : value turns Green
- When the value is incorrect : value turns Red
Test Result Clarification : clarifies why a test value is incorrect.
If you are satisfied with the proposal, you can click on “Confirm” to save this regular expression.
The regular expression Code was suggested by Copilot, but you can change this before you confirm.
Confirm : Confirmation for addition of the new regular expression. You will be asked if you want to save it.
- Yes : The new regular expression will be saved and can be used in your validation
- No : Cancels the addition of the proposed regular expression and returns to the proposal window to make any changes
Regenerate : Regenerates a new regular expression. In case another regular expression is generated, you should re-examine the given suggestion. The prompt given was not detailed enough to generate only one possible expression.
Discard: Discards the proposal of the new regular expression and closes the proposal window.
Dismiss : Closes the message “Your generated regular expression is loaded. Make sure to test if the expression works in the field Test Value.”
¶ Field validation with type Calculated
Since version 3.2.20210NN.0 you can validate by type Calculated. This type allows you to validate by System Variable (User ID, User Security ID and Current Company, by type Date Formula and by the logged in user where the system can use the table 91 (User Setup) and table 156 (Resource).
The field Validation Type must be set to Calculated. After that, a Calculated filter can be selected in the Calculated Filter Field. Examples of Calculated Filters can be imported from the Calculated Filters setup page with the button Load Demo Data.
In Field No. choose the field on which the validation has to be performed.
In the conditions you can state what condition has to be met for this validation to be applied on.
In the below example the validation on the Order date will be triggered with the calculated filter CurrentPeriod (=this month). When the Order date is not in the current month (in this case January) and the status of the Order is / changes to Open, an error occurs.
The Field Validation is inactive until the Starting date contains a date in the past or today or if the field End Date contains a date in the past. (!)
The user who set up this Field Validation should log out and then in again of Business Central to activate this Field Validation.
This applies also to the user(s) for which this Field Validation was set up.