1. Data Management
  2. Data Quality Studio
  3. Set up data quality policies
  4. Set up data quality policy versions
  5. Set up validation rules

Set up validation rule of type Query

Use validation rules to check if the data is in line with the defined standards.
To validate data, you can use several validation types. This flow explains how to set up a validation rule of type 'Query'. 

Use a query validation rule to validate if an entered field value is allowed using a dynamic query. Using a dynamic query, you can validate the field value based on one or more field values in another table. 

Examples of validations where you can apply a query validation rule:

  • On creating a sales order, check if address information or contact information is defined for the selected customer. If no address or contact information is defined, the customer cannot be used on a sales order.

  • On the worker setup, for the worker position, a cost center can be defined in the 'Cost center' financial dimension. Validate if the defined cost center is active. If not active, a warning or error message is given for the worker.

  • On terminating an employee, check if positions are reporting to the employee. If positions are still reporting to the employee, you cannot terminate the employee.

Before you can set up a query validation rule, set up the dynamic query to be used.

For each validation rule, you can set up:
  • A validation message that is shown if the validation rule is not met.
  • Conditions to define the data to which the rule applies.


Data quality administrator Data quality administrator The data quality administrator (DQSDataQualityAdministrator) can set up and maintain: Data quality policies Data quality studio parameters Data quality studio general setup Start Start Need new or changed  dynamic query? Need new or changed  dynamic query? Set up dynamic queries

Set up dynamic queries

Use a dynamic query to find one or more records in the database. The found records are the input for further processing.

You can use a dynamic query on:

  • An enrichment rule of type Data query.
  • An action rule of type Data query.
  • A condition that is applied to a validation rule, duplicate check rule, or action rule.
  • A configurable lookup of type Dynamic query. You can apply the configurable lookup on a validation rule of type Configurable lookup.

To define a query range, you can use a function.

 Set up validation rule of type Query Set up validation rule of type Query Use validation rules to check if the data is in line with the defined standards.To validate data, you can use several validation types. This topic explains how to set up a validation rule of type 'Query'. Use a query validation rule to validate if an entered field value is allowed using a dynamic query. Using a dynamic query, you can validate the field value based on one or more field values in another table. Examples of validations where you can apply a query validation rule:On creating a sales order, check if address information or contact information is defined for the selected customer. If no address or contact information is defined, the customer cannot be used on a sales order.On the worker setup, for the worker position, a cost center can be defined in the 'Cost center' financial dimension. Validate if the defined cost center is active. If not active, a warning or error message is given for the worker.On terminating an employee, check if positions are reporting to the employee. If positions are still reporting to the employee, you cannot terminate the employee.Before you can set up a query validation rule, set up the dynamic query to be used.You can add validation rules in these ways:Manually.Select the fields to be validated from a page. Procedure 1. Click Data quality management. 2. On the Data quality policies tab, in the list, click the link of the desired data quality policy. Note: If you want to edit another data quality policy version than the currently shown version, first select the desired version. To do so, on the Action Pane, on the Version tab, click Versions. On the dialog, select the desired version and click OK. 3. Sub-task: Manually create validation rule. 4. On the Validation rules tab, click Add. 5. Define the table of the field which value must be validated. In the Table field, enter or select a value. Note: If you want to validate field values in inherited tables, define the child table instead of the base table. For example, instead of DirPartyTable, use DirOrganization or DirPerson. 6. In the Field field, enter or select a value. 7. Sub-task: Create validation rules by selecting fields. 8. On the Validation rules tab, click Select fields. 9. Open the page from which you want to select fields. 10. On the Field picker dialog, click Select fields. 11. Click the '+' button for each field that you want to select. 12. On the Field picker dialog, click Done. 13. On the Field picker dialog, click Submit to save the selected fields to the validation rule. 14. In the Record type field, select an option. 15. In the Validation type field, select 'Query'. 16. In the Event field, select 'Record validation'. Note: A query validation rule applies only to record validation. The validation rule is applied just before the standard record validation is done. 17. Define what is done if the validation rule is not met: Warning: A warning message is shown. The record or the field value is saved. Error: An error message is shown. The record or the field value is not saved. In the Outcome field, select an option. Note: For each validation rule, you can define a message to be shown. If you do not define a validation rule message, in case of a warning or error, a default message is shown. 18. In the Dynamic query field, enter or select a value. 19. Usually, you use the current record as the range for the dynamic query. Select Yes in the Set current record in query field. Note: If you do not use the current record as range, set the ranges manually. 20. Sub-task: Set the result fields. 21. On the Properties tab, click Add. 22. Select the Result check box. 23. Select the result field from the dynamic query. In the Field field, enter or select a value. Note: You can use fields from a table other than the validated field's table. 24. Use a range expression to define the values that must be met during validation. For example, if the result field must have a value, the range expression is: !=""To define a range expression, you can use: A fixed value or a range of values using the advanced query syntax A static method that defines the range. For example, you can use the SysQueryRangeUtil class to apply advanced date queries. In the Range expression field, type a value. Note: For more information on how to define ranges in the Range expression field, refer to Advanced filtering and query syntax. 25. Sub-task: Set query ranges and range values. 26. You can use other ranges in the dynamic query than the current record. On the Properties tab, click Add. Note: Only set ranges if the 'Set current record in query' field is set to 'No'. 27. Define the ranges to be applied to the dynamic query. In the Field field, enter or select a value. Note: To define a range, you can only use dynamic query fields that are set as range in the dynamic query setup. 28. In the Value type field, select an option. 29. In the Condition type field, select an option. 30. If the Value type is Field, define the field which value is used as range in the dynamic query. In the Field name field, enter or select a value. Note: Usually, you define the same field as the field that is used for the range. 31. In the Fixed value field, enter or select a value. 32. If the Value type is Custom, define the custom class with the method that you use to define a range. In the Class name field, enter or select a value. Note: The custom class must extend the DQSCustomValidationBase class. 33. In the Method name field, enter or select a value. 34. If the value type is 'Range expression, define the range expression that is used to define the range for the dynamic query.To define a range expression, you can use: A fixed value or a range of values using the advanced query syntax A static method that defines the range. For example, you can use the SysQueryRangeUtil class to apply advanced date queries. In the Range expression field, type a value. Note: For more information on how to define ranges in the Range expression field, refer to Advanced filtering and query syntax. 35. Close the page. Show message when  validation rule is not met? Show message when  validation rule is not met? Set up validation rule message Set up validation rule message You can define a message that is shown if the validation rule is not met.If no validation rule message is defined, the default message is shown if the validation is not met.In case of a data pattern validation, the applicable data pattern can also have a message defined. In this case, the message priority is:If a validation rule message is defined, this message is shown.If no validation rule message is defined, the data pattern message is shown.If no data pattern message is defined, the default message is shown.You can also enter a description of the validation rule.You can translate both the validation message and the validation rule description. Procedure 1. Click Data quality management. 2. On the Data quality policies tab, in the list, click the link of the desired data quality policy. 3. Click Edit. Note: If you want to edit another data quality policy version than the currently shown version, first select the desired version. To do so, on the Action Pane, on the Version tab, click Versions. On the dialog, select the desired version and click OK. 4. On the Validation rules tab, in the list, find and select the desired validation rule. 5. Click the Messages tab. 6. In the Validation message field, type a value. 7. In the Rule description field, type a value. 8. Sub-task: Translate validation message and description. 9. Click Translations. 10. Click Add to open the drop dialog. 11. In the list, find and select the desired language. 12. Click OK. 13. In the Rule description field, type the translation of the validation rule description. 14. In the Message field, type the translation of the validation message. Note: In the validation message translation, you can use these tags: Field name: When the validation message is shown, the [Field name] tag is replaced with the name of the validated field. Field value: When the validation message is shown, the [Field value] tag is replaced with the value that is entered in the validated field. To add a tag to the validation message translation, click Message tags, and click the desired tag. 15. Close the page. 16. Close the page. Does the validation rule  apply to specific data? Does the validation rule  apply to specific data? Set up validation rule conditions

