How to: Use Conditions in Field Validations
In this walkthrough we create controls on item data in sales documents using the Microsoft Dynamics 365 Business Central extension Compliance Field Validation.
This walkthrough is aimed at defining which fields in sales documents we want to validate and which fields from the item table we want to use for validation. The field Unit Price needs to contain a value equal or larger than the value on the item card. We require other fields to contain the same value.
The setup is active for any user logged in to any company. The extension works for any table in Business Central: default, extension and custom objects are supported.
Create the Field Validation for Sales order, Invoices and Credit memo's
Follow these steps to create a field validation to keep the item master data and item data on sales quotes, (blanket) orders and invoices aligned.
1. Choose the Search field in the top right corner and search for Field Validations List.
2. On the list of field securities, click New.
3. Fill the fields as necessary on the header of the Field Validation page:
- The No. is populated automatically.
- Document the purpose of the field security in the field Description.
- Choose the AssistEdit button in the Table ID field and select the table Sales line. Alternatively, you can enter id 37 without opening the list of tables.
- Open Conditions. We use conditions to limit the field validation to document types quote, order, invoice and blanket order.
- Enter the field you want to use for the condition in the field Field No. To do this open the AssistEdit list of fields and select the field Document Type. Alternatively you can enter the field id (1).
- In the field Filter you enter the document types you want to filter on: Quote|Order|Invoice|Blanket Order . Alternatively you type 0|1|2|4 .
- Choose Close to confirm the condition you set up.
- With the field Conditions limit applicability of the field validation to a subset
- Enter the today's date in the field Starting Date for immediate activation.
4. On the lines we enter the fields we want to keep identical to the item card. The lines are filled by choosing fields from the list opened by using the AssistEdit button (for the Field No., the first field on the line) or entering the field id manually.
- Description (id 11)
- Unit Price (22)
- Net Weight (35)
- Gen. Prod. Posting Group (75)
- Allow Invoice Disc. (32)
- Unit of Measure (13)
- VAT Prod. Posting Group (90)
- Gross Weigh (34)
5. Select the table relation created in the first step of this walkthrough on each line in the field Table RelationNo.. This tells the software where to find the related data which should be used for validation. This means the field Validation Value cannot be filled as this is determined dynamically.
6. In the field Notification you can put a text message which is displayed with the error on the field. Use this for explanation, e.g. refer to company policy or who manages the item master data.
7. Define the type of relation you want to validate. Unit price should be of type Greater or equal than, the other fields should have type Equal.
8. We already defined which fields we want to validate and the validation type. As a last step we need to define the fields we want to compare the fields in the document with, e.g. link the field Description in the sales line table to the field Description in the item table. The lines are filled by choosing fields from the list opened by using the AssistEdit button (for the Field No., the first field on the line) or entering the field id manually. The corresponding fields in the item table are(in the order of field numbers in the sales line table):
- Inventory Posting Group (id 11)
- Description (id 3)
- Unit Price (18)
- Net Weight (42)
- Gen. Prod. Posting Group (91)
- Allow Invoice Disc. (15)
- Base Unit of Measure (8)
- VAT Prod. Posting Group (90)
- Gross Weight (41)
9. If you now restart the session by signing out and in again, you can test the setup you created by trying to modify the fields on the lines of the field validation. If you go back to the page of the (in this case) sales invoice you an additional error message.