How to: Use Table Relations 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.

We create a table relation to tell the extension about the link between fields on the lines of sales documents (table sales line) and the master item data (table item). With a filter on the table relation we limit its applicability sales quotes, orders, invoices and blanket orders.

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.

Refer to https://docs.microsoft.com/en-us/dynamics365/business-central/ui-enter-criteria-filters for more information on filtering in Dynamics 365 Business Central. Our Compliance modules support any filtering capability offered by default Business Central.

Rental company RentCo wants to enhance the data quality of their sales quotes, orders, invoices and blanket orders. Credit memos and return orders are exempted from validation. Information copied from the item card - like description, posting groups, etc. - should not be changed on most sales documents to prevent complaints from customers. The unit price on the item card should be a minimum price, thus the unit price on the sales line may only have the same or a higher value than on the item card. When selecting a different item the values should change accordingly. Items which do not satisfy these conditions need to be blocked. Furthermore, any item should at least have a substitute defined so if a car is not available a customer can use an alternative. Lastly, it happens often that customers are waiting while a RentCo employee is tries to post an invoice unsuccessfully because of missing dimensions. So when releasing an item the dimensions need to be present.

Because Business Central does not offer these validations by default, RentCo implements the extension Compliance Field Validation. The extension gives detailed feedback to users, with error messages per field and the ability to enter your own notification text.

• Extension Field Validation is installed and setup is completed
• Setup of Field Validation is done
• To create field validations you need an account with SUPER-permissions or the permission set 2C-FIELDVAL-MANAGE created by the extension.
• Users need to have the permission set 2C-FIELDVAL-USE assigned for the extension to work. Otherwise users will get an error message when starting a client session.

This walkthrough shows how to create the field validation and required table relation.

Table relations tell the extension Field Validation how two tables in Business Central are related. Based upon default Business Central we supply table relations, which you can use and examine as example for creating one yourself.

Follow these steps to create a table relation between the tables item and sales line (the relation already exists, but we use this as an example).

1. Choose the Search field in the top right corner and search for Table Relations List.
2. On the list of table relations click New.

3. Fill the fields as necessary on the header of the table Relation:

• The field is populated as set up in the number series if Manual Nos. are disabled.
• Enter two table numbers by selecting the AssistEdit button and choose the table from the list, or enter the id manually. The top field contains the number for which you want to set up the field validation. The second field holds the number of the table to use as the source for validation.

4. The lines are populated with the field(s) linking the tables. For tables item and sales line this is Field No. 6 (Field Name No.) in sales line, linked to Related Field No. 1 (Related Field Name No.) in table item.

5. Only lines of the type item should be validated with data from the item table. This can be accomplished wit a condition in the table relation:

• Click on the Condition at the end of the line, open the AssistEdit list of fields in the sales line table and select the field Type. Alternatively you can enter the field id (5).
• In the field Filter enter the value Item.
• Click Close to confirm the set up of the condition.

We continue this walkthrough with configuring the field validation.

Follow these steps to create a field validation to validate on the minimum number of records for an item in the item substitute table.

1. Choose the Search field in the top right corner and search for Field Validations List and choose the related link.
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 field Table ID field and select the table Item. Alternatively, you can enter id 27 without opening the list of tables.
• By default the Validation Type is Error. We change this to Warning: users need to receive a reminder to fill out information we require, items are blocked when a warning is displayed so the impact of incorrect item data is limited.
• Items which do not satisfy the validations on the lines need to be blocked for use. We achieve this with an action after validation.
  • Open the AssistEdit menu of the field Action After Validation.
  • Choose the field Blocked from the list of fields in the item table when you open it by clicking on the AssistEdit button. Alternatively, you can enter field id 54.
  • The field Modify to Value contains the value which is entered in the field selected in the previous step. To block the item enter here Yes.

• We only want the field validation to be active on items of the Gen. Product Posting Group assigned to cars, other items should be exempted.
  • Open the list of Conditions. With conditions you can limit field validations to a subset of the records in the table.
  • Select the field Gen. Product Posting Group from the list of fields if you click the AssistEdit button in the field Field No.. Alternatively you can enter the field id 91.
  • Enter the filter in the field Filter, in this case CAR. Note you can test the filter on the item list before entering as a filter for the condition. the field validation will be active on any presented line.
  • Click Close to go back to the field validation page.

4. Enable the switch for Validate Default Dimensions. Any time this field validation is processed, the extension also whether the by default Business Central required dimensions exist.
5. We fill the Starting Date at the end of this step of the walkthrough; before activation we first want to finish this step of the walkthrough.
6. So far the header, next we fill the fields we want to validate on the lines.
7. First, we create the validation for the number of sales prices for the item.

• On the first line, select Related no. of Records for the Validation Type at the start of the line.
• Fill the field Table Relation with the table relation you would like to use.

8. Filter: use default Business Central filtering functionality to in- or exclude lines from the table, e.g. to filter on posting groups <>IMPORT&<>EU or to filter on BOM nos: 1*|456*.
9. Enter the filter value in the field Validation Value. Note you can test the filter before entering on the relevant list.

• Refer to https://docs.microsoft.com/en-us/dynamics365/business-central/ui-enter-criteria-filters for more information on filtering in Business Central.
• Select the validation type when configuring the table relation.

10. Now you can fill the field Field No. with a field you want to validate. Open the list by clicking the AssistEdit button (three dots visible when the cursor is in the field) to search or enter the field no. manually if you know it. You can enter as much fields as you like. For this example we enter the unit price and the two description fields.
11. You can enter a custom text in the field Notification. This is displayed in the list of results upon validation of the values entered. See the screenshot below.
12. Fill the Starting Date on the header with today's date or a date in the past to activate the setup.