Set up validation rule conditions

For each validation rule, you can set up conditions. The validation rule is only applied if the conditions are met.

To define a validation rule condition, you use a query. The validation rule is applied only to the records that are found by the query.
You can use these types of queries:
  • Inquiry: Use the standard D365 F&SCM Inquiry (SysQueryForm) page to define the records to which the validation rule applies. The inquiry only queries the table as defined for the validation rule.
  • Dynamic query: Use a dynamic query to define the records to which the validation rule applies. Before you can set up a condition with a dynamic query, set up the dynamic query to be applied. You can use a dynamic query, for example, to query other tables than the validation rule table, to define table relations, and to have only one record returned.

 End End Yes No Yes No Yes No

Activities

Name Responsible Description

Set up dynamic queries

Data quality administrator

Use a dynamic query to find one or more records in the database. The found records are the input for further processing.

You can use a dynamic query on:

  • An enrichment rule of type Data query.
  • An action rule of type Data query.
  • A condition that is applied to a validation rule, duplicate check rule, or action rule.
  • A configurable lookup of type Dynamic query. You can apply the configurable lookup on a validation rule of type Configurable lookup.

To define a query range, you can use a function.

Set up validation rule of type Query

Data quality administrator

Use validation rules to check if the data is in line with the defined standards.
To validate data, you can use several validation types. This topic explains how to set up a validation rule of type 'Query'. 

Use a query validation rule to validate if an entered field value is allowed using a dynamic query. Using a dynamic query, you can validate the field value based on one or more field values in another table. 

Examples of validations where you can apply a query validation rule:

  • On creating a sales order, check if address information or contact information is defined for the selected customer. If no address or contact information is defined, the customer cannot be used on a sales order.

  • On the worker setup, for the worker position, a cost center can be defined in the 'Cost center' financial dimension. Validate if the defined cost center is active. If not active, a warning or error message is given for the worker.

  • On terminating an employee, check if positions are reporting to the employee. If positions are still reporting to the employee, you cannot terminate the employee.

Before you can set up a query validation rule, set up the dynamic query to be used.

You can add validation rules in these ways:

  • Manually.
  • Select the fields to be validated from a page.

Set up validation rule message

Data quality administrator

 		You can define a message that is shown if the validation rule is not met.
If no validation rule message is defined, the default message is shown if the validation is not met.
In case of a data pattern validation, the applicable data pattern can also have a message defined. In this case, the message priority is:
  1. If a validation rule message is defined, this message is shown.
  2. If no validation rule message is defined, the data pattern message is shown.
  3. If no data pattern message is defined, the default message is shown.
You can also enter a description of the validation rule.
You can translate both the validation message and the validation rule description.

Set up validation rule conditions

Data quality administrator

For each validation rule, you can set up conditions. The validation rule is only applied if the conditions are met.

To define a validation rule condition, you use a query. The validation rule is applied only to the records that are found by the query.
You can use these types of queries:
  • Inquiry: Use the standard D365 F&SCM Inquiry (SysQueryForm) page to define the records to which the validation rule applies. The inquiry only queries the table as defined for the validation rule.
  • Dynamic query: Use a dynamic query to define the records to which the validation rule applies. Before you can set up a condition with a dynamic query, set up the dynamic query to be applied. You can use a dynamic query, for example, to query other tables than the validation rule table, to define table relations, and to have only one record returned.

Provide feedback