One of the most common tasks when writing code in Microsoft Dynamics 365 for Finance and Operations, is to retrieve a record from the database. Once you have the record, you can read, update, or delete it. In this article I will show you how to write a D365 find method. A ‘Find’ method makes the process of locating a single record on a table easier. Given that almost every table is usually read from later on, and not just inserted, it is very important to know how to write this method.
Why Are They Helpful?
As a best practice, whenever a new table is created, developers should create a method named ‘Find‘. First, this method will have method parameters for each value in the unique index on the table. Secondly, the method will then have a ‘select statement‘. This code will use the values in the parameters passed in to find the one record that matches those values. Finally, the method will return a populated table buffer with those values to the calling method, for further use.
Writing a D365 Find method is very useful for several reasons. First, a developer that needs to update a single record on the table, can use the Find method to return a table buffer containing just the one record.
Secondly, the developer does not usually need to look at the table indexes to understand what fields make up a unique index. Instead, whatever parameters are passed into the Find method, should be everything that is needed to use a unique index on the table.
Thirdly, the developer does not need to spend the time to write the select statement. It is already done for them. And even though these are simple methods, it does save some time.
Fourthly, calling a Find method is easier for future developers to read the code and understand what it is doing. Even though ‘select statements’ are pretty easy to read, they are just more words then calling the ‘Find‘ method.
Lastly, if in the course of creating a new table, you decide to change how to select a unique record on that table, you may only need to change the Find method. And not necessarily every place where you selected a record from that table.
Example Find Method
For example, let us look at the Find method that exists on the CustTable. In Visual Studio, go to the Application Explorer. Then, in the top bar, type CustTable, and hit enter.
Located the node ‘CustTable’ under AOT>Data Model>Tables>CustTable. Right click on it, and select ‘Open designer‘.
Immediately, a new window will open in the middle showing the table.
First, we need to validate that there even exists a D365 Find method on this table. Many tables do not actually have a ‘Find‘ method.
Next, expand the methods node. And scroll down until you find the ‘find‘ method. Right click on the node and select ‘View code’.
In case you do not see the option for ‘View code’, right click on the very top table node labeled ‘CustTable’, and you should then see ‘view code’.
Finally, a window will open with the x++ code. Locate the Find method. It will look like this.
static CustTable find(CustAccount _custAccount,
boolean _forUpdate = false)
{
CustTable custTable;
if (_custAccount)
{
if (_forUpdate)
{
custTable.selectForUpdate(_forUpdate);
}
select firstonly custTable
index hint AccountIdx
where custTable.AccountNum == _custAccount;
}
return custTable;
}Finding The Unique Key
Now that you have seen an existing find method, it is time to create a new Find method on another table.
Next, if you haven’t already create or find the table you wish to add a find method to. See How to create A Table In D365. As shown in the linked article, I created a table named TutCar. For the sake of this article, I will demonstrate using this table. But you can also use your own.
First, search for ‘TutCar’ in the Application Explorer. Right click on the table, and select Open designer.
Secondly, in the window that opens, expand the Fields, Indexes, and Methods nodes. I can see that this table does not already have a Find method. Therefore, I should add one. Also notice that there exists a unique index on this table, that contains the field ‘CarId‘. I can tell this because the ‘Allow Duplicates‘ property on the table is set to no.
Given these points, when I create the D365 find method, I need to pass in a parameter that will filter on the CarId field. This will allow the system to find a single unique record.
If my find method had passed in the description field, for example, this information would not allow me to find a unique record. Therefore, I need to always pass in whatever parameters will allow me to find a single unique record.
Add A New Method
There are two ways of writing a method on a table. First, right click on the Methods node on the table, and select ‘New Method’. A code editor window will open with an empty method generated for you.
Alternatively, right click on the table node, and select ‘View code’. Then begin typing your method in the code editor window that opens.
Writing The Code
To write your own D365 find method, copying an existing method, and modifying it is usually the easiest thing to. Copy the CustTable find method written earlier in this article and paste it into the code editor window for your table.
The method you copied will find and return a CustTable table buffer. We need to change it to use our table. In my example, replace everywhere it says ‘CustTable’ with ‘TutCar’. Additionally, everywhere the table buffer variable is used replace ‘custTable’ with ‘tutCar’. The result should look like the following. I have highlighted in bold all of the replacements.
static TutCar find(CustAccount _custAccount,
boolean _forUpdate = false)
{
TutCar tutCar;
if (_custAccount)
{
if (_forUpdate)
{
tutCar.selectForUpdate(_forUpdate);
}
select firstonly tutCar
index hint AccountIdx
where tutCar.AccountNum == _custAccount;
}
return tutCar;
}While we are closer, we are not done. Next, we need to change the ‘where’ clause so that only one record is returned. Also, we need to pass in as a parameter the value that will be used in the where clause.
First, change the first parameter ‘CustAccount _custAccount‘ to ‘TutCarId _carId‘.
Secondly, change the where clause to now read ‘where tutCar.CarId == _carId;‘
Thirdly, change the ‘if (_custAccount)‘ line to read ‘if (_carId)‘.
Lastly, remove the line of code ‘index hint AccountIdx‘. That index does not exist on our table. And it is actually a best practice to NOT have index hints anymore when writing select statements. The SQL Server Database can choose the best index to use.
The completed D365 Find method should look like this.
static TutCar find(TutCarId _carId,
boolean _forUpdate = false)
{
TutCar tutCar;
if (_carId)
{
if (_forUpdate)
{
tutCar.selectForUpdate(_forUpdate);
}
select firstonly tutCar
where tutCar.CarId == _carId;
}
return tutCar;
}ForUpdate Parameter
You may have noticed, there is a second parameter named _forUpdate that is passed into the method. Notably, this parameter is extremely important. When the value of this parameter is true, the code will call the method named selectForUpdate(_forUpdate) on the table.
This will do the same thing as if we had written ‘select firstonly forupdate <tableBuffer>’. However, passing in the parameter allows us to conditionally control this.
When a table buffer is ‘selected for update’ it means the table will be locked
When a table buffer is ‘selected for update’ it means the SQL sever database will not allow any other process to update this record.
If other processes were allowed to also update the data while our process was working with it, we might inadvertently set values back to a value before another process intentionally set them. So each process needs to take their turn reading the values, then only updating what they intend to.
The _forUpdate parameter should be set to true, when the calling code plans to update or delete the record being found. If the calling program only plans to read values on this record, the _forUpdate parameter should be set to false.
Using The Find Method
So far, you have seen an example of a find method. Now, let’s look at an example of how to call a D365 find method.
In any method in the system, you can right click on the method definition and select ‘Find All References‘. Do this now. First, right click on the word ‘find’. Then, select ‘Find All References‘.
As can be see, the Find Symbol Results window will show locations in code where this method is called. Locate and double click on the record /Classes/RetailTransactionServiceCustomer/Methods/updateCustomerFiscalCode
After double clicking on the result, a window will open showing the code. See screenshot above.
First, the code declares a table buffer of type ‘CustTable‘, with the name ‘custTable‘.
Second, the find method is called. And the result of that method is assigned to the table buffer variable.
In this instance, the ‘find’ method is a static method. Therefore, to call it use :: (colon colon) instead of a period. Next, pass in any parameter values used to find the record. Finally, when updating or deleting a record, pass in ‘true’ as the value for the parameter _forUpdate.
After setting the table buffer variable, a developer can read, update, or delete the record.
In the above code example, first the field FiscalCode is set. Then, the update() method is called on the table buffer. And finally, the ttscommit; causes the system to write the change to the database. See How to Update Data In D365 for more details.
Conclusion
In many cases, whenever a table is created, someone needs the ability to read, update, or delete records from that table in code. In order to do this, a ‘select statement‘ needs to be written. D365 find methods contain this select statement, so that it only needs to be written once. Then, whenever a record from that table needs to be read, updated, or deleted, the find method can be used. This results in code that is quicker and easier to write. And code that is easier for other people to read.
