A staple of relational databases is the ability to reference on one table a related record on another table. A lookup method in D365 forms allows users to view multiple columns from a related record to help with their selection. In this article I will show you how to let the system automatically generate these lookups for you. As well as how to override them to make them work differently in different scenarios.
Why Use a Lookup Method?
Often times, a user needs to specify a value on one table that relates to unique identifier of a record on another table. Consider entering the Item ID on a sales line. The user should not be required to know all of the Item ID’s that exist in the system. There may be many thousands!
So instead, a lookup method in D365 forms allows the user to click on a drop-down and view all of the items ID’s that exist in the system. Users can even use the lookup form to filter on different columns such as ‘item name’ to find the item they are looking for. When a user selects the item they are looking for, the item ID value is then copied into the sales line Item ID field.
Without this lookup form, the user would always need to type in the item ID. Additionally, users would not be shown additional column information to help make sure they are selecting the correct one.
The relations that are setup between tables that support the lookup functionality, also ensure that when a user does type in a value, that the value exists in the related table. If it does not, the system can show an error message. This is crucial to maintaining the integrity of the data.
Similarly, when the user tries to delete a record in a form, the system can inform them if there are any other related records that reference that record.
Setting Up an Automatic Lookup
One of the great things about a Lookup method in D365 forms is that we do not need to write code for them to be shown. In most cases, we only need to setup the relations on the table, and set what fields we want to be shown in the Auto Lookup property on the table. The system will then generate the lookup form for us. Let’s walk through an example.
In my example, I have a vehicle table. The table contains a unique identifier ‘Vehicle ID’. But it also allows the user to select a Make and a Model for the vehicle.
When selecting the Make or the Model, I want the user to only be able to select a Make ID or Model ID that is already setup in the related form and table. I do not want the user to be able to type in whatever they want. This way, I can store additional information on the Make and Model tables, that I do not have to duplicate on this vehicle table.
In order for the lookup to show, all I need to do is two things.
- Setup a relation between my vehicle table, and the related make and model tables.
- Populate the AutoLookup nodes on the related Make and Model tables with the columns I want the lookup form to show.
Setup A Relation
Open your primary table in Visual Studio. In my example, my table is named ‘rsmVehicle’.
Select the ‘Relations’ node. Right click on the relations node and select New>Relation.
A new node is created. Right click the node and select ‘Properties’. The Properties window will open.
Set the ‘Related Table’ property to be the name of the related table. Set the ‘Name’ property to be the same value. In my case, my related table is ‘rsmModel’.
Next, right click on the node, in my case named ‘rsmModel’, and select New>Normal.
A new node is created inside of the parent node. Select that node. Then right click the node and select Properties. In the Properties window set the ‘Field’ and ‘Related Field’ properties to the names of the two fields that are related to each other. In my case the field ‘ModelID’ on my rsmVehicle table should be related to the ‘ModelID’ field on my rsmModel table.
Look at the ‘Custom Display Name’ row to ensure that you setup the relationship correctly.
Finally, in my example I repeated these steps for the rsmMake relation to specify a relationship between the MakeID field on the rsmVehicle table and the MakeID field on the rsmMake table. The end result looks like this.
Setup AutoLookup
The second thing that we need to do in order for the lookup form to be generated for us, is to specify which columns on the related record should show. Open the related table in Visual Studio. In my example, the table is named rsmMake. Expand the ‘Field groups’ node. Expand the ‘AutoLookup’ group underneath ‘Field groups’.
There are two ways you can add fields to this field group.
- You can drag fields from the ‘Fields’ node of the table to the ‘AutoLookup’ field group.
- Or you can right click on the ‘AutoLookup’ node and select ‘New Field’. Then type in the name of the field.
I find the first method quicker and easier.
I dragged all three fields from my rsmMake table into the AutoLookup field group.
Finally, we can see what this looks like when we click the drop down in the Vehicle form. There exists a drop-down arrow. And when we click it, a lookup form displays all the records in the rsmMake table. And the form shows the two columns, MakeID and Description, that were added to the AutoLookup group.
Conclusion
In this article you learned how to create a lookup method in D365 forms. First, I showed you how to let the system create lookup forms automatically by using relations on the tables. Then, you learned how you can control what columns show in the lookup by adding the fields to the AutoLookup field group on the table. Microsoft has made creating these lookup forms extremely easy. And in many of the scenarios, we do not even need to write any x++ code. In the next article I will explain how to override a lookup method and explain when you would need to do this.
i’ve a custom table called ManufacturedIn in that the 3 fields are country, countryId and Tax rate, here i’ve to add another field, whenever i give country for example if i type Cuba, in new field the starting 3 letters of Cuba should fill automaticaly in d365 fo, can you help me!
You will need to go into visual studio and add a table relation from your table with the country field back to the logisticspostaladdresscountry table. I forget the exact name of the table. But it should be related to the table with the country. Then the lookup and the auto fill will work.